CODEWITHSUNDEEP
phparraysloops
Tony
Tony

Reputation: 59

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

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

Upvotes: 0

Views: 81

Answers (2)

Ja͢ck
Ja͢ck

Reputation: 173572

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>';

Upvotes: 1

StackSlave
StackSlave

Reputation: 10627

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.

Upvotes: 1

Related Questions