Learn Infinity

Learn Some Thing New

Single Page CRUD with Php, MySql, validate.js and Ajax-Post

Single Page CRUD with Php, MySql, validate.js and Ajax-Post

In this exercise, we will see an illustration program to figure out how to do database CRUD operations utilizing PHP and MySQL with Ajax-Post. CRUD represented as Create, Read, Update and Delete operations with database table records.

In this post, we will go to handle the data values into a database as well as view page with the help of front-end validations like required details using validate.js. We have users table containing users data like full name, username, password, and etc. Using this users table, we need to perform CRUD utilizing MySQL with Ajax-Post.

Step 1:

Initially, we need to configure our database with our application and create a required table(s) in it. Following code, use to connect our database and create user table structure.

1
2
3
4
5
<?php
$conn = mysql_connect("localhost","root","") or die(mysql_error());
$db = mysql_select_db("php_crud",$conn) or die(mysql_error());
date_default_timezone_set('Asia/Kolkata');
?>

User Table Structure:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE `tv_users` (
  `user_id` int(11) NOT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(250) NOT NULL,
  `full_name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `contact_no` varchar(50) NOT NULL,
  `role` varchar(10) NOT NULL,
  `status` int(11) NOT NULL,
  `created_at` varchar(10) NOT NULL,
  `created_by` int(11) NOT NULL,
  `updated_at` varchar(10) NOT NULL,
  `updated_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step 2:

The below code use to design the Front-End UI to handle the database operations. This HTML form contains input fields to enter user information to be saved into the user table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
<form id="hl_form" name="hl_form">
<input type="hidden" id="form_name" name="form_name" value="add_user" />
<input type="hidden" id="edit_id" name="edit_id" value="0" />
<input type="hidden" id="old_password" name="old_password" value="" />
<div class="row">
<div class="col-md-offset-2 col-md-8">
<div class="panel panel-default">
<div class="panel-heading">User Details:</div>
<div class="panel-body">
<div class="alert icon-alert with-arrow alert-success form-alter" role="alert">
<i class="fa fa-fw fa-check-circle"></i>
<strong> Success ! </strong> Data saved successfully.
</div>
<div class="alert icon-alert with-arrow alert-danger form-alter" role="alert">
<i class="fa fa-fw fa-times-circle"></i>
<strong> Note !</strong> Data saving failed.
</div>
<div class="col-md-6">
<div class="form-group">
<label for="full_name" class="required" >Full Name:</label>
<input type="text" class="form-control" id="full_name" name="full_name" required />
</div>
<div class="form-group">
<label for="username" class="required" >Username:</label>
<input type="text" class="form-control dup-check" id="username" name="username" required />
<span class="dup-chek-details"></span>
</div>
<div class="form-group">
<label for="password" class="required" >Password:</label>
<input type="password" class="form-control" id="password" name="password" required />
</div>
<div class="form-group">
<label for="c_password" class="required" >Confirm Password:</label>
<input type="password" class="form-control" id="c_password" name="c_password" required />
</div>
</div>
<div class="col-md-6">
<div class="form-group">
<label for="email" class="required" >Email:</label>
<input type="email" class="form-control" id="email" name="email" required />
</div>
<div class="form-group">
<label for="contact_no" class="required" >Contact No.:</label>
<input type="text" class="form-control" id="contact_no" name="contact_no" required />
</div>
<div class="form-group">
<label for="user_role" class="required" >User Role:</label>
<select class="form-control selectpicker show-tick" id="user_role" name="user_role" data-live-search="true" required >
<option value="">-- select --</option>
<option value="Admin">Admin</option>
<option value="User">User</option>
</select>
</div>
</div>
<div class="clearfix"></div>
<div class="form-action-group">
<button type="button" class="btn btn-primary btn-form-action btn-submit">Submit</button>
<button type="button" class="btn btn-danger btn-form-action btn-reset">Clear</button>
</div>
</div>
</div>
</div>
</div>
</form>

Step 3:

The below code use to validate the entered user information matched with our requirements or not using validate.js. Validate JS is widely used for validating the HTML forms with rules and expressions.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
$("#hl_form").validate({
// Specify the validation rules
rules: {
full_name: {
required: true
},
email: {
required: true,
email: true
},
username: {
required: true
},
password: {
required: true,
minlength: 5
},
c_password: {
required: true,
minlength: 5,
equalTo: "#password"
},
user_role: "required"
},
// Specify the validation error messages
messages: {
full_name: "Please enter your First Name",
email: "Please enter your Email name",
username: {
required: "Please enter your Username"
},
password: {
required: "Please provide a password",
minlength: "Your password must be at least 5 characters long"
},
c_password: "Password Mismatch",
user_role: "Please Choose User Role"
},
submitHandler: function(form) {
form.submit();
}
});

Step 4:

The below code use to process the form data with validation. If validation gets matched means, it will submit the form data to the server for store the values into database using a $.post() method. Here we can use $.ajax() method also to perform form processing with database and front-end.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
$(document).on('click', '.btn-submit', function(ev){
ev.preventDefault();
var btn_button = $(this);
if($("#hl_form").valid() == true){
var data = $("#hl_form").serialize();
btn_button.html(' <i class="fa fa fa-spinner fa-spin"></i> Processing...');
btn_button.attr("disabled",true);
$.post('save_details.php', data, function(data,status){
console.log("Data: " + data + "\nStatus: " + status);
if( data == "1"){
//alert("Data: " + data + "\nStatus: " + status);
$(".alert-danger").hide();
$(".alert-success").fadeIn(800);
btn_button.html('<i class="fa fa fa-check-circle"></i> Done');
setTimeout(function(){  location.reload(); }, 2000);
}
else{
//alert("Data: " + data + "\nStatus: " + status);
$(".alert-success").hide();
$(".alert-danger").fadeIn(800);
btn_button.html('Submit').attr("disabled",false);
}
});
}
});

and PHP Store procedure,

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
require_once('connect.php');
 
$form_name = $_POST['form_name'];
 
$created_at = $updated_at = date("d/m/Y");
$created_by = $updated_by = 0;
$updated_addon_query = " updated_at='$updated_at', updated_by='$updated_by' ";
 
if($form_name == 'add_user'){
$full_name = mysql_real_escape_string(trim($_POST['full_name']));
$username = mysql_real_escape_string(trim($_POST['username']));
$password = md5($_POST['password']);
$contact_no = mysql_real_escape_string(trim($_POST['contact_no']));
$email = mysql_real_escape_string(trim($_POST['email']));
$user_role = mysql_real_escape_string(trim($_POST['user_role']));
$query = "insert into tv_users(username,password,full_name,email,contact_no,role,created_at,created_by)
  values('$username','$password','$full_name','$email','$contact_no','$user_role','$created_at','$created_by')";
$result = mysql_query($query) or die(mysql_error());
if($result)
echo "1";
else
echo "0";
}

Step 5:

The below code use to retrieve the data’s from the database and display it in our view page with HTML table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
<table class="table table-striped table-hover js-basic-example dataTable">
<thead>
<tr>
<th width="100px">S.No</th>
<th>Name</th>
<th>Email</th>
<th>Contact No.</th>
<th>Role</th>
<th width="150px">Action</th>
</tr>
</thead>
<tbody>
<?php
$sno = 1;
$query = "select * from tv_users";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
?>
<tr>
<td><?php echo $sno++; ?></td>
<td><?php echo $row['full_name']; ?></td>
<td><?php echo $row['email']; ?></td>
<td><?php echo $row['contact_no']; ?></td>
<td><?php echo $row['role']; ?></td>
<td align="center">
<div class="form-group">
<button type="button" class="btn btn-sm btn-primary btn-edit" id="<?php echo $row['user_id']; ?>" title="Edit details" >
<i class="fa fa-pencil-square-o" aria-hidden="true"></i>
</button>
<button type="button" class="btn btn-sm btn-warning btn-status" id="<?php echo $row['user_id']; ?>" data-status="<?php echo $row['status']; ?>" title="Active/Deactive details" >
<?php if(empty($row['status'])){ ?>
<i class="fa fa-thumbs-up" aria-hidden="true"></i>
<?php }else{ ?>
<i class="fa fa-ban" aria-hidden="true"></i>
<?php } ?>
</button>
<button type="button" class="btn btn-sm btn-danger btn-delete" id="<?php echo $row['user_id']; ?>" title="Delete details" data-toggle="modal" data-target="#confirmModal" >
<i class="fa fa-trash-o" aria-hidden="true"></i>
</button>
</div>
</td>
</tr>
<?php
}
?>
</tbody>
</table>

