AJAX / JQUERY / MySQL / PHP

Simple PHP CRUD tutorial with DEMO Example

- by Chintan Panchal - Leave a Comment
php crud tutorial operation

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

[sociallocker]
 Download  Demo
[/sociallocker]

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.

Related:  JQuery File Upload Progress bar Using Ajax - PHP

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>[email protected]</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.

Related:  How to run PHP file on windows using XAMPP

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 [email protected]\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.

Related:  Login and Logout Example using Session in PHP

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. 🙂

Related Posts

jquery ajax autocomplete example using PHP and Ajax

Jquery Ajax Autocomplete Example Using PHP and MySQL

JQuery File Upload Progress bar Using Ajax – PHP

load more content while scrolling with jquery ajax and PHP

Load More Content While Scrolling With jQuery Ajax and PHP

About Chintan Panchal

I am web developer having 6+ year of experience of web development. Currently working on laravel and have worked on codeigniter, cakephp, symfony. Thank you :)

View all posts by Chintan Panchal →

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.