Skip to main content
php crud tutorial operation

Simple PHP CRUD tutorial with DEMO Example

Hello friends, Today i am going to share one more tutorial named simple PHP CRUD tutorial with mysql. This is most basic part while you create any application. Any web application should have CRUD functionality. Normally CRUD is stands for CREATE, READ, UPDATE and DELETE. So in this tutorial you will learn how to use CRUD in PHP with MySQL. So i will not waste your more time and come to the point. Let’s start step by step.

Required files for this tutorial

config.php
index.php
insert.php
edit.php
delete.php

First of all we have to create a MySQL database for CRUD operation. Use below query for to generate MySQL database structure.

CREATE TABLE php_crud (
user_id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50) NOT NULL,
phone INT(11) NOT NULL,
gender ENUM('Male', 'Female') NOT NULL
)

After creating MySQL database structure our next step is to connect with newly created database. So let’s create config.php file and connect PHP with MySQL database.

config.php

<?php
$db_hostname = 'localhost';         // Database hostname
$db_username = 'root';				// Database username
$db_password = '';					// Database password
$db_name = 'test';					// Database name

$conn = mysqli_connect($db_hostname, $db_username, $db_password, $db_name);

if(!$conn) 
{
	echo "Unable to connect database".mysqli_error($conn);die;
}
else
{
	// echo "Database connected successfully";
}
?>

Please make sure that you change your database name, localhost name, password and other required stuff as per your system. Using config.php file you are able to connect the MySQL database. Now our next step is to create a front page for showing all the data from database with add, edit and delete button and icon respectively. So for that i will create a index.php file. See my index.php file for getting idea about this front-page. Here i am using bootstrap framework. Please don’t copy the code. If you want to be a true programmer then please try and practice it.

Index.php

<!DOCTYPE html>
<html>
<head>
	<title>PHP CRUD Tutorial</title>
	<link rel="stylesheet" type="text/css" href="css/bootstrap.min.css">
	<link rel="stylesheet" type="text/css" href="css/bootstrap-theme.min.css">
	<link rel="stylesheet" type="text/css" href="css/custom.css">

	<!-- Script for Bootstrap JS -->
	<script type="text/javascript" src="js/jquery-1.11.1.js"></script>
	<script type="text/javascript" src="js/bootstrap.min.js"></script>
</head>
<body>
	<div class="container">
		<div class="row">
			<div class="col-md-12">
				<center><h2>PHP CRUD TUTORIAL</h2></center>
			</div>
		</div>
		<br>
		<div class="row">
			<div class="col-md-12">
				<div class="pull-right">
					<button class="btn btn-primary" class="add_new_user" id="add_new_user"><i class="glyphicon glyphicon-plus"></i>&nbsp;&nbsp;Add New</button>
				</div>
				</br></br>
				<table class="table table-striped table-responsive" id="usersdata">
					<tr>
						<th>Name</th>
						<th>Email</th>
						<th>Phone</th>
						<th>Gender</th>
						<th>Action</th>
					</tr>

					<?php 
					include 'config.php';
					$sql = "SELECT * FROM php_crud";
					$query =  mysqli_query($conn, $sql);
					$rows = mysqli_num_rows($query);
					if($rows>0)
					{
						while($data = mysqli_fetch_array($query))
						{
					?>
					<tr class="user_<?php echo $data['user_id']; ?>">
						<td><?php echo $data['firstname'] . " " . $data['lastname']; ?></td>
						<td><?php echo $data['email']; ?></td>
						<td><?php echo $data['phone']; ?></td>
						<td><?php echo $data['gender']; ?></td>
						<td>
							<a href="javascript:void(0);" onclick="edit_user('<?php echo $data['user_id']; ?>')"><i class="glyphicon glyphicon-pencil"></i></a>&nbsp;&nbsp;&nbsp;&nbsp;
							<a href="javascript:void(0);" onclick="delete_user('<?php echo $data['user_id']; ?>')"><i class="glyphicon glyphicon-trash"></i></a>
						</td>
					</tr>
					<?php
						}
					} /*if condition*/
					?>
				</table>
			</div>
		</div>
	</div>	
</body>

