Simple CRUD with PHP, MYSQLI

CRUD (Create, Retrieve, Update, Delete) is a usual job in web development. In Later Career, as a web developer, you’ll encounter lots of CRUD functionality in your career. The design of a CRUD will allow the users to create/retrieve/update/remove the data from the front end of the system. Generally, with the help of PHP as a server-side programming language, the data will be stored in MYSQL Database. PHP as a server-side language will be manipulating MYSQL Database tables to perform a particular action triggered by the users.

Table of Content

  1. PHP CRUD Part 1 Introduction
  2. PHP CRUD Part 2 Configure Database
  3. PHP CRUD Part 3 Setting up project folder
  4. PHP CRUD Part 4 Database Connection
  5. PHP CRUD Part 5 Create
  6. PHP CRUD Part 6 Retrieve
  7. PHP CRUD Part 7 Update
  8. PHP CRUD Part 8 Remove

1. PHP CRUD Part 1 Introduction

In this section, the overall CRUD operation is introduced as well as we are going to develop a simple CRUD (Create, Retrieve, Update, Delete) PHP operation. In a web application, these are the basic stuff required to create, retrieve, update, and delete the data using PHP with MYSQL Database. You will be creating the database tables and inserting the data into the database tables without any fatal errors. This is an uncomplicated and easy tutorial to learn a CRUD operation. To understand how CRUD operations work, I recommend you go through each part of this tutorial.

The below video illustrates the final system of this tutorial. The source code of this application will be provided at the end of this tutorial.

2. PHP CRUD Part 2 Configure Database

Database Name : php_crud
Table Name : members
Table Column : id, fname, lname, contact, age, active

To create the database in this tutorial, there are two ways.

2.1 First way to create database

Copy and paste the following SQL command in your MySQL database to create a database and table



CREATE DATABASE `php_crud`; 

