Loop over array, not returning desired results

Question

I'm using the following foreach loop to build a grid in a table of points from a racing series.

I was using a while loop with php mysql interface, but I have changed the entire site to us PDO, and this is the last part I need to get working. I'm really struggling with it.

The problem I am having is that it misses some data, doesn't display it on the page and the elseif does nothing. I don't get the blank cells. I need them so that it will pad the table to the right amount of columns so that the table doesn't look hideous.

Also just noticed that it's simply outputting wrong data.

This foreach happens inside of another foreach where I am matching the current line "schedule_id" with an array of "schedule_ids" from the race schedule. in that loop I'm doing a query to find the current rider

foreach ($results as $data) {
    var_dump($data);
    if ($data['schedule_id'] == $schedule) {
        if ($data['finish'] == "1") {
            echo "" . $data['finish'] . "
" . $data['points'] . "
";
        } else {
            echo "" . $data['finish'] . "
" . $data['points'] . "
";
        }
     } elseif ($data['points'] == "0") {
        echo "  

";
    }
}

The $data array looks like this:

array (size=5)
 'schedule_id' => int 27
 'finish' => int 1
 'points' => int 20
 'class_id' => int 1
 'rider_id' => int 1
array (size=5)
 'schedule_id' => int 43
 'finish' => int 3
 'points' => int 13
 'class_id' => int 1
 'rider_id' => int 1
array (size=5)
 'schedule_id' => int 27
 'finish' => int 3
 'points' => int 13
 'class_id' => int 1
 'rider_id' => int 3
array (size=5)
 'schedule_id' => int 43
 'finish' => int 2
 'points' => int 16
 'class_id' => int 1
 'rider_id' => int 3
array (size=5)
 'schedule_id' => int 43
 'finish' => int 4
 'points' => int 11
 'class_id' => int 1
 'rider_id' => int 129

Here is the complete code that produces this table. Any help getting this sorted out would be greatly appreciated.

$season_id = 3;


$stmt = $db->prepare("SELECT id, date FROM schedule WHERE raceevent = '1' AND cancelled = '0' AND season_id = :season_id ORDER BY date");
$stmt->execute(array(':season_id'=>$season_id));
$num_rows = $stmt->rowCount();
$numrows = ($num_rows * 2 + 5);


                           //display first blank cells of table
echo " 
";

while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $date =  date_create($r['date']);
    $schedule_id_array[] = $r['id'];

                            //display the schedule dates for active races in the season
    echo  "" . date_format($date, 'm-d') . "
";

}
                            //display final two columns in table head
echo "Total
Points% entered";
echo "";

                            //display second row of table head
for ($i=1; $i<=$num_rows; $i++) {
    echo "FINPTS
";
}
                            //close table head
echo "
";

                            //get rider classes
$stmt1 = $db->prepare("SELECT id, class FROM classes");
$stmt1->execute();
while ($r1 = $stmt1->fetch(PDO::FETCH_ASSOC)) {
                            //get each rider in each class
    $stmt2 = $db->prepare("SELECT DISTINCT SUM(results.points) AS points, results.class_id, results.rider_id FROM results WHERE results.class_id = :id AND season_id = :season_id GROUP BY results.class_id, results.rider_id ORDER BY points DESC");
    $stmt2->execute(array(':id'=>$r1['id'], ':season_id'=>$season_id));
    while ($r2 = $stmt2->fetch(PDO::FETCH_ASSOC)) {
                            //display classes and riders in each cleass
        echo "" . $r2['class_id'] . " - " . $f->getClassName($db, $r2['class_id']) ."
" . $r2['rider_id'] . " - " . $f->getRiderName($db, $r2['rider_id']) . "
";

                            //loop thru the scheduled races         
        foreach($schedule_id_array as $schedule) {
                            //get points for each rider in each class unless the points for that rider = 0
        $stmt3 = $db->prepare("SELECT DISTINCT `schedule_id`, `finish`, `points`, `class_id`, `rider_id` FROM results WHERE schedule_id = :schedule and `rider_id` = :rider_id AND `class_id` = :class_id AND  `season_id` = :season_id ORDER BY schedule_id");
        $stmt3->execute(array(':schedule'=>$schedule,':rider_id'=>$r2['rider_id'], ':class_id'=>$r1['id'], ':season_id'=>$season_id));

            $results = $stmt3->fetchAll(PDO::FETCH_ASSOC);

                            //loop thru each rider and display points for each schedule race                
            foreach ($results as $data) {

                var_dump($results);

                if ($data['schedule_id'] == $schedule) {

                    if ($data['finish'] = 1) {
                        echo "" . $data['rider_id'] . " - " . $data['finish'] . "
" . $data['rider_id'] . " - "  . $data['points'] . "
";
                    } else {
                        echo "" . $data['rider_id'] . " - "  . $data['finish'] . "
" . $data['rider_id'] . " - "  . $data['points'] . "
";
                    }

                } elseif ($data['points'] = 0) {
                    echo "  

";
                }

            }


        }

                            // end foreach schedule_id_array; print out total points and percent ran
        $stmt4 = $db->prepare("SELECT DISTINCT `schedule_id`, `finish`, `points` FROM results WHERE `rider_id` = :rider_id AND `class_id` = :id and `season_id` = :season_id");
        $stmt4->execute(array(':rider_id'=>$r2['rider_id'], ':id'=>$r1['id'], ':season_id'=>$season_id));
        $races = $stmt4->rowCount();
        $percent_races = ($races/$num_rows) * 100;
        echo "" .  $r2['points'] . "";
        printf("%.0f", $percent_races);
        echo "%";

    }
}