<div class="modal fade" id="add_new_user_modal">
	<div class="modal-dialog">
		<div class="modal-content">
			<div class="modal-header">
				<button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
				<h4 class="modal-title">Fill Details</h4>
			</div>
			<div class="modal-body">
				<form method="POST" role="form">

					<div class="form-group">
						<label for="">Firstname</label>&nbsp;&nbsp;&nbsp;<span class="f_name_error error"></span>
						<input type="text" class="form-control" id="f_name" name="f_name" placeholder="Firstname">
					</div>

					<div class="form-group">
						<label for="">Lastname</label>&nbsp;&nbsp;&nbsp;<span class="l_name_error error"></span>
						<input type="text" class="form-control" id="l_name" name="l_name" placeholder="Lastname">
					</div>

					<div class="form-group">
						<label for="">Email</label>&nbsp;&nbsp;&nbsp;<span class="email_error error"></span>
						<input type="text" class="form-control" id="email" name="email" placeholder="Email">
					</div>

					<div class="form-group">
						<label for="">Phone</label>&nbsp;&nbsp;&nbsp;<span class="phone_error error"></span>
						<input type="text" class="form-control" id="phone" name="phone" placeholder="Phone Number">
					</div>

					<div class="form-group">
						<label for="">Gender</label><br>
						<input type="radio" name="gender" class="gender" value="Male"> Male
						<input type="radio" name="gender" class="gender" value="Female"> Female
					</div>

					<input type="hidden" id="action" name="action" value="add">
					<input type="hidden" id="user_id" name="user_id" value="">
					<button type="button" id="submit" class="btn btn-primary">Submit</button>
					<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
				</form>				
			</div>
			<!-- <div class="modal-footer">
				<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
				<button type="button" class="btn btn-primary">Save changes</button>
			</div> -->
		</div>
	</div>
</div>

<!-- Script for add new data -->
<script type="text/javascript">
$("#add_new_user").click(function(){
	$("#action").val("add");
	$("#user_id").val("");
	$('#f_name').val("");
	$('#l_name').val("");
	$('#email').val("");
	$('#phone').val("");
	$("#user_id").val("");
	$("#add_new_user_modal").modal('show');
});

$("#submit").click(function(){
	var f_name = $('#f_name').val();
	var l_name = $('#l_name').val();
	var email = $('#email').val();
	var phone = $('#phone').val();
	var gender = $("input:radio[name='gender']:checked").val();

	var html = "";
	var email_validate = "";
	var phone_validate = "";
	var action = $("#action").val();
	var user_id = $("#user_id").val();
	var valid = true;
	
	if(f_name == "" || f_name == null)
	{
		valid = false;
		$(".f_name_error").html("* This field is required.");
	}
	else
	{
		$(".f_name_error").html("");	
	}

	if(l_name == "")
	{
		valid = false;
		$(".l_name_error").html("* This field is required.");
	}
	else
	{
		$(".l_name_error").html("");	
	}

	if(email == "")
	{
		valid = false;
		$(".email_error").html("* This field is required.");
	}
	else
	{
		email_validate = isEmail(email);
		if(!email_validate)
		{
			valid = false;
			$(".email_error").html("* Invalid email format. Please try like this <b>test@example.com</b>");
		}
		else
		{
			$(".email_error").html("");
		}
	}

	if(phone == "")
	{	
		valid = false;
		$(".phone_error").html("* This field is required.");
	}	
	else
	{
		phone_validate = isPhone(phone);
		if(!phone_validate)
		{
			valid = false;
			$(".phone_error").html("* Phone number must be numaric and have 10 digit. E.g. 0123456789");
		}
		else
		{
			$(".phone_error").html("");
		}
	}

	if(valid == true)
	{
		var form_data = {
			f_name : f_name,
			l_name : l_name,
			email : email,
			phone : phone,
			gender : gender,
			action : action,
			user_id : user_id
		};
		
		$.ajax({
			url : "insert.php",
			type : "POST",
			data : form_data,
			dataType : "json",
			success: function(response){
				if(response['valid']==false)
				{
					alert(response['msg']);
				}
				else
				{
					if(action == 'add')
					{
						$("#add_new_user_modal").modal('hide');
						html += "<tr class=user_"+response['user_id']+">";
						html += "<td>"+response['firstname']+" "+response['lastname']+"</td>";
						html += "<td>"+response['email']+"</td>";
						html += "<td>"+response['phone']+"</td>";
						html += "<td>"+response['gender']+"</td>";
						html += "<td><a href='javascript:void(0);' onclick='edit_user("+response['user_id']+");'><i class='glyphicon glyphicon-pencil'></i></a>&nbsp;&nbsp;&nbsp;&nbsp;<a href='javascript:void(0);' onclick='delete_user("+response['user_id']+");'><i class='glyphicon glyphicon-trash'></i></a></td>";
						html += "<tr>";
						$("#usersdata").append(html);
					}
					else
					{
						window.location.reload();
					}
				}
			}
		});
	}
	else
	{
		return false;
	}
});

