A CRUD (Create, Read, Update, and Delete) application is essential in web development. It allows users to perform basic operations on data. This tutorial will use PHP and MySQL to create a simple CRUD application. We’ll start by creating a database and table to store data. Then, we’ll create PHP files to perform CRUD operations. By the end, you’ll have a basic understanding of using PHP and MySQL to build a CRUD application.
Here are the basic steps to create a PHP MySQL CRUD application;
Step 1: Create a MySQL Database
You need to create a MySQL database to store your data. You can use any tool you prefer to create a database, such as PHPMyAdmin, MySQL Workbench, or the command line.
Step 2: Create a PHP file to establish a database connection
Create a PHP file to establish a connection to your MySQL database using the mysqli_connect() function.
<?php
$host = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";
$conn = mysqli_connect($host, $username, $password, $database);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
?>Replace your_username, your_password, and your_database with your MySQL database credentials.
Step 3: Create a PHP file to create records
Create a PHP file to insert records into your database using the mysqli_query() function.
<?php
include "db_connection.php";
$name = $_POST['name'];
$email = $_POST['email'];
$phone = $_POST['phone'];
$sql = "INSERT INTO users (name, email, phone) VALUES ('$name', '$email', '$phone')";
if (mysqli_query($conn, $sql)) {
echo "Record created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>Replace users with your table name.
Step 4: Create a PHP file to read records
Create a PHP file to retrieve records from your database using the mysqli_query() function.
<?php
include "db_connection.php";
$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) {
echo "Name: " . $row["name"]. " - Email: " . $row["email"]. " - Phone: " . $row["phone"]. "<br>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>Step 5: Create a PHP file to update records
Create a PHP file to update records in your database using the mysqli_query() function.
<?php
include "db_connection.php";
$id = $_POST['id'];
$name = $_POST['name'];
$email = $_POST['email'];
$phone = $_POST['phone'];
$sql = "UPDATE users SET name='$name', email='$email', phone='$phone' WHERE id=$id";
if (mysqli_query($conn, $sql)) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . mysqli_error($conn);
}
mysqli_close($conn);
?>Replace the id with your primary key column name.
Step 6: Create a PHP file to delete records
Create a PHP file to delete records from your database using the mysqli_query() function.
<?php
include "db_connection.php";
$id = $_GET['id'];
$sql = "DELETE FROM users WHERE id=$id";
if (mysqli_query($conn, $sql)) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . mysqli_error($conn);
}
mysqli_close($conn);
?>Replace the id with your primary key column name.
Step 7: Create a PHP file to display records
Create a PHP file to display records from your database in a table format.
<?php
include "db_connection.php";
$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);
?>
<table>
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Phone</th>
<th>Action</th>
</tr>
<?php while($row = mysqli_fetch_assoc($result)) { ?>
<tr>
<td><?php echo $row["id"]; ?></td>
<td><?php echo $row["name"]; ?></td>
<td><?php echo $row["email"]; ?></td>
<td><?php echo $row["phone"]; ?></td>
<td>
<a href="edit.php?id=<?php echo $row["id"]; ?>">Edit</a>
<a href="delete.php?id=<?php echo $row["id"]; ?>">Delete</a>
</td>
</tr>
<?php } ?>
</table>
<?php mysqli_close($conn); ?>Replace users with your table name.
Step 8: Create a PHP file to update records
Create a PHP file to update records in your database using a form.
<?php
include "db_connection.php";
$id = $_GET['id'];
$sql = "SELECT * FROM users WHERE id=$id";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
$row = mysqli_fetch_assoc($result);
} else {
echo "0 results";
}
mysqli_close($conn);
?>
<form action="update.php" method="post">
<input type="hidden" name="id" value="<?php echo $row["id"]; ?>">
<label>Name:</label>
<input type="text" name="name" value="<?php echo $row["name"]; ?>"><br>
<label>Email:</label>
<input type="email" name="email" value="<?php echo $row["email"]; ?>"><br>
<label>Phone:</label>
<input type="text" name="phone" value="<?php echo $row["phone"]; ?>"><br>
<input type="submit" value="Update">
</form>Replace users with your table name.
Step 9: Create a PHP file to handle the form submission
Create a PHP file to handle the form submissions and update records in your database.
<?php
include "db_connection.php";
$id = $_POST['id'];
$name = $_POST['name'];
$email = $_POST['email'];
$phone = $_POST['phone'];
$sql = "UPDATE users SET name='$name', email='$email', phone='$phone' WHERE id=$id";
if (mysqli_query($conn, $sql)) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . mysqli_error($conn);
}
mysqli_close($conn);
?>Replace users with your table name.
Step 10: Test your CRUD application
You can now test your CRUD application by opening the create, read, update, and delete pages in your web browser. Make sure you have your server is running.
Conclusion
In this tutorial, you learned how to create a PHP MySQL CRUD application. You learned how to create a database, and a table, and how to perform create, read, update, and delete operations using PHP and MySQL. You also learned how to connect to a database, display records in a table format, and handle form submissions. You can now take this basic knowledge and build more complex applications using PHP and MySQL.