Updating data in the database without a page refresh is an excellent way to achieve user experience as it is 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. The admin can unlock the account or even lock an account if required by just clicking a button. This can be applied to any similar 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 an html table with the 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.
Folders and Files
The folder structure and files we will be using are given below:
We have a '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 the database in an html table and provides the option for the admin to change the 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 displays the updated list of users.
Below is a screenshot of the list of users displayed by index.php.
Check the last two columns; the status of the user is displayed and then a button is used to make the user Active/Inactive depending on the current status of the user.
We will create a table named 'user' in the MySQL database. We will use the "test" database. Just make sure the same table does not exist already. The 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;
Connect to MySQL database (dbconnect.php)
Use the 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 a list of users with their status in an html table. There will be an 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 the 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 has been added to run jQuery Ajax. Our jQuery Ajax code will be written in js/status.js. Note that we have included "table_data.php" to display data in an html table. See below:
Let us see the last two columns of the table. If the 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.
Write Ajax script to update status (status.js and update_user_status.php)
This function runs an Ajax request with the below parameters:
type: post. We are using the POST method
url : update_user_status.php. This PHP updates the status of the user and refreshes the user list in the table
data: we are sending two data values with the request, one is userid and the other is status
success: we write the response of the Ajax request in the table with the class "table" defined in table_data.php. Response from Ajax call is nothing but displaying an updated list of users. In case of error, we display the error message in a div element 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 updates the user status 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, the updated status of the user will be visible.
Start Apache and MySQL services in the 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 is changed. Also, the action button for that user changes after the status gets changed. Check in the database table also if the status for the user is updated in the ‘users’ table.
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 the user to update specific data on a web page without a form submit or page reload. I hope it will be useful for you.
Post a Comment