JQUERY, PHP CRUD operation

  • connect.php
  • setup.sql
  • form.php
  • users.php
  • edit.php
  • server.php

First of all create new folder in xampp\htaccess with named project.

project folder will contain all these above files.

Create new database & users table

Create new database with name project and users table with setup.sql

CREATE TABLE IF NOT EXISTS users(
id INT(10) AUTO_INCREMENT PRIMARY KEY,
fname VARCHAR(255) NOT NULL,
lname VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL
);

To establish connection between server and database

connect.php

<?php 
$conn=mysqli_connect("localhost","root","","project");
 ?>
server: localhost
user : root
password: 
database: project

Server.php to implement all operation request sent using ajax

<?php
//this is server
require_once("connect.php");

$action=$_POST['action'];


if($action=="register"){

	$fname=$_POST['fname'];
	$lname=$_POST['lname'];
	$email=$_POST['email'];
	$password=$_POST['password'];
	$cpassword=$_POST['cpassword'];

	// send data from server to php

$sql="INSERT INTO users(fname, lname, email, password) VALUES('$fname','$lname','$email','$password')";
$result=mysqli_query($conn,$sql);
if($result){
	echo"successfully added";
}
else{
	echo "failed".mysqli_error($conn);
}

}


if($action=="load_data"){
   $message="";
   $success=false;

   $sql="SELECT  *  FROM users";
   $result=mysqli_query($conn,$sql);
   if($result){
   		$succes=true;

   		$records=[];
   		while($rows=mysqli_fetch_array($result)){
   			array_push($records, $rows);
   		}

   		$message=$records;
	}
	else{	
		$success=false;
		$message="error";
	}
	$data=["success"=>$success,"message"=>$message];
	echo json_encode($data);	
}




if($action=="delete"){
	$success=false;
	$message="";
	$id=$_POST['id'];
	$sql="DELETE FROM users WHERE id='$id'";
	$result=mysqli_query($conn,$sql);
	if($result){
		$success=true;
		$message="deleted";
	}
	else{
		$success=false;
		$message="failed";
	}
	$data=["success"=>$success,"message"=>$message];
	echo json_encode($data);
}

if($action=="edit"){

$success=false;
$message="";

$fname=$_POST['fname'];
	$lname=$_POST['lname'];
	$email=$_POST['email'];
	$password=$_POST['password'];
	$cpassword=$_POST['cpassword'];
	$id=$_POST['id'];

	// send data from server to php

$sql="UPDATE users SET fname='$fname', lname='$lname', email='$email', password='$password' WHERE id='$id'";
$result=mysqli_query($conn,$sql);
if($result){
	$success=true;
	$message="successfully updated";
}
else{
	$success=false;
	$message="Error";
}

$data=["success"=>$success,"message"=>$message];
	echo json_encode($data);

}

?>

Create new form to add new users

<!DOCTYPE html>
<html>
<head>
	<title></title>
	<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>

</head>
<body>
	<?php require_once("menubar.php"); ?>

	<form>
		<p>
			<label for="fname">First name</label>
			<input type="text" class="fname">
			<small class="error"></small>
		</p>

		<p>
			<label for="lname">Last name</label>
			<input type="text" class="lname">
			<small class="error"></small>
		</p>

		<p>
			<label for="email">Email id</label>
			<input type="text" class="email">
			<small class="error"></small>

		</p>

		<p>
			<label for="password">Password</label>
			<input type="text" class="password">
			<small class="error"></small>
		
		</p>

		<p>
			<label for="cpassword">Confirm Password</label>
			<input type="text" class="cpassword">
			<small class="error"></small>
		</p>

		<p>
			<button type="button" class="register_button">Register</button>
		</p>	
	</form>

</body>
</html>

<script type="text/javascript">
	
$(function(){

$(document).on("click",".register_button",function(){


var fname,lname,email,password,cpassword;

fname=$(".fname").val()

if(fname.length<=0){
	$(".fname").next(".error").html("First name is required").css("color","red");
	return false
}
else{
	$(".fname").next(".error").html('');
}


lname=$(".lname").val()

if(lname.length<=0){
	$(".lname").next(".error").html("last name is required").css("color","red");
	return false
}
else{
	$(".lname").next(".error").html('');
}

email=$(".email").val()

if(email.length<=0){
	$(".email").next(".error").html("email id is required").css("color","red");
	return false
}
else{
	$("email").next(".error").html('');
}

password=$(".password").val()

if(password.length<=0){
	$(".password").next(".error").html("password is required").css("color","red");
	return false
}
else{
	$(".password").next(".error").html('');
}

cpassword=$(".cpassword").val()

if(cpassword.length<=0){
	$(".cpasswor").next(".error").html("confirm password is required").css("color","red");
	return false
}
else{
	$(".cpassword").next(".error").html('');
}


// anchronous java script &amp; xml

$.ajax({
	url:"server.php",
	method:"post",
	datatype:"text",
	data:{
		action:"register",
		fname:fname,
		lname:lname,
		email:email,
		password:password,
		cpassword:cpassword
	},
	beforeSend:function(){

	},
	success:function(data){
		alert(data)
	}
})

})
})
</script>

