PHP Fetch Data From MySQL DB Without Page Refresh Using Jquery Ajax

 PHP Fetch Data Without Page Refresh JQuery Ajax

In this php tutorial you will learn how to fetch data from mysql daabase using jquery ajax. jquery ajax tutorial to fetch and display data from mysql database and display data from mysql table in html form using html tags. 

Create Database And Table

  
  CREATE DATABASE att_kpiauthdb;
  CREATE TABLE `users` (
  `id` int(15) NOT NULL,
  `firstname` varchar(100) NOT NULL,
  `lastname` varchar(100) NOT NULL,
  `email` varchar(50) NOT NULL,
  `phone` varchar(150) NOT NULL,
  `user_type` varchar(50) NOT NULL,
  `user_status` int(2) NOT NULL,
  `password` varchar(150) NOT NULL,
  `token` varchar(150) NOT NULL,
  `reg_date` date NOT NULL,
  `profile_pic` varchar(150) DEFAULT NULL,
  `bind_userid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `users` (`id`, `firstname`, `lastname`, `email`, `phone`, `user_type`, `user_status`, `password`, `token`, `reg_date`, `profile_pic`, `bind_userid`) VALUES
(1, 'Khaled', 'irshad', 'bad3r1407@hotmail.com', '030068423088', 'user', 1, '202cb962ac59075b964b07152d234b70', 'c5826a459cbc521d2af90a835bbb2b1e', '2019-10-02', 'parent dashboard.png', NULL),
(2, 'wajid', 'malik', 'wajidirshad92@gmail.com', '030068423088', 'admin', 1, '202cb962ac59075b964b07152d234b70', '74f9f24ed9cddd0aba2da1f061fc9795', '2019-10-02', 'Capture.PNG', NULL),
(3, 'majid', 'irshad', 'majidirshad10@gmail.com', '030068423088', 'user', 1, '202cb962ac59075b964b07152d234b70', '90b5f2e8dfeae30bfe4c0b9d9fd0b281', '2019-10-02', 'Capture4.PNG', 3),
(4, 'ahmed', 'mubeen', 'ahmed@gmail.com', '030068423088', 'user', 1, '81dc9bdb52d04dc20036dbd8313ed055', '7eeb1e912da1b0821a4fdc4f84a8d658', '2019-10-05', NULL, 1),
(5, 'shakeel', 'akhter', 'sak34@gmail.com', '030068423088', 'user', 1, '202cb962ac59075b964b07152d234b70', 'a4b830124d9f76d84f18fb66de43a66c', '2019-10-05', 'vlcsnap-2019-08-03-18h15m32s618.png', 2),
(6, 'khalil', 'saraj', 'khalil@gmail.com', '030068423088', 'user', 1, '81dc9bdb52d04dc20036dbd8313ed055', '7563d4b6307fcd791c758df345c34ec2', '2019-10-05', NULL, NULL),
(7, 'sheraz', 'riaz', 'sheraz@gmail.com', '030068423088', 'user', 1, '202cb962ac59075b964b07152d234b70', '8207fa2df4f99c65c2196f9bf7c01b37', '2019-11-11', NULL, NULL),
(8, 'waheed', 'irshad', 'waheed@gmail.com', '030068423088', 'user', 0, '202cb962ac59075b964b07152d234b70', 'ea0aa42c2b3340889d5034896c15c97e', '2019-11-11', NULL, NULL),
(9, 'asif', 'asif', 'asif@gmail.com', '030068423088', 'user', 0, '202cb962ac59075b964b07152d234b70', 'beb88e53d5bdeeb58cdfc8d0ddd8c653', '2019-11-11', NULL, NULL),
(10, 'qaiser', 'ali', 'qaiser@gmail.com', '030068423088', 'user', 0, '202cb962ac59075b964b07152d234b70', '821841c9aa6fcf396c92343720264ebc', '2019-11-11', NULL, NULL),
(11, 'shahid', 'ali', 'shahid@gmail.com', '030068423088', 'user', 0, '202cb962ac59075b964b07152d234b70', '74f44742f95db2b4c4091f09fe1dd354', '2019-11-11', NULL, NULL),
(12, 'asad', 'ali', 'asad@gmail.com', '030068423088', 'user', 0, '202cb962ac59075b964b07152d234b70', 'c4c041eb50dba8c93ce8f15bd7c98c4d', '2019-11-11', NULL, NULL),
(13, 'sajid', 'ali', 'sajid@gmail.com', '030068423088', 'user', 0, '202cb962ac59075b964b07152d234b70', 'b4f5b16f8e9241dab65847e869b9aed3', '2019-11-11', NULL, NULL);

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);
  ALTER TABLE `users`
  MODIFY `id` int(15) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;
  

How to create dropdown list by fetching data from mysql database

In this example of php and jquery ajax tutorial I will use a dropdown list with some data displayed in this select box from a database users table. Select box of html tag has option tag with value attribute which is used to save value attribute data into database and for some other purposes we can use value attribute of select box. 

Here you will see front end html page code. In this code I have created html form , html select box dynamic list data from mysql database and a button for jquery on click function execution. When a user will select a name from dropdown menu and click on submit button then backend php code will execute with the help of ajax call to php script to retrieve data from mysql database.

Here is code for dropdown list with dynamic data list

<!--php 
$conn=mysqli_connect("localhost","root","","att_kpiauthdb") or die("Failed To connect With Database");

?-->
<!--DOCTYPE html-->
<html>
<head>
<title>PHP & JSON Data 
</title>
<script src="./scripts/jquery-3.4.1.min.js"></script>
 <link href="./style/bulma-0.7.4/css/bulma.min.css" rel="stylesheet" type="text/css">

</head>
<body>
<div class="container">
<div class="column is-10 is-offset-1">
<div class="box">
<h3 class="title is-4 has-text-centered">Jquery fetch data from MySQL db without page refresh Using PHP</h3>
<form method="post" enctype="multipart/form-data">
<div class="columns">
<div class="column is-8">
<span class="select">
<select class="input is-info is-medium" id="select_users">
<option value="">All Users List</option>
<!--php 
$sql="select * from users";
$query=mysqli_query($conn,$sql);
while($row=mysqli_fetch_array($query))
{
?-->
<option value="<?php echo $row["id"];?>"><!--php echo $row["firstname"].' '.$row["lastname"]; ?--></option>
<!--php }?-->
</select>
</span>
</div>
<div class="column">
<input type="button" name="btnSubmit" id="btnSubmit" class="button is-info is-medium" value="Submit" />
</div>
</div>
</form>
<table class="table is-bordered" id="tbl_user" style="display:none">
<tbody>
<tr><th>Name</th><td id="name"></td></tr>
<tr><th>Email</th><td id="email"></td></tr>
<tr><th>Phone</th><td id="phone"></td></tr>
</tbody>
</table>
</div>
</div>
</div>
</body>
</html>

JQuery Ajax Calling PHP script to Fetch Data From Mysql Database

After writing above code into your web page file. Now you need to write jquery code to implement ajax call on button click. Write a jquery script opening and closing tag above closing body tag of basic html structure. In this script write $document.ready(function(){}).This document dot ready function is parent function and you have to write all jquery code within this jquery important function.Within ajax first thing is url, it is the url of php script file. To fetch dynamic data from mysql databse you have to pass paramters in data attribute of ajax like below I have added userid and stored here dropdownlist id.When ajax will call the php script successfully it will then data retrieved will be used to display in html table.

Below is the jquery script of button click to fetch data without page refresh from mysql database

    
    <script>
$(document).ready(function(){
	
	$("#btnSubmit").click(function(){
		$.ajax({
			url:"fetch.php",
			type:"POST",
			data:{userid:$("#select_users").val()},
			dataType:"JSON",
			success:function(data){		
				$("#tbl_user").show();
				$("#name").text(data.name);
				$("#email").text(data.email);
				$("#phone").text(data.phone);
			}
		})
	});
});
</script>
    
    

PHP Script To Fetch Data From Mysql Database

First create a database connection variable.PHP mysqli_connect function is used with four paramters localhost,username,password and database name. Next line is mysql select query with where cluase.Next execute the query. Now create an array type vaiable. Next start a while loop and store each mysql table column field into array variable with specific array name. After closing the while loop echo json data.

    
    < ? php
$conn=mysqli_connect("localhost","root","","att_kpiauthdb") or die("Failed To connect With Database");
$id=$_POST["userid"];
$sql="select * from users where id=$id";	

$query=mysqli_query($conn,$sql);
$data=array();
while($row=mysqli_fetch_array($query))
{
	$data['name']=$row['firstname'];
	$data['email']=$row['email'];
	$data['phone']=$row['phone'];
}
echo json_encode($data);
? >
    
    

PHP How to fetch data from mysql database without page refresh jqueru ajax

Download source code Click Here

#JQuery tutorial #PHP Tutorial