How to update data using Ajax in PHP and MySQL without page refresh
Updating data in the database without a page refresh is an excellent way to achieve user experience as it is very fast. Using Ajax with PHP, you can update or refresh a specific part of the web page without reloading the entire page.
In this topic, I will develop an application to update user status (Active or Inactive) without submitting a form using PHP, Ajax and MySQL. Admin can unlock the account or even can lock an account if required by just clicking a button. This can be applied to any similar type of requirement where a specific field is to be updated in the database and the updated value is to be displayed on the screen instantly.
So, how it works? We will first display a list of users from the database in a html table with details of users with their status. If the user status is Active, Admin can make it Inactive by clicking a button. Similarly, if the user status is Inactive, Admin can activate the user by clicking the button. An Ajax script is executed on the onClick event of the button to update the status in the database. After updating the status, it displays the list of users with updated status.
Assumption
You need basic knowledge of PHP, HTML, CSS and JavaScript/jQuery.
Also you should have basic understanding of Ajax. You can read below topics for more details:
Folder structure and files we will be using as given below:
We have 'user' folder under 'xampp/htdocs'.
Folder 'cfg' is for dbconnect.php which is used to establish a connection to the MySQL database.
Folder 'css' is for custom stylesheet (style.css).
Folder 'js' is for custom JavaScripts file (status.js). Ajax Script is written in this file.
index.php displays a list of users from database in an html table and provides option for admin to change status of a user.
update_user_status.php is the php program called from Ajax. This php program updates the user status in MySQL database and display the updated list of users.
Below is a screenshot of list of users displayed by index.php.
Check the last two columns; status of the user is displayed and then a button is displayed to make the user Active/Inactive depending on current status of the user.
We will create a table named 'user' in MySQL database. We will use "test" database. Just make sure same table does not exist already. Table structure is given below:
Table: user
This table stores all users.
Table has 4 columns in it.
id - primary key and auto incremented to identify each row
name - Name of the user
email - email ID of the user
status - Status of the user, enum (A - Active, I - Inactive)
Script to create the table and insert statements for this table are given below; you can use this code to create the table and populate data.
user.sql
CREATE TABLE `users` (
`id` int(11) NOT NULL,
`name` varchar(200) NOT NULL,
`email` varchar(100) NOT NULL,
`status` enum('A','I') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
INSERT INTO `users` (`id`, `name`, `email`, `status`) VALUES
(1, 'User Name1', 'user1@test.com', 'I'),
(2, 'User Name2', 'user2@test.com', 'A'),
(3, 'User Name3', 'user3@test.com', 'I'),
(4, 'User Name4', 'user4@test.com', 'A'),
(5, 'User Name5', 'user5@test.com', 'A');
ALTER TABLE `users`
ADD PRIMARY KEY (`id`);
ALTER TABLE `users`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
Step 2 - Connect to MySQL database (dbconnect.php)
Use below script to connect to the database. This will be included in the PHP program whenever we need database access.
dbconnect.php
$server = "localhost";
$uid = "root";
$pwd = "";
$dbname = "test";
$conn = new mysqli($server, $uid, $pwd, $dbname);
if ($conn->connect_error)
die ("Error in Connection".$conn->connect_error);
Our index.php displays the list of users along with their status in an html table. There will be Action for each user either to make a user active or inactive. It is a simple php code to select all users from the database and display them in an html table. Depending on status of the user a green or red button is displayed. Let's see the code:
index.php
<!DOCTYPE html>
<html lang="en">
<head>
<title>Use Ajax in PHP</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="css/style.css" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
<script src="js/status.js"></script>
</head>
<body>
<?php
include "cfg/dbconnect.php";
$sql = "select * from users order by name";
$stmt = $conn->prepare($sql);
$stmt->execute();
$result = $stmt->get_result();
?>
<div class="container">
<h1>Use Ajax in PHP</h1>
<div class="table-responsive">
<h2>List of Users</h2>
<?php include "table_data.php"; ?>
</div>
</div>
</body>
</html>
we are using Bootstrap 5, jQuery library is added to run jQuery Ajax. Our jQuery Ajax code will be written in js/status.js. Note that we have included "table_data.php" which is used to display data in an html table. See below:
Let us see the last two columns of the table. If status is 'A', it displays 'Active', otherwise it displays 'Inactive'. JavaScript function updateStatus() is called on the onClick event of the buttons with two parameters, userid and status.
Step 4 - Write Ajax script to update status (status.js and update_user_status.php)
This function runs an Ajax request with below parameters:
type: post. We are using POST method
url : update_user_status.php. This PHP updates the status of the user and refresh the user list in the table
data: we are sending two data values with the request, one is userid and other is status
success: we write the response of Ajax call in the table with class "table" which is defined in table_data.php. Response from Ajax call is nothing but displaying updated list of users. In case of error we are displaying the error message in a div with class="errMsg" defined in table_data.php.
Now, let us see the code for update_user_status.php.
update_user_status.php
include "cfg/dbconnect.php";
$id = $_POST['id'];
$status = $_POST['status'];
$sql_u = "update users set status = ? where id = ?";
try{
$stmt_u = $conn->prepare($sql_u);
$stmt_u->bind_param("si",$status, $id);
$stmt_u->execute();
$sql = "select * from users order by name";
$stmt = $conn->prepare($sql);
$stmt->execute();
$result = $stmt->get_result();
// refresh the table to show updated status
include "table_data.php";
}
catch(Exception $e){
echo "error";
}
It takes the userid and status from Ajax data parameter and update the status of the user in the database. Once updated, it displays the list of users in the html table. We just included table_data.php. So, in this table updated status of the user will be visible.
Start Apache and MySQL services in XAMPP control panel. In the browser run localhost/user_admin. You will see the home page as displayed below:
You will see the users are displayed with name, email, status and action for each user. Click on the button for a user and see if the status gets changed. Also, the action button for that user changes after the status gets changed. Check in database table also if the status for the user is updated in the users table.
Our development and testing are complete now. Hope you could understand all the steps and test it successfully.
Scope of Improvement
You can use a search option in case number of users are more. Here we are using only 5 users so search is not important. Application might have thousands of users we do not want to display all users at the same time. So, better option is to use search criteria. It will display only those users matching search criteria and then update the status.
Download Source Code
Click on the Download button below and download the zip file. You can always modify them as per your requirements.
Conclusion
You can apply this in your project where you want user to update specific data in a web page without a form submit or page reload. Hope it will be useful for you.
