How to Add, Edit and Delete Records Using jQuery, Ajax, PHP and MySQL

admin | Posted on May 13, 2021 |

The tutorial teaches you how to build a simple system where you can easily add new records to a database, view and update the added records in the database or delete the records complete from the database using Ajax/jQuery and PHP. You may also like How to Delete Multiple Records From MySQL Using PHP and How to Create, Edit and Delete File using PHP

Database Connection

<?php
define('DB_SERVER', "localhost");
define('DB_USER', "root");
define('DB_PASS', "");
define('DB_DATABASE', "ieltsmedidb");
$con = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE);
?>

HTML Code

<?php
$select = mysqli_query($con, "SELECT * FROM medical_student");
?>
<table align="center" cellpadding="10" border="1" id="user_table">
    <tr>
        <th>NAME</th>
        <th>AGE</th>
        <th></th>
    </tr>
    <?php
    while ($row = mysqli_fetch_assoc($select)) 
    {
    ?>
        <tr id="row<?php echo $row['student_id']; ?>">
            <td id="name_val<?php echo $row['student_id']; ?>"><?php echo $row['student_name']; ?></td>
            <td id="email_val<?php echo $row['student_id']; ?>"><?php echo $row['email']; ?></td>
            <td>
                <input type='button' class="edit_button" id="edit_button<?php echo $row['student_id']; ?>" value="edit" onclick="edit_row('<?php echo $row['student_id']; ?>');">
                <input type='button' class="save_button" id="save_button<?php echo $row['student_id']; ?>" value="save" onclick="save_row('<?php echo $row['student_id']; ?>');">
                <input type='button' class="delete_button" id="delete_button<?php echo $row['student_id']; ?>" value="delete" onclick="delete_row('<?php echo $row['student_id']; ?>');">
            </td>
        </tr>
    <?php
    }
    ?>
    <tr id="new_row">
        <td><input type="text" id="new_name"></td>
        <td><input type="text" id="email"></td>
        <td><input type="button" value="Insert Row" onclick="insert_row();"></td>
    </tr>
</table>

JQuery Code

<head>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
</head>
<script>
    function edit_row(id)
    {
     var name=document.getElementById("name_val"+id).innerHTML;
     var email=document.getElementById("email_val"+id).innerHTML;

     document.getElementById("name_val"+id).innerHTML="<input type='text' id='name_text"+id+ "' value='" + name + "'>";
                document.getElementById("email_val" + id).innerHTML = "<input type='text' id='email_text" + id + "' value='" + email + "'>";

                document.getElementById("edit_button" + id).style.display = "none";
                document.getElementById("save_button" + id).style.display = "block";
            }

    function save_row(id)
    {
        var name = document.getElementById("name_text" + id).value;
        var email = document.getElementById("email_text" + id).value;

        $.ajax
                ({
                    type: 'post',
                    url: 'modify_records.php',
                    data: {
                        edit_row: 'edit_row',
                        row_id: id,
                        name_val: name,
                        email: email
                    },
                    success: function (response) {
                        if (response == "success")
                        {
                            document.getElementById("name_val" + id).innerHTML = name;
                            document.getElementById("email_val" + id).innerHTML = email;
                            document.getElementById("edit_button" + id).style.display = "block";
                            document.getElementById("save_button" + id).style.display = "none";
                        }
                    }
                });
    }

    function delete_row(id)
    {
        $.ajax
                ({
                    type: 'post',
                    url: 'modify_records.php',
                    data: {
                        delete_row: 'delete_row',
                        row_id: id,
                    },
                    success: function (response) {
                        if (response == "success")
                        {
                            var row = document.getElementById("row" + id);
                            row.parentNode.removeChild(row);
                        }
                    }
                });
    }

    function insert_row()
    {
        var name = document.getElementById("new_name").value;
        var email = document.getElementById("email").value;

        $.ajax
                ({
                    type: 'post',
                    url: 'modify_records.php',
                    data: {
                        insert_row: 'insert_row',
                        name_val: name,
                        email: email
                    },
                    success: function (response) {
                        if (response != "")
                        {
                            var id = response;
                            var table = document.getElementById("user_table");
                            var table_len = (table.rows.length) - 1;
                            var row = table.insertRow(table_len).outerHTML = "<tr id='row" + id + "'><td id='name_val" + id + "'>" + name + "</td><td id='email_val" + id + "'>" + email + "</td><td><input type='button' class='edit_button' id='edit_button" + id + "' value='edit' onclick='edit_row(" + id + ");'/><input type='button' class='save_button' id='save_button" + id + "' value='save' onclick='save_row(" + id + ");'/><input type='button' class='delete_button' id='delete_button" + id + "' value='delete' onclick='delete_row(" + id + ");'/></td></tr>";

                            document.getElementById("new_name").value = "";
                            document.getElementById("email").value = "";
                        }
                    }
                });
    }
</script>

PHP Code (modify_records.php)

<?php
define('DB_SERVER', "localhost");
define('DB_USER', "root");
define('DB_PASS', "");
define('DB_DATABASE', "ieltsmedidb");
$con = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE);



if (isset($_POST['edit_row'])) {
    $row = $_POST['row_id'];
    $name = $_POST['name_val'];
    $email = $_POST['email'];

    mysqli_query($con,"update medical_student set student_name='$name',email='$email' where student_id='$row'");
    echo "success";
    exit();
}

if (isset($_POST['delete_row'])) {
    $row_no = $_POST['row_id'];
    mysqli_query($con,"delete from medical_student where student_id='$row_no'");
    echo "success";
    exit();
}

if (isset($_POST['insert_row'])) {
    $name = $_POST['name_val'];
    $email = $_POST['email'];
    mysqli_query($con,"insert into medical_student(student_name,email) values('$name','$email')");
    echo mysqli_insert_id($con);
    exit();
}

?>

Leave a Reply Cancel reply