How to load data using jQuery and JSON?

Question

I want to load data from a MySQL database to a PHP page using jQuery and JSON. When the user choose the name from the select box, it loads the person's data into the text field.

This is my HTML code (select)

<select name='name' id='name'>
    <option value='1'>John</option>
    <option value='2'>Marco</option>
    <option value='3'>Charles</option>
</select>

The text field that I want to populate with the person data:

<input type='text' name='firstname' value='' id='firstname'/>
<input type='text' name='phonenum' value='' id='phonenum'/>

getpersonData.php

<?php
    include('dbconfig.php');
    $id = $_POST['id'];
    $query = "select * from person where ID='$id'";
    $result = mysql_query($query);
    $data = mysql_fetch_array($result);

    echo json_encode($data);
?>

The Ajax call:

$.ajax({
    type: "POST",
    async : false,
    url: 'http://test.com/getpersonData.php',
    dataType: 'json',
    data : {
        id : $("#id").val(),
    },
    success : function(data) {
        //What to insert here?        
    },
    error : function(XMLHttpRequest, textStatus, errorThrown) {
        alert(XMLHttpRequest + " : " + textStatus + " : " + errorThrown);
    }
});

What code should I use for the success function?

Don't interpolate any user data directly into code, as you do with `$_POST['id']` into `select * from person where ID='$id'`. This leaves your code open to [SQL injection](http://unixwiz.net/techtips/sql-injection.html). Switch from the outdated mysql driver to [PDO](http://php.net/PDO) and [prepared statements](http://www.php.net/PDO.prepared-statements). — outis, Oct 25 '10 at 02:44
Also, you [shouldn't use `SELECT *`](http://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select) here. The result row should be fetched as an associative array rather than combined associative and numeric array. Doing both will just result in a longer response message. — outis, Oct 25 '10 at 02:46
@outis Whilst "SELECT *" isn't fantastic, I think you meant "don't use mysql_fetch_array". It should be either `mysql_fetch_array($result, MYSQL_ASSOC)` or just `mysql_fetch_assoc($result)` — Phil, Oct 25 '10 at 02:49
If you need a PDO tutorial, try "[Writing MySQL Scripts with PHP and PDO](http://www.kitebird.com/articles/php-pdo.html)". However, don't let that stop you from searching for others. — outis, Oct 25 '10 at 02:49
@Phil: the advice about `SELECT *` and combined arrays are supposed to be separate things, but I can no longer edit the comment to clarify this. — outis, Oct 25 '10 at 02:51

score 3 · Accepted Answer · answered Oct 25 '10 at 02:45

First, you should set your Content-type header right before you echo your json_encode in getpersonData.php:

header("Content-type: application/json");
echo json_encode($data);

In your success function you would do something like:

$("#firstname").val(data.firstname); //firstname is the key of the user's firstname from your mysql query

score 1 · Answer 2 · answered Oct 25 '10 at 02:44

1

I'm guessing your DB column names here, but maybe something like this

$('#firstname').val(data.firstname);
$('#phonenum').val(data.phonenum);

answered Oct 25 '10 at 02:44

Phil

157,677
23
242
245

Thank you. Going to test now! :D – cyberfly Oct 25 '10 at 02:57
Don't forget to set the content-type header as Calvin pointed out – Phil Oct 25 '10 at 03:01

score -1 · Answer 3 · edited Dec 04 '12 at 21:16

getpersonData.php

<?php
include('dbconfig.php');
$id = $_POST['id'];
$query = "select * from person where ID='$id'";
$result = mysql_query($query);
$data = mysql_fetch_array($result);

header("Content-type: application/json");
echo json_encode($data); ?>

main file

<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>Load JSON data through jQuery and PHP </title>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function()
{
        load_data($('#name').val());
    $('#name').change(function(){
        load_data($('#name').val());
    });
});
function load_data(personid)
{
       $.getJSON("getpersonData.php", {id: personid}, function(data){
           $('#firstname').val(data['firstname']);
        $('#phonenum').val(data['phonenum']);
     });    
}
</script>
</head>
<body>
<select name="name" id="name">
<option value='1'>John</option>
    <option value='2'>Marco</option>
    <option value='3'>Charles</option>
</select>
   <input type='text' name='firstname' value='' id='firstname'/>
<input type='text' name='phonenum' value='' id='phonenum'/>
</body>
</html>

Thanks for posting your answer! Please be sure to read the [FAQ on Self-Promotion](http://stackoverflow.com/faq#promotion) carefully. Also note that it is *required* that you post a disclaimer every time you link to your own site/product. — Andrew Barber, Nov 05 '12 at 18:48

How to load data using jQuery and JSON?

getpersonData.php

3 Answers3

Linked