PHP – AJAX and MySQL

Welcome to The Coding College! In this tutorial, we will demonstrate how to use AJAX to interact with a MySQL database using PHP. By combining these technologies, you can create dynamic, responsive applications that retrieve or update database content without reloading the webpage.

Why Use AJAX with MySQL?

Combining AJAX, PHP, and MySQL provides many benefits:

  • Dynamic Updates: Retrieve or update database content without page reloads.
  • Improved Performance: Reduce unnecessary requests and load only relevant data.
  • Seamless User Experience: Ideal for search filters, live updates, and forms.

AJAX, PHP, and MySQL Workflow

  1. HTML & JavaScript: Sends an AJAX request to a PHP script.
  2. PHP: Queries the MySQL database and processes the request.
  3. MySQL: Returns the query result.
  4. JavaScript: Updates the webpage dynamically with the result.

Example 1: Fetch Data from MySQL Using AJAX and PHP

Let’s create a simple example where data is fetched from a MySQL database and displayed on a webpage without refreshing the page.

Step 1: Create a MySQL Table

First, create a table in your MySQL database.

CREATE DATABASE ajax_demo;

USE ajax_demo;

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

INSERT INTO users (name, email) VALUES
('John Doe', 'john@example.com'),
('Jane Smith', 'jane@example.com'),
('Mark Taylor', 'mark@example.com');

Step 2: HTML and JavaScript

Here’s the front-end HTML with JavaScript to send an AJAX request.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>AJAX with PHP and MySQL</title>
    <script>
        // Function to fetch data from the server
        function fetchData() {
            var xhr = new XMLHttpRequest(); // Create XMLHttpRequest object
            xhr.open("GET", "fetch_users.php", true); // Request to the server

            // Handle response
            xhr.onreadystatechange = function() {
                if (xhr.readyState == 4 && xhr.status == 200) {
                    document.getElementById("user-data").innerHTML = xhr.responseText;
                }
            };

            xhr.send(); // Send the request
        }
    </script>
</head>
<body>
    <h1>Fetch Data from MySQL Using AJAX and PHP</h1>
    <button onclick="fetchData()">Load Users</button>
    <div id="user-data">Click the button to fetch users.</div>
</body>
</html>

Step 3: PHP Script (fetch_users.php)

This PHP script connects to the MySQL database, retrieves the data, and sends it as a response.

<?php
// Database credentials
$host = "localhost";
$user = "root";
$password = "";
$database = "ajax_demo";

// Connect to MySQL database
$conn = new mysqli($host, $user, $password, $database);

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

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

// Display data
if ($result->num_rows > 0) {
    echo "<table border='1'>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
            </tr>";
    while ($row = $result->fetch_assoc()) {
        echo "<tr>
                <td>" . $row['id'] . "</td>
                <td>" . $row['name'] . "</td>
                <td>" . $row['email'] . "</td>
              </tr>";
    }
    echo "</table>";
} else {
    echo "No records found!";
}

$conn->close();
?>

Step 4: How It Works

  1. When the user clicks the “Load Users” button, the JavaScript fetchData() function sends an AJAX GET request to fetch_users.php.
  2. The PHP script (fetch_users.php) connects to the MySQL database, retrieves the data, and formats it as an HTML table.
  3. The response is sent back to the browser, where JavaScript dynamically updates the user-data <div>.

Example 2: Insert Data into MySQL with AJAX and PHP

This example demonstrates how to use AJAX with a POST request to insert data into a MySQL database.

HTML and JavaScript (Form Submission)

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>AJAX Insert Example</title>
    <script>
        // Function to insert data
        function insertData() {
            var name = document.getElementById("name").value;
            var email = document.getElementById("email").value;

            var xhr = new XMLHttpRequest();
            xhr.open("POST", "insert_user.php", true);
            xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");

            xhr.onreadystatechange = function() {
                if (xhr.readyState == 4 && xhr.status == 200) {
                    document.getElementById("response").innerHTML = xhr.responseText;
                    document.getElementById("user-form").reset(); // Reset form
                }
            };

            xhr.send("name=" + name + "&email=" + email);
        }
    </script>
</head>
<body>
    <h1>Insert Data into MySQL Using AJAX and PHP</h1>
    <form id="user-form" onsubmit="event.preventDefault(); insertData();">
        Name: <input type="text" id="name" required><br>
        Email: <input type="email" id="email" required><br>
        <button type="submit">Submit</button>
    </form>
    <div id="response"></div>
</body>
</html>

PHP Script (insert_user.php)

<?php
// Database credentials
$host = "localhost";
$user = "root";
$password = "";
$database = "ajax_demo";

// Connect to MySQL database
$conn = new mysqli($host, $user, $password, $database);

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

// Get POST data
$name = $_POST['name'];
$email = $_POST['email'];

// Insert data
$sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully!";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

How It Works

  1. When the user submits the form, the insertData() function sends a POST request to insert_user.php with name and email data.
  2. The PHP script inserts the data into the MySQL database.
  3. A success or error message is returned to the browser and displayed in the response <div>.

Conclusion

Combining AJAX, PHP, and MySQL allows you to create interactive, dynamic, and database-driven web applications.

  • Fetching Data: Use GET requests to retrieve data from MySQL.
  • Inserting Data: Use POST requests to send data to MySQL.

Leave a Comment