PHP CRUD Project Step-by-Step with MariaDB and PDO
A PHP CRUD project is a practical way for BCA, BTech and computer science students to learn backend development. CRUD stands for Create, Read, Update and Delete. These four operations are used in student management systems, inventory applications, customer records and many other business applications.
In this tutorial, we will plan a simple Student Management System using PHP 8, MariaDB and PDO. The examples are intended for a local development environment, not direct deployment to a production server.
1. Project Objective
The application will manage student records containing a name, email address and course.
The four main features are:
- Create a new student record.
- Read and display existing students.
- Update a student's information.
- Delete a record after confirmation.
A complete application should also include validation, authentication, authorization, error handling and CSRF protection.
2. Required Technologies
Install PHP 8 or later, MariaDB or MySQL, a code editor and a local web server.
You should understand basic HTML forms, PHP variables, arrays, functions and SQL queries.
Use a separate development database. Never experiment with DELETE or DROP commands against a live client database.
3. Suggested Project Structure
Organize the application into separate files:
student-crud/
public/
index.php
create.php
edit.php
delete.php
src/
database.php
StudentRepository.php
templates/
form.php
This structure separates database operations from the user interface.
For a production application, sensitive configuration files should remain outside the public document root.
4. Database Design
Create a local database named student_crud_demo and a students table.
Example SQL:
CREATE DATABASE student_crud_demo
CHARACTER SET utf8mb4;
USE student_crud_demo;
CREATE TABLE students (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(120) NOT NULL,
email VARCHAR(190) NOT NULL UNIQUE,
course VARCHAR(120) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
The primary key identifies each student. The unique email constraint prevents duplicate email addresses.
This schema is for a new local demonstration database only.
5. Connecting PHP to MariaDB Using PDO
PDO provides a consistent interface for database access and supports prepared statements.
Example:
<?php
$dsn = 'mysql:host=127.0.0.1;dbname=student_crud_demo;charset=utf8mb4';
$pdo = new PDO(
$dsn,
getenv('DEMO_DB_USER'),
getenv('DEMO_DB_PASSWORD'),
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]
);
Set DEMO_DB_USER and DEMO_DB_PASSWORD in your local environment. Do not hardcode production credentials in source files.
6. Create Operation
The Create operation inserts a student into the database.
First, collect name, email and course from an HTML form using the POST method.
Validate the input on the server before inserting it.
Example:
$name = trim($_POST['name'] ?? '');
$email = trim($_POST['email'] ?? '');
$course = trim($_POST['course'] ?? '');
if ($name === '' || $course === '') {
throw new InvalidArgumentException('Required fields are missing');
}
if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
throw new InvalidArgumentException('Invalid email address');
}
$statement = $pdo->prepare(
'INSERT INTO students (name, email, course)
VALUES (:name, :email, :course)'
);
$statement->execute([
'name' => $name,
'email' => $email,
'course' => $course,
]);
Prepared statements keep user values separate from SQL syntax.
Also enforce input length limits and handle duplicate email errors.
7. Read Operation
The Read operation retrieves existing records.
Example:
$statement = $pdo->query(
'SELECT id, name, email, course
FROM students
ORDER BY id DESC'
);
$students = $statement->fetchAll();
Display the returned records in an HTML table.
Escape output before rendering user-supplied text:
echo htmlspecialchars(
$student['name'],
ENT_QUOTES,
'UTF-8'
);
Output escaping helps prevent cross-site scripting vulnerabilities.
8. Update Operation
The Update operation modifies an existing student.
First, retrieve the record by its numeric ID. Display its current values in an edit form.
After validating the submitted data, run a parameterized UPDATE query.
Example:
$statement = $pdo->prepare(
'UPDATE students
SET name = :name,
email = :email,
course = :course
WHERE id = :id'
);
$statement->execute([
'name' => $name,
'email' => $email,
'course' => $course,
'id' => $studentId,
]);
Check that the user is authorized to edit the record. Never trust a record ID simply because it appears in a form.
9. Delete Operation
The Delete operation removes a record.
For this demonstration, deletion should require a POST request, a valid CSRF token and explicit user confirmation.
Example repository query:
$statement = $pdo->prepare(
'DELETE FROM students WHERE id = :id'
);
$statement->execute([
'id' => $studentId,
]);
Do not execute this example against production data.
In real applications, consider soft deletion, audit logging and retention requirements instead of immediately deleting records.
10. Form Validation
Frontend validation improves the user experience, but backend validation is essential.
Check required fields, valid email format, permitted lengths and numeric identifiers.
A secure application should also protect state-changing requests with CSRF tokens.
For public-facing applications, add authentication, authorization and appropriate rate limiting.
11. Error Handling
Database operations can fail because of duplicate keys, connection problems or invalid data.
Catch exceptions at an appropriate application boundary.
Log technical details securely, but show users a simple error message without exposing SQL statements or database credentials.
For example, a duplicate email address can produce the message:
This email address is already registered.
12. Pagination and Search
A small project may display all records, but larger applications should use pagination.
Example:
SELECT id, name, email, course
FROM students
ORDER BY id DESC
LIMIT 20 OFFSET 0;
Use validated numeric pagination values.
You can also add a search field for student names or courses. Build the search query using prepared statements.
13. Testing Checklist
Before considering the project complete, test the following:
- A valid student can be created.
- Required fields cannot be empty.
- Invalid email addresses are rejected.
- Duplicate email addresses are handled.
- Existing records appear in the listing.
- A student can be updated correctly.
- Unauthorized modifications are blocked.
- Deletion requires confirmation and CSRF protection.
- User-supplied HTML is escaped in the output.
- Database errors do not expose sensitive details.
Test everything against a local demonstration database.
14. Common Student Project Improvements
After completing basic CRUD, add login and role-based access control.
You can also implement search, pagination, CSV export, activity logs and dashboard statistics.
A reusable StudentRepository class helps organize database operations.
For a larger project, consider using a PHP framework with routing, middleware, migrations and automated tests.
15. Viva and Interview Questions
What does CRUD mean?
CRUD stands for Create, Read, Update and Delete.
Why use PDO?
PDO provides database access through a consistent interface and supports parameterized queries.
What is a prepared statement?
A prepared statement separates SQL structure from supplied parameter values.
Why is server-side validation necessary?
Users can bypass browser validation, so the server must independently verify incoming data.
What is the purpose of a primary key?
A primary key uniquely identifies a record within a table.
What is CSRF protection?
CSRF protection helps prevent unauthorized state-changing requests initiated through a user's authenticated browser session.
Conclusion
A PHP CRUD project teaches essential backend development skills, including database design, forms, validation, SQL queries and secure data handling.
Begin with a small local project, test each operation independently and gradually improve the architecture. These skills form a useful foundation for student management systems, inventory software and larger PHP applications.