jQuery Ajax Crud in PHP example tutorial

ajax crud in php

Introduction to ajax crud in php

Performing jQuery ajax CRUD in PHP (Create, Read, Update, Delete) is a powerful technique that allows you to create dynamic web applications with minimal page reloads.

Creating the Database Table and Connecting to the Database

The process involves creating a database table and connecting to the database using a PHP script.

Creating the HTML Form and Inserting Data into the Database

Once this is done, an HTML form is created to allow users to input data into the fields created earlier in the database table. AJAX is then used to send the form data to a PHP script on the server that inserts the data into the database.

Retrieving Data with the Read Operation

The Read operation requires creating a PHP script that retrieves data from the database and outputs it in JSON format. Then, use jQuery AJAX to call this PHP script and display the data on the page.

Updating Data with the Update Operation

For the Update operation, an HTML form similar to the one used for the Create operation is created but is pre-populated with the data for the record that the user wants to update. This data is then sent to a PHP script on the server that updates the corresponding record in the database.

Deleting Data with the Delete Operation

The Delete operation requires creating a button or link that triggers an AJAX request to a PHP script on the server that deletes the corresponding record from the database.

Conclusion

Performing CRUD operations using jQuery AJAX allows for more efficient and streamlined interaction with databases. By breaking the process down into these distinct steps, you can easily create dynamic web applications that interact with your database in real time.

CREATE DATABASE test;

Create Table:

