In this blog post, I’ll be showing you how to create a simple CRUD system in PHP and MySQL. If you are a beginner and want to learn PHP then CRUD system is the best practice for you.
What is CRUD in PHP?
CRUD stands for Create, Read, Update, and Delete, which are the basic operations that can be performed on data. In the context of PHP and databases, CRUD refers to the actions you can perform on database records. Here’s a brief explanation of each operation:
Create (C)
- In PHP, creating corresponds to adding new records or entries to a database.
- This typically involves submitting a form with user inputs and then processing those inputs to insert a new record into the database.
Read (R)
- Reading involves fetching and displaying data from the database.
- In PHP, this often includes retrieving records based on certain criteria (e.g., displaying a list of users or a single user profile).
Update (U)
- Updating refers to modifying existing records in the database.
- In PHP, this usually involves submitting a form with pre-populated data and then processing those inputs to update the corresponding record in the database.
Delete (D)
- Deleting involves removing records from the database.
- In PHP, this often corresponds to confirming a delete action and then processing the deletion of the specified record from the database.
In a web application, these CRUD operations are frequently associated with HTML forms, where users can input data for creation or updating, and display data through HTML views. PHP is commonly used in the server-side processing of these forms and in interacting with the database to perform CRUD operations.
Create a CRUD System in PHP and MySQL
Creating Database
Start by creating a database named crud_example. You can use phpMyAdmin or a MySQL command line for this.
CREATE DATABASE crud_example;
Create a User Table
Create a table named users inside the crud_example database.
USE crud_example;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Create Connection File
Create a file named config.php to handle the database connection.
<?php
$host = "your_host";
$username = "your_username";
$password = "your_password";
$database = "crud_example";
$conn = new mysqli($host, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
Note: Replace "your_host", "your_username", and "your_password" with your actual database credentials.
Create an HTML Form for Adding Users
Create a file named add_user.php to add users.
<?php include "config.php"; ?>
<html>
<head>
<title>Add User</title>
</head>
<body>
<h2>Add User</h2>
<form action="process_add_user.php" method="post">
Username: <input type="text" name="username" required><br>
Email: <input type="email" name="email" required><br>
<input type="submit" value="Add User">
</form>
</body>
</html>
Create a PHP Script to Process User Addition
Create a file named process_add_user.php to handle the form submission.
<?php
include "config.php";
$username = $_POST['username'];
$email = $_POST['email'];
$sql = "INSERT INTO users (username, email) VALUES ('$username', '$email')";
if ($conn->query($sql) === TRUE) {
header("Location: index.php");
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
Create PHP Script to Display Users
Create a file named index.php to display the list of users.
<?php
include "config.php";
$sql = "SELECT * FROM users";
$result = $conn->query($sql);
?>
<html>
<head>
<title>User List</title>
</head>
<body>
<h2>User List</h2>
<table border="1">
<tr>
<th>ID</th>
<th>Username</th>
<th>Email</th>
<th>Created At</th>
</tr>
<?php
while ($row = $result->fetch_assoc()) {
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['username'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "<td>" . $row['created_at'] . "</td>";
echo "</tr>";
}
?>
</table>
<br>
<a href="add_user.php">Add User</a>
</body>
</html>
This example provides basic functionality for adding users and displaying the list of users. You can further expand and enhance it by adding update and delete functionalities.
Please note that this example uses the mysqli extension for database operations, which is commonly used but has its drawbacks. Consider using a PDO or an ORM for more advanced projects. Additionally, this example does not include input validation and is vulnerable to SQL injection. Always validate and sanitize user inputs before using them in database queries to prevent security issues.