populate multidimensional array from MySQL query

Question

I am trying to create multidimensional array from MySQL query

Query:

    $STH = $DBH->query( "SELECT  value, o_ID, oName, date,  
                         DATE_FORMAT(date, '%d %m %Y') as FDate,
                         DATE_FORMAT(tsTime, '%H:%i') as FTime
                         FROM test tst
                         LEFT JOIN object o
                         ON  tst.o_ID =o.oID
                         WHERE DATE(date) = '$date'
                         ORDER BY FDate, FTime, oName ASC");
    $STH->setFetchMode(PDO::FETCH_ASSOC);

Loop:

$returnValue = array();
$data = array();

while ( $row = $STH->fetch() ) {

   $returnValue[$row['oName']] = 
       array(
         $data[] = array(
              'time' => $row['FTime'], 'value' => $row['value']
          )
       );
}

Output:

{"objectA":[{"time":"23:55","value":"15"}],"objectB":[{"time":"23:55","value":"15.90"}],..}

how can I put all values in $data array?

Desired output:

{"objectA":[{"time":"01:00","value":"15"},{"time":"02:00","value":"11"},{"time":"03:00,"value":"16"}],"objectB":[{"time":""01:00","value":"12"},{"time":""02:00","value":"25"},{"time":""03:00","value":"5"}],..}

try replacing while loop as while ( $row = $STH->fetch() ) { $returnValue[$row['oName']] = array( 'time' => $row['FTime'], 'value' => $row['value'] ); } — alwaysLearn
– alwaysLearn, Commented Jun 28, 2013 at 8:53
it will return it like this: [{"time":"01:00","value":"15"},{"time":"02:00","value":"11"},....] — InTry
– InTry, Commented Jun 28, 2013 at 8:59

Elias Van Ootegem · Accepted Answer · 2013-06-28 09:13:02Z

1

Give this a shot:

$returnValue = array();
while ( $row = $STH->fetch() )
{
   if (!$returnValue[$row['oName']])
   {
       $returnValue[$row['oName']] = array();
   }
   $returnValue[$row['oName']][]= array(
          'time' => $row['FTime'], 'value' => $row['value']
      );
}

That should give you what you want.

The difference here is that, if the oName-key doesn't exits, I create it, and assign it a new, empty array, then I add a new assiciative array to that array.
When the oName of row N already has a matching key, I'm not going to reassign that key, but I'm just going to push an extra array to that key.

edited Jun 28, 2013 at 9:13

answered Jun 28, 2013 at 9:04

Elias Van Ootegem

76.7k10 gold badges123 silver badges160 bronze badges

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

2 Comments

InTry Over a year ago

for the first few rows it says Notice: Undefined index:objectA in...,Notice: Undefined index:objectB in... but then is ok ` {"objectA":[{"time":"00:00","value":"15.90"},{"time":"01:0","value":"15.80"}...`

Elias Van Ootegem Over a year ago

@inTry: just initiate $returnValue and assign it an array, if the notices still appear, try using array_key_exists or empty instead of !$returnValue[] in the if statement

Nil'z · Accepted Answer · 2013-06-28 10:34:05Z

1

Try this:

while ( $row = $STH->fetch() ) {
    $returnValue[] = array(
       'time' => $row['FTime'], 'value' => $row['value']
      );
    }

edited Jun 28, 2013 at 10:34

answered Jun 28, 2013 at 8:51

Nil'z

7,4831 gold badge20 silver badges28 bronze badges

1 Comment

InTry Over a year ago

Parse error: syntax error, unexpected '=>' (T_DOUBLE_ARROW) in...

Collectives™ on Stack Overflow

populate multidimensional array from MySQL query

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related