Learn how to build a CRUD application with PHP and MySQL
In this example we will create a simple address book application to understand CRUD operations in PHP and MySQL. As you may already know that CRUD stands for Create, Retrieve, Update and Delete operations. We can use these operations to add, manipulate and delete database records.
We will follow following steps to complete our application:
Create Database and Table
We will first create a database and a table in MySQL. Our application consists on a single table for simplicity. We are naming our database as “address_book_db” and table as “address_table”. You can use phpMyAdmin or any MySQL frontend to create the database and table easily. Alternatively, you can use bellow script to do the same through CLI.
CREATE DATABASE address_book_db;
USE address_book_db;
CREATE TABLE address_table (
id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
name varchar(100) NOT NULL,
address varchar(255),
phone VARCHAR(50),
email VARCHAR(50)
);Optionally, we can insert some records in our table with following SQL query. So that we get some records when we query our table in next section.
INSERT INTO address_table (name, address, phone, email) VALUES ("Jack Smith", "123 ABC ST. New York", "123-456-789", "jack123@gmail.com");Retrieve Record
Now its time to fire some PHP code to show some records from our database. We will create “index.php” file for this purpose, as it will also serve as our landing page. We are using HTML tables to format the display.
Our landing page will have two portions. First we will code to connect to MySQL database and then run SQL query to get the records from our table and display it.
As we need the connectivity part in each of our subsequent operations, its a good practice to place it into a separate file. So that we can just call it and do not need to write the same code each time. We will place this part into “config.php” file.
// config.php
$mysql_server = "localhost";
// Change the username and password accordingly
$mysql_user = "mysql_username";
$mysql_password = "mysql_password";
$mysql_db_name = "address_book_db";
// Create connection with mysql
$conn = mysqli_connect($mysql_server, $mysql_user, $mysql_password, $mysql_db_name);
// Check the connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}<?php
// index.php
// Include the config file
include('config.php');
// Add a link for new record addition
echo "<a href=create.php>Add New Address</a><br>";
// Select fields
$sql = "SELECT * FROM address_table";
// Execute query
$result = mysqli_query($conn, $sql);
// Show fields
if (mysqli_num_rows($result) > 0) {
?>
<!-- Create a HTML table to format the output -->
<table cellspacing="0" cellpadding="1" border="1">
<tr>
<th>ID</th>
<th>Name</th>
<th>Address</th>
<th>Phone</th>
<th>EMail</th>
<th>Actions</th>
</tr>
<?php
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "<tr>";
echo "<td>" . $row["id"] . "</td>";
echo "<td>" . $row["name"] . "</td>";
echo "<td>" . $row["address"] . "</td>";
echo "<td>" . $row["phone"] . "</td>";
echo "<td>" . $row["email"] . "</td>";
// Links to update and delete file by passing table "id" filed
echo "<td><a href=update.php?id=" .$row["id"] . ">Update</a> ";
echo "<a href=delete.php?id=" .$row["id"] . ">Delete</a>";
echo "</td>";
echo "</tr>";
}
echo "</table>";
} else {
echo "No records found";
}
// Close mysql connection
mysqli_close($conn);
?>Create Record
We will use a simple HTML form to collect input from user. We will also write PHP code to process and insert it into MySQL table. We will name the PHP file to “create.php”. We are using form self submit using PHP for clean and concise code. $_SERVER is an array, which contains information such as headers, paths, and script locations. PHP_SELF is a variable which returns current script being executed.
<!DOCTYPE html>
<html lang="en">
<head>
<title>Create Record</title>
</head>
<body>
<!-- HTML form for user input. Form submits to itself to execute PHP code bellow -->
<form action="<?php ($_SERVER["PHP_SELF"]); ?>" method="post">
<label>Name</label><br>
<input type="text" name="form_name"><br>
<label>Address</label><br>
<input type="text" name="form_address"><br>
<label>Phone</label><br>
<input type="text" name="form_phone"><br>
<label>Email</label><br>
<input type="text" name="form_email"><br>
<input type="submit">
</form>
</body>
</html>
<?php
// Include the config file
include('config.php');
// If form submits and method is POST
if($_SERVER["REQUEST_METHOD"] == "POST"){
// Get form session variables
$input_name = $_POST["form_name"];
$input_address = $_POST["form_address"];
$input_phone = $_POST["form_phone"];
$input_email = $_POST["form_email"];
// Insert the variables's data into table
$sql = "INSERT INTO address_table (name, address, phone, email)
VALUES ('$input_name', '$input_address', '$input_phone', '$input_email')";
// Execute the query
if(mysqli_query($conn, $sql)){
// On success, redirect to landing page
header("location: index.php");
exit();
} else{
// If faild, throw an error
echo "Something went wrong. Please try again later.";
}
}
// Close mysql connection
mysqli_close($conn);
?>Update Record
Now we will code the update operation and name the file as “update.php”.
<?php
// Include the config file
include('config.php');
// Get record id from landing page link
$id=$_GET["id"];
// Select the field to be updated
$sql = "SELECT * FROM address_table where id=$id";
// execute the query
$result = mysqli_query($conn, $sql);
// Get the row
$row = mysqli_fetch_assoc($result);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Update Record</title>
</head>
<body>
<!-- HTML form with old data. Form submits to itself to execute PHP code bellow -->
<form action="<?php ($_SERVER["PHP_SELF"]); ?>" method="post">
<label>Name</label><br>
<input type="text" name="form_name" value="<?php echo $row["name"]; ?> "><br>
<label>Address</label><br>
<input type="text" name="form_address" value="<?php echo $row["address"]; ?> "><br>
<label>Phone</label><br>
<input type="text" name="form_phone" value="<?php echo $row["phone"]; ?> "><br>
<label>Email</label><br>
<input type="text" name="form_email" value="<?php echo $row["email"]; ?> "><br>
<input type="submit">
</form>
</body>
</html>
<?php
// If form submits and method is POST? Get form data into variables
if($_SERVER["REQUEST_METHOD"] == "POST"){
$input_name = $_POST["form_name"];
$input_address = $_POST["form_address"];
$input_phone = $_POST["form_phone"];
$input_email = $_POST["form_email"];
// Update record with new changed data
$sql = "UPDATE address_table SET name='$input_name', address='$input_address', phone='$input_phone', email='$input_email' where id='$id'";
// Execute the query
if(mysqli_query($conn, $sql)){
// If successfull, redirect to landing page
header("location: index.php");
exit();
} else{
// If faild, throw an error
echo "Something went wrong. Please try again later.";
}
}
// Close mysql connection
mysqli_close($conn);
?>Delete Record
The record deletion part is more easier and straight forward then previous two operations.
<?php
// Include the config file
include('config.php');
// Get record id from landing page link
$id=$_GET["id"];
// Delete field query
$sql = "DELETE FROM address_table where id=$id";
if (mysqli_query($conn, $sql)) {
header("location: index.php");
} else {
echo "Error deleting record: " . mysqli_error($conn);
}
// Close mysql connection
mysqli_close($conn);
?>Now that bring us to conclusion and end of this tutorial. I hope that it is helpful and you have enjoyed the journey. You can improve it further with CSS and JavaScript for better user interface. We have left those out for the purpose of simplicity. But presentation and data validation are important parts in production environment. We will touch these in coming articles.
If you face any difficulty or have some suggestion for improvement, please use comment bellow.
You can find the complete source code of this tutorial at Github.
Further reading:
