Ajax Live Search (CRUD Operation) in PHP

 <?php

$servername = "localhost";
$username = "root";
$password = "";
$database = "test";

//Create Connection
$conn = new mysqli($servername, $username, $password, $database);

//Check Connection
if ($conn->connect_error) {
    die("Connection failed : " . $conn->connect_error);
}
Above is mysqli_conn.php File which is included in below Files.







Below is insert-data.php File.
<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>PHP & Ajax CRUD</title>
    <link rel="stylesheet" href="css/style.css">
</head>

<body>
    <table id="main" border="0" cellspacing="0">
        <tr>
            <td id="header">
                <h1>PHP & Ajax</h1>

                <div id="search-bar">
                    <label>Search :</label>
                    <input type="text" id="search" autocomplete="off">
                </div>
            </td>
        </tr>
        <tr>
            <td id="table-form">
                <form id="addForm">
                    Student Name : <input type="text" id="name"><br>&nbsp;&nbsp;&nbsp;&nbsp;
                    Age : <input type="text" id="age" style="margin-left: 62px;"><br>
                    City : <input type="text" id="city" style="margin-left: 88px;"><br><br>
                    <input type="submit" id="save-button" value="Save"><br>
                </form>
            </td>
        </tr>
        <tr>
            <td id="table-data">
            </td>
        </tr>
    </table>
    <div id="error-message"></div>
    <div id="success-message"></div>
    <div id="modal">
        <div id="modal-form">
            <h2>Edit Form</h2>
            <table cellpadding="10px" width="100%">
            </table>
            <div id="close-btn">X</div>
        </div>
    </div>



    <script type="text/javascript" src="js/jquery-3.7.1.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function() {
            //Load Table Records
            function loadTable() {
                $.ajax({
                    url: "ajax-load.php",
                    type: "POST",
                    success: function(data) {
                        $("#table-data").html(data);
                    }
                });
            }
            loadTable(); //Load Table records on Page Load

            //Insert New Records
            $("#save-button").on("click", function(e) {
                e.preventDefault();
                var name = $("#name").val();
                var age = $("#age").val();
                var city = $("#city").val();

                if (name == "" || age == "" || city == "") {
                    $("#error-message").html("All Fields are required.").slideDown();
                    $("#success-message").slideUp();
                } else {
                    $.ajax({
                        url: "ajax-insert.php",
                        type: "POST",
                        data: {
                            student_name: name,
                            student_age: age,
                            student_city: city
                        },
                        success: function(data) {
                            if (data == 1) {
                                loadTable();
                                $("#addForm").trigger("reset");
                                $("#success-message").html("Data Inserted Successfully.").slideDown();
                                $("#error-message").slideUp();
                            } else {
                                $("#error-message").html("Can't Save Record.").slideDown();
                                $("#success-message").slideUp();
                            }

                        }
                    });
                }
            });

            //Delete Records
            $(document).on("click", ".delete-btn", function() {
                if (confirm("Do you really want to delete this record ?")) {
                    var studentId = $(this).data("id");
                    var element = this;

                    $.ajax({
                        url: "ajax-delete.php",
                        type: "POST",
                        data: {
                            id: studentId
                        },
                        success: function(data) {
                            if (data == 1) {
                                $(element).closest("tr").fadeOut();
                            } else {
                                $("#error-message").html("Can't Delete Record.").slideDown();
                                $("$success-message").slideUp();
                            }
                        }
                    });
                }
            });

            //Show Modal Box
            $(document).on("click", ".edit-btn", function() {
                $("#modal").show();
                var studentId = $(this).data("eid");

                $.ajax({
                    url: "load-update-form.php",
                    type: "POST",
                    data: {
                        id: studentId
                    },
                    success: function(data) {
                        $("#modal-form table").html(data);
                    }
                });
            });

            //Hide Modal Box
            $("#close-btn").on("click", function() {
                $("#modal").hide();
            });

            //Save Update Form
            $(document).on("click", "#edit-submit", function() {
                var sid = $("#edit-id").val();
                var sname = $("#edit-name").val();
                var sage = $("#edit-age").val();
                var scity = $("#edit-city").val();

                $.ajax({
                    url: "ajax-update-form.php",
                    type: "POST",
                    data: {
                        stuid: sid,
                        stuname: sname,
                        stuage: sage,
                        stucity: scity
                    },
                    success: function(data) {
                        if (data == 1) {
                            $("#modal").hide();
                            loadTable();
                        }
                    }
                });
            });

            //Live Search
            $("#search").on("keyup", function() {
                var search_term = $(this).val();

                $.ajax({
                    url: "ajax-live-search.php",
                    type: "POST",
                    data: {
                        search: search_term
                    },
                    success: function(data) {
                        $("#table-data").html(data);
                    }
                });
            });
        });
    </script>
</body>

</html>







Below is ajax-load.php File.
<?php
include("mysqli_conn.php");

$sql = "SELECT * FROM students";
$result = $conn->query($sql);
$output = "";

