PHP and AJAX MySQL Database Example

In the AJAX example below we will demonstrate how a web page can fetch information from a MySQL database using AJAX technology This exa...

In the AJAX example below we will demonstrate how a web page can fetch information from a MySQL database using AJAX technology

This example consists of four elements:
  • a MySQL database
  • a simple HTML form
  • a JavaScript
  • a PHP page

The Database

The database we will be using in this example looks like this:
id FirstName LastName Age Hometown Job
1 Peter Griffin 41 Quahog Brewery
2 Lois Griffin 40 Newport Piano Teacher
3 Joseph Swanson 39 Quahog Police Officer
4 Glenn Quagmire 41 Quahog Pilot

The HTML Form

The example above contains a simple HTML form and a link to a JavaScript:

<html>
<head>
<script src="selectuser.js"></script>
</head>
<body>
<form> 
Select a User:
<select name="users" onchange="showUser(this.value)">
<option value="1">Peter Griffin</option>
<option value="2">Lois Griffin</option>
<option value="3">Glenn Quagmire</option>
<option value="4">Joseph Swanson</option>
</select>
</form>
<p>
<div id="txtHint"><b>User info will be listed here.</b></div>
</p>
</body>
</html>

Example Explained - The HTML Form

As you can see it is just a simple HTML form with a drop down box called "users" with names and the "id" from the database as option values.
The paragraph below the form contains a div called "txtHint". The div is used as a placeholder for info retrieved from the web server.
When the user selects data, a function called "showUser()" is executed. The execution of the function is triggered by the "onchange" event.
In other words: Each time the user changes the value in the drop down box, the function showUser() is called.

The JavaScript

This is the JavaScript code stored in the file "selectuser.js":

var xmlHttp;
function showUser(str)
{ 
xmlHttp=GetXmlHttpObject();
if (xmlHttp==null)
 {
 alert ("Browser does not support HTTP Request");
 return;
 }
var url="getuser.php";
url=url+"?q="+str;
url=url+"&sid="+Math.random();
xmlHttp.onreadystatechange=stateChanged;
xmlHttp.open("GET",url,true);
xmlHttp.send(null);
}
function stateChanged() 
{ 
if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete")
 { 
 document.getElementById("txtHint").innerHTML=xmlHttp.responseText;
 } 
}
function GetXmlHttpObject()
{
var xmlHttp=null;
try
 {
 // Firefox, Opera 8.0+, Safari
 xmlHttp=new XMLHttpRequest();
 }
catch (e)
 {
 //Internet Explorer
 try
  {
  xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
  }
 catch (e)
  {
  xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
 }
return xmlHttp;
}

Example Explained

The showUser() Function
If an item in the drop down box is selected the function executes the following:
  1. Calls on the GetXmlHttpObject function to create an XMLHTTP object
  2. Defines the url (filename) to send to the server
  3. Adds a parameter (q) to the url with the content of the dropdown box
  4. Adds a random number to prevent the server from using a cached file
  5. Call stateChanged when a change is triggered
  6. Opens the XMLHTTP object with the given url.
  7. Sends an HTTP request to the server

The PHP Page

The server page called by the JavaScript, is a simple PHP file called "getuser.php".
The page is written in PHP and uses a MySQL databse.
The code runs a SQL query against a database and returns the result as an HTML table:

<?php
$q=$_GET["q"];

$con = mysql_connect('localhost', 'peter', 'abc123');
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }

mysql_select_db("ajax_demo", $con);

$sql="SELECT * FROM user WHERE id = '".$q."'";

$result = mysql_query($sql);

echo "<table border='1'>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
<th>Hometown</th>
<th>Job</th>
</tr>";

while($row = mysql_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>";

mysql_close($con);
?>

Example Explained

When the query is sent from the JavaScript to the PHP page the following happens:
  1. PHP opens a connection to a MySQL server
  2. The "user" with the specified name is found
  3. A table is created and the data is inserted and sent to the "txtHint" placeholder
Name

ADO,131,ASP,3,C++,61,CORE JAVA,1,CSS,115,HTML,297,index,5,JAVASCRIPT,210,OS,47,PHP,65,SAD,53,SERVLETS,23,SOFTWARE ENGINEERING,245,SQL,71,TCP/IP,1,XHTML,9,XML,18,
ltr
item
Best Online Tutorials | Source codes | Programming Languages: PHP and AJAX MySQL Database Example
PHP and AJAX MySQL Database Example
Best Online Tutorials | Source codes | Programming Languages
https://www.1000sourcecodes.com/2012/10/php-and-ajax-mysql-database-example.html
https://www.1000sourcecodes.com/
https://www.1000sourcecodes.com/
https://www.1000sourcecodes.com/2012/10/php-and-ajax-mysql-database-example.html
true
357226456970214079
UTF-8
Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content