While printing multidimensional array data in a loop, "Array" is displayed

Question

Please help me to iterate may array.

My mysql stored procedure returns the following output when I run it on the command line:

mysql> call sp_test();
+----------+------------+------------+------------+
| startt   | 2013-04-01 | 2013-04-02 | 2013-04-03 |
+----------+------------+------------+------------+
| 08:00:00 | Donald     | Daisy      | Mickey     |
| 12:00:00 | Pluto      | Goofy      | Minnie     |
| 14:00:00 | NULL       | Mickey     | NULL       |
+----------+------------+------------+------------+
3 rows in set (0.00 sec)

(This is a dynamic query, the date values in the column headings are not static.)

I can call this stored procedure from php and successfully populate an array which I do thus:

  6 <?php
  7 $db = new mysqli('localhost', 'root', 'password', 'db');
  8 $sql = "CALL sp_test()";
  9 $query = $db->query($sql);
 10 $result = array();
 11 while ($row = $query->fetch_assoc()) {
 12     $result[] = $row;
 13     }
 14     $query->close();
 15     $db->close();

The array contains:

array(3) {
    [0]=> array(4) {
        ["startt"]=> string(8) "08:00:00"
        ["2013-04-01"]=> string(6) "Donald"
        ["2013-04-02"]=> string(5) "Daisy"
        ["2013-04-03"]=> string(6) "Mickey"
    }
    [1]=> array(4) {
        ["startt"]=> string(8) "12:00:00"
        ["2013-04-01"]=> string(5) "Pluto"
        ["2013-04-02"]=> string(5) "Goofy"
        ["2013-04-03"]=> string(6) "Minnie"
    }
    [2]=> array(4) {
        ["startt"]=> string(8) "14:00:00"
        ["2013-04-01"]=> NULL
        ["2013-04-02"]=> string(6) "Mickey"
        ["2013-04-03"]=> NULL
    }
}

My problems begin when I try to iterate over the array (using php) to present the results.

Would you suggest a 'loop' which will iterate over the array and produce the html equivalent of the command line result (as shown at the top of this post)?

I have tried the following which did not do exactly what I wanted, then I became completely confused. Hope you can help.

 17 echo "<table>";
 18 foreach ($result as $value) {
 19     foreach ($value as $key => $data) {
 20         echo "<tr>";
 21         echo "<td>" . $key . "</td>";
 22         echo "<td>" . $value . "</td>";
 23         echo "</tr>";
 24         }
 25         echo "\n";
 26     }
 27 echo "</table>";
 28 //var_dump($query);
 29 ?>

Output from this is:

startt  Array    
 
2013-04-01  Array    
 
2013-04-02  Array    
 
2013-04-03  Array    
 
startt  Array    
 
2013-04-01  Array    
 
2013-04-02  Array    
 
2013-04-03  Array    
 
startt  Array    
 
2013-04-01  Array    
 
2013-04-02  Array    
 
2013-04-03  Array

Ja͢ck · Accepted Answer · 2013-07-25 06:49:59Z

1

Your inner foreach is this:

foreach ($value as $key => $data) {
    echo "<tr>";
    echo "<td>" . $key . "</td>";
    echo "<td>" . $value . "</td>";
    echo "</tr>";
}

The problem is that $value is the row and thus an array; when you try to echo that it will print Array (and raise a notice). Instead, you want $data which is the column:

    echo "<td>" . $data . "</td>";

Also, you should use proper HTML escaping:

foreach ($value as $key => $data) {
    printf("<tr><td>%s</td><td>%s</td></tr>\n",
        htmlspecialchars($key),
        htmlspecialchars($data)
    );
}

Update

To retain the table format from the query output, you first have to extract the function that prints a whole row:

function printRow($data)
{
    echo '<tr>' . join('', array_map(function($item) {
        return sprintf('<td>%s</td>', htmlspecialchars($item));
    }, $data)) . '</tr>';
}

Then you have to treat the first row as special:

echo '<table>';
$firstRow = true;
while ($row = $query->fetch_assoc()) {
    if ($firstRow) {
        printRow(array_keys($row));
        $firstRow = false;
    }
    printRow($row);
}
echo '</table>';

edited Jul 25, 2013 at 6:49

answered Jul 24, 2013 at 5:50

Ja͢ck

174k39 gold badges269 silver badges316 bronze badges

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

4 Comments

Tony Over a year ago

Thank you @Jack. Your suggestion has eliminated the error that I was getting, a leap forward. Finally I would like to interchange where date and time appear. I want date as column headings and time in the row (first cell). Presently it is the opposite. I tried some changes but produced more errors. If you can suggest how to interchange column and row values then I can mark this post the correct answer. Thans again for your previous helpful input.

startt	08:00:00 2013-04-01	Donald 2013-04-02	Daisy 2013-04-03	Mickey startt	12:00:00 2013-04-01	Pluto 2013-04-02	Goofy 2013-04-03	Minnie

Tony Over a year ago

sorry that the formatting of the example is lost in my reply.

Tony Over a year ago

I implemented your suggestion. The code runs but... it is returning `ArrayArrayArrayArray~ Something is not quite right. could we be referring to the wrong index?

Tony Over a year ago

your solution is ideal, it works just as I wanted it to. Thank you for your assistance.

StackSlave · Accepted Answer · 2013-07-24 01:54:46Z

1

fetch_assoc() in a while loop just grabs the row itself, and assumes that you know the column names. Lets say you have columns in your table named username and id. Your while loop could look something like:

while($row = $query->fetch_assoc()){
  $result[] = 'Username: '.$row['username'].', ID: '.$row['id'];
}

Maybe you want your code to look like:

$rows = $query->fetch_row(); $out = '';
foreach($rows as $r){
  $out .= '<tr>';
  foreach($r as $k => $v){
    $out .= "<td>$k</td><td>$v</td>";
  }
  $out .= '</tr>';
}
echo "<table>$out</table>";

P.S.
I didn't use fetch_assoc() since there was no point. You just want each row, I got them by their numeric indexes. You don't need to initialize arrays in PHP, before you create them by index, so you don't need to write $result = array(). You should also put your new mysqli() on a separate, secure page.

edited Jul 24, 2013 at 1:54

answered Jul 24, 2013 at 1:35

StackSlave

10.6k2 gold badges20 silver badges35 bronze badges

1 Comment

Tony Over a year ago

thanks @PHPglue. so what can I use instead of fetch_assoc()?

Collectives™ on Stack Overflow

While printing multidimensional array data in a loop, "Array" is displayed

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related