While working on a project I was to get records from database and show
on some textboxes. I knew that I need to use jQuery to do this, but how
can I do this. After somegoogling I find that I can do it very easily
with JSON. I just need to fetch recordsfrom database and then convert
that records to JSON and then send it to jQuery. Andthrough jQuery we
can easily display JSON encode data. In other words, through JSON wecan
interchange data between web server and browser.
In this tutorial I’ll show you, how to load JSON data in select (combo) box and how can we put JSON data in textboxes.
Lets suppose we have to fetch data from category table of a database (lets say database name is
myblog).
|
|
CREATE TABLE Category
{
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(200),
detail VARCHAR(255)
}
|
We need to create some files for database connections and fetching data from database.
connect.php
You can configure server and database name here.
|
|
<?php
$HOST = "localhost";
$USER = "root";
$PASS = "";
$DATABASE = "myblog";
$con = mysql_connect($HOST, $USER, $PASS);
$db = mysql_select_db($DATABASE, $con);
?>
|
Now we need to fetch all categories from the database, encode into
JSON and then we can put all those categories in the combo box. All we
have to do is to create a file json_data_combo.php
json_data_combo.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
<?php
require_once 'connect.php';
$sql = mysql_query("select * from Category");
if(mysql_num_rows($sql))
{
$data = array(); $index = 0;
while($recset = mysql_fetch_array($sql))
{
$data[$index] = $recset;
$index++;
}
echo json_encode($data);
}
?>
|
when you open json_data_combo.php directly the result will looks like
|
|
{"0":"1","id":"1","1":"HTML 4.01","name":"HTML 4.01","2":null,"detail":"HTML 4.01 detail here"}
|
Now we have to read this file through jQuery and display this json encoded data in the combo
we need to include following jQuery
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function()
{
$.getJSON("json_data_combo.php",function(data){
var select = $('#cboCategory'); //combo/select/dropdown list
if (select.prop) {
var options = select.prop('options');
}
else {
var options = select.attr('options');
}
$('option', select).remove();
$.each(data, function(key, value){
options[options.length] = new Option(value['name'], value['id']);
});
});
});
</script>
|
and here is the complete working code for main file where we will use jQuery and show dropdown list
main.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
|
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>Load JSON data through jQuery and PHP | Sharp Coders - http://sharp-coders.com</title>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function()
{
$.getJSON("json_data_combo.php",function(data){
var select = $('#cboCategory');
if (select.prop) {
var options = select.prop('options');
}
else {
var options = select.attr('options');
}
$('option', select).remove();
$.each(data, function(key, value){
options[options.length] = new Option(value['name'], value['id']);
});
});
});
</script>
</head>
<body>
<table width="400" border="0" cellspacing="2" cellpadding="2">
<tr>
<td width="105">Category</td>
<td width="281"><select name="cboCategory" id="cboCategory">
</select></td>
</tr>
</table>
</body>
</html>
|
Our first task is complete, we have successfully loaded the JSON data
into dropdown list through jQuery and PHP. Now, our next task is to
show data in text boxes. Now, what we’ll do, select a category from a
drop down list and press load button to put selected category name and
description in text boxes and textarea. For this purpose we need to
create a php file to get category by its id and convert that category
data into json and send to jquery to display.
json_data_category.php
1
2
3
4
5
6
7
8
9
10
11
12
|
<?php
require_once 'connect.php';
if(isset($_GET['id']))
{
$sql = mysql_query("select * from Category where id='".intval($_GET['id'])."'");
if(mysql_num_rows($sql))
{
$data = mysql_fetch_array($sql);
echo json_encode($data);
}
}
?>
|
Now we need to modify our main.html file so replace the code of main.html with this.
main.html
Sharp Coders
|
http://sharp-coders.com
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
|
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>Load JSON data through jQuery and PHP | Sharp Coders - http://sharp-coders.com</title>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function()
{
$.getJSON("json_data_combo.php",function(data){
var select = $('#cboCategory');
if (select.prop) {
var options = select.prop('options');
}
else {
var options = select.attr('options');
}
$('option', select).remove();
$.each(data, function(key, value){
options[options.length] = new Option(value['name'], value['id']);
});
});
$('#btnLoad').click(function(){
$.getJSON("json_data.php", {id: $('#cboCategory').val()}, function(data){
$('#txtID').val(data['id']);
$('#txtName').val(data['name']);
$('#txtDescription').val(data['detail']);
});
});
});
</script>
</head>
<body>
<table width="400" border="0" cellspacing="2" cellpadding="2">
<tr>
<td width="105">Category</td>
<td width="281"><select name="cboCategory" id="cboCategory">
</select>
<input type="button" id="btnLoad" value="Load" />
</td>
</tr>
<tr>
<td>ID</td>
<td><input type="text" name="txtID" id="txtID"></td>
</tr>
<tr>
<td>Name</td>
<td><input type="text" name="txtName" id="txtName"></td>
</tr>
<tr>
<td>Description</td>
<td><textarea name="txtDescription" id="txtDescription" cols="30" rows="5"></textarea></td>
</tr>
</table>
<h3>Sharp Coders</h3> | <a href="http://sharp-coders.com" target="_blank">http://sharp-coders.com</a>
</body>
</html>
|
