CRUD Application with PHP PDO Ajax, and MySQL Part 2

2 months ago admin PHP

In the second part of this tutorial, we will get all the students from the database and display them on the home page, next, we will see how to create update, and delete students.

Update student

Inside the public folder add a new file and name it “updateStudent.php” Inside we will have the code to update an existing student in the database.

                                                    
                                                                                                                
<?php 
include "../database/db.php";

if($_SERVER["REQUEST_METHOD"] === "GET") {
    $student_id = $_GET["id"];

    $stmt = $conn->prepare("SELECT * FROM students WHERE id=:student_id ");

    $stmt->bindParam(":student_id",$student_id,PDO::PARAM_INT);

    $stmt->execute();

    $student = $stmt->fetch(PDO::FETCH_ASSOC);

    $response = [
        "status" => 200,
        "student" => $student
    ];

    echo json_encode($response);

    return;
}

if($_SERVER["REQUEST_METHOD"] === "POST") {
    $student_id = $_POST["student_id"];
    $name = $_POST["name"];
    $regist_number = $_POST["regist_number"];
    $avg = $_POST["avg"];
    $move_next = $_POST["move_next"];

    $stmt = $conn->prepare("UPDATE students set name=:name,regist_number=:regist_number,
        avg=:avg,move_next=:move_next WHERE id=:student_id ");

    $stmt->bindParam(":student_id",$student_id,PDO::PARAM_INT);
    $stmt->bindParam(":name",$name);
    $stmt->bindParam(":regist_number",$regist_number);
    $stmt->bindParam(":avg",$avg);
    $stmt->bindParam(":move_next",$move_next);

    $updated = $stmt->execute();

    if($updated) {
        $content = "";

        $stmt = $conn->prepare("SELECT * FROM students");

        $stmt->execute();

        $students = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $content = "";

        foreach($students as $key => $student) {
            $content .= "<tr>";
            $content .= "<td>".++$key."</td>";
            $content .= "<td>".$student['name']."</td>";
            $content .= "<td>".$student['regist_number']."</td>";
            $content .= "<td>".$student['avg']."</td>";
            $content .= "<td>".$student['move_next']."</td>";
            $content .= '<td>
                <button type="button"
                    value="'.$student['id'].'"
                    class="btn btn-sm btn-warning btn__update" 
                    data-bs-toggle="modal" data-bs-target="#editStudentModal">
                    <i class="fas fa-edit"></i>
                </button>
                <button type="button" 
                    value="'.$student['id'].'"
                    class="btn btn-sm btn-danger btn__delete">
                    <i class="fas fa-trash"></i>
                </button>
            </td>';
            $content .= "</tr>";
        }

        $response = [
            "status" => 200,
            "message" => "Student updated successfully",
            "content" => $content
        ];

        echo json_encode($response);

        return;
    }else {
        $response = [
            "status" => 500,
            "message" => "Student not updated"
        ];

        echo json_encode($response);

        return;
    }
}

Delete student

Inside the public folder add a new file and name it “deleteStudent.php” Inside we will have the code to remove an existing student from the database.

                                                        
                                                                                                                        
<?php 
include "../database/db.php";

if($_SERVER["REQUEST_METHOD"] === "GET") {
    $student_id = $_GET["id"];

    $stmt = $conn->prepare("DELETE FROM students WHERE id=:student_id");

    $stmt->bindParam(":student_id",$student_id,PDO::PARAM_INT);

    $deleted = $stmt->execute();

    if($deleted) {
        $content = "";

        $stmt = $conn->prepare("SELECT * FROM students");

        $stmt->execute();

        $students = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $content = "";

        foreach($students as $key => $student) {
            $content .= "<tr>";
            $content .= "<td>".++$key."</td>";
            $content .= "<td>".$student['name']."</td>";
            $content .= "<td>".$student['regist_number']."</td>";
            $content .= "<td>".$student['avg']."</td>";
            $content .= "<td>".$student['move_next']."</td>";
            $content .= '<td>
                    <button type="button"
                        value="'.$student['id'].'"
                        class="btn btn-sm btn-warning btn__update" 
                        data-bs-toggle="modal" data-bs-target="#editStudentModal">
                        <i class="fas fa-edit"></i>
                    </button>
                    <button type="button" 
                        value="'.$student['id'].'"
                        class="btn btn-sm btn-danger btn__delete">
                        <i class="fas fa-trash"></i>
                    </button>
            </td>';
            $content .= "</tr>";
        }

        $response = [
            "status" => 200,
            "message" => "Student deleted successfully",
            "content" => $content
        ];

        echo json_encode($response);

        return;
    }else {
        $response = [
            "status" => 500,
            "message" => "Student not deleted"
        ];

        echo json_encode($response);

        return;
    }
}

Add the header

Inside the public folder add a new folder and name it “layouts” Inside add a new file and name it “header.php” and add the code below inside.

                                                        
                                                                                                                        
<?php include '../database/db.php'; ?>
<!doctype html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <title>PHP Ajax CRUD App</title>
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">
        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.6.0/css/all.min.css" integrity="sha512-Kc323vGBEqzTmouAECnVceyQqyqdsSiqLQISBL29aUW4U/M7pSPA/gEUZQqv1cwx4OnYxTxve5UMg5GT6L4JJg==" crossorigin="anonymous" referrerpolicy="no-referrer" />
    </head>
    <body class="bg-light">

Add the footer

Inside the “layouts” folder add a new file and name it “footer.php” Inside add the code below.

                                                        
                                                                                                                        
    <script src="https://code.jquery.com/jquery-3.7.1.js" integrity="sha256-eKhayi8LEQwp4NKxN+CfCh+3qOVUtJn3QNZ0TciWLP4=" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous"></script>
    </body>

    </html>

Add the home page

Inside the public folder add a new file and name it “index.php” Inside we will have the code to display all the students with the create update and delete links.


The source code is here.

                                                        
                                                                                                                        
<?php 
include "./layouts/header.php";

$stmt = $conn->prepare("SELECT * FROM students");

$stmt->execute();

$students = $stmt->fetchAll(PDO::FETCH_ASSOC);

//include the modals
include "./modals/studentSaveModal.php";
include "./modals/studentUpdateModal.php";
?>
<div class="container">
    <div class="row my-4">
        <div class="col-md-10 mx-auto">
            <div class="my-3 d-none" id="alert__message">
                <div class="alert alert-success alert-dismissible fade show" role="alert">
                    <strong id="message__content"></strong>
                    <button type="button" class="btn-close"></button>
                </div>
            </div>
            <div class="my-3">
                <button type="button" class="btn btn-primary" 
                    data-bs-toggle="modal" data-bs-target="#saveStudentModal">
                    <i class="fas fa-plus"></i> Add
                </button>
            </div>
            <div class="card">
                <div class="card-body">
                    <table class="table">
                        <thead>
                            <tr>
                                <th>#</th>
                                <th>Name</th>
                                <th>Registration Number</th>
                                <th>Average</th>
                                <th>Decision</th>
                                <th></th>
                            </tr>
                        </thead>
                        <tbody id="bodyContent">
                            <?php foreach($students as $key => $student): ?>
                                <tr>
                                    <td><?php echo $key+=1; ?></td>
                                    <td><?php echo $student['name']; ?></td>
                                    <td><?php echo $student['regist_number']; ?></td>
                                    <td><?php echo $student['avg']; ?></td>
                                    <td><?php echo $student['move_next']; ?></td>
                                    <td>
                                        <button type="button"
                                            value="<?php echo $student['id']; ?>"
                                            class="btn btn-sm btn-warning btn__update" 
                                            data-bs-toggle="modal" data-bs-target="#editStudentModal">
                                            <i class="fas fa-edit"></i>
                                        </button>
                                        <button type="button" 
                                            value="<?php echo $student['id']; ?>"
                                            class="btn btn-sm btn-danger btn__delete">
                                            <i class="fas fa-trash"></i>
                                        </button>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>
<?php include "./layouts/footer.php"; ?>
<script>
    $(document).on('submit','#saveStudentForm',function(e){
        e.preventDefault();
        
        const formData = new FormData(this);

        $.ajax({
            type: "POST",
            url: "storeStudent.php",
            data: formData,
            processData:false,
            contentType:false,
            success: function(response) {
                const res = JSON.parse(response);
                if(res.status == 200) {
                    $('#saveStudentModal').modal("hide");
                    $('#saveStudentForm')[0].reset();
                    $('#bodyContent').html(res.content);
                    $('#message__content').text(res.message);
                    $('#alert__message').removeClass("d-none");
                }else if(res.status == 500) {
                    alert(res.message);
                }else {
                    alert("Something went wrong try again later!");
                }
            }
        })
    });
    $(document).on('click','.btn__update',function(){
        
        const student_id = $(this).val();

        $.ajax({
            type: "GET",
            url: "updateStudent.php?id=" + student_id,
            success: function(response) {
                const res = JSON.parse(response);
                if(res.status == 200) {
                    $('#name').val(res.student.name);
                    $('#regist_number').val(res.student.regist_number);
                    $('#avg').val(res.student.avg);
                    $('#move_next').val(res.student.move_next);
                    $('#student_id').val(res.student.id);
                    $('#studentUpdateModal').modal("show");
                }else if(res.status == 404) {
                    alert(res.message);
                }else {
                    alert("Something went wrong try again later!");
                }
            }
        })
    });
    $(document).on('submit','#editStudentForm',function(e){
        e.preventDefault();
        
        const formData = new FormData(this);

        $.ajax({
            type: "POST",
            url: "updateStudent.php",
            data: formData,
            processData:false,
            contentType:false,
            success: function(response) {
                const res = JSON.parse(response);
                if(res.status == 200) {
                    $('#editStudentModal').modal("hide");
                    $('#editStudentForm')[0].reset();
                    $('#bodyContent').html(res.content);
                    $('#message__content').text(res.message);
                    $('#alert__message').removeClass("d-none");
                }else if(res.status == 500) {
                    alert(res.message);
                }else {
                    alert("Something went wrong try again later!");
                }
            }
        })
    });
    $(document).on('click','.btn__delete',function(){
        if(confirm("are you sure ?")) {
            const student_id = $(this).val();

            $.ajax({
                type: "GET",
                url: "deleteStudent.php?id=" + student_id,
                success: function(response) {
                    const res = JSON.parse(response);
                    if(res.status == 200) {
                        $('#bodyContent').html(res.content);
                        $('#message__content').text(res.message);
                        $('#alert__message').removeClass("d-none");
                    }else if(res.status == 500) {
                        alert(res.message);
                    }else {
                        alert("Something went wrong try again later!");
                    }
                }
            })
        }
    });
    $(document).on('click','.btn-close',function(){
        $('#alert__message').addClass("d-none");
    })
</script>
Previous Next

Related Tuorials

CRUD Application with PHP PDO Ajax, and MySQL Part 1

In this tutorial we will see how to create a crud application with PHP PDO Ajax, and MySQL, the user...

How to Sort Associative Arrays in Descending Order According to the Key Value in PHP

in this lesson, we will see how to sort associative arrays in descending order according to the key...

How to Sort Associative Arrays in Ascending Order According to the Key Value in PHP

in this lesson, we will see how to sort associative arrays in ascending order according to the key v...

How to Sort Associative Arrays in Descending Order According to the Value in PHP

in this lesson, we will see how to sort associative arrays in descending order according to the valu...

How to Sort Associative Arrays in Ascending Order According to the Value in PHP

in this lesson, we will see how to sort associative arrays in ascending order according to the value...

How Do you Sort an Array in Descending Order in PHP

In this lesson, we will see how to sort descending an array in PHP, we will use the rsort() function...

How to Sort Ascending an Array in PHP

In this lesson, we will see how to sort ascending an array in PHP, we will use the sort() function t...

How to Remove a Key and its Value from an Associative Array in PHP

In this lesson, we will see how to remove a key and its value from an associative array in PHP,&nbsp...

How to Modify a Value in an Associative Array in PHP

In this lesson, we will see how to modify a value in an associative array in PHP, an Associative arr...

How to Add an Item to an Associative Array in PHP

In this lesson, we will see how to add an item to an associative array in PHP, an Associative array...