/*Function for validate email*/
function isEmail(email) {
	var regex = /^([a-zA-Z0-9_.+-])+\@(([a-zA-Z0-9-])+\.)+([a-zA-Z0-9]{2,4})+$/;
	return regex.test(email);
}

function isPhone(phone) {
	if(phone.length<=10)
	{
		if (phone.match(/^\d{10}/)) {
	     	return true;
	    }
	    else
	    {
	    	return false;
	    }
	}
	else
	{
		return false;
	}
}

function edit_user(user_id) {
	var form_data = {
		user_id : user_id 
	};
	$.ajax({
		url : "edit.php",
		method : "POST",
		data : form_data,
		dataType : "json",
		success : function(response) {
			$('#f_name').val(response['firstname']);
			$('#l_name').val(response['lastname']);
			$('#email').val(response['email']);
			$('#phone').val(response['phone']);
			$('.gender').each(function(){
				if($(this).val() == response['gender'])
				{
					$(this).prop("checked",true);
				}
			});
			$("#user_id").val(response['user_id']);
			$("#add_new_user_modal").modal('show');
			$("#action").val("edit");
		}
	});
}

function delete_user(user_id) {
	var form_data = {
		user_id : user_id 
	};
	$.ajax({
		url : "delete.php",
		method : "POST",
		data : form_data,
		success : function(response) {
			$(".user_"+user_id).css("background","red");
			$(".user_"+user_id).fadeOut(1000);
		}
	});
}
</script>

</html>

In the index.php file, First you will see add new button for add new user. When someone click on the add new button, bootstrap model will open with html form. You can add or edit data using this form. This html form is validated with jquery. If someone add new user then html form data will send to insert.php file. In the insert.php file server side validation occur for the data. After than data will inserted in the database. If action is edit then data will update for selected user id. See insert.php file below.

insert.php

<?php
include 'config.php';
$f_name = $l_name = $email = $phone = $error = $action = $user_id = "";
$valid = true;
if(isset($_POST['f_name']) && !empty($_POST['f_name']))
{
	$f_name = mysqli_real_escape_string($conn,$_POST['f_name']);
}
else
{
	$valid = false;
	$error .= "* Firstname is required.\n";
	$f_name = '';
}

if(isset($_POST['l_name']) && !empty($_POST['l_name']))
{
	$l_name = mysqli_real_escape_string($conn,$_POST['l_name']);
}
else
{
	$valid = false;
	$error .= "* Lastname is required.\n";
	$l_name = '';
}

if(isset($_POST['email']) && !empty($_POST['email']))
{
	$email = $_POST['email'];
	if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
		$valid = false;
  		$error .= "* Invalid email format. Valid email should be test@example.com\n";
  		$email = "";
	}
	else
	{
		$email = $_POST['email'];
	}
}
else
{
	$valid = false;
	$error .= "* Email is required.\n";
	$email = '';
}

if(isset($_POST['phone']) && !empty($_POST['phone']))
{
	$phone = $_POST['phone'];
	if (!preg_match("/^\d{10}/",$phone)) {
		$valid = false;
  		$error .= "* Phone number is not valid"; 
  		$phone = '';
	}
	else
	{
		$phone = $_POST['phone'];
	}
}
else
{
	$valid = false;
	$error .= "* Phone number is required.\n";
	$phone = '';
}

if(isset($_POST['gender']) && !empty($_POST['gender']))
{
	$gender = $_POST['gender'];
}
else
{
	$gender = "";	
}

if(isset($_POST['action']) && !empty($_POST['action']))
{
	$action = $_POST['action'];
}
else
{
	$action = "";
}

