AJAX Database

Database Operations

fetch('/api/users')

fetch('/api/users', {
  method: 'POST'
})

fetch('/api/users/1', {
  method: 'PUT'
})

fetch('/api/users/1', {
  method: 'DELETE'
})

🔹 Reading Data (SELECT)

Fetch and display data from the database:

🔸 JavaScript (Frontend)

// Load all users
function loadUsers() {
  fetch('/api/users.php')
    .then(response => response.json())
    .then(users => {
      let html = '<table border="1"><tr><th>ID</th><th>Name</th><th>Email</th><th>Actions</th></tr>';
      
      users.forEach(user => {
        html += `<tr>
          <td>${user.id}</td>
          <td>${user.name}</td>
          <td>${user.email}</td>
          <td>
            <button onclick="editUser(${user.id})">Edit</button>
            <button onclick="deleteUser(${user.id})">Delete</button>
          </td>
        </tr>`;
      });
      
      html += '</table>';
      document.getElementById('users-table').innerHTML = html;
    })
    .catch(error => console.error('Error:', error));
}

// Load users when page loads
window.onload = loadUsers;

🔸 PHP Backend

<?php
// users.php
header('Content-Type: application/json');

// Database connection
$host = 'localhost';
$dbname = 'myapp';
$username = 'root';
$password = '';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Fetch all users
    $stmt = $pdo->query("SELECT id, name, email FROM users ORDER BY name");
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo json_encode($users);
    
} catch(PDOException $e) {
    echo json_encode(['error' => 'Database error: ' . $e->getMessage()]);
}
?>

🔹 Creating Data (INSERT)

Add new records to the database:

🔸 Add User Form

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

<script>
document.getElementById('add-user-form').addEventListener('submit', function(e) {
  e.preventDefault();
  
  const userData = {
    name: document.getElementById('user-name').value,
    email: document.getElementById('user-email').value
  };
  
  fetch('/api/add-user.php', {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json'
    },
    body: JSON.stringify(userData)
  })
  .then(response => response.json())
  .then(result => {
    if (result.success) {
      alert('User added successfully!');
      this.reset(); // Clear form
      loadUsers(); // Refresh the user list
    } else {
      alert('Error: ' + result.message);
    }
  });
});
</script>

🔸 PHP Insert Handler

<?php
// add-user.php
header('Content-Type: application/json');

$input = json_decode(file_get_contents('php://input'), true);
$name = $input['name'] ?? '';
$email = $input['email'] ?? '';

// Validate input
if (empty($name) || empty($email)) {
    echo json_encode(['success' => false, 'message' => 'Name and email required']);
    exit;
}

try {
    $pdo = new PDO("mysql:host=localhost;dbname=myapp", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Insert new user
    $stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
    $stmt->execute([$name, $email]);
    
    echo json_encode([
        'success' => true, 
        'message' => 'User added successfully',
        'id' => $pdo->lastInsertId()
    ]);
    
} catch(PDOException $e) {
    echo json_encode(['success' => false, 'message' => 'Database error']);
}
?>

🔹 Updating Data (UPDATE)

Modify existing database records:

🔸 Edit User Function

function editUser(userId) {
  // First, get current user data
  fetch(`/api/get-user.php?id=${userId}`)
    .then(response => response.json())
    .then(user => {
      // Populate form with current data
      document.getElementById('edit-user-id').value = user.id;
      document.getElementById('edit-user-name').value = user.name;
      document.getElementById('edit-user-email').value = user.email;
      
      // Show edit form
      document.getElementById('edit-form').style.display = 'block';
    });
}

function updateUser() {
  const userData = {
    id: document.getElementById('edit-user-id').value,
    name: document.getElementById('edit-user-name').value,
    email: document.getElementById('edit-user-email').value
  };
  
  fetch('/api/update-user.php', {
    method: 'PUT',
    headers: {
      'Content-Type': 'application/json'
    },
    body: JSON.stringify(userData)
  })
  .then(response => response.json())
  .then(result => {
    if (result.success) {
      alert('User updated successfully!');
      document.getElementById('edit-form').style.display = 'none';
      loadUsers(); // Refresh list
    } else {
      alert('Error: ' + result.message);
    }
  });
}

🔸 PHP Update Handler

<?php
// update-user.php
header('Content-Type: application/json');

$input = json_decode(file_get_contents('php://input'), true);
$id = $input['id'] ?? 0;
$name = $input['name'] ?? '';
$email = $input['email'] ?? '';

if (!$id || empty($name) || empty($email)) {
    echo json_encode(['success' => false, 'message' => 'Invalid data']);
    exit;
}

try {
    $pdo = new PDO("mysql:host=localhost;dbname=myapp", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $stmt = $pdo->prepare("UPDATE users SET name = ?, email = ? WHERE id = ?");
    $stmt->execute([$name, $email, $id]);
    
    if ($stmt->rowCount() > 0) {
        echo json_encode(['success' => true, 'message' => 'User updated']);
    } else {
        echo json_encode(['success' => false, 'message' => 'No changes made']);
    }
    
} catch(PDOException $e) {
    echo json_encode(['success' => false, 'message' => 'Database error']);
}
?>

🔹 Deleting Data (DELETE)

Remove records from the database:

🔸 Delete Function

function deleteUser(userId) {
  if (confirm('Are you sure you want to delete this user?')) {
    fetch(`/api/delete-user.php?id=${userId}`, {
      method: 'DELETE'
    })
    .then(response => response.json())
    .then(result => {
      if (result.success) {
        alert('User deleted successfully!');
        loadUsers(); // Refresh the list
      } else {
        alert('Error: ' + result.message);
      }
    })
    .catch(error => {
      console.error('Error:', error);
      alert('An error occurred while deleting the user.');
    });
  }
}

🔸 PHP Delete Handler

<?php
// delete-user.php
header('Content-Type: application/json');

$userId = $_GET['id'] ?? 0;

if (!$userId) {
    echo json_encode(['success' => false, 'message' => 'User ID required']);
    exit;
}

try {
    $pdo = new PDO("mysql:host=localhost;dbname=myapp", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
    $stmt->execute([$userId]);
    
    if ($stmt->rowCount() > 0) {
        echo json_encode(['success' => true, 'message' => 'User deleted']);
    } else {
        echo json_encode(['success' => false, 'message' => 'User not found']);
    }
    
} catch(PDOException $e) {
    echo json_encode(['success' => false, 'message' => 'Database error']);
}
?>