AJAX Database Operations

Last Updated : 22 Dec, 2022

In this article, we will discuss how to access the information available in the database using the AJAX. We will use the MySQL database, access the information stored in the database, and display the information on the webpage using the AJAX.

To perform this task we will create some sample entries in our MySQL database. Start the XAMPP control panel and start the MySQL service and open the MySQL Admin page.

MySQL Database: Click on new, create a new database, and name the database according to you.

Create a new table within the database.

Insert the following entries into the table.

Create the client-side PHP file which contains the AJAX request object and displays the result.

Client Side:

main.php: We have created a Student Database that contains the details of the student. We want to display this data based on the condition that the student’s CGPA in DB should be equal to the user-entered CGPA input.

In this PHP file, we have used basic HTML tags and created a form that accepts the user inputs and one submit button.
When the user click submit button the JavaScript function ajax_fun() will be called. This function contains the required AJAX functions.
The first line const ajax_Request = new XMLHttpRequest(); will create an AJAX HTTP request object.
If AJAX requests status will be OK or 200. we are just changing the HTML content of the HTML div element with id “result-box”

document.getElementById("result-box").innerHTML = ajax_Request.responseText;

We are sending the AJAX request to the server PHP page “index.php” along with the CGPA value that the user has entered.

var str = document.getElementById('cgpa_val').value;
ajax_Request.open("GET", "index.php?q="+str, true);
ajax_Request.send();

PHP

<!DOCTYPE html> 
<html> 
  
<head> 
    <style> 
        input { 
            padding: 0.5rem 2rem; 
        } 
    </style> 
</head> 
  
<body> 
    <h1 style="color:green;">GeeksforGeeks</h1> 
    <h3>Ajax Database operations</h3> 
    <hr><br> 
    <form> 
          <label> Enter the CGPA : </label> 
          <input type="text" name="stu_cgpa" id="cgpa_val"> 
          <input type='button' onclick='ajax_fun()' value='Submit' /> 
    </form> 
    <br> 
    <hr> 
    <div id="result-box">Result will display here</div> 
  
    <script language="javascript" type="text/javascript"> 
              
        function ajax_fun() { 
  
            const ajax_Request = new XMLHttpRequest(); 
  
             ajax_Request.onreadystatechange = function() { 
  
                if (ajax_Request.readyState == 4 && this.status == 200) { 
                    document.getElementById("result-box").innerHTML =  
                    ajax_Request.responseText; 
                } 
             } 
             var str = document.getElementById('cgpa_val').value; 
  
             ajax_Request.open("GET", "index.php?q=" + str, true); 
             ajax_Request.send(); 
        } 
    </script> 
</body> 
</html> 

Server Side:

index.php: We will create the server-side PHP file that will make a connection with the database to get the responses and will send the responses to the AJAX request object.

We are creating the Database Connection using the following PHP function.

$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "sampledb";

$con = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);

if($con->connect_error){
    exit('Could not connect');
}

If there will be an error in connection, con->connect_error will show to the user.
We are storing the user-entered CGPA into another variable.

$cgpa = $_GET['q'];

An SQL query will run on the DB and it will fetch the result from the DB and display the result in an HTML table.

PHP

<?php 
  
    $dbhost = "localhost"; 
    $dbuser = "root"; 
    $dbpass = ""; 
    $dbname = "sampledb"; 
  
    $con = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname); 
  
    if($con->connect_error){ 
        exit('Could not connect'); 
    } 
  
    $cgpa = $_GET['q']; 
    $sql = "SELECT * FROM collegedb WHERE cgpa = '$cgpa' "; 
  
    $query = mysqli_query($con, $sql); 
  
    if (!$query) { 
        echo ("Error description: " . mysqli_error($con)); 
    } 
  
    echo "<table border='1' style = 'border-collapse:collapse;'> 
            <tr> 
                <th style='padding:10px;'>FirstName</th> 
                <th style='padding:10px;'>LastName</th> 
                <th style='padding:10px;' >Rollno</th> 
                <th style='padding:10px;' >CGPA</th> 
            </tr> 
         "; 
  
    if (mysqli_num_rows($query) > 0) { 
        while ($result = mysqli_fetch_array($query))  
        { 
              echo "<tr>"; 
              echo "<td style='padding:10px;'>" .  
                 $result['firstname'] . "</td>"; 
              echo "<td style='padding:10px;'>" . 
                 $result['lastname'] . "</td>"; 
              echo "<td style='padding:10px;'>" . 
                 $result['rollno'] . "</td>"; 
              echo "<td style='padding:10px;'>" .  
                 $result['cgpa'] . "</td>"; 
              echo "</tr>"; 
            } 
         echo "</table>"; 
         mysqli_close($con); 
     } 
?> 