Step 6:

The below code use to process the database data with given condition using $.ajax() – JOSN method. After processing the function it will return the particular record set value with JOSN format. It will use to view the existing details or provide details into form inputs for the update process.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
$(document).on('click', '.btn-edit', function(ev){
ev.preventDefault();
var btn_button = $(this);
btn_button.html(' <i class="fa fa fa-spinner fa-spin"></i> ');
var tbl_id = $(this).attr("id");
$('.btn-reset').trigger('click');
$.ajax({
  cache: false,
  url: 'get_ajax_details.php', // url where to submit the request
  type : "GET", // type of action POST || GET
  dataType : 'json', // data type
  data : { cmd: "get_user_details", tbl_id: tbl_id }, // post data || get data
  success : function(result) {
btn_button.html(' <i class="fa fa fa-pencil-square-o"></i> ');
console.log(result);
$("#form_name").val("edit_user");
$("#edit_id").val(result['user_id']);
$("#full_name").val(result['full_name']).focus();
$("#username").val(result['username']).attr("readonly",true);
$("#email").val(result['email']);
$("#contact_no").val(result['contact_no']);
$("#password").val(result['password']);
$("#c_password").val(result['password']);
$("#old_password").val(result['password']);
$("#user_role").val(result['role']).change();
  },
  error: function(xhr, resp, text) {
console.log(xhr, resp, text);
  }
});
});

