AJAX for Database Operations

This tutorial is created to implement CRUD operations via AJAX (Asynchronous JavaScript and XML). Ajax is used for rich-internet applications that emulate the responsiveness and complex user interfaces.

It is a set of inter-related technologies like JavaScript, DOM, XML, HTML/XHTML, CSS, XMLHttpRequest. It helps to send and receive data asynchronously without reloading the web page and it's faster.

  1. Create the DB(Database)

    Create the DB and run the following SQL query to create a table student inside the MySQL database.

    SQL query to create a table named student

    CREATE TABLE student (
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(255) NOT NULL,
        username varchar(255) NOT NULL,
        password varchar(255) NOT NULL
    );

    Now, It's time to create the DB config.php file as shown below

    config.php

    <?php
    define('DB_SERVER', 'localhost');
    define('DB_USERNAME', '');
    define('DB_PASSWORD', '');
    define('DB_NAME', 'ajax_application');
    
    $link = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
    
    if ($link->connect_errno) {
        printf("Connect failed: %s\n", $link->connect_error);
        exit();
    }
    
  2. Creating the Pages

    You need to create the following files to run the Ajax CRUD application

    1. index.php
    2. add.php
    3. delete.php
    4. edit.php
    5. update.php

    index.php

    <!DOCTYPE html>
    <html>
    <head>
        <title>AJAX for Database Operations | bishrulhaq.com</title>
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css"
              integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
    </head>
    <body>
    <div class="container" style="margin-top: 20px;">
        <div class="card">
            <div class="card-body">
                <h1 class="text-center">AJAX for Database Operations</h1>
    
            </div>
        </div>
    </div>
    <div class="container" style="margin-top: 25px;">
        <div class="row">
            <div class="col-12">
                <div class="text-center">
                    <h5>Add New Record</h5>
                </div>
                <div class="form-inline" style="margin-bottom: 25px;">
                    <div class="form-group col-3">
                        <input type="text" name="name" id="name" placeholder="Name" class="form-control"/>
                    </div>
                    <div class="form-group col-3">
                        <input type="text" name="username" id="username" placeholder="Username" class="form-control"/>
                    </div>
                    <div class="form-group col-3">
                        <input type="text" id="password" name="password" placeholder="Password" class="form-control"
                               required/>
                    </div>
                    <div class="form-group col-3">
                        <button type="button" class="btn btn-primary" id="add" name="add" onclick="addRecord()">Add Record
                        </button>
                    </div>
                </div>
            </div>
        </div>
    
        <div class="row">
            <div class="col-12">
                <div id="link-edit">
    
                </div>
            </div>
        </div>
    
        <div class="row">
            <div class="col-12">
                <div id="records_content"></div>
                <div class="col-12" id="table_content">
                </div>
            </div>
        </div>
    </div>
    <script src="https://code.jquery.com/jquery-3.4.1.min.js" integrity="sha256-CSXorXvZcTkaix6Yvo6HppcZGetbYMGWSFlBw8HfCJo=" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js" integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
    <script>
    
        $(document).ready(function () {
    
            $.get("view.php", function (data) {
                $("#table_content").html(data);
            });
        });
    
        function addRecord() {
    
            var name = $('#name').val();
            var username = $('#username').val();
            var password = $('#password').val();
    
            $.ajax({
                url: "add.php",
                type: "POST",
                data: {name: name, username: username, password: password},
                success: function (data, status, xhr) {
                    $('#name').val('');
                    $('#username').val('');
                    $('#password').val('');
                    $.get("view.php", function (html) {
                        $("#table_content").html(html);
                    });
                    $('#records_content').fadeOut(1100).html(data);
                },
                error: function () {
                    $('#records_content').fadeIn(3000).html('<div class="text-center">Error!</div>');
                },
                beforeSend: function () {
                    $('#records_content').fadeOut(700).html('<div class="text-center">Loading...</div>');
                }
            });
        }
    
    </script>
    </body>
    </html>
    

    In this step, Let's create the "add.php" and add the following code inside it.

    add.php

    <?php
    require_once('config.php');
    
    $name = $_POST['name'];
    $username = $_POST['username'];
    $password = $_POST['password'];
    
    if (!empty($name) && !empty($username) && !empty($password)) {
        $query = $con->prepare("INSERT into student (name, username, password) VALUES (?,?,?)");
    
        $query->bind_param('sss', $name, $username, $password);
    
        $result = $query->execute();
    
        if ($result) {
            echo '
    1 Record Added!
    '; } else { exit(mysqli_error($con)); } }

    Create "delete.php" and add the following code inside it. Which will help to delete particular user by passing the ID.

    delete.php

    <?php
    require_once('config.php');
    
    $id = $_POST['id'];
    
    if (empty($id)) {
        echo '
    Error!
    '; } $query = $con->prepare("DELETE FROM student where id= ?"); $query->bind_param('i', $id); $result = $query->execute(); if ($result) { echo '
    1 Record Deleted!
    '; } else { exit(mysqli_error($con)); }

    Now, Let's Create the "edit.php" and add the following code inside it.

    edit.php

    <?php
    
    require_once('config.php');
    $id = $_POST['id'];
    
    if (empty($id)) { ?>
        <div class="text-center">No records found under this selection <a href="#" onclick="$('#link-add').hide();$('#show-add').show(700);">Hidethis</a></div>
        <?php die();
    }
    
    $query = "SELECT * FROM student where id = ?";
    if ($stmt = $con->prepare($query)) {
        $stmt->bind_param("i", $id);
        if ($stmt->execute()) {
            $result = $stmt->get_result();
            if ($result->num_rows > 0) {
                while ($row = $result->fetch_assoc()) {
        ?>
        <div class="form-inline" id="edit-data" style="margin-bottom: 20px;">
            <div class="form-group col-3">
                <input type="text" name="student_name" id="student_name" value="<?php echo $row['name']; ?>"
                       placeholder="Name" class="form-control" required/>
            </div>
            <div class="form-group col-3">
                <input type="text" name="student_username" id="student_username" placeholder="Username" class="form-control"
                       value="<?php echo $row['username']; ?>" required/>
            </div>
            <div class="form-group col-3">
                <input type="text" id="student_password" name="student_password" placeholder="Password" class="form-control"
                       value="<?php echo $row['password']; ?>" required/>
            </div>
            <div class="form-group col-3">
                <button type="button" class="btn btn-primary update" id="<?php echo $row['id']; ?>" name="update"
                        onclick="updateRecord(<?php echo $row['id']; ?>)">Update Record
                </button>
                <button type="button" href="javascript:void(0);" class="btn btn-default" id="cancel"
                        onclick="$('#link-edit').slideUp(400);$('#show-add').show(700);">Cancel
                </button>
            </div>
        </div>
    <?php }}}} ?>
    
    <script type="text/javascript">
        function updateRecord(student_id) {
    
            var id = student_id;
            var name = $('#student_name').val();
            var username = $('#student_username').val();
            var password = $('#student_password').val();
    
            $.ajax({
                url: "update.php",
                type: "POST",
                data: {id: id, name: name, username: username, password: password},
                success: function (data, status, xhr) {
                    $('#name').val('');
                    $('#username').val('');
                    $('#password').val('');
                    $('#records_content').fadeOut(1100).html(data);
                    $.get("view.php", function (html) {
                        $("#table_content").html(html);
                    });
                    $('#records_content').fadeOut(1100).html(data);
                },
                complete: function () {
                    $('#link-edit').hide();
                    $('#show-add').show(700);
                }
            });
        }
    </script>

    Create "update.php" and add the following code inside it. Which will help to update the particular user data

    update.php

    <?php
    require_once('config.php');
    
    $id = $_POST['id'];
    $name = $_POST['name'];
    $username = $_POST['username'];
    $password = $_POST['password'];
    
    if (!empty($name) && !empty($username) && !empty($password) && !empty($id)) {
        $query = "UPDATE student SET name = ?, username = ?, password = ? WHERE id = ?";
        if ($stmt = $con->prepare($query)) {
            $stmt->bind_param("sssi", $name, $username, $password, $id);
            $stmt->execute();
            if ($stmt->error) {
                echo '
    ' . $stmt->error . '
    '; } else { echo '
    1 Record updated!
    '; } } } else { echo '
    error while updating record
    '; }

Hope this tutorial helped you! Feel free to drop your opinion at the comment section.