PHP MySQL CRUD (Create, Read, Update, Delete) Operations using jQuery
19:45
PHP MYSQL CRUD
PHP MySQL CRUD is all about INSERT, UPDATE, DELETE and SELECT SQL
queries using PHP , it will help beginners to know about PHP and MySQL
operations.
Note: Don’t use this script into your
live projects, the motive of the script is to just provide basic ideas
to the beginners for learning, if you want to use this feature in the
live projects then you should focus on following points:
Prevent your script from SQL Injections
Use PDO extension or other DBAL/ORM’s like Laravel Eloquent or Doctrine.
Tutorial Features:
Insert records into MySQL Database
Read the records from Database and list
Update the record
Delete the record.
Technologies Used:
HTML
PHP with MySQL
jQuery
Bootstrap
CSS
JSON
Before starting let’s download basic lib files needed : ( if you
already having this lib files you can use your existing files )
Let’s start of creating our demo web application to learn CRUD
operations, first thing we are going see is to create a database and
tables required. ( if you have your database ready in mysql go ahead and
create tables using following sql code) I am assuming that you have
database created and ready to use. users table
1
2
3
4
5
6
CREATE TABLE`test`.`users`(
`id`INT(11)NOTNULLAUTO_INCREMENT PRIMARY KEY,
`first_name`VARCHAR(40)NOTNULL,
`last_name`VARCHAR(40)NOTNULL,
`email`VARCHAR(50)NOTNULL
)ENGINE=MYISAM;
Take a note: test.users – where test is the database name and users is a table name. Users Table Structure
Create index.php file and include basic files for jQuery and Bootstrap as showing below:
So we have our basic file ready to go, now let’s add button to open
add new record popup along with basic formatting like to have page
heading and record container, refer following code:
The above code is actually part of our index.php file, if you look at it, you will notice we have application heading and Add New Record button which refers to add_new_record_modal modal popup. we also have records_content div, this div is going to display the data rendering from Ajax, we are going to see that next.
Now we need to add modal popup, we are using bootstrap modal popups
here, go a head and use following code to include popup in the index.php
page.
If you’re not familiar with Bootstrap no worries you just need to
copy this code later on you can read about it, so now go ahead and add
below modal to you index.php page.
If you notice in the above code we have popup called add_new_record_modal which includes different input field in the ‘modal-body’ tag. so we have first name, last name and email address here. modal-footer – that’s the important part of the popup to
call action such as addRecord, we have button with JS method call with
onclick event.
let’s save index.page page and try to load on browser. Index.php Page: Add New Record Popup:
Next Step is to create Add New Record and Read Records feature.
We have seen the basic setup of our application now we are going to
look at the CREATE and READ operation. That is also called as INSERT and
SELECT operation in MySQL, basically to create new record in the table.
It’s time to code jQuery plus JavaScript:
Let’s create our custom JS file called script.js file under JS folder and add following code: js/script.js
If you notice in the above code, we have addRecord()
function which is doing following operation: Get the Values from the
input fields and send it to addRecord.php file using Ajax call. After
that it’s closing the popup and reading records using readRecords() that is next function to it. Create ajax/addRecord.php file and use following code:
$data.='<tr><td colspan="6">Records not found!</td></tr>';
}
$data.='</table>';
echo$data;
?>
In both files above I have included the db_connection.php file using php include()
function, this files is use to define our database connection string.
It is better practice to add repetitive code in the separate file, let’s
create the file. Create ajax/db_connection.php file.
Note: Make change in the connection file according to your server configuration. (Host, Username, Password and Database name)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?php
// Connection variables
$host="localhost";// MySQL host name eg. localhost
$user="root";// MySQL user. eg. root ( if your on localserver)
$password="";// MySQL user password (if password is not set for your root user then keep it empty )
$database="test_db";// MySQL Database name
// Connect to MySQL Database
$con=newmysqli($host,$user,$password,$database);
// Check connection
if($con->connect_error){
die("Connection failed: ".$con->connect_error);
}
?>
Test the application: try to add new record, you will need to be able
to add the record and have it listed, have a look on below screen shoot
I have added few dummy records:
Fill the fields and click on Add Record button: Add New Test RecordDummy Records List
Whenever we load the page we needs to have our existing records to be
list on the page right? So go ahead ad added following code in to the script.js file and try to page again.
1
2
3
4
$(document).ready(function(){
// READ recods on page load
readRecords();// calling function
});
Are you Good so far?
So now have we have our CREATE and READ feature is ready and tested, let’s go to next step and add DELETE and UPDATE feature as well.
Add DeleteUser() function in the custom scrip.js file:
1
2
3
4
5
6
7
8
9
10
11
12
13
functionDeleteUser(id){
varconf=confirm("Are you sure, do you really want to delete User?");
if(conf==true){
$.post("ajax/deleteUser.php",{
id:id
},
function(data,status){
// reload Users by using readRecords();
readRecords();
}
);
}
}
Create ajax/deleteUser.php file and add following code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?php
// check request
if(isset($_POST['id'])&&isset($_POST['id'])!="")
{
// include Database connection file
include("db_connection.php");
// get user id
$user_id=$_POST['id'];
// delete User
$query="DELETE FROM users WHERE id = '$user_id'";
if(!$result=mysqli_query($con,$query)){
exit(mysqli_error($con));
}
}
?>
UPDATE Feature
How does it work?
Let me explain in the step:
User clicks on update button from the list
Popup open up with the existing details field in
User can click on Save Changes button to update and save the records.
Get back to the code, so add required modal popup to update the record.
Go ahead and use the following html code and add to the index.php page, next to the existing modal popup.
Add getUserDetails() function in to the script.js file:
This function is used to read the existing user details and fill input fields from modal popup and open it up.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
functionGetUserDetails(id){
// Add User ID to the hidden field for furture usage
$("#hidden_user_id").val(id);
$.post("ajax/readUserDetails.php",{
id:id
},
function(data,status){
// PARSE json data
varuser=JSON.parse(data);
// Assing existing values to the modal popup fields
$("#update_first_name").val(user.first_name);
$("#update_last_name").val(user.last_name);
$("#update_email").val(user.email);
}
);
// Open modal popup
$("#update_user_modal").modal("show");
}
Create ajax/readUserDetails.php file:
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
<?php
// include Database connection file
include("db_connection.php");
// check request
if(isset($_POST['id'])&&isset($_POST['id'])!="")
{
// get User ID
$user_id=$_POST['id'];
// Get User Details
$query="SELECT * FROM users WHERE id = '$user_id'";
if(!$result=mysqli_query($con,$query)){
exit(mysqli_error($con));
}
$response=array();
if(mysqli_num_rows($result)>0){
while($row=mysqli_fetch_assoc($result)){
$response=$row;
}
}
else
{
$response['status']=200;
$response['message']="Data not found!";
}
// display JSON data
echo json_encode($response);
}
else
{
$response['status']=200;
$response['message']="Invalid Request!";
}
Add another JS function called UpdateUserDetails() in to the script.js file:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
functionUpdateUserDetails(){
// get values
varfirst_name=$("#update_first_name").val();
varlast_name=$("#update_last_name").val();
varemail=$("#update_email").val();
// get hidden field value
varid=$("#hidden_user_id").val();
// Update the details by requesting to the server using ajax
$.post("ajax/updateUserDetails.php",{
id:id,
first_name:first_name,
last_name:last_name,
email:email
},
function(data,status){
// hide modal popup
$("#update_user_modal").modal("hide");
// reload Users by using readRecords();
readRecords();
}
);
}
Create ajax/updateUserDetails.php file:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?php
// include Database connection file
include("db_connection.php");
// check request
if(isset($_POST))
{
// get values
$id=$_POST['id'];
$first_name=$_POST['first_name'];
$last_name=$_POST['last_name'];
$email=$_POST['email'];
// Updaste User details
$query="UPDATE users SET first_name = '$first_name', last_name = '$last_name', email = '$email' WHERE id = '$id'";
if(!$result=mysqli_query($con,$query)){
exit(mysqli_error($con));
}
}
Folder Structure: Folder Structure
Complete Source code of script.js file:
Finally we are done with the CREATE, READ, UPDATE and DELETE feature,
test it on your system and let me know if you get any issue with the
all above code.