and PHP Update procedure,

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
if($form_name == "edit_user"){
$edit_id = mysql_real_escape_string(trim($_POST['edit_id']));
$full_name = mysql_real_escape_string(trim($_POST['full_name']));
$username = mysql_real_escape_string(trim($_POST['username']));
$contact_no = mysql_real_escape_string(trim($_POST['contact_no']));
$email = mysql_real_escape_string(trim($_POST['email']));
$user_role = mysql_real_escape_string(trim($_POST['user_role']));
$password = mysql_real_escape_string(trim($_POST['password']));
$old_password = mysql_real_escape_string(trim($_POST['old_password']));
if($old_password == $password) $new_password = $old_password;
else $new_password = md5($password);
$query = "update tv_users set full_name='$full_name', username='$username', password='$new_password', email='$email', contact_no='$contact_no', role='$user_role', $updated_addon_query  where user_id='$edit_id'";
$result = mysql_query($query) or die(mysql_error());
if($result)
echo "1";
else
echo "0";
}

Step 7:

The below code use to change the record status like Active / Inactive users. Based on the record current status it will change into another status like a toggle.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
$(document).on('click', '.btn-status', function(ev){
ev.preventDefault();
var btn_button = $(this);
var status = 0;
btn_button.html(' <i class="fa fa fa-spinner fa-spin"></i> ');
var tbl_id = $(this).attr("id");
var tbl_status = $(this).data("status");
if(tbl_status == 0) status = 1;
else status = 0;
$.post('save_details.php', { form_name: "user_status", tbl_id: tbl_id, status: status }, function(data,status){
console.log(data);
if(data == "1"){
$('.warning-modal-message').html("Record status changed successfully.");
$('#warningModal').modal('show');
setTimeout(function(){  location.reload(); }, 2000);
}
else{
$('.warning-modal-message').html("Data deletion failed.");
}
});
});

