CRUD Application with PHP PDO Ajax, and MySQL Part 1

2 months ago admin PHP

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

Create the database

First, let’s create the database name it as you want for me I gave it ‘php_ajax_crud’ as the name, and this is the only table we need:

                                                    
                                                                                                                
CREATE TABLE `students` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `regist_number` varchar(255) NOT NULL,
  `avg` float NOT NULL,
  `move_next` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `students` (`id`, `name`, `regist_number`, `avg`, `move_next`) VALUES
(2, 'john doe', '5566776', 18, 'Student move to the next '),
(71, 'yaya', '556656', 5, 'Student move to the next '),
(78, 'karl', '556656', 9, 'Student move to the next ');

Connect to database

Next, let’s connect to our database first add a new folder and give it “database” as the name inside the created folder add a new file give it “db.php” as the name inside the created file add the code below:

                                                        
                                                                                                                        
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "php_ajax_crud";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname",$username,$password);
    $conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo "Connection failed: ".$e->getMessage();
}

Create the save student modal

Inside the main project folder add a new folder and name it “public” Inside add a new folder and name it “modals” Inside the modals folder add a new file and name it “studentSaveModal.php” Inside the newly created file let’s add the save student modal here we will use Bootstrap 5 modals.

                                                        
                                                                                                                        
<!-- Modal -->
<div class="modal fade" id="saveStudentModal" tabindex="-1" aria-labelledby="exampleModalLabel" aria-hidden="true">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <h1 class="modal-title fs-5" id="exampleModalLabel">Add new student</h1>
                <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
            </div>
            <form id="saveStudentForm">
                <div class="modal-body">
                    <div class="mb-3">
                        <label for="student_name" class="col-form-label">Name*</label>
                        <input type="text" name="name" required id="student_name"
                            class="form-control"
                            placeholder="Name">
                    </div>
                    <div class="mb-3">
                        <label for="student_register_number" class="col-form-label">Registration Number*</label>
                        <input type="number" name="regist_number" required id="student_register_number"
                            class="form-control"    
                            placeholder="Registration Number">
                    </div>
                    <div class="mb-3">
                        <label for="student_avg" class="col-form-label">Average*</label>
                        <input type="number" name="avg" required id="student_avg"
                            class="form-control"
                            placeholder="Average">
                    </div>
                    <div class="mb-3">
                        <label for="student_move_next" class="col-form-label">Decision*</label>
                        <input type="text" name="move_next" required id="student_move_next"
                            class="form-control"
                            placeholder="Decision">
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="submit" class="btn btn-primary">Submit</button>
                </div>
            </form>
        </div>
    </div>
</div>

Create the update student modal

Inside the folder “modals” add a new file and name it “studentUpdateModal.php” Inside the newly created file let’s add the update student modal.

                                                        
                                                                                                                        
<!-- Modal -->
<div class="modal fade" id="editStudentModal" tabindex="-1" aria-labelledby="exampleModalLabel" aria-hidden="true">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <h1 class="modal-title fs-5" id="exampleModalLabel">Edit new student</h1>
                <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
            </div>
            <form id="editStudentForm">
                <div class="modal-body">
                    <div class="mb-3">
                        <input type="hidden" name="student_id" id="student_id">
                    </div>
                    <div class="mb-3">
                        <label for="name" class="col-form-label">Name*</label>
                        <input type="text" name="name" required id="name"
                            class="form-control"
                            placeholder="Name">
                    </div>
                    <div class="mb-3">
                        <label for="regist_number" class="col-form-label">Registration Number*</label>
                        <input type="number" name="regist_number" required id="regist_number"
                            class="form-control"    
                            placeholder="Registration Number">
                    </div>
                    <div class="mb-3">
                        <label for="avg" class="col-form-label">Average*</label>
                        <input type="number" name="avg" required id="avg"
                            class="form-control"
                            placeholder="Average">
                    </div>
                    <div class="mb-3">
                        <label for="move_next" class="col-form-label">Decision*</label>
                        <input type="text" name="move_next" required id="move_next"
                            class="form-control"
                            placeholder="Decision">
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="submit" class="btn btn-warning">Submit</button>
                </div>
            </form>
        </div>
    </div>
</div>

Store new student

Inside the public folder add a new file and name it “storeStudent.php” Inside we will have the code to store new students inside the database.

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

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

    $stmt = $conn->prepare("INSERT INTO students (name,regist_number,avg,move_next)
        VALUES(:name,:regist_number,:avg,:move_next)");
    
    $added = $stmt->execute([
        ":name" => $name,
        ":regist_number" => $regist_number,
        ":avg" => $avg,
        ":move_next" => $move_next,
    ]);
    
    if($added) {
        $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 created successfully",
            "content" => $content
        ];

        echo json_encode($response);

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

        echo json_encode($response);

        return;
    }
}
?>
Previous Next

Related Tuorials

CRUD Application with PHP PDO Ajax, and MySQL Part 2

In the second part of this tutorial, we will get all the students from the database and display them...

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