PHP - AJAX and MySQL Database

PHP - AJAX and MySQL Database

AJAX (Asynchronous JavaScript and XML) allows you to update web pages asynchronously by exchanging small amounts of data with the server behind the scenes. This means that it is possible to update parts of a web page, without reloading the whole page.

When combined with PHP and MySQL, you can dynamically interact with your database to fetch, insert, or update data without refreshing the browser.


Key Concepts:

  • AJAX: Makes asynchronous HTTP requests using JavaScript (commonly with XMLHttpRequest or fetch).

  • PHP: Processes the request, interacts with MySQL, and returns data.

  • MySQL: Stores and retrieves data requested via PHP.

  • JSON: Often used for returning data from PHP to JavaScript.


Example: Fetch Data from MySQL using AJAX + PHP

1. 📄 index.html — Frontend with AJAX

<!DOCTYPE html>
<html>
<head>
  <title>AJAX with PHP and MySQL</title>
  <script>
    function loadData() {
      const xmlhttp = new XMLHttpRequest();
      xmlhttp.onreadystatechange = function () {
        if (this.readyState === 4 && this.status === 200) {
          document.getElementById("output").innerHTML = this.responseText;
        }
      };
      xmlhttp.open("GET", "fetch.php", true);
      xmlhttp.send();
    }
  </script>
</head>
<body>

  <h2>Load Users from Database</h2>
  <button onclick="loadData()">Load Data</button>
  <div id="output"></div>

</body>
</html>

2. 📄 fetch.php — Backend with PHP and MySQL

<?php
// Database connection
$host = "localhost";
$user = "root";
$password = "";
$database = "demo";

$conn = new mysqli($host, $user, $password, $database);

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

// Fetch data
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);

// Output data
if ($result->num_rows > 0) {
  echo "<ul>";
  while ($row = $result->fetch_assoc()) {
    echo "<li>ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "</li>";
  }
  echo "</ul>";
} else {
  echo "No records found.";
}

$conn->close();
?>


Sample MySQL Table

CREATE DATABASE demo;

USE demo;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com');


How it Works

  1. User clicks the "Load Data" button.

  2. JavaScript sends an AJAX GET request to fetch.php.

  3. fetch.php connects to MySQL, fetches user data, and returns it.

  4. The result is displayed in the browser without reloading the page.


Benefits of Using AJAX with PHP & MySQL

  • Faster and more dynamic user experience.

  • Reduces page reloads.

  • Seamless interaction with the database.