if ($result->num_rows > 0) {
    $output = "<table border='1' width='100%' cellspacing='0' cellpadding='10px'>
                <tr>
                    <th width='50px'>Id</th>
                    <th>Student Name</th>
                    <th>Age</th>
                    <th>City</th>
                    <th width='90px'>Edit</th>
                    <th width='90px'>Delete</th>
                </tr>";
    while ($row = $result->fetch_assoc()) {
        $output .= "<tr><td>{$row['id']}</td><td>{$row['student_name']}</td><td>{$row['age']}</td><td>{$row['city']}</td><td align='center'><button class='edit-btn' data-eid='{$row['id']}'>Edit</button></td><td align='center'><button class='delete-btn' data-id='{$row['id']}'>Delete</button></td></tr>";
    }
    $output .= "</table>";
    $conn->close();
    echo $output;
} else {
    echo "<h2>No Record Found.</h2>";
}







Below is ajax-insert.php File.
<?php

$stu_name = $_POST["student_name"];
$stu_age = $_POST["student_age"];
$stu_city = $_POST["student_city"];

include("mysqli_conn.php");

$sql = "INSERT INTO students(student_name, age, city) VALUES('{$stu_name}','{$stu_age}','{$stu_city}')";

if ($conn->query($sql) == TRUE) {
    echo 1;
} else {
    echo 0;
}







Below is show-data.php File.
<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <script src="jquery-3.7.1.min.js"></script>
</head>

<body>
    <table id="main" border="0" cellspacing="0">
        <tr>
            <td id="header">
                <h1>PHP with Ajax</h1>
            </td>
        </tr>
        <tr>
            <td id="table-load">
                <input type="button" id="load-button" value="Load Data"><br><br>
            </td>
        </tr>
        <tr>
            <td id="table-data">
            </td>
        </tr>
    </table>
    <script type="text/javascript" src="js/jquery.js"></script>
    <!-- Whenever we use Ajax, there is no need to use <Form> and
     also no need to use method "GET" or "POST" -->
    <script type="text/javascript">
        $(document).ready(function() {
            $("#load-button").on("click", function(e) {
                $.ajax({
                    url: "ajax-load.php",
                    type: "POST",
                    success: function(data) {
                        $("#table-data").html(data);
                    }
                });
            });
        });
    </script>
</body>

</html>







Below is ajax-delete.php File.
<?php

$student_id = $_POST["id"];

include("mysqli_conn.php");

$sql = "DELETE FROM students WHERE id = {$student_id}";

if ($conn->query($sql) == TRUE) {
    echo 1;
} else {
    echo 0;
}







Below is css/style.css File.
body {
    font-family: arial;
    background: #b2bec3;
    padding: 0;
    margin: 0;
}

h1 {
    text-align: center;
    margin: 15px;
}

#main {
    width: 800px;
    margin: 0 auto;
    background: white;
    font-size: 19px;
}

#header {
    background: #f7d794;
}

#table-form {
    background: #55efc4;
    padding: 20px 10px;
}

#table-data {
    padding: 15px;
    height: 500px;
    vertical-align: top;
}

#table-data th {
    background: #74b9ff;
}

#table-data tr:nth-child(odd) {
    background-color: #ecf0f1;
}

#success-message {
    background: #DEF1D8;
    color: green;
    padding: 10px;
    margin: 10px;
    display: none;
    position: absolute;
    right: 15px;
    top: 15px;
}

#error-message {
    background: #EFDCDD;
    color: red;
    padding: 10px;
    margin: 10px;
    display: none;
    position: absolute;
    right: 15px;
    top: 15px;
}

#save-button {
    background-color: #000000;
    color: #fff;
    border-radius: 8px;
    cursor: pointer;
    padding: 10px 30px;
    margin: 0 130px;
}

.delete-btn {
    background: red;
    color: #fff;
    border: 0;
    padding: 5px 20px;
    border-radius: 3px;
    cursor: pointer;
}

.edit-btn {
    background: #27ae60;
    color: #fff;
    border: 0;
    padding: 5px 20px;
    border-radius: 3px;
    cursor: pointer;
}

#modal {
    background: rgba(0, 0, 0, 0.7);
    position: fixed;
    left: 0;
    top: 0;
    width: 100%;
    height: 100%;
    z-index: 100;
    display: none;
}

#modal-form {
    background: #fff;
    width: 40%;
    position: relative;
    top: 20%;
    left: calc(50% - 20%);
    padding: 15px;
    border-radius: 4px;
}

#modal-form h2 {
    /*margin: top right and left bottom*/
    margin: 0 0 15px;
    padding-bottom: 10px;
    border-bottom: 1px solid #000;
}

#close-btn {
    background: #f00;
    color: white;
    width: 30px;
    height: 30px;
    line-height: 30px;
    text-align: center;
    border-radius: 50%;
    position: absolute;
    top: -15px;
    right: -15px;
    cursor: pointer;
}







Below is load-update-form.php File.
<?php

$student_id = $_POST["id"];

include("mysqli_conn.php");