and PHP Update procedure,

1
2
3
4
5
6
7
8
9
10
11
if($form_name == "user_status"){
$tbl_id = mysql_real_escape_string($_POST['tbl_id']);
$status = mysql_real_escape_string($_POST['status']);
$query = "update tv_users set status='$status' where user_id='$tbl_id'";
$result = mysql_query($query) or die(mysql_error());
if($result)
echo "1";
else
echo "0";
}

Step 8:

The below code use to delete or remove the unwanted record or information from the database table. Before doing this we need to get the approval details to avoid unexpected data loss. For this here we user modal dialog box to get the confirmation while clicking the delete button. If click confirms button, our system will send the request to the server for deletion process.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
$(document).on('click', '.btn-confirm-delete', function(ev){
ev.preventDefault();
var btn_button = $(this);
var tbl_id = $('.btn-confirm-delete').attr("id");
$('#confirmModal').modal('hide');
$.post('save_details.php', { form_name: "del_user", tbl_id: tbl_id }, function(data,status){
console.log(data);
if(data == "1"){
btn_button.html('<i class="fa fa fa-check-circle "></i> Done');
$('.warning-modal-message').html("This details deleted successfully.");
$('#warningModal').modal('show');
setTimeout(function(){  location.reload(); }, 2000);
}
else if(data == "404-del"){
$('.warning-modal-message').html("This details reflect in another record. So you can't delete !!!");
$('#warningModal').modal('show');
}
else{
$('.warning-modal-message').html("Data deletion failed.");
btn_button.html('Yes');
}
});
});
 
$(document).on('click', '.btn-delete', function(ev){
ev.preventDefault();
$(".btn-confirm-delete").attr("id",$(this).attr('id'));
});
 
$(document).on('click', '.btn-confirm-close', function(ev){
ev.preventDefault();
$(".btn-confirm-delete").attr("id","0");
});

Modal popup confirmation dialog box,

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<!-- Small Size -->
<div class="modal fade" id="confirmModal" tabindex="-1" role="dialog">
<div class="modal-dialog modal-md" role="document">
<div class="modal-content modal-col-danger">
<div class="modal-header">
<h4 class="modal-title" id="smallModalLabel">Confirmation:</h4>
</div>
<div class="modal-body">
Do you want to Delete This Record ?
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default btn-confirm-delete">Confirm</button>
<button type="button" class="btn btn-default btn-confirm-close" data-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
 
 
<!-- Small Size -->
<div class="modal fade" id="warningModal" tabindex="-1" role="dialog">
<div class="modal-dialog modal-md" role="document">
<div class="modal-content">
<div class="modal-header">
<h4 class="modal-title" id="smallModalLabel">Info:</h4>
</div>
<div class="modal-body warning-modal-message">
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default btn-warning-close" data-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>

and PHP Delete procedure,

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
if($form_name == "del_user"){
$chk_val = 0;
$tbl_id = mysql_real_escape_string($_POST['tbl_id']);
if( $chk_val == 0){
$query = "delete from tv_users where user_id='$tbl_id'";
$result = mysql_query($query) or die(mysql_error());
if($result)
echo "1";
else
echo "0";
}
else{
echo "404-del";
}
}

Download   Demo

Prev post Digital E-Signature pad with saving it as image using html2canvas and Ajax, PHP
Next post Embedding or Installing PHP inside HTML page

Learn Infinity

Learn Infinity is the most famous Programming & Web Development blog. Our principal is to provide the best online tutorial on web development. We execute the best tutorials for web experts this will help developers, programmers, freelancers and proving free resource you can download or preview the tutorials.

Related Posts

Leave a Reply

Your email address will not be published.

Back to top