To display all registered users, delete user

<!DOCTYPE html>
<html>
<head>
	<title></title>
	<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
	<style type="text/css">
	table{
		width: 100%;
		border-collapse: collapse;
	}
	th{
		height: 40px;
		background-color: yellow; 
	}
	td{
		height: 30px;
	}
		table,th,td{
			border: 1px solid black;
		}
	</style>
</head>
<body>

<?php require_once("menubar.php"); ?>


<h1>Users list</h1>

<table>
	<thead>
		<tr>
			<th>First name</th>
			<th>Last name</th>
			<th>Email id</th>
			<th>Password</th>
			<th>Delete</th>
			<th>Edit</th>
		</tr>	
	</thead>
	<tbody class="table_container">
		
	</tbody>
</table>


</body>
</html>

<script type="text/javascript">
	
$(function(){
	var table_container=$(".table_container")

function load_data(){
$.ajax({
	url:"server.php",
	method:"post",
	datatype:"text",
	data:{
		action:"load_data"
	},
	beforeSend:function(){
		table_container.html("loading...");
	},
	success:function(data){

		table_container.html("");

		var obj=JSON.parse(data);
		var users=obj.message;
		console.log(users);
		$.each(users,function(index,item,arr){
			table_container.append(`
				<tr>
					<td>${item.fname}</td>
					<td>${item.lname}</td>
					<td>${item.email}</td>
					<td>${item.password}</td>	
					<td>				
					<button
					type="button"
					id="${item.id}"
					class="delete">Delete</button>
					</td>
					<td>
					<a href="edit.php?id=${item.id}">Edit</a>
					</td>
				</tr>
				`);	
		})
	}
})	
}	

load_data();

$(document).on("click",".delete",function(){

if(confirm("Are you sure to delete this record?")){
	var me=$(this);
	var id=me.attr("id");
	var parent=me.parents("tr");
	$.ajax({
		url:"server.php",
		method:"post",
		datatype:"text",	
		data:{
			action:"delete",
			id:id
		},
		beforeSend:function(){

		},
		success:function(data){
			var obj=JSON.parse(data);
			if(obj.success==true){
				parent.remove();
			}
		}
	})

}

})

//last
})
</script>

Edit user data

<!DOCTYPE html>
<html>
<head>
	<title></title>
	<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>

</head>
<body>


<?php 
require_once("connect.php");
$id=$_GET['id'];
$sql="SELECT * FROM users WHERE id='$id'";
$result=mysqli_query($conn,$sql);
$row=mysqli_fetch_array($result);
// print_r($row);
?>

	<form>
		<p>
			<input type="hidden" name="id" class="id" value="<?=$row['id']?>">
			<label for="fname">First name</label>
			<input type="text" class="fname" value="<?=$row['fname']?>">
			<small class="error"></small>
		</p>

		<p>
			<label for="lname">Last name</label>
			<input type="text" class="lname" value="<?=$row['lname']?>">
			<small class="error"></small>
		</p>

		<p>
			<label for="email">Email id</label>
			<input type="text" class="email" value="<?=$row['email']?>">
			<small class="error"></small>

		</p>

		<p>
			<label for="password">Password</label>
			<input type="text" class="password" value="<?=$row['password']?>">
			<small class="error"></small>
		
		</p>

		<p>
			<label for="cpassword">Confirm Password</label>
			<input type="text" class="cpassword" value="<?=$row['password']?>">
			<small class="error"></small>
		</p>

		<p>
			<button type="button" class="update_button">Save changes</button>
		</p>	
	</form>

</body>
</html>

<script type="text/javascript">
	
$(function(){

$(document).on("click",".update_button",function(){


var fname,lname,email,password,cpassword,id;

id=$(".id").val();

fname=$(".fname").val()

if(fname.length<=0){
	$(".fname").next(".error").html("First name is required").css("color","red");
	return false
}
else{
	$(".fname").next(".error").html('');
}


lname=$(".lname").val()

if(lname.length<=0){
	$(".lname").next(".error").html("last name is required").css("color","red");
	return false
}
else{
	$(".lname").next(".error").html('');
}

email=$(".email").val()

if(email.length<=0){
	$(".email").next(".error").html("email id is required").css("color","red");
	return false
}
else{
	$("email").next(".error").html('');
}

password=$(".password").val()

if(password.length<=0){
	$(".password").next(".error").html("password is required").css("color","red");
	return false
}
else{
	$(".password").next(".error").html('');
}

cpassword=$(".cpassword").val()

if(cpassword.length<=0){
	$(".cpasswor").next(".error").html("confirm password is required").css("color","red");
	return false
}
else{
	$(".cpassword").next(".error").html('');
}


// anchronous java script &amp; xml

$.ajax({
	url:"server.php",
	method:"post",
	datatype:"text",
	data:{
		action:"edit",
		fname:fname,
		lname:lname,
		email:email,
		password:password,
		cpassword:cpassword,
		id:id
	},
	beforeSend:function(){

	},
	success:function(data){
		var obj=JSON.parse(data);
		if(obj.success==true){
			window.location.href="users.php";
		}
	}
})

})
})
</script>

Leave a Reply

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