Nested loops and SQL data

L

lemmas46

New member
I have a question on how to perform a nested loop based on some SQL data. I have a SQL query in which I'm looking for some records within a date range and ordered by a 'Trainer' field. I want to take that data and perform a count of certain fields for each trainer and put them in a table. I have the php code that finds me the records but I'm having trouble with the inside loop that would go through the records and perform a running count for each trainer. I'm not sure what I should use for the inside loop and what criteria I need to use with it. Do I use a while or a foreach or something else?

What I have so far is:
$CTOQuery = "SELECT * from Training where (Date between '" . $_REQUEST['StartDate'] . "' and '" . $_REQUEST['EndDate'] . "') order by CTO";
$CTOResult = sqlsrv_query($conn, $CTOQuery, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));

while ($CTOrow = sqlsrv_fetch_array($CTOResult, SQLSRV_FETCH_ASSOC)) {
// Inside loop here
}
 
You can fetch data using while loop also, please referee below code,

Code: 
$CTOQuery = "SELECT * FROM Training WHERE (Date BETWEEN '" . $_REQUEST['StartDate'] . "' AND '" . $_REQUEST['EndDate'] . "') ORDER BY CTO";
$CTOResult = sqlsrv_query($conn, $CTOQuery, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));
$trainerCounts = array();


while ($CTOrow = sqlsrv_fetch_array($CTOResult, SQLSRV_FETCH_ASSOC))
{
    $trainer = $CTOrow['Trainer'];
    
    // If the trainer is not in the array, initialize count to 1
    if (!isset($trainerCounts[$trainer]))
    {
        $trainerCounts[$trainer] = 1;
    } else
    {
        // Increment the count 
        $trainerCounts[$trainer]++;
    }
}


// Output the counts for each trainer
echo "<table>";
echo "<tr><th>Trainer</th><th>Count</th></tr>";
foreach ($trainerCounts as $trainer => $count) {
    echo "<tr><td>$trainer</td><td>$count</td></tr>";
}
echo "</table>";


I hope it will work for you.
 
To achieve your goal of performing a nested loop based on SQL data in PHP, you can use a while loop to iterate over each trainer's records and then use another loop inside to perform a count of certain fields for each trainer.

check following modified/example code:

Code: 
$CTOQuery = "SELECT * FROM Training WHERE (Date BETWEEN '" . $_REQUEST['StartDate'] . "' AND '" . $_REQUEST['EndDate'] . "') ORDER BY CTO";
$CTOResult = sqlsrv_query($conn, $CTOQuery, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));


// Associative array to store counts for each trainer
$trainerCounts = array();


while ($CTOrow = sqlsrv_fetch_array($CTOResult, SQLSRV_FETCH_ASSOC)) {
    // Assuming 'Trainer' is the field you want to count for
    $trainer = $CTOrow['Trainer'];


    // Increment count for the trainer
    if (isset($trainerCounts[$trainer])) {
        $trainerCounts[$trainer]++;
    } else {
        $trainerCounts[$trainer] = 1;
    }
}


// Output the results in a table
echo "<table>";
echo "<tr><th>Trainer</th><th>Count</th></tr>";


foreach ($trainerCounts as $trainer => $count) {
    echo "<tr><td>$trainer</td><td>$count</td></tr>";
}


echo "</table>";

Adjust the field names and table structure as per your actual data schema and requirements. i hope it will work for you
 
I understand what both replies are doing but that's not quite what I'm looking for. I have 4 trainers and let's say there 10 reports found from my query. Each report had fields that I want to get to total for each trainer.

while the trainer equals this name, count these fields for that trainer, move to the next trainer and count the fields for that trainer. Does that make sense? I can visualize this but my skill set isn't there yet.
 
Please check below code,