// create table
CREATE TABLE `php_crud`.`members` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fname` varchar(255) NOT NULL,
`lname` varchar(255) NOT NULL,
`contact` varchar(255) NOT NULL,
`age` varchar(255) NOT NULL,
`active` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;



2.2 Second way to create database

A tutorial video to demonstrate to you, how to create a database and tables for this tutorial

3. PHP CRUD Part 3 Setting up project folder

In this part, we will be creating a project folder and files for this tutorial. First of all, create the project folder as “crud_php”. After that create the create.php, edit.php, index.php, and remove.php files inside this project folder as shown [crud_php/create.php], [crud_php/edit.php] and so on.

Second, Create a folder named “php_action” inside this project folder as [crud_php/php_action] which will handle the server-side processing actions. In this directory create a create.php, db_connect.php, remove.php and update.php file as shown [crud_php/php_action/create.php], [crud_php/php_action/db_connect.php], and so on.

The current file structure should look like the below if you have followed the steps correctly:

CRUD_PHP_FOLDER

The video displays you to create a project folder and files for this tutorial.

4. PHP CRUD Part 4 Database Connection

The db_connect.php file i.e. [php_action/db_connect.php], this file contains a database connection. In this application, the db_connect.php file will be used to perform any action for CRUD. Such as connecting to the database, executing the query, and closing the connection.


<?php 

$localhost = "127.0.0.1"; 
$username = "root"; 
$password = ""; 
$dbname = "php_crud"; 

// create connection 
$connect = new mysqli($localhost, $username, $password, $dbname); 

// check connection 
if($connect->connect_error) {
	die("connection failed : " . $connect->connect_error);
} else {
	// echo "Successfully Connected";
}

?>

5. PHP CRUD Part 5 Create

5.1 Adding “Add Member” Button and “Action” button

To start with the create.php page we need a button that will lead to create.php page. Open index.php file which we created in Chapter 3 in this tutorial. And add an “Add Member” button at the top of the table, and an “Edit”, and “Remove” buttons at each row of the table.

Now the index.php file’s code should look like below, the highlighted code is what we need to implement in this tutorial.



<?php require_once 'php_action/db_connect.php'; ?>

<!DOCTYPE html>
<html>
<head>
	<title>PHP CRUD</title>

	<style type="text/css">
		.manageMember {
			width: 50%;
			margin: auto;
		}

		table {
			width: 100%;
			margin-top: 20px;
		}

	</style>

</head>
<body>

<div class="manageMember">
	<a href="create.php"><button type="button">Add Member</button></a>
	<table border="1" cellspacing="0" cellpadding="0">
		<thead>
			<tr>
				<th>Name</th>
				<th>Age</th>
				<th>Contact</th>
				<th>Option</th>
			</tr>
		</thead>
		<tbody>
			
		</tbody>
	</table>
</div>

</body>
</html>

Now if you go to the index.php page, you’ll see the “Add member” button. Eventually, the create.php is created at Chapter 3 and it will lead to a blank page when you click the “Add Member” button. In the next step, we will create a form to enter the member information into the system and add the member’s information to the database.

add member btn

5.2 Creating a Create page

In create.php file which you created in Chapter 3. This file contains an HTML form where the user’s input data will pass to the server side and add the information to the database.

The first part of this code is to create an HTML form with the required input field and pass the data to the server.


<!DOCTYPE html>
<html>
<head>
	<title>Add Member</title>

	<style type="text/css">
		fieldset {
			margin: auto;
			margin-top: 100px;
			width: 50%;
		}

		table tr th {
			padding-top: 20px;
		}
	</style>

</head>
<body>

<fieldset>
	<legend>Add Member</legend>

	<form action="php_action/create.php" method="post">
		<table cellspacing="0" cellpadding="0">
			<tr>
				<th>First Name</th>
				<td><input type="text" name="fname" placeholder="First Name" /></td>
			</tr>		
			<tr>
				<th>Last Name</th>
				<td><input type="text" name="lname" placeholder="Last Name" /></td>
			</tr>
			<tr>
				<th>Age</th>
				<td><input type="text" name="age" placeholder="Age" /></td>
			</tr>
			<tr>
				<th>Contact</th>
				<td><input type="text" name="contact" placeholder="Contact" /></td>
			</tr>
			<tr>
				<td><button type="submit">Save Changes</button></td>
				<td><a href="index.php"><button type="button">Back</button></a></td>
			</tr>
		</table>
	</form>

</fieldset>

</body>
</html>


The second part of this code insert process happens. Look through the codes and we will go through them afterward:


<?php 

require_once 'db_connect.php';

if($_POST) {
	$fname = $_POST['fname'];
	$lname = $_POST['lname'];
	$age = $_POST['age'];
	$contact = $_POST['contact'];

	$sql = "INSERT INTO members (fname, lname, contact, age, active) VALUES ('$fname', '$lname', '$contact', '$age', 1)";
	if($connect->query($sql) === TRUE) {
		echo "<p>New Record Successfully Created</p>";
		echo "<a href='../create.php'><button type='button'>Back</button></a>";
		echo "<a href='../index.php'><button type='button'>Home</button></a>";
	} else {
		echo "Error " . $sql . ' ' . $connect->connect_error;
	}

	$connect->close();
}

?>

Let us look at the beginning of the code. It first checks if the form is submitted by using a $_POST global variable. If a form is submitted then it inserts the data into the database by using $_POST. And check if the query is executed successfully then it displays a successful message and link button.

The end result should look like this if you have followed the instruction correctly. Click on Add Member on the index page and enter the member information into the input field. Click on the Save Changes button.

create

After creating some records by entering the member information, you should be able to see a CRUD grid as below:

create2

6. PHP CRUD Part 6 Retrieve

In the index.php page, we are going to retrieve the data that are stored in the database. This part is quite easy. We need a database connection file to execute the SQL command.
The highlighted codes are created for the edit and remove buttons. You can copy all the codes below.


<?php require_once 'php_action/db_connect.php'; ?>

<!DOCTYPE html>
<html>
<head>
	<title>PHP CRUD</title>

	<style type="text/css">
		.manageMember {
			width: 50%;
			margin: auto;
		}

		table {
			width: 100%;
			margin-top: 20px;
		}

	</style>

</head>
<body>

<div class="manageMember">
	<a href="create.php"><button type="button">Add Member</button></a>
	<table border="1" cellspacing="0" cellpadding="0">
		<thead>
			<tr>
				<th>Name</th>
				<th>Age</th>
				<th>Contact</th>
				<th>Option</th>
			</tr>
		</thead>
		<tbody>
			<?php
			$sql = "SELECT * FROM members WHERE active = 1";
			$result = $connect->query($sql);

			if($result->num_rows > 0) {
				while($row = $result->fetch_assoc()) {
					echo "<tr>
						<td>".$row['fname']." ".$row['lname']."</td>
						<td>".$row['age']."</td>
						<td>".$row['contact']."</td>
						<td>
							<a href='edit.php?id=".$row['id']."'><button type='button'>Edit</button></a>
							<a href='remove.php?id=".$row['id']."'><button type='button'>Remove</button></a>
						</td>
					</tr>";
				}
			} else {
				echo "<tr><td colspan='5'><center>No Data Avaliable</center></td></tr>";
			}
			?>
		</tbody>
	</table>
</div>

</body>
</html>

Now if you open this page “index.php”, you should see the member’s information on the table. As well as you should notice the “Edit” and “Remove” buttons at each table row data. They are not functional for this part. In the next part, we are going to learn about the update functionality for this tutorial.

index

7. PHP CRUD Part 7 Update

Go to edit.php file in [crud_php/edit.php] which was creating in Chapter 3. We will teach in two parts; the first part creating HTML form, and the second part server-side processing. The first part of the code is an HTML form to update the information of members. But it will not only update the data, but it will also display the member information. Copy the code below to the edit.php file at [crud_php/edit.php]:


<?php 

require_once 'php_action/db_connect.php';

if($_GET['id']) {
	$id = $_GET['id'];

	$sql = "SELECT * FROM members WHERE id = {$id}";
	$result = $connect->query($sql);

	$data = $result->fetch_assoc();

	$connect->close();

?>

<!DOCTYPE html>
<html>
<head>
	<title>Edit Member</title>

	<style type="text/css">
		fieldset {
			margin: auto;
			margin-top: 100px;
			width: 50%;
		}

		table tr th {
			padding-top: 20px;
		}
	</style>

</head>
<body>

<fieldset>
	<legend>Edit Member</legend>

	<form action="php_action/update.php" method="post">
		<table cellspacing="0" cellpadding="0">
			<tr>
				<th>First Name</th>
				<td><input type="text" name="fname" placeholder="First Name" value="<?php echo $data['fname'] ?>" /></td>
			</tr>		
			<tr>
				<th>Last Name</th>
				<td><input type="text" name="lname" placeholder="Last Name" value="<?php echo $data['lname'] ?>" /></td>
			</tr>
			<tr>
				<th>Age</th>
				<td><input type="text" name="age" placeholder="Age" value="<?php echo $data['age'] ?>" /></td>
			</tr>
			<tr>
				<th>Contact</th>
				<td><input type="text" name="contact" placeholder="Contact" value="<?php echo $data['contact'] ?>" /></td>
			</tr>
			<tr>
				<input type="hidden" name="id" value="<?php echo $data['id']?>" />
				<td><button type="submit">Save Changes</button></td>
				<td><a href="index.php"><button type="button">Back</button></a></td>
			</tr>
		</table>
	</form>

</fieldset>

</body>
</html>

<?php
}
?>

Let’s look at the code. At the beginning of the code, it catches the $id from a $_GET request. Then it fetches the information by that $id variable. After the data is retrieved from the database, the information is added to the input field. As well as the new input field is also appended to a name “id” to match the specific member data when the form is submitted.

In the Second part, the data update process is coded here. Open the update.php file in crud_php/php_action/update.php which was creating in Chapter 3. Open the file and copy and paste these codes.


<?php 

require_once 'db_connect.php';

if($_POST) {
	$fname = $_POST['fname'];
	$lname = $_POST['lname'];
	$age = $_POST['age'];
	$contact = $_POST['contact'];

	$id = $_POST['id'];

	$sql = "UPDATE members SET fname = '$fname', lname = '$lname', age = '$age', contact = '$contact' WHERE id = {$id}";
	if($connect->query($sql) === TRUE) {
		echo "<p>Succcessfully Updated</p>";
		echo "<a href='../edit.php?id=".$id."'><button type='button'>Back</button></a>";
		echo "<a href='../index.php'><button type='button'>Home</button></a>";
	} else {
		echo "Erorr while updating record : ". $connect->error;
	}

	$connect->close();

}

?>

Let us look at the beginning of the code. It first checks if the form is submitted by using a $_POST global variable. If a form is submitted then it updates the data in the database by using $_POST. And check if the query is executed successfully then it displays a successful message and link button.

If you followed the step correctly then your edit.php file should look like the below:

update

8. PHP CRUD Part 8 Remove

Go to remove.php file in [crud_php/remove.php] which was creating in Chapter 3. The logic is the same as we learned in Chapter 7 Update . In the first part, we’ll create HTML, and in the second part a server-side processing file. Copy the code below to the edit.php file at [crud_php/remove.php]:


<?php 

require_once 'php_action/db_connect.php';

if($_GET['id']) {
	$id = $_GET['id'];

	$sql = "SELECT * FROM members WHERE id = {$id}";
	$result = $connect->query($sql);
	$data = $result->fetch_assoc();

	$connect->close();
?>

<!DOCTYPE html>
<html>
<head>
	<title>Remove Member</title>
</head>
<body>

<h3>Do you really want to remove ?</h3>
<form action="php_action/remove.php" method="post">

	<input type="hidden" name="id" value="<?php echo $data['id'] ?>" />
	<button type="submit">Save Changes</button>
	<a href="index.php"><button type="button">Back</button></a>
</form>

</body>
</html>

<?php
}
?>

Let’s look at the code. At the beginning of the code, it catches the $id from a $_GET request. Then it fetches the information by that $id variable. After the data is retrieved from the database, a new input field is also appended named “id” to match the specific member data when the form is submitted.

If you followed correctly, then the remove.php page should be similar to the below:

remove



<?php 

require_once 'db_connect.php';

if($_POST) {
	$id = $_POST['id'];

	$sql = "UPDATE members SET active = 2 WHERE id = {$id}";
	if($connect->query($sql) === TRUE) {
		echo "<p>Successfully removed!!</p>";
		echo "<a href='../index.php'><button type='button'>Back</button></a>";
	} else {
		echo "Error updating record : " . $connect->error;
	}

	$connect->close();
}

?>


Let’s look at the beginning of the code. It first checks if the form is submitted by using a $_POST global variable. If a form is submitted then it removes the data. And checks if the query is executed successfully then it displays a successful message and button link.

Download Source Code!!!!

Download

Related Posts:

17 comments

  1. Great tutorial. Thank you!

  2. I really enjoyed the tutorial. However, I am receiving an error message (as below). Any help would be appreciated. Many thanks

    Erorr while updating record : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘
    Notice: Undefined index: id in /Applications/XAMPP/xamppfiles’ at line 1

    Here is my code:
    query($sql) === TRUE) {
    echo “Succcessfully Updated”;
    echo “Back“;
    echo “Home“;
    } else {
    echo “Erorr while updating record : “. $connect->error;
    }

    $connect->close();

    }

    ?>

  3. THANK YOU SO MUCH AM SO HAPPY TO GET UR CODE,,,,I WAS TRYING IT SO FAR

  4. Nice job guys. Go Sri Lanka Go !
    Machan mata mail ekak danna mage podi php project ekak thiyanwa karanna, pay karannam. please.

  5. Please put all of the rest of the code 🙂

  6. Finally i am able to create and understand the methods of CRUD. Thank you very much. This means a lot to me.

  7. If you could teach about session(creating academic year in a dashboard) ,it will a great help.

  8. I would recommend using prepared statements to execute crud operations in PHP (https://www.cloudways.com/blog/execute-crud-in-mysql-php/ ). This is because prepared statements execute single query multiple times, making the safest and more efficient method of doing it.

  9. Please Help me,
    When I am Inserting data in database getting Error. I am Using PHP version 7. My code is
    prepare($sql9);
    $query->bindParam(‘:pid’,$pid,PDO::PARAM_STR);
    $query->bindParam(‘:name’,$name,PDO::PARAM_STR);
    $query->bindParam(‘:mobile’,$mobile,PDO::PARAM_STR);
    $query->bindParam(‘:fromdate’,$fromdate,PDO::PARAM_STR);
    $query->bindParam(‘:todate’,$todate,PDO::PARAM_STR);
    $query->bindParam(‘:comment’,$comment,PDO::PARAM_STR);
    $query->bindParam(‘:useremail’,$useremail,PDO::PARAM_STR);
    $query->bindParam(‘:status’,$status,PDO::PARAM_STR);
    $query->execute();
    $lastInsertId = $dbh->lastInsertId();
    if($lastInsertId)
    {
    $msg=”Booked Successfully “;
    }
    else
    {
    $error=”Something went wrong. Please try again”;
    }

    }
    ?>

  10. Great tutorial, thanks.

    It may be useful to sanitize the posted values and add validation functions.

  11. thank you….really helped a lot…………..

  12. In this topic below: 5.2 Creating a Create page
    After this line comes an image, with an image link:
    The end result should look like this if you have followed the instruction correctly. Click on Add member at the index page and enter the member information into the input field. Click on Save Changes button.
    https://codersfolder.com/wp-content/uploads/2016/08/create.png
    The image does not appear on the page because it is saved with “jpg” extension, and the link is like “png”.

  13. PERFECTTTTT!!!!!!!THANK YOU SO MUCH

  14. Thanks, for this article, this helped me, php for life

  15. Awesome piece of coding, with a few alterations it is just what I was looking for; thank you!

  16. Nilton Oliveira May 20, 2020 at 6:33 am

    When updating, displays error for each line:
    Notice: Undefined index: fname in C:\wamp64\www\my_proj\php_action/update.php on line 13

    Notice: Undefined index: lname in C:\wamp64\www\my_proj\php_action/update.php on line 14

    Notice: Undefined index: age in C:\wamp64\www\my_proj\php_action/update.php on line 15

    Notice: Undefined index: contact in C:\wamp64\www\my_proj\php_action/update.php on line 16

  17. Nilton Oliveira May 20, 2020 at 7:16 am

    I managed to find what caused the error.

    For example, in the line below:
    <input type="text" id="fname" placeholder="First Name" value="” />
    I forgot to put name=”fname”
    Correcting:
    <input type="text" id="fname" name="fname" placeholder="First Name" value="” />