In the last post we entered our values without refreshing our page. In this post we will learn how to retrieve data from a database in PHP using AJAX jQuery method. We will fetch data from the database without refreshing the webpage. So let\’s get started, but before you read the last post, you can read it here.
See also
How to Insert data to MySql from PHP using jQuery AJAX
Here we using three file for View/display data in MySql database using Ajax.
Creating the Database Table
The first step in this tutorial is the creation of a MySQL database. you can create tables by running SQL queries. Create a table ‘students’ in database using the following SQL query.
-- -- Table structure for table `users` -- CREATE TABLE `users` ( `id` int(50) NOT NULL PRIMARY KEY AUTO INCREAMENT, `name` varchar(50) NOT NULL, `email` varchar(50) NOT NULL, `password` varchar(50) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
You also need to insert some values in your table
Create MySql Connection in PHP
For importing and exporting database in MySql will make a separate file ‘database.php’. Add the following code and replace the database credentials with yours. You can find your db credentials in Application Access details:
<?php
// create database connectivity
$servername = "localhost";
$username = "root";
$password = "";
$database = "testing";
// Create connection
$con = new mysqli($servername, $username, $password, $database);
// Check connection
if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}
?> Server name is your web server name if your are using XAMPP than your server name is localhost username is root, password is blank because in my case I have no password and database name is testing.
If connection is successfully than retrieve data from the database table called users
Include your database.php connection file in action.php page.
Creating HTML Page
- Now we will create a simple index page to show users table records after inserting in users table by fetching value from database.
- You can use the following HTML code to create your page. Copy and save it as index.php in your project folder.
index.php
<!DOCTYPE html>
<html lang="en">
<head>
<title>How to display data to MySql from PHP using jQuery AJAX</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<h2 style="text-align: center;">How to display data to MySql from PHP using jQuery AJAX</h2>
<div id="tableData">
</div>
</div>
</body>
</html>- To write the jQuery Script we should open <script> tag. You can put it in after </head> element.
Creating jQuery AJAX Script.
<script type="text/javascript">
// fetch data from student table..
function loadTableData(){
$.ajax({
url : "display.php",
type : "POST",
success:function(data){
$("#tableData").html(data);
}
});
}
loadTableData();
</script>Index.php file work is finish now working on view.php file first of all create database connectivity for retrieve data from the database.
action.php
<?php
// include database connectivity
include_once('database.php');
//SELECT DATA FROM DATABASE
$query = "SELECT * FROM users";
$result = $con->query($query);
$rowArr = [];
if ($row = $result->fetch_array(MYSQLI_ASSOC)) {
$rowArr[] = $row;
return $rowArr;
}else{
return false;
}
?>You can always support by sharing on social media or recommending my blog to your friends and colleagues.
If you have any suggestions / problems about this tutorial, please comment on the form below.😊
Nice! Thank you!
I noticed one small error:
url : "display.php"
but your tutorial references view.php
I would love to see a bit more in this for the Edit and Delete buttons you created!