Tag: ajax

Categories
JS

AJAX for Database Operations

AJAX for Database

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
);
Code language: PHP (php)

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();
}
Code language: HTML, XML (xml)
  1. 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>
Code language: HTML, XML (xml)

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));
    }

}
Code language: HTML, XML (xml)

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));
}
Code language: HTML, XML (xml)

Now, Let’s Create the “edit.php” and add the following code inside it. 

<?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>
Code language: HTML, XML (xml)

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';
}
Code language: HTML, XML (xml)

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

Github

Latest Posts