← Back to Blog

Building a Complete CRUD Application with PHP and MySQL: A Step-by-Step Tutorial

CRUD (Create, Read, Update, Delete) operations form the backbone of most web applications. Whether you are building a student management system, a content management platform, or an inventory tracking tool, understanding how to implement CRUD functionality securely is essential. In this tutorial, I walk through building a complete CRUD application using PHP and MySQL, with a focus on security best practices including prepared statements to prevent SQL injection.

Prerequisites and Setup

Before we begin, ensure you have a local development environment with PHP 7.4+ and MySQL installed. I recommend using XAMPP or WAMP for local development. You will also need a code editor like VS Code. We will build a simple student records management system that allows users to add, view, edit, and delete student information.

Database Design

Start by creating the database and table. Our students table will include id (auto-increment primary key), name, email, phone, course, and created_at fields. The SQL is straightforward: CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE, phone VARCHAR(20), course VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP). Using prepared statements with this structure ensures that user input is never directly concatenated into SQL queries.

Database Connection with PDO

I use PHP Data Objects (PDO) for database connections because it provides a consistent interface for different database systems and supports prepared statements natively. Create a config.php file with the database credentials and a db.php file that returns a PDO instance. The connection should use the following options: set error mode to exceptions for better error handling, set default fetch mode to associative arrays, and set character set to UTF-8. Here is the connection code: new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);

Implementing Create (C)

The create operation inserts new records into the database. Create a create.php file with an HTML form that collects student data. When the form is submitted via POST, use a prepared statement to insert the data: $stmt = $pdo->prepare("INSERT INTO students (name, email, phone, course) VALUES (:name, :email, :phone, :course)"); then bind parameters and execute. Always validate and sanitize input data before inserting. Use filter_var for email validation and trim/strip_tags for other text fields.

Implementing Read (R)

The read operation retrieves and displays records. Create an index.php file that fetches all students: $stmt = $pdo->query("SELECT * FROM students ORDER BY created_at DESC"); $students = $stmt->fetchAll(); Then loop through the results in HTML to display them in a table with columns for name, email, phone, course, and action buttons. Add a search feature using a LIKE query with a prepared statement to filter results based on user input.

Implementing Update (U)

The update operation allows editing existing records. Create an edit.php file that accepts an ID parameter, fetches the corresponding record: $stmt = $pdo->prepare("SELECT * FROM students WHERE id = :id"); $stmt->execute(['id' => $id]); $student = $stmt->fetch(); Display a form pre-filled with existing data. On submission, use an UPDATE prepared statement: $stmt = $pdo->prepare("UPDATE students SET name = :name, email = :email, phone = :phone, course = :course WHERE id = :id");

Implementing Delete (D)

The delete operation removes records. Add a delete.php file that accepts an ID and confirms the deletion before executing: $stmt = $pdo->prepare("DELETE FROM students WHERE id = :id"); Use a confirmation dialog (JavaScript confirm) to prevent accidental deletions. After successful deletion, redirect back to the main list with a success message.

Security Best Practices

Throughout this tutorial, we have used several security best practices. Prepared statements prevent SQL injection by separating SQL logic from data. Input validation ensures data integrity and prevents malicious input. Output escaping using htmlspecialchars() prevents XSS attacks when displaying user-submitted data. Password hashing (if you add authentication) should use PHP's password_hash() and password_verify(). HTTPS should be enforced in production. Regular security audits and updates keep the application protected against emerging threats.