It’s Festive Season! Enjoy 30% Off

Use code FESTIVE30 at checkout

Code has been added to clipboard!

Learn to Store and Access Your Data Using PHP AJAX Example

Reading time 4 min
Published Aug 8, 2017
Updated Oct 15, 2019

AJAX is powerful and rather widespread, so it's not surprising some beginners might get the wrong idea that it is an independent programming language. Actually, AJAX stands for Asynchronous JavaScript And XML.

Basically, it requests data from a web server using an XMLHttpRequest object that is built into the browser. Then, it uses JavaScript and HTML DOM to use or display said data. It means that you don't need to reload the webpage to see updates.

MySQL is considered to be the most popular database management system in the world. The reasons it is so widespread are simple: it is free, open-source and runs on almost any system, including but not limited to Windows, Linux, and Mac OS.

Using MySQL, AJAX, and PHP together will let you effectively communicate with a database and display interactive data. Let's see how it's done, using a simple PHP AJAX example.

PHP - AJAX and MySQL: Main Tips

  • MySQL is extremely popular in the world for storing data. It's reliable, free, and runs on any system.
  • AJAX acts as a communicator that lets the browser and server communicate behind the scenes.

Example Of a MySQL Database

In this PHP AJAX tutorial, we will explain how to access and sort data using a AJAX MySQL connection. But before we try, we have to first have a database to access. For the sake of simplicity and learning, we will keep it short for now. Here we have a table that looks like this:

id FirstName LastName Age Birthplace Occupation
1 Mark Dooley 32 London Programmer
2 Lewis Kirkbride 25 New York Artist
3 Jack Lee 19 California Firefighter
4 Mary Jefferson 46 Quebec Army officer

Making of Web Application: HTML Example

When trying to build a PHP AJAX example web application, we first have to make a dropdown list of names. It will then access and use information directly from the chosen database. When someone selects one particular person that is on the list, their personal information will be displayed in a table.

To accomplish this, upon selecting a person from the dropdown, the show_user() function is called, which is triggered by the onchange event.

Below is the required HTML:

Example
<html>
<head>
  <script>
    function showUser(str) {
      if (str == '') {
        document.getElementById("txt_hint").innerHTML = "";
        return;
      } else { 
        if (window.XMLHttpRequest) {
          // script for browser version above IE 7 and the other popular browsers (newer browsers)
          xmlhttp = new XMLHttpRequest();
        } else {
          // script for the IE 5 and 6 browsers (older versions)
          xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
        }
        xmlhttp.onreadystatechange = function() {
          if (this.readyState == 4 && this.status == 200) {
            // get the element in which we will use as a placeholder and space for table
            document.getElementById("txt_hint").innerHTML = this.responseText;
          }
        };
        xmlhttp.open("GET", "getuser.php?q="+str, true);
        xmlhttp.send();
      }
    }
  </script>
</head>
<body>
  <form>
    <select name="users" onchange="showUser(this.value)">
    <option value="">Select a person:</option>
    <option value="1">Mark Dooley</option>
    <option value="2">Lewis Kirkbride</option>
    <option value="3">Jack Lee</option>
    <option value="4">Mary Jefferson</option>
    </select>
  </form>
  <br>
  <div id="txt_hint"><b>This is where info about the person is displayed.</b></div>
</body>
</html>

Example Explained

Firstly, we should check in the PHP AJAX example if anyone is selected at all. If it is true what we suspected and a selection has not been made (str == ""), we clear the txt_hint placeholder and exit the function.

However, if a particular person is selected, this is what happens in our PHP AJAX example:

  • First, we create an XMLHttpRequest object to make a server request.
  • Then we create a function for when the server response has been prepared.
  • Finally, we make a request to the server that holds our database.

Note: Notice how a parameter q with the dropdown selection is added to the URL.

Fill HTML Table With Data: PHP Example

The page that the Javascript above calls is get_user.php.

The source code found inside the get_user.php file will run a query against our MySQL database, then return the result. We will see it formatted as an HTML table:

Example
<!DOCTYPE html>
<html>
<body>
  <?php
    $q = intval($_GET['q']);

    $conn = mysqli_connect('localhost', 'johny', 'abcde12345', 'db');
    if (!$conn) {
      die('Could not connect: ' . mysqli_error($con));
    }

    mysqli_select_db($conn, "ajax_");
    $sql = "SELECT * FROM users WHERE id = '".$q."'";
    $result = mysqli_query($conn,$sql);

    echo "<table>
      <tr>
        <th>First name</th>
        <th>Last name</th>
        <th>Age</th>
        <th>Birthplace</th>
        <th>Occupation</th>
      </tr>";
    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['Birthplace'] . "</td>";
      echo "<td>" . $row['Occupation'] . "</td>";
      echo "</tr>";
    }
    echo "</table>";
    mysqli_close($conn);
  ?>
</body>
</html>

Comments on the Example

The following happens after calling the PHP script in get_user.php

  • An AJAX MySQL connection is established.
  • A corresponding user is found.
  • The script creates and fills the HTML table with data, which is then sent back to the txt_hint placeholder.

Carefully study the AJAX PHP examples and HTML codes portrayed and try to make a similar one yourself. Not only you will find it rather easy, you will find a whole new world of functionality that a combination of MySQL, AJAX and PHP provides.

PHP - AJAX and MySQL: Summary

  • AJAX is powerful tool used to create dynamic applincations. It establishes connection between a browser and a server.
  • MySQL is the most popular database management system in the world due to its reliability, security and force.