CREATE TABLE `test`.`users` (`id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(100) NOT NULL , `email` VARCHAR(100) NOT NULL , `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , `updated` DATETIME NULL DEFAULT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

Create index.php

<!-- index.html -->
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>CRUD Operations</title>
  <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
  <style type="text/css">
        table {
            border-collapse: collapse;
            width: 50%;
            margin: 0 auto;
        }
        th, td {
            padding: 8px;
            text-align: left;
            border-bottom: 1px solid #ddd;
        }
        th {
            background-color: #4CAF50;
            color: white;
        }

        input[type=text], input[type=email] {
            width: 100%;
            padding: 12px 20px;
            margin: 8px 0;
            display: inline-block;
            border: 1px solid #ccc;
            border-radius: 4px;
            box-sizing: border-box;
        }
        button {
            background-color: #4CAF50; /* Green */
            border: none;
            color: white;
            padding: 10px 20px;
            text-align: center;
            text-decoration: none;
            display: inline-block;
            font-size: 16px;
            margin: 4px 2px;
            cursor: pointer;
            border-radius: 5px;
        }
        button:hover {
            background-color: #3e8e41;
        }
    </style>
</head>
<body>
  
  <!-- Create Operation -->
  <h2>Create User</h2>
  <form id="createUserForm" method="POST">
    <label for="name">Name:</label>
    <input type="text" id="name" name="name">
    <br><br>
    <label for="email">Email:</label>
    <input type="text" id="email" name="email">
    <br><br>
    <button type="submit" id="addUserBtn">Add User</button>
  </form>
  
  <hr>

  <!-- Read Operation -->
  <h2>Users List</h2>
  <table id="usersTable">
    <thead>
      <tr>
        <th>ID</th>
        <th>Name</th>
        <th>Email</th>
        <th>Action</th>
      </tr>
    </thead>
    <tbody>
    </tbody>
  </table>

  <hr>

  <!-- Update Operation -->
  <h2>Update User</h2>
  <form id="updateUserForm" method="POST">
    <label for="userId">User ID:</label>
    <input type="text" id="userId" name="id">
    <br><br>
    <label for="userName">Name:</label>
    <input type="text" id="userName" name="name">
    <br><br>
    <label for="userEmail">Email:</label>
    <input type="text" id="userEmail" name="email">
    <br><br>
    <button type="submit" id="updateUserBtn">Update User</button>
  </form>

  <hr>

  <!-- Delete Operation -->
  <h2>Delete User</h2>
  <form id="deleteUserForm" method="POST">
    <label for="deleteUserId">User ID:</label>
    <input type="text" id="deleteUserId" name="id">
    <br><br>
    <button type="submit" id="deleteUserBtn">Delete User</button>
  </form>

  <script src="crud.js"></script>
</body>
</html>

Make DB file db.php

<?php
$host = 'localhost';
$dbname = 'test';
$username = 'root';
$password = '';

try {
  $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
  die("Connection failed: " . $e->getMessage());
}

Create Operation

  1. Create a database table and connect to the database using a PHP script.
  2. Create an HTML form that allows users to input data into the fields created earlier in the database table.
  3. Use AJAX to send the form data to a PHP script on the server that inserts the data into the database.
  4. Receive the response from the server in JavaScript and display it on the page.

Make a file create-user.php

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

$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (?, ?)');
$result = $stmt->execute([$name, $email]);

if ($result) {
  $id = $pdo->lastInsertId();
  echo '<tr data-id="'.$id.'"><td>'.$id.'</td><td class="name">'.$name.'</td><td class="email">'.$email.'</td><td><button class="editBtn" data-id="'.$id.'" data-name="'.$name.'" data-email="'.$email.'">Edit</button><form id="deleteUserForm" method="POST">
  <input type="hidden" id="deleteUserId" value="' . $id . '" name="id">
  <button type="submit" id="deleteUserBtn">Delete User</button>
</form></td></tr>';
} else {
  echo 'Error creating user';
}

Read Operation

  1. Create a PHP script that retrieves data from the database and outputs it in JSON format.
  2. Use jQuery AJAX to call this PHP script and display the data on the page.

Make a file get-users.php

<?php
include "db.php";
$stmt = $pdo->query('SELECT * FROM users');

while ($row = $stmt->fetch()) {
    echo '<tr data-id="' . $row['id'] . '"><td>' . $row['id'] . '</td><td class="name">' . $row['name'] . '</td><td class="email">' . $row['email'] . '</td><td><button class="editBtn" data-id="' . $row['id'] . '" data-name="' . $row['name'] . '" data-email="' . $row['email'] . '">Edit</button> <form id="deleteUserForm" method="POST">
    <input type="hidden" id="deleteUserId" value="' . $row['id'] . '" name="id">
    <button type="submit" id="deleteUserBtn">Delete User</button>
  </form></td></tr>';
}

Update Operation

  1. Create an HTML form similar to the one used for the Create operation but pre-populated with the data for the record that the user wants to update.
  2. Send this data to a PHP script on the server that updates the corresponding record in the database using AJAX.
  3. Receive the response from the server in JavaScript and display it on the page.

Make a file update-user.php

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

$stmt = $pdo->prepare('UPDATE users SET name = ?, email = ? WHERE id = ?');
$result = $stmt->execute([$name, $email, $id]);

if ($result) {
  echo json_encode(['id' => $id, 'name' => $name, 'email' => $email]);
} else {
  echo 'Error updating user';
}

Delete Operation

  1. Create a button or link that triggers an AJAX request to a PHP script on the server that deletes the corresponding record from the database.
  2. Receive the response from the server in JavaScript and remove the corresponding row from the HTML table.

Make a file delete-user.php

<?php
include "db.php";

$id = $_POST['id'];

$stmt = $pdo->prepare('DELETE FROM users WHERE id = ?');
$result = $stmt->execute([$id]);

if ($result) {
  echo json_encode(['id' => $id]);
} else {
  echo 'Error deleting user';
}

Make a js script crud.js that added in index.php

$(document).ready(function() {
  var usersTableTbody = $('#usersTable tbody');
  var createUserForm = $('#createUserForm');
  var updateUserForm = $('#updateUserForm');
  var deleteUserForm = $('#deleteUserForm');

  // Read Operation
  $.ajax({
    url: 'get-users.php',
    type: 'GET',
    success: function(response) {
      // Append the users to the table
      usersTableTbody.append(response);
    },
    error: function(xhr, status, error) {
      console.error(error);
    }
  });
 
  // Create Operation
  $(document).on('submit', '#createUserForm', function(event) {
    event.preventDefault();
    var formData = $(this).serialize();

    $.ajax({
      url: 'create-user.php',
      type: 'POST',
      data: formData,
      success: function(response) {
        // Append the new user to the table
        usersTableTbody.append(response);

        // Clear the input fields
        createUserForm[0].reset();
      },
      error: function(xhr, status, error) {
        console.error(error);
      }
    });
  });

  // Edit Operation
  $(document).on('click', '.editBtn', function(event) {
    event.preventDefault();
    var userId = $(this).data('id');
    var userName = $(this).data('name');
    var userEmail = $(this).data('email');

    // Set the form values
    updateUserForm.find('input[name="id"]').val(userId);
    updateUserForm.find('input[name="name"]').val(userName);
    updateUserForm.find('input[name="email"]').val(userEmail);
  });

  $(document).on('submit', '#updateUserForm', function(event) {
    event.preventDefault();
    var formData = $(this).serialize();

    $.ajax({
      url: 'update-user.php',
      type: 'POST',
      data: formData,
      success: function(data) {
        var response = JSON.parse(data);        
        // Find the row corresponding to the updated user
        var row = usersTableTbody.find('tr[data-id="' + response.id + '"]');

        // Update the name and email columns
        row.find('.name').text(response.name);
        row.find('.email').text(response.email);

        // Clear the input fields
        updateUserForm[0].reset();
      },
      error: function(xhr, status, error) {
        console.error(error);
      }
    });
  });

  // Delete Operation
  $(document).on('submit', '#deleteUserForm', function(event) {
    event.preventDefault();
    var formData = $(this).serialize();

    $.ajax({
      url: 'delete-user.php',
      type: 'POST',
      data: formData,
      success: function(data) {
        var response = JSON.parse(data);   
        // Remove the row corresponding to the deleted user
        usersTableTbody.find('tr[data-id="' + response.id + '"]').remove();

        // Clear the input fields
        deleteUserForm[0].reset();
      },
      error: function(xhr, status, error) {
        console.error(error);
      }
    });
  });
});
Output Image:
ajax crud in php

Overall, performing CRUD operations using jQuery AJAX allows for more efficient and streamlined interaction with databases, reducing the need for page refreshes and providing a better user experience.


Our Recommendation

Avatar of Akhand Pratap Singh

Akhand Pratap Singh

Greetings and a warm welcome to my website! I am Akhand Pratap Singh, a dedicated professional web developer and passionate blogger.

Related Post

Leave a Comment





Newsletter

Subscribe for latest updates

We don't spam.

Loading

Categories