CRUD Operations using PHP, MySQL, APACHE on LINUX

Demonstration of CRUD Operations in Database using PHP (PDO and Prepared Statements)

Title: Mastering CRUD Operations in PHP with PDO and Prepared Statements

Introduction:

In this blog post, we will explore how to perform CRUD (Create, Read, Update, Delete) operations in a database using PHP, focusing on the PDO (PHP Data Objects) extension and prepared statements. We'll use a hypothetical "Student Management System" as our dataset to illustrate each operation.

Step 1: Start the MySQL Server by command: sudo service mysql start

Step 2: Create User and Password for the database:

            1. Use Command: sudo mysql --user=root mysql

            2. Use Query to create user and password to access database:

                CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password';

            3. Grant all privileges to the user by query:

                GRANT ALL PRIVILEGES on *.* TO 'username'@'localhost' WITH GRANT OPTION;

Step 3: Defining the database structure:

            1. Open the Terminal CTRL + ALT + T, then enter command: mysql -u username -p

            2. Enter the password for get into mysql database.

            3. Create a database name 'student_management' using query:

                CREATE DATABASE student_management;

            4. Use the database by query:

                USE student_management;

            5. Creating a students table in the database by query:

                CREATE TABLE students (id INT AUTO_INCREAMENT PRIMARY KEY,

                name VARCHAR(255) NOT NULL,

                age INT, grade CHAR(1));

Step 4: Connecting to the Database with PDO:

            1. Make a 'crud' named folder in the directory '/var/www/html/'

            2. Open text editor and write the code here and save it to the 'crud' folder naming 'db_config.php'

Step 5: Create Operation (INSERT):

            1. Open new file in the text editor and write the code here and save it to 'crud' folder naming 'create.php'.

create.php render:

Step 6: Read Operation (SELECT):

           1. Open new file in the text editor and write the code here and save it to 'crud' folder naming 'read.php'.

            - Retrieve and display student records from the database.

            - Use prepared statements to prevent SQL injection.

read.php render:

Step 7. Update Operation (UPDATE):

            1. Open new file in the text editor and write the code here and save it to 'crud' folder naming 'update.php'.

            - Modify existing student records.

           - Implement prepared statements for safe data modification.

update.php render:

Step 8. Delete Operation (DELETE):

            1. Open new file in the text editor and write the code here and save it to 'crud' folder naming 'delete.php'.

            - Remove a student record from the database.

            - Employ prepared statements to ensure secure data deletion.

delete.php render:

Step 9. Index Page:

            1. Open new file in the text editor and write the code here and save it to 'crud' folder naming 'index.php'.

            2. Link all pages to perform CRUD Operations:

Final Step:

                1. Open Web Browser, go to 'http:/localhost/crud/index.php' to run the script to Perform CRUD Operations on Apache server using MySQL.

Blog by

KHUSHI