Posted on by

Show data from database on page load using Ajax, Php and jQuery

In this tutorial I am going to discuss how to show data from database on page load. With the help of AJAX we are going to achieve this. I know AJAX is one of the very hot topic for PHP beginners. Because it is using almost in every site. I will use jQuery and its ajax function to trigger ajax.

Also Read:

What I am going to do?

I will create a page that shows customer information in a table. I will achieve this with the help of following steps.

  • I will trigger ajax on page load (index.php). I will also show a loading bar unless I get the data.
  • Ajax request will receive by ajax_get_data.php. ajax_get_data.php fetch records from mysql database using MYSQLi OOP.
  • Then I will create a html table and insert customers record in table column and print table.
  • Ajax response(table html) will receive on index.php page. Then i will show that response on index.php page.

Show data from database Ajax, Php and jQuery

Show data from database on page load using ajax, php and jquery

Create Database:

1
2
3
 
create database demo;
 

Create Mysql Table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
CREATE TABLE `customers` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(255) NULL DEFAULT NULL,
`last_name` VARCHAR(255) NULL DEFAULT NULL,
`address` VARCHAR(255) NULL DEFAULT NULL,
`email` VARCHAR(255) NULL DEFAULT NULL,
`phone` VARCHAR(255) NULL DEFAULT NULL,
`country` VARCHAR(255) NULL DEFAULT NULL,
`city` VARCHAR(255) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;
 

Insert Records In Mysql 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
 
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (1, 'Alvina', 'Champlin', '3605 Larson Meadow\nCasandraville, VT 73844-0293', 'schiller.thomas@example.net', '+15718182740', 'Congo', 'North Brody');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (2, 'Jeanette', 'Johns', '38265 Stehr Course Apt. 958\nSouth Berthafort, AK 47740', 'miracle72@example.com', '1-761-330-5631 x8750', 'Isle of Man', 'North Josephine');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (3, 'Felicity', 'Walsh', '392 Kautzer Mountain\nAronstad, FL 34152', 'zulauf.melody@example.net', '1-528-589-0695', 'Belize', 'Katrineberg');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (4, 'Alexandria', 'Goodwin', '7192 Dietrich Unions Suite 178\nNorth Fatimamouth, AL 54635-8353', 'reilly94@example.org', '1-380-388-6370 x8648', 'Guam', 'Collierville');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (5, 'Francis', 'Paucek', '19435 Cicero Well\nWest Jarrell, MA 51035-4794', 'juana.fahey@example.org', '1-995-393-1347 x986', 'Bouvet Island (Bouvetoya)', 'New Eliseohaven');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (6, 'Jerrod', 'Kilback', '359 Roob Fall Suite 950\nEast Jaida, ME 49692-0730', 'cecelia.kub@example.net', '836.979.1645 x343', 'India', 'Boyerhaven');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (7, 'Royal', 'Waelchi', '29651 Moen Shores Apt. 710\nWest Ninabury, AL 37371-4514', 'bkoss@example.org', '937-421-6387 x182', 'Netherlands Antilles', 'Lake Lorinemouth');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (8, 'Prudence', 'Padberg', '11889 Cathy Stravenue\nWalshfort, IA 12348-3147', 'amelia24@example.net', '(802) 955-6535 x78249', 'Peru', 'Albinaborough');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (9, 'Wilmer', 'Stoltenberg', '324 Izaiah Courts\nShyannville, MN 58488', 'auer.amber@example.org', '+1.425.306.7890', 'UK', 'East Helmer');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (10, 'Bulah', 'Rohan', '4859 Blake Islands Suite 518\nKleinton, OR 86307', 'brayan99@example.net', '(412) 934-6791', 'Georgia', 'Adahview');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (11, 'Roselyn', 'Lockman', '4345 Nitzsche Mountains Suite 107\nEast Lavonne, PA 92446', 'berneice.reinger@example.net', '1-710-786-9448', 'Monaco', 'Eugeniahaven');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (12, 'Lora', 'Ryan', '51854 Stefan Mission Apt. 834\nNew Adolfotown, HI 18148', 'carter.smitham@example.net', '(280) 692-7491', 'Bulgaria', 'New Wilhelmineville');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (13, 'Princess', 'Sanford', '816 Welch Land Suite 770\nLuciusshire, MS 42451-5941', 'wuckert.macey@example.net', '937.706.9143', 'Comoros', 'Uptonton');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (14, 'Yadira', 'Luettgen', '16318 Bonita Bridge Apt. 709\nAthenaport, IL 26511', 'zstrosin@example.net', '+1-294-375-1907', 'Vanuatu', 'Zulaufborough');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (15, 'Treva', 'Jones', '10151 Shawna Shore Apt. 033\nMarjoriemouth, KY 22030-1574', 'effie21@example.org', '1-391-670-3346 x25463', 'Cayman Islands', 'Lake Flavio');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (16, 'Lincoln', 'Bode', '1414 Reinger Rest Apt. 707\nSouth Kayachester, AR 39004-3831', 'richard81@example.net', '+1.963.935.0710', 'Turks and Caicos Islands', 'North Adam');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (17, 'Ilene', 'Marks', '4231 Minnie Ferry\nMedhurstburgh, WY 70923-2667', 'ludwig20@example.org', '896.479.9846 x3199', 'Heard Island and McDonald Islands', 'New Yesseniaburgh');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (18, 'Francisca', 'Rowe', '853 Albina Groves\nAsamouth, NM 27737-1890', 'odie.schowalter@example.com', '528.595.8020', 'Myanmar', 'Murazikfort');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (19, 'Alexie', 'Langosh', '80539 Barrows Stream Apt. 651\nNorth Joshuahaven, CO 01023-3558', 'fahey.cassie@example.net', '(658) 878-7250 x92671', 'Cocos (Keeling) Islands', 'Pollyton');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (20, 'Macey', 'Towne', '3383 Andrew Lights\nSouth Lilianechester, DC 59528-5129', 'wkozey@example.org', '217.941.6891 x200', 'Monaco', 'Wolfshire');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (21, 'Chanel', 'Metz', '46394 Henri Ridge Suite 479\nCarterburgh, WY 68717', 'verlie22@example.com', '791-339-7627 x997', 'Botswana', 'New Willhaven');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (22, 'Amy', 'Kihn', '9103 Colt Mountains\nSouth Virgieburgh, NM 94218', 'gardner25@example.org', '1-767-419-4318 x078', 'Honduras', 'Port Jermaineville');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (23, 'Bell', 'Considine', '757 Aurelia Dam Suite 479\nOtisside, TN 21421-1341', 'hipolito.robel@example.net', '267-277-9949', 'Denmark', 'West Jannie');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (24, 'Bessie', 'Marvin', '8656 Vivienne Expressway\nCrooksshire, OR 27293', 'smitham.osvaldo@example.com', '(886) 260-6447 x45131', 'Saint Helena', 'McClureland');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (25, 'Wilmer', 'Fahey', '815 Ned Viaduct Apt. 575\nNew Agustinmouth, IN 62033', 'prohaska.jaylin@example.com', '647-912-3180 x0905', 'Saint Vincent and the Grenadines', 'South Abbiefurt');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (26, 'Jettie', 'Leffler', '54351 Johanna Radial Apt. 305\nKeelingfort, AL 64645-3086', 'uosinski@example.com', '662.543.9041 x0938', 'Western Sahara', 'New Lavernaview');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (27, 'Serena', 'Trantow', '7674 Hirthe Street\nSchroederton, DE 90488-5532', 'zackary63@example.org', '563.692.9141 x701', 'Guinea-Bissau', 'Lake Ambrose');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (28, 'Bradly', 'Wiegand', '646 Yost Landing Apt. 211\nHagenesborough, KS 13712', 'bridget.rice@example.net', '+1 (227) 935-1583', 'Uzbekistan', 'Daneland');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (29, 'Ford', 'Zboncak', '9406 Akeem Harbors Suite 742\nFarrellfort, WV 29735', 'alfonso.streich@example.org', '557-538-5973 x165', 'Iraq', 'New Darienhaven');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (30, 'Daphne', 'Corkery', '339 Angel Cliffs Suite 830\nLake Chaunceyburgh, NY 07754-0015', 'casey.wintheiser@example.net', '259.748.0377', 'Indonesia', 'Powlowskifurt');
 

