Loop through nested array to generate tables of di

Question

I'm trying to show demand for exams of different levels in different locations.

I have some code that outputs a series of 4 two-column tables across a page.

Each table shows total registrations in the first column and total payments in brackets in the second column.

There's a table for each level. There's a row in each table for each centre at that level.

E.g. 1st, leftmost column:

A1
______________
______________
Arlington > A1
______________ 
26 (17) 
--------------
El Paso > A1 
______________
8 (8) 
--------------
White Ridge > A1
________________ 
0 (0) 
----------------
Jonestown > A1 
_______________
10 (9) 
----------------
Hochberg > A1 
_____________
5 (0) 
-------------

Each exam level requires different centres, so I have used a nested array. I can display tables of varying lengths for each level but am getting no values from my sql queries - just 0 for everything.

Also, I can't display individual centres for each level in my tables. i.e. the line

<td class="width8">' . $centre . ' > ' . $level . '</td>

isn't working. It just displays "Array > A1", "Array > A2" etc.

Any ideas?

// create array
$levels = array(
    "A1" => array(
        'Arlington',
        'El Paso',
        'White Ridge',
        'Jonestown',
        'Hochberg'
    ),
    "A2" => array(
        'Arlington',
        'El Paso',
        'Hochberg'
    ),
    "B1" => array(
        'El Paso',
        'White Ridge',
        'Jonestown',
        'Hochberg'
    ),
    "B2" => array(
        'Arlington',
        'El Paso',
        'White Ridge'
    )
);

// Loop through centres and levels 
foreach ($levels as $level => $centres) {
    echo '<div id="' . $level . '_column">
                <h2 class="'.$level.'">'.$level.'</h2>';
    foreach ($centres as $centre) {
// Prepare
        $stmt1 = $db->prepare("SELECT COUNT(Centre) 
                            FROM exam 
                            WHERE exam.Centre=:centre   
                            AND exam.Level=:level");

        $stmt2 = $db->prepare("SELECT COUNT(Centre) 
                            FROM exam
                            JOIN personal 
                            ON exam.P_ID=personal.P_ID
                                WHERE personal.Paid='1' 
                            AND exam.Centre=:centre 
                            AND exam.Level=:level");

// Bind 
        $stmt1->bindParam(':centre', $centre, PDO::PARAM_STR);
        $stmt1->bindParam(':level', $level, PDO::PARAM_STR);

        $stmt2->bindParam(':centre', $centre, PDO::PARAM_STR);
        $stmt2->bindParam(':level', $level, PDO::PARAM_STR);

// Execute
        $stmt1->execute();
        $stmt2->execute();

// Fetch 
        $row = $stmt1->fetch(PDO::FETCH_ASSOC);
        $row2 = $stmt2->fetch(PDO::FETCH_ASSOC);

        echo '<table class="Font3White">
                       <tr class="Title">
                        <td class="width8">' . $centre . ' > ' . $level . '</td>
                       <tr>';

        if ($row && $row2) {
            foreach ($row as $key => $value) {
                echo '<td>';
                echo $value;
                echo '</td>';
            }

            foreach ($row2 as $key2 => $value2) {
                echo '<td> (';
                echo $value2;
                echo ')</td>';
            }

            echo '</tr>';
        }
        echo '</table>';
    } echo '</div>';
}

Answer 1

Maybe, this shall help you.

You just need to pay attention at foreach syntax:

foreach($array as $key => $value)

For example:

$array = array(
    "key1" => /*values*/ array(
        "value1",
        "value2"
    ),
    "key2" => /*values*/ array(
        "value1",
        "value2"
    )
);

foreach($array as $key => $values){
    foreach($values as $value){
        echo "$key => $value\n";
    }
}

Will echo:

key1 => value1  
key1 => value2  
key2 => value1  
key2 => value2  

So, you code should be:

// Creates arrays 
$levels = array(
    "A1" => array(
        'Arlington',
        'El Paso',
        'White Ridge',
        'Jonestown',
        'Hochberg'
    ),
    "A2" => array(
        'Arlington',
        'El Paso',
        'Hochberg'
    ),
    "B1" => array(
        'El Paso',
        'White Ridge',
        'Jonestown',
        'Hochberg'
    ),
    "B2" => array(
        'Arlington',
        'El Paso',
        'White Ridge'
    )
);

// Loop through centres and levels 
foreach ($levels as $level => $centres) {
    echo '<div id="' . $level . '_column">
                <h2 class="' . $level . '">' . $level . '</h2>';
    foreach ($centres as $centre) {
// Prepare
        $stmt1 = $db->prepare("SELECT COUNT(Centre) 
                            FROM exam 
                            WHERE exam.Centre=:centre   
                            AND exam.Level=:level");

        $stmt2 = $db->prepare("SELECT COUNT(Centre) 
                            FROM exam
                            JOIN personal 
                            ON exam.P_ID=personal.P_ID
                                WHERE personal.Paid='1' 
                            AND exam.Centre=:centre 
                            AND exam.Level=:level");

// Bind 
        $stmt1->bindParam(':centre', $centre, PDO::PARAM_STR);
        $stmt1->bindParam(':level', $level, PDO::PARAM_STR);

        $stmt2->bindParam(':centre', $centre, PDO::PARAM_STR);
        $stmt2->bindParam(':level', $level, PDO::PARAM_STR);

// Execute
        $stmt1->execute();
        $stmt2->execute();

// Fetch 
        $row = $stmt1->fetch(PDO::FETCH_ASSOC);
        $row2 = $stmt2->fetch(PDO::FETCH_ASSOC);

        echo '<table class="Font3White">
                       <tr class="Title">
                        <td class="width8">' . $centre . ' > ' . $level . '</td>
                       <tr>';

        if ($row && $row2) {
            foreach ($row as $key => $value) {
                echo '<td>';
                echo $value;
                echo '</td>';
            }

            foreach ($row2 as $key2 => $value2) {
                echo '<td> (';
                echo $value2;
                echo ')</td>';
            }

            echo '</tr>';
        }
        echo '</table>';
    } echo '</div>';
}