quickshiftin · Accepted Answer

Still pretty ugly, but here is a stab in the right direction cleaning up the code... Once you get the code cleaned up it should become more obvious where your problem is.

As to your actual question, I sure hope this cleanup effort helps you track it down!

$season_id = 3;

// Fetch schedules
$stmt = $db->prepare("SELECT id, date FROM schedule WHERE raceevent = '1' AND cancelled = '0' AND season_id = :season_id ORDER BY date");
$stmt->execute(array(':season_id'=>$season_id));
$iNumSchedules = $stmt->rowCount();

$aSchedules = array();
while($r = $stmt->fetch(PDO::FETCH_ASSOC))
    $aSchedules[$r['id']] = date_create($r['date']);

// Fetch classes
$stmt1 = $db->prepare("SELECT id, class FROM classes");
$stmt1->execute();
$aClasses = array();
while($r = $stmt1->fetch(PDO::FETCH_ASSOC))
    $aClasses[$r['id']] = $r['class'];

function get_class_results($iClassId, $iSeasonId) {
    global $db;
    $stmt2 = $db->prepare("SELECT DISTINCT SUM(results.points) AS points, results.class_id, results.rider_id FROM results WHERE results.class_id = :id AND season_id = :season_id GROUP BY results.class_id, results.rider_id ORDER BY points DESC");
    $stmt2->execute(array(':id'=>$iClassId, ':season_id'=>$iSeasonId));
    $aClassResults = array();
    while($r = $stmt2->fetch(PDO::FETCH_ASSOC))
        $aClassResults[] = $r;
    return $aClassResults;
}

function get_schedule_results($sSchedule, $iRiderId, $iClassId, $iSeasonId) {
    global $db;
    $stmt3 = $db->prepare("SELECT DISTINCT `schedule_id`, `finish`, `points`, `class_id`, `rider_id` FROM results WHERE schedule_id = :schedule and `rider_id` = :rider_id AND `class_id` = :class_id AND  `season_id` = :season_id ORDER BY schedule_id");
    $stmt3->execute(array(':schedule'=>$sSchedule,':rider_id'=>$iRiderId, ':class_id'=>$iClassId, ':season_id'=>$iSeasonId));
    return = $stmt3->fetchAll(PDO::FETCH_ASSOC);
}

function get_races($iRiderId, $iClassId, $iSeasonId) {
    global $db;
    $stmt4 = $db->prepare("SELECT DISTINCT `schedule_id`, `finish`, `points` FROM results WHERE `rider_id` = :rider_id AND `class_id` = :id and `season_id` = :season_id");
    $stmt4->execute(array(':rider_id'=>$iRiderId, ':id'=>$iClassId, ':season_id'=>$iSeasonId));
    return $stmt4->rowCount();
}

//------------------------------------------------------------
// Build the table header row
//------------------------------------------------------------
// display first blank cells of table
echo " 
";

// display the schedule dates for active races in the season
foreach($aSchedules as $iScheduleId => $date)
    echo  "" . date_format($date, 'm-d') . "
";

// display final two columns in table head
echo "Total
Points% entered";
echo "";

// display second row of table head
for($i=1; $i<=$iNumSchedules; $i++) {
    echo "FINPTS
";
}
// close table head
echo "
";

// get rider classes
foreach($aClasses as $iClassId => $sClassName) {

    // get each rider in each class
    foreach(get_class_results($iClassId, $season_id) as $r2) {

        // display classes and riders in each cleass
        echo "" . $r2['class_id'] . " - " . $f->getClassName($db, $r2['class_id']) ."
" . $r2['rider_id'] . " - " . $f->getRiderName($db, $r2['rider_id']) . "
";

        // loop thru the scheduled races         
        foreach(array_keys($aSchedules) as $schedule) {
            $results = get_schedule_results($schedule, $r2['rider_id'], $iClassId, $season_id);

            // loop thru each rider and display points for each schedule race                
            foreach($results as $data) {

                if($data['schedule_id'] == $schedule) {

                    if($data['finish'] = 1)
                        echo "" . $data['rider_id'] . " - " . $data['finish'] . "
" . $data['rider_id'] . " - "  . $data['points'] . "
";
                    else
                        echo "" . $data['rider_id'] . " - "  . $data['finish'] . "
" . $data['rider_id'] . " - "  . $data['points'] . "
";

                } elseif($data['points'] = 0)
                    echo "  

";

            }
        }

        // end foreach schedule_id_array; print out total points and percent ran
        $races = get_races($r2['rider_id'], $iClassId, $season_id);
        $percent_races = ($races/$iNumSchedules) * 100;
        echo "" .  $r2['points'] . "";
        printf("%.0f", $percent_races);
        echo "%";
    }
}

Loop over array, not returning desired results

Answers (2)

Related Questions