Create HTML:

There is a div with a id customer-data. I will show customer data in customer-data div. ajax-loader.gif is a loader image.

showing ajax loader

1
2
3
4
5
6
7
8
9
 
<div class="container">
<h1>Show data from database on page load using php, ajax and jquery</h1>
 
<div id="customer-data">
<img src="img/ajax-loader.gif">
</div>
</div>
 

CSS:

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
 
body{
font-family:verdana;
font-size:14px;
background:#ffd0d0;
}
 
.container{
width:1120px;
margin:0 auto;
border:1px solid #eeeeee;
background:#ffffff;
padding:10px;
}
 
h1{
text-align:center;
color:#e31616;
font-size:20px;
}
table{
border:1px solid #eeeeee;
border-collapse: collapse;
width:100%;
}
 
table th{
border:1px solid #eeeeee;
text-align:center;
color:#e31616;
height:40px;
}
table td{
border:1px solid #eeeeee;
padding:5px;
}
 

jQuery Ajax Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
 
<script>
$(document).ready(function(){
$.ajax({
url: 'ajax_get_data.php',
success: function(data){
$("#customer-data").html(data);
}
})
});
</script>
 

Ajax Code:

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
 
<?php
$host = "localhost";
$dbUser = "root";
$password = "";
$database = "demo";
 
$dbConn = new mysqli($host,$dbUser,$password,$database);
 
if($dbConn->connect_error)
{
die("Database Connection Error, Error No.: ".$dbConn->connect_errno." | ".$dbConn->connect_error);
}
 
 
$qry = "select first_name, last_name, email, phone, city, country from customers";
 
$rs = $dbConn->query($qry);
 
$fetchAllData = $rs->fetch_all(MYSQLI_ASSOC);
 
$createTable = '<table>';
 
$createTable .= '<tr>';
$createTable .= '<th>First Name</th>';
$createTable .= '<th>Last Name</th>';
$createTable .= '<th>Email</th>';
$createTable .= '<th>Phone</th>';
$createTable .= '<th>City</th>';
$createTable .= '<th>Country</th>';
$createTable .= '</tr>';
 
 
foreach($fetchAllData as $customerData)
{
$createTable .= '<tr>';
$createTable .= '<td>'.$customerData['first_name'].'</td>';
$createTable .= '<td>'.$customerData['last_name'].'</td>';
$createTable .= '<td>'.$customerData['email'].'</td>';
$createTable .= '<td>'.$customerData['phone'].'</td>';
$createTable .= '<td>'.$customerData['city'].'</td>';
$createTable .= '<td>'.$customerData['country'].'</td>';
$createTable .= '</tr>';
}
 
$createTable .= '</table>';
 
echo $createTable;
 
$rs->close();
 
$dbConn->close();
 
?>
 

 

More from WDB24.com

Posted in PHP

Published by Ahsan Zameer

I am a web developer and love to search new stuff on web. Trying different approaches and ideas to make web developing more interesting and enjoyable.

Leave a Reply

Your email address will not be published. Required fields are marked *