Posted on by

How to display data from database in PHP using Ajax and JSON

As a PHP beginner working with json sometime becomes very difficult. And this difficulty converts into nightmare when json is used with ajax. But don’t worry in this tutorial I will show you how to display data from database in php using ajax and json.

I will create an Employee search form in which user need to enter first name to get the employee details. I will use Bootstrap 4 for frontend and jQuery ajax function to trigger ajax. For database connection I will use PHP PDO connection.

Also Read:


Display data from database in PHP using Ajax and JSON

demo display data from database in php using ajax and json

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
 
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
33
34
35
36
37
38
39
40
41
42
43
 
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', 'United Kingdom', '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');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (31, 'Melvin', 'Ward', '891 Marvin Path\nCorkeryshire, MD 03561-1051', 'edmund.huel@example.com', '879-914-5175 x884', 'Holy See (Vatican City State)', 'Carleeville');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (32, 'Chaim', 'Gorczany', '7814 Reichel Corner Suite 259\nRebaport, MI 56331-5271', 'welch.ena@example.com', '(438) 742-7902 x61501', 'Czech Republic', 'North Bartholome');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (33, 'Glenna', 'Blick', '787 Felicita Meadows Suite 154\nLeonardoborough, CA 20969', 'considine.jon@example.com', '1-462-734-4174', 'Thailand', 'Helenaville');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (34, 'Stephon', 'Kerluke', '32673 Windler Cove\nKutchfort, MD 49339-7545', 'kihn.keshaun@example.com', '1-629-944-0402 x3905', 'Azerbaijan', 'New Davionport');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (35, 'Gregory', 'Kuvalis', '7875 Brigitte Hills\nDianamouth, NC 99458', 'dasia.schimmel@example.org', '(847) 719-0192', 'Turks and Caicos Islands', 'Beckershire');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (36, 'Nathan', 'Hane', '8778 Nader Branch Suite 521\nNorth Germanville, MT 91405', 'crist.mckayla@example.org', '(316) 229-3693 x1819', 'Germany', 'Port Dorthystad');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (37, 'Mazie', 'Tromp', '93613 Becker Courts\nSouth Shayneton, MT 63141-1374', 'dudley10@example.org', '1-557-810-3554', 'Guadeloupe', 'Lake Vicenta');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (38, 'Carolyn', 'Anderson', '921 Gwendolyn Manor\nEast Alfonso, NC 47005', 'bosco.myrtis@example.com', '203-860-5133 x0686', 'Guernsey', 'Mohammedbury');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (39, 'Chloe', 'Murray', '73183 Adolf Avenue Suite 755\nWest Cassie, SD 97551-5896', 'bschiller@example.org', '368.775.6726', 'Holy See (Vatican City State)', 'New Lelandside');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (40, 'Wilfrid', 'Watsica', '486 Jerry Fords Suite 256\nDanialshire, ND 98164', 'qdickinson@example.com', '729-334-7231', 'Mongolia', 'Port Horaceside');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (41, 'Lesley', 'Cronin', '4383 Haley Flats\nRoyalmouth, LA 67647', 'sterry@example.com', '1-867-221-0503', 'Spain', 'New Blaise');
 

HTML:

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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
 
<title>Display data from database in php using ajax and json</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" integrity="sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z" crossorigin="anonymous">
</head>
<body>
<div class="container-fluid">
<div class="row mt-3">
<div class="col-md-5">
<h4>Employee Name Search</h4>
<form class="form-inline" method="POST" id="userFrm">
<div class="form-group mb-2">
<label for="userName">Enter First Name: </label>
<input id="userName" type="text" name="user" class="form-control" required>
</div>
<button type="submit" class="btn btn-primary mb-2 ml-2">Submit</button>
<button type="button" class="btn btn-primary mb-2 ml-2" id="clearBtn">Clear</button>
<div id="loader" class="ml-2 d-none"><img src="loader.gif"></div>
</form>
</div>
</div>
<div id="showAjaxResults"></div>
</div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js" integrity="sha384-9/reFTGAW83EW2RDu2S0VKaIzap3H66lZH81PoYlFhbGU+6BZp6G7niu735Sk7lN" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js" integrity="sha384-B4gt1jrGC7Jh4AgTPSdUtOBvfO8shuf57BaghqFfPlYxofvL8/KUEfYiJOMMV+rV" crossorigin="anonymous"></script>
<script>
$(document).ready(function(){
$("#userFrm").submit(function(e){
e.preventDefault();
var getName= $("#userName").val();
$("#loader").removeClass('d-none');
$.ajax({
dataType:'json',
type: 'POST',
data: {name:getName},
url: 'get-user-data.php',
success:function(response){
$("#loader").addClass('d-none');
if(response.length > 0)
{
var displayTable = htmlTable(response);
$("#showAjaxResults").html(displayTable);
}
else
{
$("#showAjaxResults").html("No user found");
}
}
});
});
$("#clearBtn").click(function(){
$("#showAjaxResults").html('');
});
});
function htmlTable(data)
{
if(Array.isArray(data))
{
html = data.length+' records found';
html += '<table class="table table-bordered">';
html += "<tr>";
html += "<th>First Name</th>";
html += "<th>Last Name</th>";
html += "<th>Email</th>";
html += "<th>Phone</th>";
html += "<th>Country</th>";
html += "</tr>";
for(i=0;i<=data.length-1;i++)
{
html += "<tr>";
html += "<td>"+data[i]['first_name']+"</td>";
html += "<td>"+data[i]['last_name']+"</td>";
html += "<td>"+data[i]['email']+"</td>";
html += "<td>"+data[i]['phone']+"</td>";
html += "<td>"+data[i]['country']+"</td>";
html += "</tr>";
}
html += '</table>';
return html;
}
return false;
}
</script>
</body>
</html>
 

PHP JSON Response:

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
 
<?php
 
if(isset($_POST['name']) && !empty($_POST['name']))
{
//Database Connection
try{
$conn = new PDO('mysql:host=localhost;dbname=demo','root','');
}
catch(PDOException $e){
echo "Connection failed:".$e->getMessage();
}
$user = trim($_POST['name']);
 
$sql = "select * from customers where first_name like :name";
$handle = $conn->prepare($sql);
$params = ['name' => "%".$user."%"];
$handle->execute($params);
echo json_encode($handle->fetchAll(PDO::FETCH_ASSOC));
exit();
}
 
echo "[]";
 
?>
 

 

More from WDB24.com

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 *