PHP CRUD application is generally used as PHP tutorial to understand simple process of Listing (Read), Insert (Create), Update & Delete. A simple CRUD application in any technology can helps us to understand some basic about how that technology communicate with database.

Let Try to Understand Native (Core) PHP CRUD (stands for Create, Read, Update and Delete) with using MySQL Database. Tutorial also use WAMP as server to execute this working example.

We can create “Country Module” to understand this PHP CRUD application. Now start by creating a folder in your ‘www’ root directory of WAMP, name it anything you want. We will name it “crudapp”. We can follow below mentioned steps to understand PHP CRUD application and execute successfully as PHP CRUD example.

Step1: Create a database in MySQL using PHPMYADMIN

As we want to create CRUD application we have to create database. To create it you have to import SQL file which attached with download package or follow the below steps to create a new database in MySQL using phpMyAdmin.

• Go to localhost/phpmyadmin/
• Give the Name of your database as you like or use the query below. Give a table name and add columns to the table with name you like.
• We have given the database name “Practice”. Then click on the “Practice” and go to SQL tab. Copy the following SQL query and paste it there create a table named “country”.

CREATE TABLE IF NOT EXISTS `country` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`country_code` varchar(50) NOT NULL,
`country_name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Here we have made a table named country, with three columns: id which is primary field with auto increment, country_code & country_name, both with type varchar and length 50.

Step2: Create a configuration file in PHP for generate connection with MYSQL database

Now we have database along with country table. So next step would be create script which should do the connection with database (“practice”). So in this step we make a configuration file for connecting database with our application.

Make a file with named configuration.php in your root directory of “crudapp” & Copy the below code and paste it in that file.

<?php
$link=mysqli_connect("localhost","root","","practice");
?>

In above code we used mysqli_connect function to connect with database “practice”. The First parameter here is our server name (localhost), the second parameter is username (root) of server, the third parameter is password (“”, A blank password is not recommended. This is for testing purpose only.) And the fourth parameter is our database name (practice).

Step3: Insert dummy data in country table using PHPMYADMIN

We should insert some dummy data in country table then only we can connect database and grab data from country table to listing purpose.

Copy the query below in the SQL tab of phpmyadmin and execute.

INSERT INTO `country` (`id`, `country_code`, `country_name`) VALUES
(1, 'IND', 'India'),
(2, 'AUS', 'Australia');

We have added two countries record, with country id, country code and country name. For listing purpose two row is enough because after listing we can do insert functionality so at that time we can create and insert dynamic data in country table.

Step4: Fetch data from MYSQL database and do Listing using PHP

In this step we will fetch the data from the database which we were inserted manually in previous step and list it in our html page. That is a main page of our application, so we will name it index.php.

<?php
	include("configuration.php");
	$query="select * from country";
	if(isset($_GET['msg'])){
		echo $_GET['msg'];
	}
?>
<table border="1">
<tr>
<td>No.</td>
<td>Country Code</td>
<td>Country Name</td>
</tr>
<?php
	if($resource=mysqli_query($link,$query)){
		$num=1;
		while($rowData=mysqli_fetch_assoc($resource)) {
?>
<tr>
<td><?php echo $num; ?></td>
<td><?php echo $rowData['country_code'];?></td>
<td><?php echo $rowData['country_name'];?></td>
<td><a href="form.php?cid=<?php echo $rowData['id']; ?>">EDIT</a></td>
<td><a href="delete.php?cid=<?php echo $rowData['id']; ?>">DELETE</a></td>
</tr>
<?php
		$num ++;
		}
	}
?>
<tr>
<td colspan="5"><a href="form.php">Insert New Record</a></td>
</tr>
</table>

In above code first on the top we include the connection file configuration.php because of that file we have connection ready in variable $link.

Than we prepare a MySQL select query which will be used to fetch data from the country table of practice database.

Now query is prepare so we have to execute that query using mysqli_query function. Mysqli_query function return resource id which stored into $resource variable and will use for fetch data.

We have now Resource id which have all virtual data inside it so now we can fetch data one by one using a mysql_fetch_assoc() function. Mysql_fetch_assoc() used to fetch the associative array from the result and store it in the $rowData variable which will used inside a while loop. After that, we will use the array key in html table to print the data in the table. Than we create Add, Edit and Delete link at the same page which helps us in the next steps.

Step5: Create HTML form for insert new record in PHP

On clicking “Insert New Record” link, user will be redirected to an HTML form page, where he can submit country information like name and code. It will be named form.php, which will be used for both add and edit process.

<form action="form_back.php" method="POST">
	<div class="row">
		<div class="cols">Country Code</div>
		<div class="cols">
			<input type="text" name="ccode" id="ccode" value="<?php if(isset($row['country_code'])){echo $row['country_code'];}?>">
		</div>
	</div>
	<div class="row">
		<div class="cols">Country Name</div>
		<div class="cols">
			<input type="text" name="cname" id="cname" value="<?php if(isset($row['country_name'])){echo $row['country_name'];}?>">
			</div>
	</div>
	<div class="row">
		<div class="cols">
			<input type="submit" name="submit" value="submit">
		</div>
		<div class="cols">
			<input type="hidden" name="cid" value="">
		</div>
	</div>
</form>

 

The form action attribute specifies that where we want to send the form data. In this form we give action on form_back.php .The Method attribute specifies how to send form data. Here we send a form data in POST. There are two input box country code and country name. On clicking the submit button, the form data gets submitted and will available into form_back.php page inside a super Global variable $_POST.

Please note that if you sent method as “GET” then submitted data will available into other Global variable $_GET. Also if action attribute of form element not set then submitted data will available into same page.

Step6: Insert data in MYSQL database using PHP

We have to create new php file named form_back.php because we have set it action attribute of form element. So when you click on submit button, posted (user submitted) data will available on form_back.php inside a $_POST super global variable. You can debug it using print_r() or var_dump().

E.g.,
Print_r(($_POST); or var_dump($_POST);

<?php
	include("configuration.php");
	$ccode=$_POST['ccode'];
	$cname=$_POST['cname'];
	if(isset($_POST['submit'])){
		if(empty($_POST['cid'])){
		$insert_query="insert into country (`country_code`,`country_name`) values ('".$ccode."','".$cname."')";
		$msg="Insert successful";
		}

		$result=mysqli_query($link,$insert_query);
		if($result){
			header("Location:index.php?msg=".$msg);
		} else {
			$msg="Problem with Insert";
			header("Location:index.php?msg=".$msg);
		}
} else {
	$msg="Please enter the country data";
	header("Location:index.php");
}
?>

 

In above code we create two variable to store user submitted value from $_POST global variable.

Than we use “if(isset($_POST[‘submit’])) and in next line if(empty($_POST[‘cid’])” conditions to check that POST data set or not using isset() function and “cid” hidden form element is empty or not using empty() function. If form not submitted then we can just redirect to index.php with proper message.

Note: “cid” HTML hidden element (which we created in previous step) we can use for edit process in next step. Also we will not cover server and/or client validation in this article. We will create separate article for server and client side validation.

Let assume user will submit country data and “cid” hidden form element has empty value, means both condition is true than we come to the MySQL insert query which responsible to insert country data into database’s country table.

To insert country data we have to prepare insert query and execute using mysqli_query function. If the query run successfully we redirect user to the index page using header, where the data will be listed. If data inserted properly, mysqli_query will return true which we will store into on variable $result. And base on that value of $result we will redirect to user once again index.php page with proper message.

Step7: Edit data in MYSQL using same insert html form in PHP

We have to fetch particular data from the row of table based on the unique id. We will get that unique id from the listing page when we click on the edit link of that row which data we want to update.

If you have noticed in step4, we used same page “form.php” for prepare insert and update links. So when user click on “Edit” link from index.php he will redirect to same form.php with specific data of that row. To fill data into form, copy the code below and paste it at the top of form.php.

<?php
	include("configuration.php");
	if(isset($_GET['cid']) && !empty($_GET['cid'])){
		$cid=$_GET['cid'];
		$edit_query="select * from country where id='".$cid."'";
		$result=mysqli_query($link,$edit_query);
		$row=mysqli_fetch_array($result);
	}
?>

 

We have a select query here which will fetch data by id. After that, execute query variable $edit_query using mysqli_query, we will store data $result. Now we fetch array from $result. Then add values for Country Name and Country Code in the HTML form. Also, we will add hidden input which consists country id, we will get cid from the database in the listing page. This cid value we will use for update process.

<form action="form_back.php" method="POST">
	<div class="row">
		<div class="cols">Country Code</div>
		<div class="cols">
			<input type="text" name="ccode" id="ccode" value="<?php if(isset($row['country_code'])){echo $row['country_code'];}?>">
		</div>
	</div>
	<div class="row">
		<div class="cols">Country Name</div>
		<div class="cols">
			<input type="text" name="cname" id="cname" value="<?php if(isset($row['country_name'])){echo $row['country_name'];}?>">
			</div>
	</div>
	<div class="row">
		<div class="cols">
			<input type="submit" name="submit" value="submit">
		</div>
		<div class="cols">
			<input type="hidden" name="cid" value="<?php if(isset($_GET['cid'])){echo $_GET['cid'];} ?>">
		</div>
	</div>
</form>

 

Step8: Update data using MYSQL in PHP

This step we will update country data in country table of databse. Now in this step we update data which we have now in listing. After changing data, we submit the form and data gets updated in database.

Update this code in form_back.php

<?php
include("configuration.php");
$ccode=$_POST['ccode'];
$cname=$_POST['cname'];

if(isset($_POST['ccode']) && isset($_POST['cname'])){
	if(empty($_POST['cid'])){
		$query="insert into country (`country_code`,`country_name`) values ('".$ccode."','".$cname."')";
		$msg="Insert successful";
	} else {
		$query="update country set `country_code`='".$ccode."', `country_name`='".$cname."' where `id`='".$_POST['cid']."'";
		$msg="Update successful";
	}
	
	$result=mysqli_query($link,$query);
	if($result){
		header("Location:index.php?msg=".$msg);
	} else {
		$msg="Problem with Insert";
		header("Location:index.php?msg=".$msg);
	}
} else {
	$msg="Please enter the country data";
	header("Location:index.php");
}

?>

 

In above code we have checked form is submitted or not if yes then we have checked that if “cid” hidden element is not empty then we can prepare an update query based on that id. After the execution using mysqli_query function we will set proper message and user gets redirected on index page with updated data & message.

Step9: Delete data from MYSQL database using PHP

We are near to finish our PHP CRUD application because we are in last step, DELETE functionality. The delete functionality will work based on id, which we put the id in the delete link. When we click on the delete link of index.php the script executes and data gets deleted.

Make a new php page named delete.php for delete script and copy the code below.

<?php
	include("configuration.php");
	if(isset($_GET['cid']) && !empty($_GET['cid'])){
		$query="DELETE FROM country where id='".$_GET['cid']."'";
		$result= mysqli_query($link,$query);
		if($result){
			$msg="Data deleted successfully";
			header("Location:index.php?msg=".$msg);
		}
	}
?>

 

In above code we prepare a delete query based on the id, execute it using mysqli_query function. Mysqli_query function return true if update query execute properly. Base on that status we redirect user on the index.php page with the proper message.

That’s it folks! You have successfully created a basic CRUD application of country module. You can same way “State” and “City” module’s PHP CRUD application. We hope you will find this informative. In our next post, we will do JavaScript validation in HTML form. Stay tuned.