This article will guide you through creating a simple CRUD application using MySQLi, a popular PHP extension for interacting with MySQL databases. We will cover each step in detail, along with code examples to help you understand the process better.
Setting Up the Environment
Before starting with the CRUD operations, ensure that you have a working PHP environment and a MySQL database set up. For this tutorial, we will use a database named crud_example and a table named users.
Database and Table Creation
CREATE DATABASE crud_example;
USE crud_example;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Establishing a Database Connection
To interact with the MySQL database, we need to establish a connection using MySQLi. This connection will be used throughout the CRUD operations.
PHP Code: Database Connection
<?php
// Database connection parameters
$servername = "localhost";
$username = "root";
$password = "";
$database = "crud_example";
// Create a new MySQLi connection
$conn = new mysqli($servername, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
This script will be included in all other files to establish a connection to the database.
Creating Data (Insert Operation)
The first step in our CRUD application is the creation of data. We will create a form to add a new user to the users table.
HTML Form for Creating a User
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Create User</title>
</head>
<body>
<h2>Create New User</h2>
<form action="create.php" method="post">
<label for="username">Username:</label>
<input type="text" id="username" name="username" required><br>
<label for="email">Email:</label>
<input type="email" id="email" name="email" required><br>
<button type="submit">Create</button>
</form>
</body>
</html>
PHP Code: Handling the Insert Operation
<?php
// Include the database connection
include 'db_connect.php';
if ($_SERVER["REQUEST_METHOD"] == "POST") {
$username = $_POST['username'];
$email = $_POST['email'];
// Prepare and bind
$stmt = $conn->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->bind_param("ss", $username, $email);
// Execute the statement
if ($stmt->execute()) {
echo "New user created successfully!";
} else {
echo "Error: " . $stmt->error;
}
// Close the statement and connection
$stmt->close();
$conn->close();
}
?>
This script captures the data from the form and inserts it into the users table using a prepared statement, ensuring security against SQL injection.
Reading Data (Retrieve Operation)
The next step is to read and display the data from the database. We will create a script that fetches all the records from the users table and displays them in an HTML table.
PHP Code: Retrieving Data
<?php
// Include the database connection
include 'db_connect.php';
// Query to select all users
$sql = "SELECT id, username, email, created_at FROM users";
$result = $conn->query($sql);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>View Users</title>
</head>
<body>
<h2>List of Users</h2>
<table border="1">
<tr>
<th>ID</th>
<th>Username</th>
<th>Email</th>
<th>Created At</th>
<th>Actions</th>
</tr>
<?php
if ($result->num_rows > 0) {
// Output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr>
<td>{$row['id']}</td>
<td>{$row['username']}</td>
<td>{$row['email']}</td>
<td>{$row['created_at']}</td>
<td>
<a href='update.php?id={$row['id']}'>Edit</a> |
<a href='delete.php?id={$row['id']}'>Delete</a>
</td>
</tr>";
}
} else {
echo "<tr><td colspan='5'>No users found</td></tr>";
}
?>
</table>
</body>
</html>
<?php
// Close the connection
$conn->close();
?>
This script fetches all records from the users table and displays them in a table. Each row includes options to edit or delete the user.
Updating Data (Update Operation)
The update operation allows us to modify existing data in the database. We will create a form to edit a user’s details and a PHP script to handle the update.
HTML Form for Updating a User
<?php
// Include the database connection
include 'db_connect.php';
if (isset($_GET['id'])) {
$id = $_GET['id'];
// Query to select the user by ID
$sql = "SELECT id, username, email FROM users WHERE id=?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();
if (!$user) {
echo "User not found!";
exit();
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Edit User</title>
</head>
<body>
<h2>Edit User</h2>
<form action="update.php" method="post">
<input type="hidden" name="id" value="<?php echo $user['id']; ?>">
<label for="username">Username:</label>
<input type="text" id="username" name="username" value="<?php echo $user['username']; ?>" required><br>
<label for="email">Email:</label>
<input type="email" id="email" name="email" value="<?php echo $user['email']; ?>" required><br>
<button type="submit">Update</button>
</form>
</body>
</html>
<?php
// Include the database connection
include 'db_connect.php';
if ($_SERVER["REQUEST_METHOD"] == "POST") {
$id = $_POST['id'];
$username = $_POST['username'];
$email = $_POST['email'];
// Prepare and bind
$stmt = $conn->prepare("UPDATE users SET username=?, email=? WHERE id=?");
$stmt->bind_param("ssi", $username, $email, $id);
// Execute the statement
if ($stmt->execute()) {
echo "User updated successfully!";
} else {
echo "Error: " . $stmt->error;
}
// Close the statement and connection
$stmt->close();
$conn->close();
}
?>
This script updates the user details in the users table based on the ID passed through the form.
Deleting Data (Delete Operation)
Finally, we need to implement the delete operation to remove users from the database.
PHP Code: Handling the Delete Operation
<?php
// Include the database connection
include 'db_connect.php';
if (isset($_GET['id'])) {
$id = $_GET['id'];
// Prepare and bind
$stmt = $conn->prepare("DELETE FROM users WHERE id=?");
$stmt->bind_param("i", $id);
// Execute the statement
if ($stmt->execute()) {
echo "User deleted successfully!";
} else {
echo "Error: " . $stmt->error;
}
// Close the statement and connection
$stmt->close();
$conn->close();
}
?>
This script deletes the user from the users table based on the ID passed through the URL.
Conclusion
Creating a simple CRUD application using MySQLi involves establishing a database connection, and implementing the Create, Read, Update, and Delete operations. This guide provided a step-by-step approach with examples to help you build a functional CRUD application.
In summary:
- Create: Insert new data into the database.
- Read: Retrieve and display data from the database.
- Update: Modify existing data in the database.
- Delete: Remove data from the database.
By following these steps, you can create a robust CRUD application that forms the backbone of many web applications. As you gain more experience, you can extend this basic application with features like pagination, search, and user authentication to enhance its functionality.