We will discuss a simple tutorial which is to create a CRUD application (Create, Read, Update, Delete) in the PHP programming language using a MySQL database and also bootstrap 4. CRUD applications are widely used in data processing, be it for data input, adding data, edit data or delete data that is directly connected to the database. CRUD applications are widely used for dynamic applications and the term CRUD may be familiar to back-end developers or full-stack developers, for those who don't understand, we will explain a little what CRUD is.
1. What is CRUD
CRUD stands for Create, Read, Update & Delete which have their respective functions to be implemented into data-based applications. These four points cannot be separated, because they will be directly related to the database which complement each other in processing the database.
CRUD is a facilitator in the front-end display, which has functions to change, add, delete data, which means CRUD runs on the server side. The term CRUD was first coined by James Martin in 1983.
2. How to create a simple CRUD with PHP
After we know what CRUD is fundamentally, now we will create a simple CRUD application using PHP, MySQL and Bootstrap with the theme of processing customer data.
In this tutorial, we'll see how CRUD works and we'll provide a step-by-step explanation.
3. Preparation
Some of the components needed include a code editor, web browser and web server (for local, you can use XAMPP, Laragon or others).
In this example, we will run a CRUD application on the local XAMPP web server. Create a new folder inside htdocs, name it tutorial_crud, then create the required files like so:
- add.php
- add_process.php
- database_conn.php
- delete.php
- edit php
- edit_process php
- index.php
4. Create a new table
After the components have been prepared, the next step is for us to create a new table in the XAMPP phpmyadmin database. Create a new database by entering a few lines of SQL source code below:
CREATE TABLE `customers` (
`customer_id` INT(11) NOT NULL AUTO_INCREMENT,
`firstname` VARCHAR(255) NULL DEFAULT NULL,
`lastname` VARCHAR(255) NULL DEFAULT NULL,
`email` VARCHAR(255) NULL DEFAULT NULL,
`created` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`customer_id`)
) COLLATE = 'latin1_swedish_ci' ENGINE = InnoDB AUTO_INCREMENT = 1; After the database table has been successfully created, we will enter the next step, namely connecting the database to the PHP application.
5. Connecting MySQLi with PHP applications
In order for the database that has been created to be used in applications, some additional source code is needed to activate this function. This aims to connect the database to the PHP application by entering some of the required information such as hostname, database username, database password, table names in the database.
Please follow the source code below in the database_conn.php file that has been created.
<?php // database_conn.php
/*
* genelify.com
*/
$hostname = 'localhost'; // hostnames
$database_username = 'root'; // database usernames
$database_password = ''; // database passwords
$database_name = 'test'; //database name
// connection to database
$db_connect = mysqli_connect($hostname, $database_username, $database_password, $database_name);
if(!$db_connect) {
die('Could not connect to database:' .mysql_error());
} For the database name, table name, database password, please just adjust it to what you created.
6. Read data
In the next step, we will create an initial display or home on the index.php that has been created, this file functions to read and display some information related to the data that we will display, as well as additional buttons such as edit or delete to make it easier to update data and delete data. To tidy up the appearance of the HTML table and buttons, we use Bootstrap 4.
The code in this file will retrieve all data from the MySQL database and then display it on the front-end using the while method and associative arrays to make things easier. Please follow the source code below:
<?php // index.php
/*
* genelify.com
*/
// Read and display customer data to the front-end
include 'database_conn.php';
$query = "select * from customers limit 200";
$result = mysqli_query($db_connect, $query); ?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Simple CRUD Using PHP, MySQL and Bootstrap</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" >
<script src="https://code.jquery.com/jquery-3.5.1.slim.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container mt-4">
<div class="row">
<div class="col-md-12">
<div class="alert alert-warning alert-dismissible fade show" role="alert">
<?php include 'message.php'; ?>
<button type="button" class="close" data-dismiss="alert" aria-label="Close">
<span aria-hidden="true"></span>
</button>
</div>
</div>
<div class="col-md-12">
<div class="float-left mb-4">
<h2>Customers List</h2>
</div>
<div class="float-right">
<a href="add.php" class="btn btn-success">Add new customer</a>
</div>
<table class="table">
<thead>
<tr>
<th scope="col">#</th>
<th scope="col">First name</th>
<th scope="col">Last name</th>
<th scope="col">Email</th>
<th scope="col">Join</th>
<th scope="col">Action</th>
</tr>
</thead>
<tbody>
<!-- Fetch customer data with associative array -->
<?php if ($result->num_rows > 0): ?>
<?php while($customer_data = mysqli_fetch_assoc($result)): ?>
<tr>
<th scope="row"><?php echo $customer_data['customer_id'];?></th>
<td><?php echo $customer_data['firstname'];?></td>
<td><?php echo $customer_data['lastname'];?></td>
<td><?php echo $customer_data['email'];?></td>
<td><?php $new_date = new DateTime($customer_data['created']); echo $new_date->format('Y-m-d');?></td>
<td>
<a href="edit.php?customer_id=<?php echo $customer_data['customer_id'];?>" class="btn btn-primary">Edit</a>
<a href="delete.php?customer_id=<?php echo $customer_data['customer_id'];?>" class="btn btn-danger">Delete</a>
</td>
</tr>
<?php endwhile; ?>
<?php else: ?>
<tr>
<td colspan="3" rowspan="1" headers="">No data found!</td>
</tr>
<?php endif; ?>
<?php mysqli_free_result($result); ?>
</tbody>
</table>
</div>
</div>
</div>
</body>
</html> After we have succeeded in reading and displaying data into the main view, the next important step is to add other functions, adding data, editing data and deleting data. We start first by adding data.
7. Create Data
In this step, we will create a new function that is adding data, to carry out this function, several components are needed such as front-end and server-side processes. This front-end function is to create a form and retrieve all the data contents in it such as firstname, lastname and email, then proceed to the file add_process.php which runs on the server side to process the retrieved data into the database.
You can follow the code below into the file that was created earlier called add.php.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Add data - Simple CRUD Using PHP, MySQL and Bootstrap</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>
<div class="container mt-4">
<div class="row">
<div class="col-md-12">
<div class="page-header mb-4">
<h2>Add a new customer</h2>
</div>
<form action="add_process.php" method="post">
<div class="form-group">
<label>First name</label>
<input type="text" name="firstname" class="form-control" required="">
</div>
<div class="form-group">
<label>Last name</label>
<input type="text" name="lastname" class="form-control" required="">
</div>
<div class="form-group">
<label>Email</label>
<input type="email" name="email" class="form-control" required="">
</div>
<input type="submit" class="btn btn-primary" name="submit" value="save">
</form>
</div>
</div>
</div>
</body>
</html> Next, copy and paste the source code below into the add_process.php file to process the data and add it to the database. For the date itself, the contents of the $created variable will be created automatically, according to when the user inputs data in the form.
<?php // add_process.php
/*
* genelify.com
*/
include "database_conn.php";
if (count($_POST) > 0)
{
$firstname = $_POST["firstname"];
$lastname = $_POST["lastname"];
$email = $_POST["email"];
$created = date("Y-m-d");
$query = "INSERT INTO customers (firstname, lastname, email, created) VALUES ('$firstname', '$lastname', '$email', '$created')";
if (mysqli_query($db_connect, $query)) {
$message = 1;
} else {
$message = 4;
}
}
header("Location: index.php?message=" . $message . ""); ?>
We proceed to the next process, namely editing or updating data.
8. Update data
This function is needed to change the data in the database, if you may have tried adding some data to the database and want to change the information, this function is really needed. With this edit function, you no longer have to bother opening phpmyadmin manually.
To carry out this function, 2 components are needed, namely the front-end to create forms and processes on the server side. The front-end is needed to display data in a form that is automatically filled in according to existing data. Then changes to the data in the form will be processed through the edit_process.php file to update the data in the database.
To display the form and read the data in the database, you can follow the source code below into the file that has been created, namely edit.php
<?php // edit.php
/*
* genelify.com
*/
include 'database_conn.php';
$query = "SELECT * FROM customers WHERE customer_id='" . $_GET["customer_id"] . "'"; // Fetch data from the table customers using id
$result = mysqli_query($db_connect, $query);
$customer = mysqli_fetch_assoc($result); ?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Edit customer - Simple CRUD Using PHP, MySQL and Bootstrap</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>
<div class="container mt-4">
<div class="page-header mb-4">
<h2>Edit customer</h2>
</div>
<div class="row">
<div class="col-md-12">
<form action="edit_process.php" method="POST">
<input type="hidden" name="customer_id" value="<?php echo $_GET["customer_id"]; ?>" class="form-control" required="">
<div class="form-group">
<label for="exampleInputEmail1">First name</label>
<input type="text" name="firstname" class="form-control" value="<?php echo $customer['firstname']; ?>" required="">
</div>
<div class="form-group">
<label for="exampleInputEmail1">Last name</label>
<input type="text" name="lastname" class="form-control" value="<?php echo $customer['lastname']; ?>" required="">
</div>
<div class="form-group">
<label for="exampleInputEmail1">Email</label>
<input type="email" name="email" class="form-control" value="<?php echo $customer['email']; ?>" required="">
</div>
<button type="submit" class="btn btn-primary" value="submit">Submit</button>
</form>
</div>
</div>
</div>
</body>
</html> And then what is important is the process on the server side, this file is needed to update data or change data into the database. However, to perform this update function, one parameter is needed to mark which data will be changed so that it is not confused. In the example, we use the customer_id parameter as the data marker. To activate the edit function, please copy and paste the source code in edit_process.php
<?php // edit_process.php
/*
* genelify.com
*/
include "database_conn.php";
if (count($_POST) > 0)
{
$customer_id = $_POST["customer_id"];
$firstname = $_POST["firstname"];
$lastname = $_POST["lastname"];
$email = $_POST["email"];
$query =
"UPDATE customers set customer_id='" .
$customer_id .
"', firstname='" .
$firstname .
"', lastname='" .
$lastname .
"', email='" .
$email .
"' WHERE customer_id='" .
$customer_id .
"'"; // update form data from the database
if (mysqli_query($db_connect, $query)) {
$message = 2;
} else {
$message = 4;
}
}
header("Location: index.php?message=" . $message . "");
7. Delete data
The last function is the delete function, where this function is to delete the data contained in the database according to our wishes. To run this delete feature, additional source code is needed in the delete.php file that you created earlier.
Additional source code for delete.php file:
<?php // delete.php
/*
* genelify.com
*/
include "database_conn.php";
$customer_id = $_GET["customer_id"];
$query = "DELETE FROM customers WHERE customer_id='" . $customer_id . "'";
if (mysqli_query($db_connect, $query)) {
$message = 3;
} else {
$message = 4;
}
header("Location: index.php?message=" . $message . ""); Same with the process of updating data, this delete function will delete data according to the parameters used so there is no confusion, we use the customer_id parameter as a marker of which data will be deleted.
8. Create an alert message feature
We will make additional features to make the CRUD application more perfect by adding alerts or warning messages. This additional feature functions to display a message when the CRUD process is running, whether it is successful or not. To activate this alert feature, create a new file called message.php and copy and paste the source code below:
<?php // message.php
/*
* genelify.com
*/
$messages = [
1 => "Data successfully added",
2 => "Data successfully updated",
3 => "Data successfully deleted",
4 => "MySQL Database Error, please check the entered query",
];
$messages_id = isset($_GET["message"]) ? (int) $_GET["message"] : 0;
if (isset($messages[$messages_id])) {
echo $messages[$messages_id];
} else {
echo "How to make simple CRUD with php & mysqli | genelify.com";
} After all the components are ready and the source code is in their respective files, please open the browser and run the application.
Closing
Thus a short tutorial on making CRUD with PHP, MySQL and Bootstrap. In processing data in an application, CRUD is an important component needed to run dynamic data-based applications. CRUD functions to connect between databases and interfaces with certain functions according to our wishes, such as deleting, changing, adding, reading data.
