0

I have a very strange issue. I am querying data and formatting it into a json. The format works great, but when i try to add a case that prevents a , from appearing in the last item of the json, the "Order By Date" of my SQL statement doesn't work, i just orders it by ID. Any thoughts why? 

$sql = "SELECT * FROM events ORDER BY date";
$res = mysql_query($sql,$con);
$number = mysql_num_rows($res);
$json = 'var event_data={';
$i = 1;
while ($row = mysql_fetch_assoc($res))
    {
    $json .= '"'.$row['id'].'": {';
    $json .= '"loc_id":"'.$row['loc_id'].'"';
    $json .= ', "type":"'.$row['type'].'"';
    $json .= ', "time":"'.$row['time'].'"';
    $json .= ', "date":"'.$row['date'].'"}';
    if ($i < $number)
       {
           $json .= ',';  //<----- this is the problem child
       }else{
           $json .= '';
       }
     $i ++;
    }
$json .= '};';  
echo $json;
Jorge Campos
  • 22,647
  • 7
  • 56
  • 87
Dan Rivers
  • 173
  • 3
  • 14
  • 5
    Why are you doing this by hand? Why not just use json_encode? http://php.net/manual/en/function.json-encode.php – Kai Qing Jul 17 '14 at 17:43
  • You should not use MYSQL_* functions as it is deprecated. Also you could use something easier to convert your rows to a json like this: http://stackoverflow.com/a/383664/460557 – Jorge Campos Jul 17 '14 at 17:44

1 Answers1

7

Please stop what you're doing now and check out: http://php.net/manual/en/function.json-encode.php

Building your own json encoder is going to be difficult to do correctly and will take quite take quite a bit of processing if you're doing it from PHP. Build up your data structure using PHP then encode the entire thing in one pass:

$o = array();
while ( $row = mysql_fetch_assoc($res) ) {
    $n = array();
    $n['loc_id'] = $row['loc_id'];
    $n['type'] = $row['type'];
    # ...
    $o[ $row['id'] ] = $n;
}
echo json_encode($o);
smassey
  • 5,875
  • 24
  • 37