🗃️ AJAX – Database Operations Using PHP with MySQL and AJAX

🧲 Introduction – Why Use AJAX with PHP and MySQL?

Using AJAX with PHP and MySQL allows developers to perform database operations asynchronously—without refreshing the entire page. This is incredibly useful in modern web apps where user interaction needs to be smooth, responsive, and dynamic.

Whether you’re building a CRUD app, live search, or a dynamic dashboard, this combination allows seamless backend communication and database manipulation via background requests.

🎯 In this guide, you’ll learn:

• How to fetch, insert, update, and delete data with AJAX + PHP + MySQL
• Complete examples using fetch() and XMLHttpRequest
• How to structure front-end JavaScript and backend PHP logic
• Tips for securing and validating input

🧱 Project Overview

/ajax-db-app
│
├── index.html          ← User interface
├── script.js           ← AJAX code
├── db.php              ← DB connection file
├── insert.php          ← Handles data insertion
├── fetch.php           ← Fetches data from DB
├── delete.php          ← Deletes records
└── update.php          ← Updates records

✅ These files create a basic CRUD application powered by AJAX.

🧾 Step 1: Create Database and Table (MySQL)

CREATE DATABASE ajax_demo;
USE ajax_demo;

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

✅ This will store user records we’ll manage through AJAX.

🔧 Step 2: Database Connection (db.php)

<?php
$conn = new mysqli("localhost", "root", "", "ajax_demo");
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
?>

📥 Step 3: Insert Data (insert.php)

<?php
include "db.php";

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

$sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
if ($conn->query($sql)) {
  echo "User added successfully.";
} else {
  echo "Error: " . $conn->error;
}
?>

🔍 Step 4: Fetch Data (fetch.php)

<?php
include "db.php";

$result = $conn->query("SELECT * FROM users");
$data = "";

while ($row = $result->fetch_assoc()) {
  $data .= "<tr>
              <td>{$row['id']}</td>
              <td>{$row['name']}</td>
              <td>{$row['email']}</td>
              <td>
                <button onclick='deleteUser({$row['id']})'>Delete</button>
              </td>
           </tr>";
}

echo $data;
?>

🧹 Step 5: Delete User (delete.php)

<?php
include "db.php";
$id = $_POST['id'];

$sql = "DELETE FROM users WHERE id = $id";
echo ($conn->query($sql)) ? "User deleted." : "Deletion failed.";
?>

🧾 Step 6: Front-End UI (index.html)

<form id="userForm">
  <input type="text" name="name" placeholder="Name" required />
  <input type="email" name="email" placeholder="Email" required />
  <button type="submit">Add User</button>
</form>

<table border="1">
  <thead>
    <tr><th>ID</th><th>Name</th><th>Email</th><th>Actions</th></tr>
  </thead>
  <tbody id="userTable"></tbody>
</table>

<script src="script.js"></script>

🔁 Step 7: AJAX Script (script.js)

document.getElementById("userForm").addEventListener("submit", function (e) {
  e.preventDefault();
  const formData = new FormData(this);

  fetch("insert.php", {
    method: "POST",
    body: formData
  })
    .then(res => res.text())
    .then(msg => {
      alert(msg);
      this.reset();
      loadUsers();
    });
});

function loadUsers() {
  fetch("fetch.php")
    .then(res => res.text())
    .then(data => {
      document.getElementById("userTable").innerHTML = data;
    });
}

function deleteUser(id) {
  const formData = new FormData();
  formData.append("id", id);

  fetch("delete.php", {
    method: "POST",
    body: formData
  })
    .then(res => res.text())
    .then(msg => {
      alert(msg);
      loadUsers();
    });
}

loadUsers(); // Initial load

✅ What You Can Build with This Stack

🔐 Security and Validation Tips

• ✅ Use prepared statements to prevent SQL injection
• ✅ Validate input on both client-side and server-side
• ✅ Sanitize all POST/GET values
• ✅ Use CSRF tokens for sensitive AJAX requests

📌 Summary – Recap & Takeaways

AJAX combined with PHP and MySQL provides a robust and responsive way to perform database operations. This setup allows users to interact with data in real time, improving usability without overloading the server.

🔍 Key Takeaways:

• Use fetch() or XMLHttpRequest to submit/receive data asynchronously
• PHP handles database queries and returns output as HTML/JSON
• MySQL serves as a scalable backend for dynamic data storage

⚙️ Next Steps:

• Add pagination and search filters using AJAX
• Use JSON instead of HTML for better frontend control
• Implement edit/update functionality with modal forms

❓ FAQs – AJAX and MySQL Integration with PHP

❓ Can I send JSON from JavaScript to PHP?
✅ Yes. Use fetch() with Content-Type: application/json and decode on PHP with json_decode().

❓ Is MySQL fast enough for real-time AJAX requests?
✅ Yes, for moderate load. Use indexes and optimized queries for scalability.

❓ Can I use PDO instead of mysqli?
✅ Absolutely. PDO is more secure and flexible with prepared statements.

❓ How do I prevent SQL injection in AJAX apps?
✅ Always use prepared statements (mysqli or PDO) and validate inputs.

❓ Is it better to return HTML or JSON from PHP?
✅ JSON is recommended for cleaner separation of data and view layers.