How to push two-element rows into a result array while looping?

Question

This is a working example of my query Retrieve data as JSON using PHP:

$result = mysql_query("SELECT unix_timestamp(date), sum(ksi2k) FROM accounting where lhc_vo like 'ops' group by year(date), month(date)");
$rows = array();
$rows['type'] = 'area';
$rows['name'] = 'Ops';
while($r = mysql_fetch_array($result)) {
    $rows['data'][] = $r[0]*1000;
    $rows['data'][] = $r[1];

    array_push($rows);
}

print json_encode($rows, JSON_NUMERIC_CHECK);

The JSON results look like this:

{"type":"area","name":"Ops","data":[1167664515000,0,1170342915000,0,1172762115000,0,1175436915000,0,1178028915000,0]}

But I need the JSON results should look like this:

{"type":"area","name":"Ops","data":[[1167664515000,0],[1170342915000,0],[1172762115000,0],[1175436915000,0],[1178028915000,0]]}

This question is similar to: Php pushing values to a 2-dimensional array. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. — mickmackusa
– mickmackusa ♦, Commented Dec 8, 2024 at 7:24

eggyal · Accepted Answer · 2013-05-21 11:15:06Z

0

while($r = mysql_fetch_array($result)) {
    $rows['data'][] = array($r[0]*1000, $r[1]);
}

answered May 21, 2013 at 11:15

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ким Клименко Over a year ago

I am appalled by speed, simplicity and elegance of your solutions! Everything works perfectly

Ким Клименко Over a year ago

I can only add that the query should be more than one. If the request only one, you have manually close the array.

eggyal Over a year ago

@КимКлименко: I'm not sure what you mean by that, but perhaps you simply want to define $rows['data'] = array(); before the loop?

Ким Клименко Over a year ago

Yes, that's right $result = array();array_push($result,$rows);array_push($result,$rows1);

eggyal Over a year ago

@КимКлименко: You shouldn't need to do any of that array_push() stuff at all.

eggyal · Accepted Answer · 2013-05-21 12:37:03Z

0

In addition to my other answer, you ought to consider switching away from the ancient (and now deprecated, as of PHP v5.5) ext/mysql. Here is an example using PDO, in which you can see how simple your problem becomes:

<?php
  $con = new PDO(
    'mysql:hostname=localhost;dbname=admin_accounting',
    'user',
    'password'
  );

  $result = $con->query('
    SELECT   1000*UNIX_TIMESTAMP(date), SUM(ksi2k)
    FROM     accounting
    WHERE    lhc_vo LIKE "ops"
    GROUP BY YEAR(date), MONTH(date)
  ');

  print json_encode([
    'type' => 'area',
    'name' => 'Ops',
    'data' => $result->fetchAll(PDO::FETCH_NUM)
  ]);
?>

Note that:

I have moved the multiplication into the database layer in order that I can simply call fetchAll() to obtain the resulting array;
MySQL will select an indeterminate value from amongst those in each group for the first column in the resultset; should this be undesirable, you will need to apply a suitable aggregate function to the reference to the date column; and
I have used the short array syntax, which is only available from PHP v5.4—if you're using an earlier version, you will need to replace the [ … ] of the argument to json_encode() with array( … ).

answered May 21, 2013 at 12:37

eggyal

127k18 gold badges220 silver badges244 bronze badges

1 Comment

Ким Клименко Over a year ago

Thank you once again for a qualified full answer.

Collectives™ on Stack Overflow

How to push two-element rows into a result array while looping?

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related