Working with Ajax, PHP and MySQL

Last update on August 19 2022 21:50:46 (UTC/GMT +8 hours)

Preface

In this tutorial, we will see how to make Ajax work with PHP and MySQL. We will create a small web application. In that, as soon as you start typing an alphabet in the given input field, a request goes to the PHP file via Ajax, a query is made to the MySQL table, it returns some results and then those results are fetched by Ajax and displayed.

Let's dive in and explore how this works.

MySQL Table

Following is the structure of the MySQL table we have used.

User interface for supplying data

We have a simple user interface created with HTML and CSS where the user can supply data in the input field.

HTML Code

This is the basic HTML code. Though, we will make modifications on it and add JavaScript code later.

<!DOCTYPE html> 
<html lang="en"> 
<head> 
<meta charset="utf-8"> 
<title>User interface for Ajax, PHP, MySQL demo</title> 
<meta name="description" content="HTML code for user interface for Ajax, PHP and MySQL demo."> 
<link href="../includes/bootstrap.css" rel="stylesheet">
<style type="text/css">
body {padding-top: 40px; padding-left: 25%}
li {list-style: none; margin:5px 0 5px 0; color:#FF0000}
</style>
</head>
<body>
<form class="well-home span6 form-horizontal" name="ajax-demo" id="ajax-demo">
<div class="control-group">
              <label class="control-label" for="book">Book</label>
              <div class="controls">
                <input type="text" id="book">
			  </div>
 </div>
 <div class="control-group">
              <div class="controls">
                <button type="submit" class="btn btn-success">Submit</button>
              </div>
 </div>
</form>
</body>
</html>

Following is the screenshot of how it looks

Send data to server using Ajax

We will now create JavaScript code to send data to the server. And we will call that code on onkeyup event of the of the input field given.

function book_suggestion()
{
var book = document.getElementById("book").value;
var xhr;
 if (window.XMLHttpRequest) { // Mozilla, Safari, ...
    xhr = new XMLHttpRequest();
} else if (window.ActiveXObject) { // IE 8 and older
    xhr = new ActiveXObject("Microsoft.XMLHTTP");
}
var data = "book_name=" + book;
	 xhr.open("POST", "book-suggestion.php", true); 
     xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");                  
     xhr.send(data);
}

Explanation of the above code

var book = document.getElementById("book").value;

Value of the input field, whoose id is 'book', is collected.

var xhr;
 if (window.XMLHttpRequest) { // Mozilla, Safari, ...
    xhr = new XMLHttpRequest();
} else if (window.ActiveXObject) { // IE 8 and older
    xhr = new ActiveXObject("Microsoft.XMLHTTP");
}

A variable xhr is declared. Then, a new XMLHttpRequest object is created. If a your target audience use browsers older than Internet Explorer 8, ActiveXObject is used to create XMLHttpRequest.

var data = "book_name=" + book;

Data will be sent using 'data' valiable by using string book_name and book variable will carry the user supplied data.

xhr.open("POST", "book-suggestion.php", true);

By using 'open' method of XMLHttpRequest object, a new request to the server is initialized. There are three parameters passed by this method. 'POST' determines the type of the httprequest. 'book-suggestion.php' sets the server side file and setting the third parameter 'true' states that the request should be handled asynchronously.

In case asynchronous requests, you receive a callback upon data received. And in turn, while your request is being processed, the browser may continue to work normally.

xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");

'setRequestHeader' method is used to set the value of an HTTP request header. Two parameters are passed along with this method. 'Content-Type' states the header of the string(DOMString saying precisely) and 'application/x-www-form-urlencoded' sets the body of the header.

xhr.send(data);

'send' method is used to send data contained in the 'data' variable to the server.

Processing data on server side

<?php
include('../includes/dbopen.php');
$book_name = $_POST['book_name'];
$sql = "select book_name from book_mast where book_name LIKE '$book_name%'";
$result = mysql_query($sql);
while($row=mysql_fetch_array($result))
{
echo "".$row['book_name']."";
}
?>

Explanation of the code

include('../includes/dbopen.php');

This code incldes dbopen.php file, which opens a connection to the MySQL database.

$book_name = $_POST['book_name'];

This way we collect the data (user supplied) send via Ajax to PHP.

$sql = "select book_name from book_mast where book_name LIKE '$book_name%'";
$result = mysql_query($sql);
while($row=mysql_fetch_array($result))
{
echo "<p>".$row['book_name']."</p>";
}

We run a MySQL query to select all the books start with the letter supplied by user and prepare the data returned to be displayed.

Retrieve and render data using Ajax

Now we have to retrieve data returned by MYSQL and display that data using Ajax. So, we will write that code in our HTML file. We will add following code

xhr.onreadystatechange = display_data;
	function display_data() {
	 if (xhr.readyState == 4) {
      if (xhr.status == 200) {
       document.getElementById("suggestion").innerHTML = xhr.responseText;
      } else {
        alert('There was a problem with the request.');
      }
     }
  }

We have to add <div id="suggestion"></div> bellow the input field whose id is book.

Explanation of the Ajax code

xhr.onreadystatechange = display_data;

'onreadystatechange' is a property of XMLHttpRequest object which is called whenever 'readyState' attribute is changed. We assign this to the function to be declared next.

if (xhr.readyState == 4)

We check whether the value of the 'readyState' property is 4, which denotes that the operation is complete.

if (xhr.status == 200)

If the operation is completed, the status of the response to the request is checked. It returns the HTTP result code. Result code 200 states that the response to the request is successful.

document.getElementById("suggestion").innerHTML = xhr.responseText;

Now we set the value of the string to be displayed within the div whose id is 'suggestion' as 'responseText' property of the XMLHttpRequest object. 'responseText' is the response to the request as text.

So, the final JavaScript code of the HTML file becomes as follows:

function book_suggestion()
{
var book = document.getElementById("book").value;
var xhr;
 if (window.XMLHttpRequest) { // Mozilla, Safari, ...
    xhr = new XMLHttpRequest();
} else if (window.ActiveXObject) { // IE 8 and older
    xhr = new ActiveXObject("Microsoft.XMLHTTP");
}
var data = "book_name=" + book;
     xhr.open("POST", "book-suggestion.php", true); 
     xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");                  
     xhr.send(data);
	 xhr.onreadystatechange = display_data;
	function display_data() {
	 if (xhr.readyState == 4) {
      if (xhr.status == 200) {
       //alert(xhr.responseText);	   
	  document.getElementById("suggestion").innerHTML = xhr.responseText;
      } else {
        alert('There was a problem with the request.');
      }
     }
	}
}

We encourage you to play around with the code, speak your mind in our comment section and share this tutorial.

Previous: Introduction to Ajax
Next: Using Ajax with XML