$sql = "SELECT * FROM students WHERE id = {$student_id}";
$result = $conn->query($sql);
$output = "";

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $output .= "<tr>
                    <td>Student Name</td>
                    <td>
                        <input type='text' id='edit-name' value='{$row['student_name']}'>
                        <input type='text' id='edit-id' hidden value='{$row['id']}'>
                    </td>
                </tr>
                <tr>
                    <td>Age</td>
                    <td><input type='text' id='edit-age' value='{$row['age']}'></td>
                </tr>
                <tr>
                    <td>City</td>
                    <td><input type='text' id='edit-city' value='{$row['city']}'></td>
                </tr>
                <tr>
                    <td><input type='submit' id='edit-submit' value='save'></td>
                </tr>";
    }
    $conn->close();
    echo $output;
} else {
    echo "<h2>No Record Found.</h2>";
}







Below is ajax-update-form.php File.
<?php

$stu_id = $_POST["stuid"];
$stu_name = $_POST["stuname"];
$stu_age = $_POST["stuage"];
$stu_city = $_POST["stucity"];

include("mysqli_conn.php");

$sql = "UPDATE students SET student_name = '{$stu_name}', age = '{$stu_age}', city = '{$stu_city}' WHERE id = '{$stu_id}'";

if ($conn->query($sql) == TRUE) {
    echo 1;
} else {
    echo 0;
}







Below is ajax-live-search.php File.
<?php

$search_value = $_POST["search"];

include("mysqli_conn.php");

$sql = "SELECT * FROM students WHERE student_name LIKE '%{$search_value}%' OR city LIKE '%{$search_value}%'";
$result = $conn->query($sql);
$output = "";

if ($result->num_rows > 0) {
    $output .= "<table border='1' width='100%' cellspacing='0' cellpadding='10px'>
                <tr>
                    <th width='60px'>Id</th>
                    <th>Student Name</th>
                    <th>Age</th>
                    <th>City</th>
                    <th width='90px'>Edit</th>
                    <th width='90px'>Delete</th>
                </tr>";
    while ($row = $result->fetch_assoc()) {
        $output .= "<tr><td align='center'>{$row['id']}</td><td>{$row['student_name']}</td><td>{$row['age']}</td><td>{$row['city']}</td><td align='center'><button class='edit-btn' data-eid='{$row['id']}'>Edit</button></td><td align='center'><button class='delete-btn' data-id='{$row['id']}'>Delete</button></td></tr>";
    }
    $output .= "</table>";
    $conn->close();
    echo $output;
} else {
    echo "<h2>No Record Found.</h2>";
}













Comments

Post a Comment

Popular posts from this blog

Logical_Operators

  <?php     $age = 20 ;     $age1 = 25 ;     if ( $age >= 18 && $age <= 21 )     {         echo "You are Eligible<br>" ;     }         if ( $age >= 18 || $age <= 21 )     {         echo "You are Eligible<br>" ;     }     //Below condition is True So that "Not Operator(!)" will return False condition     if (!( $age >= 18 ))     {         echo "You are Eligible<br>" ;     }     //Below condition is False So that "Not Operator(!)" will return True condition     if (!( $age <= 18 ))     {         echo "You are Eligible<br>" ;     }     //If both condition is "True" OR "False" at that time xor will return "False" and if one condition is     //"True" and s...
Read more

SubQuery with EXISTS and NOT EXISTS in PHP

 SELECT with SubQuery Syntax : SELECT columns FROM table1 WHERE column = (SELECT columns FROM table2 WHERE condition); In "personal" table, there are 6 columns named id, name, percentage, age, gender, city. In "personal" table, column name "id" is PRIMARY KEY and column name "city" is FOREIGN KEY. In "courses" table, there are 2 columns named course_id and course_name. In "courses" table, column name "course_id" is PRIMARY KEY. In "city" table, there are 2 columns named cid and cityname. In "city" table, column name "cid" is PRIMARY KEY. If we want to select records from "personal" table who belong to Delhi from "city" table using SubQuery then we can use below shown sql query : SELECT name FROM personal WHERE city = (SELECT cid FROM city WHERE cityname = "Delhi"); If we want to select records from "personal" table who belong to Delhi and Agra from...
Read more

Get Functions

  <?php //There are many Get Functions in PHP as shown below: //get_class, get_parent_class, get_class_methods, get_class_vars, get_object_vars, get_called_class, //get_declared_classes, get_declared_interfaces, get_declared_traits, class_alias class ParentClass {     function name ()     {         echo "Class Name : " . get_class ( $this ) . "<br>" ;   //Print the particualr Class Name         //Print the particular Parent Class Name as shown below:         echo "Parent Class Name : " . get_parent_class ( $this ) . "<br>" ;     } } class ChildClass extends ParentClass {     function __construct ()     {}     function myfunction ()     {}     function myfunction2 ()     {} } $obj = new ChildClass (); $obj -> name (); echo "Class Name is : " . get_class ( $obj ) . "<br>" ; echo "Paren...
Read more