Fetching MySql data in PHP using AJAX

Fetching MySql data in PHP using AJAX

Howdy people, yet another interesting tutorial about Fetching MySql data in PHP using AJAX, will enable you to understand the working princple behind the technology. Simple enough but for beginner’s it might be quite take some time to adpat, don’t worry, I will try to make this tutorial as simple as possible. But, before further proceeding, you have to promise yourself, enjoy as you learn like playing high end graphics game, fun though.

Things you should know:

Fetching Mysql data in Php using AJAX, requires three main components to learn. You must have a good knowledge in PHP, basic knowledge in MySql and some knowledge in Javascript as AJAX is itself a Javascript and full form is Asynchronous Javascript and XML. As the name suggests AJAX works dependently with XML, but knowledge in XML is not required in this tutorial, if you know its added advantage for future.

A quick recap: You must know:

  1. PHP (Good).
  2. MySql (Basic).
  3. Javascript (Some).

Let me assume, you got the idea, so let’s proceed.

How AJAX works?

Okay, let’s see how Ajax works. Suppose, you’re developing a chat application, where you sent your message, it appears in the chat message box without reloading the browser. Yeah, perfect example indeed, AJAX does not require you to refresh the webpage to fetch the message from the database. Let’s see how AJAX works step by step:

  1. When user request information from the server, the XMLHttpRequest or ActiveXObject handles the request and respond back to the user browser.
  2. The request is of HTTP protocol.
  3. The server interacts with server side scripting language like PHP, JSP, ASP, etc.
  4. The server returns the XML data or JSON to the XMLHttpRequest object.
  5. The overall process does not require to reload the complete webpage, instead it works internally the respond back to the client.

Design the database:




Before jumping into the code, the most important thing for you to design the database. The database is going to be MySql. Follow the steps to perform the design:

  1. Turn on Apache from XAMP, MAMP or WAMP.
  2. Turn on MySql from XAMP, MAMP or WAMP.
  3. Open the phpmyadmin for MySql via localhost/phpmyadmin. If it does not work, try to open from the XAMP, MAMP or WAMP starter page.
  4. Create database. In this example, I named it db1.
  5. Create table for the databse. In this example, I named it Emp.
  6. Create 6 columns.
  7. Place the column fields as ID of type int(11), make it Auto Increment and define the key as Primary.
  8. Place the other columns as FirstName, LastName of type varchar(20) each.
  9. Place the next column  as Age of type int(11).
  10. Finally, the other two columns as HomeTown and Job as varchar(20) each.
  11. Next, touch is insert values/data/records into the table.

MySQL queries:

A. CREATE database db1; //creates the database named db1.

//Create table named Emp

B. CREATE TABLE Emp (

id int(11) AUTO INCREMENT,

FirstName varchar(20), LastName varchar(20), Age int(11), HomeTown varchar(20), Job varchar(20),

PRIMARY KEY (id)

);

//Insert values to Emp table

C. INSERT INTO Emp VALUES (NULL, ‘Raktim’, ‘Nath’, ’25’, ‘Assam’, ‘Software Engineer’), (NULL, ‘Ritwik’, ‘Nath’, ’22’, ‘Assam’, ‘Accountant’), (NULL, ‘Mark’, ‘Morton’, ’35’, ‘Virginia’, ‘Guitarist’);

Overview of this example:

We have two php files here:

  1. Index.php -That outputs the AJAX data.
  2. GetUser.php -That takes care of the AJAX request and response back to the Index.php.

Please, pay attention to the codes and corresponding comment, where you will understand about the activity.

index.php:

<!DOCTYPE html>
<!–
Created by Raktim Nath.
Credit: W3Schools.
–>
<html>
<head>
<meta charset=”UTF-8″>
<title>AJAXMysql Example</title>
<!–AJAX calling script from mysql database–>
<script>
function showUser(str){
if (str==””){
document.getElementById(“txtHint”).innerHTML=””; //if none selected return no value
return;
}
else{
if(window.XMLHttpRequest){
xmlhttp=new XMLHttpRequest(); //create XMLHttpRequest object to open connection/calling
//for Safari, Mozilla
}
else{
xmlhttp=new ActiveXObject(“Microsoft.XMLHTTP”);
//for Internet Explorer. It uses ActiveXObject instead of XMLHttpRequest
}
// prepare your created request object to serve the request
xmlhttp.onreadystatechange=function(){
if(this.readyState==4 && this.status==200){
document.getElementById(“txtHint”).innerHTML=this.responseText;
//print the mysql database response text here
}
};
xmlhttp.open(“GET”,”getuser.php?q=”+str,true); //open the link to query with mysql database via another php
xmlhttp.send(); //send the request.
}
}

</script>
<!–AJAX calling script from mysql database ends–>
</head>
<body>
<!–Form design begins–>
<form>
<select name=”users” onchange=”showUser(this.value)”>
<option value=” “>Select users: </option>
<option value=”1″>Raktim Nath</option>
<option value=”2″>Ritwik Nath</option>
<option value=”3″>Mark Morton</option>
</select><br>
<!– Form Design ends–>

<div id=”txtHint”></div> <!–The output/result will be printed here–>
</form>
</body>
</html>

I assume, you are clear about the index.php, let’s see the getuser.php code.

getuser.php

<html>
<head>
<title>User</title>
<style>
table{
width: 100%;
border-collapse: collapse;
}
table,td,th{
border: 1px solid black;
padding: 5px;
}
th{
text-align: left;
}

</style>
</head>
<body>
<?php
$q= intval($_GET[‘q’]); //get the query via GET as a integer as we mentioned dropdown options value as numbers
$con= mysqli_connect(‘localhost’, ‘root’, ‘root’, ‘db1’); //connect to database
if(!$con){
die(‘Could not connect to datanase’. mysqli_error($con)); //prompt error if couldnot connect to database
}

$sql=”SELECT * FROM Emp WHERE id='”.$q.”‘”; //select query
$result= mysqli_query($con,$sql); //run the select sql
echo “<table>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
<th>Hometown</th>
<th>Job</th>
</tr>”;
//Output the database records in tabular form.
while($row= mysqli_fetch_array($result)){
echo “<tr>”;
echo “<td>”.$row[‘FirstName’].”</td>”;
echo “<td>”.$row[‘LastName’].”</td>”;
echo “<td>”.$row[‘Age’].”</td>”;
echo “<td>”.$row[‘HomeTown’].”</td>”;
echo “<td>”.$row[‘Job’].”</td>”;
echo “</tr>”;
}
echo “</table>”;
mysqli_close($con); //closes the database connection
?>
</body>
</html>

Okay, that is it. Feel free to copy paste the code, but I highly recommend you to understand try it by yourself without copy pasting.

Conclusion:

I assume, I have explain in a much easier way possible. But, being a beginner you might not get at first shot. Try to review it twice or thrice, I am sure you’ll code like a pro. If for any doubts or queries, please join the comment thread and if I missed something, make sure to correct me.

Leave a Reply

Your email address will not be published. Required fields are marked *