if(isset($_POST['user_id']) && !empty($_POST['user_id']))
{
	$user_id = $_POST['user_id'];
}
else
{
	$user_id = "";
}

if($valid)
{
	if($action == 'add')
	{
		$sql = "INSERT INTO php_crud (user_id, firstname, lastname, email, phone, gender) VALUES (NULL, '$f_name', '$l_name', '$email', '$phone', '$gender')";
		$query = mysqli_query($conn, $sql);
		if($query)
		{
			$retrive_sql = "SELECT * FROM php_crud WHERE user_id = (SELECT MAX(user_id) FROM php_crud)";
			$retrive_query = mysqli_query($conn, $retrive_sql);
			if($retrive_query)
			{
				$data = mysqli_fetch_assoc($retrive_query);
				echo json_encode($data);
			}
		}
		else
		{
			$data = array("valid"=>false, "msg"=>"Data not inserted.");
			echo json_encode($data);
		}
	}

	if($action == 'edit')
	{
		$sql = "UPDATE php_crud SET firstname = '$f_name', lastname = '$l_name', email = '$email', phone = '$phone', gender = '$gender' WHERE user_id = '$user_id' ";
		$query = mysqli_query($conn, $sql);
		if($query)
		{
			$data = array("valid"=>true, "msg"=>"Data successfully updated.");
			echo json_encode($data);
		}
		else
		{
			$data = array("valid"=>false, "msg"=>"Data not updated.");
			echo json_encode($data);
		}	
	}

}
else
{
	$resp = [];
	$resp = array("valid"=>false, "msg"=>$error);
        echo json_encode($resp);
}

?>

Now let’s look up at edit operation. Here i have one edit.php file for that. When someone click on the edit button then html form open with selected user’s data. After made any update in the data when you hit the submit button, selected user’s data will update. See edit.php file below.

Edit.php

<?php 
include 'config.php';
$user_id = '';
if(isset($_POST['user_id']) && !empty($_POST['user_id']))
{
	$user_id = $_POST['user_id'];
}

$sql = "SELECT * FROM php_crud WHERE user_id = '$user_id' ";
$query = mysqli_query($conn, $sql);

if($query)
{
	$data = mysqli_fetch_assoc($query);
	echo json_encode($data);
}

?>

Now final operation of PHP CRUD tutorial is DELETE the data. Every row contains a delete button icon at the end. When someone click on the delete button, delete.php file performs. In the delete.php file MySQL delete query perform and delete the selected id record. See the delete.php file.

Delete.php

<?php 
include 'config.php';
$user_id = '';
if(isset($_POST['user_id']) && !empty($_POST['user_id']))
{
	$user_id = $_POST['user_id'];
}

$sql = "DELETE FROM php_crud WHERE user_id = '$user_id'";
$query = mysqli_query($conn, $sql);

if($query)
{
	echo "Data successfully removed.";
}
?>

Here i am using simple css and jquery trick to show delete functionality more awesome. Check that operation, i hope that you may like. If you like this php crud operation tutorial then please don’t forget to share. Thank you very much. Finally one more thing, if you have any query regarding this tutorial then please let me know in the comment. I will help you to solve that. 🙂

admin

HI, i am software developer having knowledge of PHP, C, C++ programming languages. I am very passionate about programming and ability to solve the programming problems. CodeandTuts is my blog where i like to put new tutorial and help new developer.

11 thoughts on “Simple PHP CRUD tutorial with DEMO Example

  1. Hi!

    Thank you for the tutorial.

    I adapted your code to a more simple system that I’m developing but only the DELETE function is working. Insert and Update aren’t. It just returns from the $.ajax() function and does nothing. The insert/update form does nothing more that validate the emptiness of the fields.

    Can you help me?
    Thanks.
    Regards.

  2. Greetings! Very useful advice in this particular post!
    It’s the little changes which will make the most
    important changes. Thanks for sharing!

  3. Great !
    It’s a big help to start a back-office…
    But lack of ajax pagination, the script is not usable with big sql tables.

    So, nearly perfect 😉

  4. In this tutorial one correction I want to suggest ie instead of creating db structure you written table structure . Create a database, then creation of table,then updation…. I’m I right 🙂 but overall nice tutorial….

  5. Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\php-crud-operation\index.php on line 40

Leave a Reply

Your email address will not be published. Required fields are marked *