Code: 
$CTOQuery = "SELECT * FROM Training WHERE (Date BETWEEN '" . $_REQUEST['StartDate'] . "' AND '" . $_REQUEST['EndDate'] . "') ORDER BY CTO";
$CTOResult = sqlsrv_query($conn, $CTOQuery, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));

//An array to store totals for each trainer
$totals = [];

while ($CTOrow = sqlsrv_fetch_array($CTOResult, SQLSRV_FETCH_ASSOC)) {
    // trainer's name
    $trainer = $CTOrow["CTO"];

    // If this is the first time encountering this trainer, initialize their totals
    if (!isset($totals[$trainer])) {
        $totals[$trainer] = ["field1" => 0, "field2" => 0];
    }
    // Add the values of desired fields to the totals for this trainer
    // Replace "field1" and "field2" with actual field names from your database
    $totals[$trainer]["field1"] += $CTOrow["field1"];
    $totals[$trainer]["field2"] += $CTOrow["field2"];
}
// Output the totals for each trainer
foreach ($totals as $trainer => $total) {
    echo "Trainer: $trainer\n";
    echo "Total field1: " . $total["field1"] . "\n";
    echo "Total field2: " . $total["field2"] . "\n";
    echo "\n";
}
 
MilesWeb - Thank you!!

I had to tweak some counters because it wasn't resetting with each trainer.

PHP: 
$CTOQuery = "SELECT * FROM Training WHERE (Date BETWEEN '" . $_REQUEST['StartDate'] . "' AND '" . $_REQUEST['EndDate'] . "') ORDER BY CTO";
$CTOResult = sqlsrv_query($conn, $CTOQuery, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));

//An array to store totals for each trainer and initialize counters
$NICount = 0;
$PSCount = 0;
$NOCount = 0;
$CCount = 0;
$NRTCount = 0;
$HoursCount = 0;
$Totals = [];

while ($CTOrow = sqlsrv_fetch_array($CTOResult, SQLSRV_FETCH_ASSOC)) {
    $CTOCount = array_count_values($CTOrow);

    if ($CTO != $CTOrow["CTO"]) {
        $NICount = 0;
        $PSCount = 0;
        $NOCount = 0;
        $CCount = 0;
        $NRTCount = 0;
        $HoursCount = 0;   
    }
    $NICount = $CTOCount["NI"] + $NICount;
    $PSCount = $CTOCount["PS"] + $PSCount;
    $NOCount = $CTOCount["NO"] + $NOCount;
    $CCount = $CTOCount["C"] + $CCount;
    $NRTCount = $CTOCount["NRT"] + $NRTCount;
    $HoursCount = $CTOrow["Hours1"] + $CTOrow["Hours2"];
    $CTO = $CTOrow['CTO'];
    $Totals[$CTO] = ["NI" => 0, "PS" => 0, "NO" => 0, "C" => 0, "NRT" => 0];
    $Totals[$CTO]["NI"] += $NICount;
    $Totals[$CTO]["PS"] += $PSCount;
    $Totals[$CTO]["NO"] += $NOCount;
    $Totals[$CTO]["C"] += $CCount;
    $Totals[$CTO]["NRT"] += $NRTCount;
    $Totals[$CTO]["Hours"] += $HoursCount;
    
    echo "<table valign=top width=400 cellpadding=2>\n<tr>\n";
    
    foreach ($Totals as $CTO => $total) {
        echo "<tr><td>" . $CTO . "</td>\n";
        echo "<td align=center>" . $total["NI"] . "</td>\n";
        echo "<td align=center>" . $total["PS"] . "</td>\n";
        echo "<td align=center>" . $total["NO"] . "</td>\n";
        echo "<td align=center>" . $total["C"] . "</td>\n";
        echo "<td align=center>" . $total["NRT"] . "</td>\n";
        echo "<td align=center>" . $total["Hours"] . "</td></tr>\n";
        echo "\n";
    }
    echo "</tr>\n</table>\n";
 
You must log in or register to reply here.
Back
Top