MySQL + JSON: Retrieve all data where id = term

Question

I am trying to retrieve data with JSON from my database and parse them in a table.

Currently I retrieve it like this:

xmlhttp = new XMLHttpRequest()

xmlhttp.onreadystatechange = function () {
    if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
        var jsontext = xmlhttp.responseText;
        var json = JSON.parse(jsontext);

        console.log(json.service)

    }
}

xmlhttp.open("GET", "mysql.php?p=getservice" + "&carid=" + "KYF111", true);
xmlhttp.send();

This will give me one result, no matter how many rows I have with id 'term'.

My mysql.php file looks like this:

case 'getservice':

$q = mysql_real_escape_string($_GET['q']);
$carid = stripslashes($_GET['carid']);
$query = "SELECT * FROM Service WHERE carid = '".$carid."'";

$result = mysql_query($query);

$json = array();
while ($row = mysql_fetch_array($result)) {

    $json['carid'] = $row['carid'];
    $json['service'] = $row['service'];
    $json['date'] = $row['date'];
    $json['nextdate'] = $row['nextdate'];
    $json['kilometers'] = $row['kilometers'];
    $json['servicedby'] = $row['servicedby'];
    $json['invoice'] = $row['invoice'];
    $json['cost'] = $row['cost'];
    $json['remarks'] = $row['remarks'];

}
print json_encode($json);

mysql_close();

break;

This is how my database looks like:

enter image description here

so the term would be the cardid, and I want all the values from the rows which contains carid the term. Then each value on a single row gets between a . Something like this:

<tbody>
    <tr>
        <td>Tyre</td>
        <td>10-10-2012</td>
        <td>31-10-2012</td>
        <td></td>
        <td>George</td>
        <td>8951235</td>
        <td>0</td>
        <td>Lorem Ipsum</td>
    </tr>
    <tr>
        <td>Lights</td>
        <td>17-10-2012</td>
        <td>23-10-2012</td>
        <td></td>
        <td>Antony</td>
        <td>4367234</td>
        <td>0</td>
        <td>Lorem Ipsum</td>
    </tr>
</tbody>

Barmar · Answer 1 · 2012-10-20T06:05:06.567

1

Your while loop is overwriting the same array entries each time through, instead of making a two-dimensional array. It should be:

$json = array();
while ($row = mysql_fetch_array($result)) {
  $json[] = $row;
}
echo json_encode($json);

In your JavaScript, you need to loop over the returned array. Instead of console.log(json.service) it should be:

for (var i = 0; i < json.length; i++) {
    console.log(json[i].service);
}

edited Oct 20 '12 at 06:05

answered Oct 20 '12 at 05:13

Barmar

741,623
53
500
612

I still get only one value, which is Lights – jQuerybeast Oct 20 '12 at 05:59
1

Your JavaScript has to loop over the array, see my edited answer. I'm surprised you got anything logged to the console. – Barmar Oct 20 '12 at 06:06
In this case either will work. See [this question](http://stackoverflow.com/questions/1647322/whats-the-difference-between-echo-print-and-print-r-in-php) for an explanation of the difference. – Barmar Oct 20 '12 at 06:18

score 1 · Accepted Answer · answered Oct 20 '12 at 05:14

Try like this:

    $temp=0;
    $json = array();
        while ($row = mysql_fetch_array($result)) {

            $json[$temp]['carid'] = $row['carid'];
            $json[$temp]['service'] = $row['service'];
            $json[$temp]['date'] = $row['date'];
            $json[$temp]['nextdate'] = $row['nextdate'];
            $json[$temp]['kilometers'] = $row['kilometers'];
            $json[$temp]['servicedby'] = $row['servicedby'];
            $json[$temp]['invoice'] = $row['invoice'];
            $json[$temp]['cost'] = $row['cost'];
            $json[$temp]['remarks'] = $row['remarks'];
    $temp++;
        }
print json_encode($json);

I get no results. Im not sure what Im doing wrong. Is the way of retrieve the data correct? — jQuerybeast, Oct 20 '12 at 06:00

MySQL + JSON: Retrieve all data where id = term

2 Answers2