AJAX Database

Interact with databases dynamically using AJAX

🗄️ What is AJAX Database?

AJAX Database operations let you fetch, insert, update, and delete database records without page reloads. JavaScript sends requests to PHP, which queries the database and returns results instantly. 


// Fetch data from database via AJAX
fetch("getusers.php")
    .then(response => response.json())
    .then(users => console.log(users));

Output:

[{id: 1, name: "John"}, {id: 2, name: "Jane"}]

Key Database Operations

AJAX enables full CRUD (Create, Read, Update, Delete) operations on databases. JavaScript communicates with PHP scripts that execute SQL queries, allowing real-time data manipulation without disrupting the user experience.

📖

Read (SELECT)

Fetch records from database 

SELECT * FROM users
WHERE id = 1;
➕

Create (INSERT)

Add new records 

INSERT INTO users
VALUES (NULL, 'John');
✏️

Update

Modify existing records 

UPDATE users SET
name='Jane' WHERE id=1;
🗑️

Delete

Remove records 

DELETE FROM users
WHERE id = 1;

🔹 Database Connection (PHP)

First, create a database connection file:

🔸 db_connect.php 

<?php
// Database credentials
$host = "localhost";
$username = "root";
$password = "";
$database = "mydb";

// Create connection
$conn = new mysqli($host, $username, $password, $database);

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

echo "Connected successfully";
?>

Output:

Connected successfully

🔹 Fetching Data (SELECT)

Retrieve database records using AJAX:

🔸 JavaScript 

function loadUsers() {
    fetch("getusers.php")
        .then(response => response.json())
        .then(users => {
            let html = "<table border='1'><tr><th>ID</th><th>Name</th></tr>";
            users.forEach(user => {
                html += `<tr><td>${user.id}</td><td>${user.name}</td></tr>`;
            });
            html += "</table>";
            document.getElementById("userList").innerHTML = html;
        });
}

loadUsers();

🔸 PHP (getusers.php) 

<?php
include 'db_connect.php';

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

$users = array();

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $users[] = $row;
    }
}

// Return JSON
header('Content-Type: application/json');
echo json_encode($users);

$conn->close();
?>

Output:

ID Name
1 John Doe
2 Jane Smith

🔹 Inserting Data (INSERT)

Add new records to database via AJAX:

🔸 HTML + JavaScript 

<input type="text" id="userName" placeholder="Enter name">
<button onclick="addUser()">Add User</button>
<div id="message"></div>

<script>
function addUser() {
    var name = document.getElementById("userName").value;
    
    fetch("adduser.php", {
        method: "POST",
        headers: {"Content-Type": "application/x-www-form-urlencoded"},
        body: "name=" + name
    })
    .then(response => response.text())
    .then(data => {
        document.getElementById("message").innerHTML = data;
        document.getElementById("userName").value = "";
    });
}
</script>

🔸 PHP (adduser.php) 

<?php
include 'db_connect.php';

$name = $_POST['name'];

// Insert query
$sql = "INSERT INTO users (name) VALUES ('$name')";

if ($conn->query($sql) === TRUE) {
    echo "<p style='color: green;'>✓ User added successfully!</p>";
} else {
    echo "<p style='color: red;'>✗ Error: " . $conn->error . "</p>";
}

$conn->close();
?>

Output:

✓ User added successfully!

🔹 Updating Data (UPDATE)

Modify existing database records:

🔸 JavaScript 

function updateUser(userId, newName) {
    fetch("updateuser.php", {
        method: "POST",
        headers: {"Content-Type": "application/x-www-form-urlencoded"},
        body: `id=${userId}&name=${newName}`
    })
    .then(response => response.text())
    .then(data => {
        console.log(data);
        alert("User updated!");
    });
}

// Usage
updateUser(1, "John Updated");

🔸 PHP (updateuser.php) 

<?php
include 'db_connect.php';

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

// Update query
$sql = "UPDATE users SET name='$name' WHERE id=$id";

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

$conn->close();
?>

Output:

Record updated successfully

🔹 Deleting Data (DELETE)

Remove records from database:

🔸 JavaScript 

function deleteUser(userId) {
    if (confirm("Are you sure?")) {
        fetch("deleteuser.php?id=" + userId)
            .then(response => response.text())
            .then(data => {
                alert(data);
                loadUsers(); // Refresh list
            });
    }
}

// Usage
deleteUser(3);

🔸 PHP (deleteuser.php) 

<?php
include 'db_connect.php';

$id = $_GET['id'];

// Delete query
$sql = "DELETE FROM users WHERE id=$id";

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

$conn->close();
?>

Output:

User deleted successfully

🔹 Search with Database

Create a live database search:

🔸 JavaScript 

function searchUsers() {
    var query = document.getElementById("searchBox").value;
    
    fetch("searchusers.php?q=" + query)
        .then(response => response.json())
        .then(users => {
            let html = "";
            users.forEach(user => {
                html += `<p>${user.id}. ${user.name}</p>`;
            });
            document.getElementById("results").innerHTML = html;
        });
}

🔸 PHP (searchusers.php) 

<?php
include 'db_connect.php';

$query = $_GET['q'];

$sql = "SELECT * FROM users WHERE name LIKE '%$query%'";
$result = $conn->query($sql);

$users = array();
while($row = $result->fetch_assoc()) {
    $users[] = $row;
}

header('Content-Type: application/json');
echo json_encode($users);

$conn->close();
?>

Output (searching "John"):

1. John Doe

5. Johnny Smith

🧠 Test Your Knowledge

Which SQL command is used to retrieve data?