Recent Posts

GraphQL in Laravel Using Lighthouse

2 months ago

Sanitize and Create URLs via Laravel command

3 months ago

Short Summary : The Obstacle is the way

2 years ago

CRUD Operations In Laravel 8

3 years ago

Installing PHP on Windows

3 years ago
  • Calculating...
  • 5 years ago
  • 3.3K Views
  • Archived This is an Archived post.
    The content within this may not be used or replaced with newer versions.

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();
}
  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="sha384ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384JZR6Spejh4U02d8jOt6vLEHfe/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.

<?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';
}
 

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

 

Tags:
programming
Share:

Related Post

JavaScript

JSON in JavaScript

This tutorial is created to provide you with an introduction to working with JSON (JavaScript Object Notation) in JavaScript. JSON is mostly used with AJAX (Asynchronous JavaScript) and XML.

  • 5 years ago
JavaScript

How to Get, Set and Delete Div Background Image using jQuery?

This tutorial is created to Set, Get and Delete background image. In this tutorial, You’ll learn to Get, Set and Remove the background image using jQuery.

  • 5 years ago