Categories
JS

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

Leave a Reply

Your email address will not be published.