CRUD is one of the most fundamental concepts in web application development. CRUD stands for Create, Read, Update, and Delete, representing the four basic operations performed on data stored in a database.
If you are building a PHP-based website, admin panel, CRM, CMS, inventory system, or any database-driven application, you will use CRUD operations regularly.
In this guide, we will build the foundation of a PHP CRUD system using PHP, MySQL, PDO, prepared statements, and soft deletion. The examples are designed to be simple and practical, so you can adapt them to your own projects.
CRUD represents four operations:
- Create – INSERT – Add new records
- Read – SELECT – Retrieve records
- Update – UPDATE – Modify existing records
- Delete – DELETE – Remove records
For example, imagine a `users` table:
users
------------------------------------------------
id | userid | fullname | username | email
------------------------------------------------
1 | U001 | John Doe | john | john@example.com
2 | U002 | Jane Doe | jane | jane@example.comA CRUD application allows an administrator to:
- Create a new user
- View existing users
- Edit user information
- Delete a user
In this article, we will use PDO throughout the examples.
- Creating the Database Connection
- Understanding PDO
- READ – Fetching Records From MySQL
- CREATE – Inserting a New Record
- Checking for Duplicate Usernames
- UPDATE – Updating an Existing Record
- READ – Fetching a Single Record
- DELETE – Removing Records
- Complete CRUD Flow
- Using CRUD With an HTML Form
- Password Handling in CRUD Applications
- Why Prepared Statements Matter
- Handling Database Errors
- CRUD Best Practices
- CRUD in Real-World PHP Applications
1. Creating the Database Connection
The first step in any PHP/MySQL application is establishing a database connection.
A common approach is to create a `config.php` file and include it wherever database access is required.
<?php
define('DB_NAME', '');
define('DB_USER', '');
define('DB_PASSWORD', '');
define('DB_HOST', 'localhost');
$database = DB_NAME;
$hostname = DB_HOST;
$user = DB_USER;
$password = DB_PASSWORD;
global $conn;
$conn = new PDO("mysql:host=$hostname;dbname=$database",$user,$password);
try {
$conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>You would normally replace the empty database constants with your actual database credentials:
define(‘DB_NAME’, ‘mydatabase’);
define(‘DB_USER’, ‘root’);
define(‘DB_PASSWORD’, ‘your_password’);
define(‘DB_HOST’, ‘localhost’);
It is generally better to keep database credentials in a configuration file rather than repeating them throughout individual PHP files.
2. Understanding PDO
PDO stands for PHP Data Objects. It provides a consistent interface for communicating with databases and supports prepared statements. One of the biggest advantages of PDO is that prepared statements help protect your application against SQL injection when used correctly.
For example, instead of directly putting a username into a SQL query:
$query = "SELECT * FROM users WHERE username = '$username'";we can use a parameter:
$stmt = $conn->prepare("SELECT * FROM users WHERE username = :username");
$stmt->execute([':username' => $username]);The value is supplied separately from the SQL statement. This pattern should be preferred for user-supplied values.
3. READ – Fetching Records From MySQL
The Read operation retrieves information from the database.
Suppose we have a `category` table and only want to display records that have not been deleted.
The query can be written as:
$query = "SELECT * FROM category WHERE deleted='0' ORDER BY id DESC";
$stmt = $conn->prepare($query);
$stmt->execute(array());
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
// Display category information here
}Let’s understand what happens here. First, the query selects all records:
SELECT * FROM category
Then:
WHERE deleted=’0′
ensures that only active records are returned.
Finally:
ORDER BY id DESC
displays the newest records first.
The `while` loop processes every returned record:
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { }
Inside the loop, you can access individual columns:
echo $row[‘id’];
echo $row[‘categoryname’];
This is the basic Read operation of CRUD.
4. CREATE – Inserting a New Record
The Create operation adds a new record to the database. For example, suppose we want to create a new user in the `users` table.
A prepared statement can be used:
$stmt = $conn->prepare("INSERT INTO users (userid,fullname,username,email) VALUES(:userid,:fullname,:username,:email)");
$ok = $stmt->execute([
':userid' => $userid,
':fullname' => $fullname,
':username' => $username,
':email' => $email
]);The placeholders such as:
:userid
:fullname
:username
:email
are parameters.
Their values are supplied through the array passed to `execute()`. After execution, `$ok` can be checked:
if ($ok) {
echo “User created successfully.”;
} else {
echo “Unable to create user.”;
}
5. Checking for Duplicate Usernames
Before creating a user, it is often necessary to check whether the username already exists.
For example:
$checkusername = $conn->prepare("SELECT id FROM users WHERE username = :username AND deleted = '0'");
$checkusername->execute([':username' => $username]);
if ($checkusername->rowCount() > 0) {
echo "Username already exists.";
}Here the database searches for an active user with the supplied username. If the number of matching records is greater than zero:
$checkusername->rowCount() > 0
the application knows that the username is already being used.
The insert operation can then be skipped.
For production applications, duplicate prevention should also be enforced at the database level with an appropriate `UNIQUE` constraint. Application-level checking alone can still suffer from race conditions when multiple requests happen simultaneously.
Also Read: Top 20 Advanced SQL Commands You Need To Know
6. UPDATE – Updating an Existing Record
The Update operation modifies an existing database record. For example, the following query updates a user:
$stmt = $conn->prepare("UPDATE users SET fullname = :fullname, email = :email, username = :username, WHERE id = :id LIMIT 1");
$ok = $stmt->execute([
':fullname' => $fullname,
':email' => $email,
':username' => $username,
':id' => $id
]);The important part is:
WHERE id = :id
This tells MySQL which record should be updated.
For example, if:
$id = 10;
then the query updates the user whose ID is `10`.
After executing the query:
if ($ok) {
echo “User updated successfully.”;
}
you can display a success message or redirect the administrator back to the users page.
7. READ – Fetching a Single Record
CRUD applications frequently need to retrieve one particular record. For example, an edit page might receive an ID through the URL:
edit-category.php?id=15
The ID can then be retrieved:
$id = htmlspecialchars($_GET[‘id’]);
The record can be fetched with:
$query = "SELECT * FROM category WHERE deleted='0' AND id=? LIMIT 1";
$stmt = $conn->prepare($query);
$stmt->execute([$id]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);The retrieved information can then be displayed in a form:
<input type=”text” name=”categoryname” value=”<?php echo htmlspecialchars($row[‘categoryname’]); ?>”>
The administrator can modify the value and submit the form to an update script.
Although:
htmlspecialchars()
is useful when outputting data into HTML, it is not a replacement for validation. A better approach is to validate the ID according to what the application expects.
For example:
$id = filter_input(INPUT_GET, ‘id’, FILTER_VALIDATE_INT);
if (!$id) {
die(“Invalid ID.”);
}
Then use the validated value in the prepared statement.
8. DELETE – Removing Records
The fourth CRUD operation is Delete. There are two common approaches.
a) Hard Delete
A hard delete permanently removes the record:
$stmt = $conn->prepare("DELETE FROM category WHERE id = :id");
$stmt->execute([':id' => $id]);Once deleted, the record is normally no longer available in the table. However, many applications should avoid permanently deleting important records.
b) Soft Delete
A very useful alternative is soft deletion. Instead of deleting the record, the application changes a `deleted` column.
For example:
deleted = 0
means the record is active.
And:
deleted = 1
means the record has been deleted.
A soft-delete query could be:
$stmt = $conn->prepare("UPDATE category SET deleted = '1' WHERE id = :id LIMIT 1");
$ok = $stmt->execute([':id' => $id]);The record remains in the database but is excluded from normal queries:
SELECT * FROM category WHERE deleted=’0′
This approach is useful when you may need to restore deleted records later or maintain historical information.
9. Complete CRUD Flow
A typical PHP CRUD application can be structured like this:
- config.php
- index.php
- READ
- add.php
- CREATE
- edit.php
- READ + UPDATE
- delete.php
- DELETE / Soft Delete
10. Using CRUD With an HTML Form
For example, an HTML form for adding a user might look like:
<form method="post" action="add-user.php">
<input type="text" name="fullname" placeholder="Full Name" required>
<input type="text" name="username" placeholder="Username" required>
<input type="email" name="email" placeholder="Email" required>
<button type="submit">Add User</button>
</form>The PHP processing file can then retrieve the submitted values:
$fullname = $_POST[‘fullname’] ?? ”;
$username = $_POST[‘username’] ?? ”;
$email = $_POST[’email’] ?? ”;
These values should be validated before being inserted into the database.
11. Password Handling in CRUD Applications
One important security issue in user CRUD systems is password storage. Passwords should never be stored as plain text.
Instead of:
$password = $_POST[‘password’];
followed by directly storing `$password`, use:
$password = password_hash($_POST[‘password’],PASSWORD_DEFAULT);
When authenticating the user later, use:
password_verify($password,$row[‘password’]);
This is significantly safer than storing plain-text passwords or using outdated hashing approaches such as MD5.
12. Why Prepared Statements Matter
Consider an unsafe query:
$query = “SELECT * FROM users WHERE username = ‘$username'”;
If `$username` comes directly from user input, this pattern can create SQL injection vulnerabilities.
Instead, use:
$stmt = $conn->prepare(“SELECT * FROM users WHERE username = :username “);
$stmt->execute([ ‘:username’ => $username ]);
Prepared statements separate SQL instructions from parameter values. This is one of the most important practices when developing PHP CRUD applications.
13. Handling Database Errors
Because PDO is configured with:
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
database errors can be handled using exceptions.
For example:
try {
$stmt = $conn->prepare(“INSERT INTO category (categoryname) VALUES (:categoryname)“);
$stmt->execute([‘:categoryname’ => $categoryname]);
} catch (PDOException $e) {
echo “Database error.”;
}
In production applications, avoid displaying raw database error messages to visitors. Log detailed errors securely and show users a generic error message.
14. CRUD Best Practices
When building PHP CRUD applications, keep the following practices in mind:
a) Use PDO prepared statements
Avoid constructing SQL queries by concatenating user input.
b) Validate input
Validate IDs, emails, usernames, dates, and other fields according to their expected format.
c) Escape output
When displaying database content inside HTML, use: htmlspecialchars($value, ENT_QUOTES, ‘UTF-8’);
d) Hash passwords
Use: password_hash() and: password_verify()
e) Consider soft deletion
For important records, using a `deleted` field can be safer than permanently removing rows.
f) Add database constraints
For fields such as usernames or emails that must be unique, consider a database-level `UNIQUE` constraint.
g) Use CSRF protection
Forms that create, update, or delete records should normally include CSRF protection, especially in authenticated administration panels.
h) Don’t trust GET parameters
A URL such as: delete.php?id=10 does not mean that `10` is automatically safe or valid. Validate it before using it.
i) Use POST for state-changing operations
Creating, updating, and deleting records should generally use POST requests rather than performing state changes through GET links.
15. CRUD in Real-World PHP Applications
CRUD is not limited to user management.
The same concepts can be used for almost any database entity:
- Users
- Categories
- Products
- Orders
- Customers
- Employees
- Projects
- Tickets
- Blog Posts
- Places
- Invoices
- Tasks
- Inventory
For example, a product management system might have:
products
———————————–
id
name
description
price
stock
status
datetime
lastupdate
deleted
The same four operations apply:
- CREATE → Add Product
- READ → Display Products
- UPDATE → Edit Product
- DELETE → Remove/Archive Product
Once you understand the CRUD pattern, you can reuse it throughout an entire PHP application.
Conclusion
CRUD is the foundation of many PHP and MySQL applications. Using PDO makes it straightforward to communicate with MySQL while taking advantage of prepared statements and parameterized queries.
The examples in this article demonstrate a practical approach using a reusable `config.php`, PDO prepared statements, record validation, duplicate checking, user creation, record retrieval, updates, and soft deletion.
The most important thing is not simply learning the four CRUD commands, but learning how to implement them securely using prepared statements, validation, output escaping, password hashing, CSRF protection, and appropriate database constraints.
