Outputting SQL query as line by line JSON

Question

My SQL query is selecting date and doing a count, as well as grouping by date.

I don't know how to output line by line JSON output.

 $sql = "SELECT DATE(timestamp), COUNT(eventid)
 FROM `tablex`
 WHERE timestamp >= date_sub(CURRENT_DATE, interval 30 day)
 GROUP BY DATE(timestamp) ";

 $stream = mysqli_query($conn, $sql);


if (mysqli_num_rows($stream) > 0) {

   while($row = mysqli_fetch_assoc($stream)) {

     // Is this where I should echo JSON? 
     // The problem is I'm not retrieving records but count and doing
     // a grouping

     }}

possible duplicate of [Convert php result array to json](http://stackoverflow.com/questions/2122233/convert-php-result-array-to-json) — ankitr, Feb 20 '15 at 10:14
Output format should be like: [{ "timestamp": "2015-02-20", "count": 417 }] — Cody Raspien, Feb 20 '15 at 10:18

score 0 · Answer 1 · answered Feb 20 '15 at 10:17

Use mysqli_fetch_array(), also take the count with mysql_num_rows():

$sql = "SELECT DATE(timestamp), COUNT(eventid)
 FROM `tablex`
 WHERE timestamp >= date_sub(CURRENT_DATE, interval 30 day)
 GROUP BY DATE(timestamp) ";

 $stream = mysqli_query($conn, $sql);

if (mysqli_num_rows($stream) > 0) {
   $rowCount = mysql_num_rows($result);
   while($row = mysqli_fetch_array($result, MYSQLI_NUM)){
       // Process JSON here use $row[0], $row[1], and so
   }
}

score 0 · Answer 2 · answered Feb 20 '15 at 10:21

Whatever you like to return as a JSON-string, you should always write your data into a single array, then use this nice little PHP-function called json_encode(). For example:

$sql = "SELECT
   DATE(timestamp) as date_timestamp,
   COUNT(eventid) as count_eventid
   FROM `tablex`
   WHERE timestamp >= date_sub(CURRENT_DATE, interval 30 day)
   GROUP BY DATE(timestamp);";

$stream = mysqli_query($conn, $sql);

// prepare return array
$mydata = array(
    'eventcount' => 0
);

if (mysqli_num_rows($stream) > 0) {
    while($row = mysqli_fetch_assoc($stream)) {
        // just collect your data here, do not JSONify here
        $mydata['eventcount'] += $row['count_eventid'];
    }
}
// We got all our data, so return JSON encoded array
echo json_encode($mydata);

You will get something like

{"eventcount":"1234"}

I get {"eventcount":0} as output. How do I incl. timestamp alongside eventcount? — Cody Raspien, Feb 20 '15 at 10:24
If yout get 0, then you most likely don't retrieve any rows at all. You should first check if your SQL statement retrieves any records. You may also want to try this: $mydata = array( ); if (mysqli_num_rows($stream) > 0) { while($row = mysqli_fetch_assoc($stream)) { // group counts by date $mydata[$row['date_timestamp']] = $row['count_eventid']; } } — T Grando, Feb 20 '15 at 10:37

score 0 · Answer 3 · answered Feb 20 '15 at 10:35

0

I managed to get an output:

$stream = mysqli_query($conn, $sql);

$lines = array();
while($line = mysqli_fetch_assoc($stream)) {
      $llines[] = $line;
}

print json_encode($lines);

answered Feb 20 '15 at 10:35

Cody Raspien

1,753
5
26
51

Outputting SQL query as line by line JSON

3 Answers3