Get Data from MySQL with PHP-Ajax

The following example will demonstrate how a web page pass parameter and can fetch information from a database with AJAX in a PHP file. We need to complete 4 steps to run the example:

Step-1:
Create a database (suppose-tutpub_db) and run the following sql in that database. We also need to know the user name and password of the database. In this example we define the user name as-tutpub_user and password as-123456 in the “get_info_result.php” file.

CREATE TABLE IF NOT EXISTS user_info (
id int(5) unsigned NOT NULL AUTO_INCREMENT,
name varchar(100) NOT NULL,
country varchar(50) NOT NULL,
age varchar(20) NOT NULL,
email varchar(100) NOT NULL,
gender varchar(20) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `user_info` (`id`, `name`, `country`, `age`, `email`, `gender`) VALUES
(1, ‘James Waugh’, ‘USA’, ’28’, ‘james@tutpub.com’, ‘male’),
(2, ‘Sophie Bishop’, ‘Canada’, ’25’, ‘sophie@tutpub.com’, ‘female’),
(3, ‘Amin’, ‘Bangladesh’, ’28’, ‘shahalom@tutpub.com’, ‘male’),
(4, ‘Prodip’, ‘India’, ’26’, ‘prodip@tutpub.com’, ‘male’),
(5, ‘Ausin’, ‘India’, ’23’, ‘ausin@tutpub.com’, ‘female’),
(6, ‘Karim’, ‘Pakistan’, ’36’, ‘karim@tutpub.com’, ‘male’);

Step-2:
Our First page will “index.php”. Write the following code in your index.php.

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html xmlns=”http://www.w3.org/1999/xhtml”>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″ />
<title>PHP, Ajax and Databse Combination Example</title>

<script type=”text/javascript”>
function showInfo(str_param) {
if (str_param==””) {//CHECK IF NOTHING SELECTED
document.getElementById(“msg_show”).innerHTML=”Please select a country to show information”;
return;
}

//CREATING HTTP REQUEST AFTER CHECKING BROWSER
if (window.XMLHttpRequest) {// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
} else {// code for IE6, IE5
xmlhttp=new ActiveXObject(“Microsoft.XMLHTTP”);
}

xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState==4 && xmlhttp.status==200) {//CHECK THE RESULT STATUS                 document.getElementById(“msg_show”).innerHTML=xmlhttp.responseText;    //SHOW THE RESULT TO msg_show DIV
}
}
xmlhttp.open(“GET”,”get_info_result.php?country=”+str_param,true);
xmlhttp.send();
}
</script>
</head>
<body>
<form>
<select name=”country” onchange=”showInfo(this.value)”>
<option value=””>Select a country:</option>
<option value=”usa”>USA</option>
<option value=”india”>India</option>
<option value=”bangladesh”>Bangladesh</option>
<option value=”pakistan”>Pakistan</option>
<option value=”canada”>Canada</option>
</select>
</form><br />
<div id=”msg_show”><b>Information will be shown here</b></div>
</body>
</html>

Output may like the following snapshot:
AJAX Tutorial, AJAX-Database

Step-3:
Now we need to create a file that will process or AJAX request and reply the result. Suppose we create “get_info_result.php”. Below is the code for “get_info_result.php”.

<?php

$country=$_GET[“country”];//GET THE COUNTRY NAME
$server = “localhost”;
$database_name = “tutpub_db”;
$user_name = “tutpub_user”;
$pass = “123456”;

$con = mysql_connect($server, $user_name, $pass);
if (!$con) {//KILL THE CODE IF CONNECTION FAILED
die(‘Could not connect: ‘ . mysql_error());
}

mysql_select_db($database_name, $con);//SELECT DATABASE
$sql=”SELECT * FROM user_info WHERE country

LIKE’%”.$country.”%'”;//CREATING QUERY
$results = mysql_query($sql);//GET DATA FROM DATABASE BY EXECUTE THE QUERY

echo “<table border=’1’>
<tr>
<th width=”200″>Name</th>
<th width=”100″>Country</th>
<th width=”50″>Age</th>
<th width=”300″>Email</th>
<th width=”100″>Gender</th>
</tr>”;

while($row = mysql_fetch_array($results)) {
echo “<tr>”;
echo “<td>” . $row[‘name’] . “</td>”;
echo “<td>” . $row[‘country’] . “</td>”;
echo “<td>” . $row[‘age’] . “</td>”;
echo “<td>” . $row[’email’] . “</td>”;
echo “<td>” . $row[‘gender’] . “</td>”;
echo “</tr>”;
}
echo “</table>”;

mysql_close($con);
?>

Step-4:
Deploy your files directory to your wamp/xamp server.

You can also download the entire code by clicking here
Enjoy!!!

Author Info

Shah Alom

Hi, This is Mohammad Shah Alom, My passion is Programming & Web Development. I am Founder of Micro Solutions Bangladesh. My Facebook profile shahalom1983 & Twitter Profile shahalom_83

  • wow nice share….thanks..its very much helpful for me and hope as well as others too.

  • Thank You for Your comment and sorry for late response. If You like the post please don’t forget to share with you friends via facebook, twitter, stumbleupon, or may digg social sites.

  • Diablo 3

    Fantastic submit. I’m dealing with several these issues.