In this tutorial, we will learn how to implement CRUD operations (Create, Read, Update, Delete) using MySQL and PHP. CRUD operations are the basic functions that are commonly used in database management systems.
To follow along with this tutorial, you will need:
- A local development environment with PHP and MySQL installed
- An understanding of PHP and SQL syntax
Let’s get started!
Setting up the Database
The first step is to set up a MySQL database. Open your MySQL client and create a new database:
CREATE DATABASE mydatabase;
Next, create a table to store our data:
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL
);
This table will have three columns: id, name, and email. The id column will serve as the primary key and will automatically increment for each new record.
Connecting to the Database
To connect to the MySQL database using PHP, we need to establish a connection using the mysqli extension. Create a new file called config.php and add the following code:
<?php
$host = 'localhost';
$dbUsername = 'username'; // Replace with your database username
$dbPassword = 'password'; // Replace with your database password
$dbName = 'mydatabase';
$conn = new mysqli($host, $dbUsername, $dbPassword, $dbName);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
Make sure to replace 'username' and 'password' with your actual database credentials.
To test the connection, create a new file called index.php and include the config.php file:
<?php
require 'config.php';
echo "Connection established!";
When you visit index.php in your browser, you should see the message “Connection established!” if the connection was successful.
Creating Records
To create new records in the database, we need to create an HTML form to collect the input from the user. Create a new file called create.php and add the following code:
<?php
require 'config.php';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$name = $_POST['name'];
$email = $_POST['email'];
$sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
if ($conn->query($sql) === true) {
echo "Record created successfully!";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
}
$conn->close();
?>
<!DOCTYPE html>
<html>
<head>
<title>Create User</title>
</head>
<body>
<h1>Create User</h1>
<form method="post" action="create.php">
<label for="name">Name:</label>
<input type="text" name="name" required>
<label for="email">Email:</label>
<input type="email" name="email" required>
<button type="submit">Create</button>
</form>
</body>
</html>
This code will display an HTML form to collect the user’s name and email. When the form is submitted, it will insert the values into the database using an SQL INSERT statement.
To test this functionality, open create.php in your browser and submit the form. You should see the message “Record created successfully!” if the insertion was successful.
Retrieving Records
To retrieve records from the database, we can use an SQL SELECT statement. Create a new file called read.php and add the following code:
<?php
require 'config.php';
$sql = "SELECT * FROM users";
$result = $conn->query($sql);
$userData = array();
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$userData[] = $row;
}
}
$conn->close();
?>
<!DOCTYPE html>
<html>
<head>
<title>View Users</title>
</head>
<body>
<h1>View Users</h1>
<?php if (!empty($userData)): ?>
<table>
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
</tr>
</thead>
<tbody>
<?php foreach ($userData as $user): ?>
<tr>
<td><?php echo $user['id']; ?></td>
<td><?php echo $user['name']; ?></td>
<td><?php echo $user['email']; ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<?php else: ?>
<p>No users found.</p>
<?php endif; ?>
</body>
</html>
This code will retrieve all records from the users table and display them in an HTML table. If no records are found, it will display the message “No users found.”
To test this functionality, open read.php in your browser. If there are any records in the database, they will be displayed in a table.
Updating Records
To update existing records in the database, we need to create a similar form as the one used for creating records, but with the pre-filled data of the selected record. Create a new file called update.php and add the following code:
<?php
require 'config.php';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$id = $_POST['id'];
$name = $_POST['name'];
$email = $_POST['email'];
$sql = "UPDATE users SET name='$name', email='$email' WHERE id='$id'";
if ($conn->query($sql) === true) {
echo "Record updated successfully!";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
} elseif (isset($_GET['id'])) {
$id = $_GET['id'];
$sql = "SELECT * FROM users WHERE id='$id'";
$result = $conn->query($sql);
if ($result->num_rows === 1) {
$row = $result->fetch_assoc();
$name = $row['name'];
$email = $row['email'];
} else {
echo "No user found with ID: " . $id;
exit();
}
}
$conn->close();
?>
<!DOCTYPE html>
<html>
<head>
<title>Update User</title>
</head>
<body>
<h1>Update User</h1>
<?php if (isset($_GET['id'])): ?>
<form method="post" action="update.php">
<input type="hidden" name="id" value="<?php echo $_GET['id']; ?>">
<label for="name">Name:</label>
<input type="text" name="name" value="<?php echo $name; ?>" required>
<label for="email">Email:</label>
<input type="email" name="email" value="<?php echo $email; ?>" required>
<button type="submit">Update</button>
</form>
<?php else: ?>
<p>No user ID specified.</p>
<?php endif; ?>
</body>
</html>
This code will retrieve the data of the selected user by its ID and display an HTML form to update the values. When the form is submitted, it will update the corresponding record in the database using an SQL UPDATE statement.
To test this functionality, open update.php?id=1 in your browser, replacing 1 with the ID of an existing user. The form will be pre-filled with the user’s data, and any changes made will be saved in the database.
Deleting Records
To delete records from the database, we can use an SQL DELETE statement. Create a new file called delete.php and add the following code:
<?php
require 'config.php';
if (isset($_GET['id'])) {
$id = $_GET['id'];
$sql = "DELETE FROM users WHERE id='$id'";
if ($conn->query($sql) === true) {
echo "Record deleted successfully!";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
} else {
echo "No user ID specified.";
exit();
}
$conn->close();
?>
This code will delete the record matching the specified ID from the users table.
To test this functionality, open delete.php?id=1 in your browser, replacing 1 with the ID of an existing user. The user’s record will be deleted from the database, and you will see the message “Record deleted successfully!”.
Conclusion
Congratulations! You have successfully implemented CRUD operations using MySQL and PHP. You learned how to create, read, update, and delete records in a MySQL database, using PHP to interact with the database.
Remember to always validate and sanitize user input to prevent security vulnerabilities. Additionally, consider implementing user authentication and authorization to restrict access to the CRUD operations if required.
Feel free to explore more advanced features, such as pagination, search functionality, and input validation, to enhance your CRUD operations.
Keep practicing and building exciting projects with PHP and MySQL!