• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

AJAX Database

← Previous Next →

AJAX becomes more powerful when combined with a database, allowing web pages to fetch, insert, update, or delete data dynamically without refreshing the page. This is essential for building responsive web applications such as live search, real-time notifications, or interactive dashboards.

In this tutorial, we will learn how to use AJAX with PHP and MySQL to interact with a database efficiently and update content dynamically.

Setting Up the Database

First, create a database and a table for demonstration.

SQL:

CREATE DATABASE ajax_demo;

USE ajax_demo;

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    course VARCHAR(50) NOT NULL
);

INSERT INTO students (name, age, course) VALUES
('Riya Sharma', 20, 'Web Development'),
('Ananya Singh', 22, 'Data Science'),
('Meera Patel', 21, 'UI/UX Design');

Connecting PHP to the Database

Create a PHP file db.php to handle the database connection:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "ajax_demo";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

Fetching Data Dynamically with AJAX

HTML File: index.html

<!DOCTYPE html>
<html>
<head>
  <title>AJAX Database Example</title>
  <script>
    function fetchStudents() {
      var xhr = new XMLHttpRequest();
      xhr.open("GET", "fetch_students.php", true);
      xhr.onreadystatechange = function() {
        if (xhr.readyState == 4 && xhr.status == 200) {
          document.getElementById("result").innerHTML = xhr.responseText;
        }
      };
      xhr.send();
    }
  </script>
</head>
<body>
  <h2>Students List</h2>
  <button onclick="fetchStudents()">Load Students</button>
  <div id="result" style="margin-top:20px;"></div>
</body>
</html>

PHP File: fetch_students.php

<?php
include "db.php";

$sql = "SELECT * FROM students";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table border='1' cellpadding='5'><tr><th>ID</th><th>Name</th><th>Age</th><th>Course</th></tr>";
    while($row = $result->fetch_assoc()) {
        echo "<tr>
                <td>".$row['id']."</td>
                <td>".$row['name']."</td>
                <td>".$row['age']."</td>
                <td>".$row['course']."</td>
              </tr>";
    }
    echo "</table>";
} else {
    echo "No records found";
}
$conn->close();
?>

Clicking the “Load Students” button fetches student records from the database without reloading the page.

Inserting Data Using AJAX

You can also insert data dynamically:

HTML Form:

<input type="text" id="name" placeholder="Name">
<input type="number" id="age" placeholder="Age">
<input type="text" id="course" placeholder="Course">
<button onclick="addStudent()">Add Student</button>
<div id="addStatus"></div>

<script>
function addStudent() {
  var name = document.getElementById("name").value;
  var age = document.getElementById("age").value;
  var course = document.getElementById("course").value;

  var xhr = new XMLHttpRequest();
  xhr.open("POST", "add_student.php", true);
  xhr.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
  xhr.onreadystatechange = function() {
    if (xhr.readyState == 4 && xhr.status == 200) {
      document.getElementById("addStatus").innerHTML = xhr.responseText;
      fetchStudents(); // Refresh the list
    }
  };
  xhr.send("name=" + name + "&age=" + age + "&course=" + course);
}
</script>

PHP File: add_student.php

<?php
include "db.php";

$name = $_POST['name'];
$age = $_POST['age'];
$course = $_POST['course'];

$sql = "INSERT INTO students (name, age, course) VALUES ('$name', $age, '$course')";

if ($conn->query($sql) === TRUE) {
    echo "Student added successfully!";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

This allows you to add new students to the database dynamically.

Updating Records Dynamically

You can also update a student’s record using AJAX:

JavaScript Example:

function updateStudent(id) {
  var newName = prompt("Enter new name:");
  if(newName) {
    var xhr = new XMLHttpRequest();
    xhr.open("POST", "update_student.php", true);
    xhr.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
    xhr.onreadystatechange = function() {
      if (xhr.readyState == 4 && xhr.status == 200) {
        alert(xhr.responseText);
        fetchStudents(); // Refresh the list
      }
    };
    xhr.send("id=" + id + "&name=" + newName);
  }
}

PHP File: update_student.php

<?php
include "db.php";

$id = $_POST['id'];
$name = $_POST['name'];

$sql = "UPDATE students SET name='$name' WHERE id=$id";

if ($conn->query($sql) === TRUE) {
    echo "Student updated successfully!";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

Deleting Records Dynamically

AJAX can also delete records without page reload:

JavaScript:

function deleteStudent(id) {
  if(confirm("Are you sure to delete this student?")) {
    var xhr = new XMLHttpRequest();
    xhr.open("POST", "delete_student.php", true);
    xhr.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
    xhr.onreadystatechange = function() {
      if(xhr.readyState == 4 && xhr.status == 200) {
        alert(xhr.responseText);
        fetchStudents();
      }
    };
    xhr.send("id=" + id);
  }
}

PHP File: delete_student.php

<?php
include "db.php";

$id = $_POST['id'];
$sql = "DELETE FROM students WHERE id=$id";

if ($conn->query($sql) === TRUE) {
    echo "Student deleted successfully!";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

Advantages of AJAX with Database

  1. Dynamic content updates without page reload.

  2. Improved user experience for web applications.

  3. Efficient server communication – only relevant data is sent.

  4. Supports CRUD operations (Create, Read, Update, Delete) dynamically.

  5. Works seamlessly with PHP and MySQL.

Summary of the Tutorial

Using AJAX with PHP and MySQL allows developers to create interactive and responsive web applications. Key concepts:

  • Use XMLHttpRequest to send requests to PHP scripts.

  • PHP scripts interact with MySQL for CRUD operations.

  • Responses can be in HTML, JSON, or plain text.

  • Dynamic updates improve speed, usability, and interactivity.

This approach is fundamental for building modern web apps like dashboards, live search, and real-time content updates.

Practice Questions

  1. Create an HTML page that fetches all student records from a MySQL database using AJAX and displays them in a table.

  2. Add a form to insert a new student into the database dynamically using AJAX POST.

  3. Modify the student fetch example to sort students by age or name without reloading the page.

  4. Create an AJAX request to update a student’s name when clicking an “Edit” button.

  5. Implement an AJAX-based delete button for each student row to remove records dynamically.

  6. Use AJAX to search for students by name in real time and display matching results.

  7. Create a PHP script that returns student data in JSON format. Fetch and display it using AJAX.

  8. Add error handling for all AJAX database operations and display alerts for success or failure.

  9. Implement a dynamic dropdown list of courses that updates student lists based on selection using AJAX.

  10. Build a small dashboard that dynamically updates the total number of students in the database whenever a record is added or deleted.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

PHP

online coding class codepractice

PHP Basics

PHP Data

Control Flow

PHP Functions

PHP Arrays

PHP Forms

PHP Advanced

Cookies & Sessions

Filters & JSON

PHP Exceptions

PHP OOP

MySQL with PHP

PHP and XML

AJAX with PHP

codepractice logo
Login
Go Back Top