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