CODEWITHSUNDEEP
phpmysqlforeach
Bill Flippen
Bill Flippen

Reputation: 453

foreach loop from mysql query

Coming back to my project after putting it down for a while: Cycling through a query.

I understand that the below code can be cleaned up (PHP usage and table arrangement) and that MySQL commands are deprecated. ( I am working on that part).

But I can't see why I can't make this work. The print_r() gives Resource ID #5 error. My results show 2 tables, each with identical results all from the same course. I am expecting 8 tables, each table with a different course.

Should I use a while loop? if so how? I realize this is elementary, but this is still all new to me so please be gentle.

<?php

include 'inc.php';

$varVeh=$_POST['Veh_num'];
$sql_course="select course_num from hc_course";
$results_course=mysql_query($sql_course);

print_R($results_course);

foreach(mysql_fetch_array($results_course) as $rc)
{
    $sql_HiScores = "SELECT c.course_name as course,  e.distance as distance, e.score as score, e.time as time, e.user as User from hc_entries e left join hc_course c on e.course=c.course_num WHERE c.course_num=$rc and e.vehicle=$varVeh ORDER BY course, score DESC ";
    $result_HiScores = mysql_query($sql_HiScores);

    $sql_vehName="select  Veh_name from hc_vehicle_type where Veh_num=$varVeh ";
    $result_vehName = mysql_query($sql_vehName);
    $vehName=mysql_fetch_assoc($result_vehName);

    echo "<table><tr><th>Best Scores for ".$vehName['Veh_name']."</th> </tr></table>";
    echo "<table border='1'>";
    echo "<tr><th>Course</th><th>Score</th><th>Distance</th><th>Player</th><th>Time</th></tr>";

    while($row = mysql_fetch_array($result_HiScores))
    {
        echo "<tr>";  
        echo "<td>" .$row['course'] . "</td>";
        echo "<td>" .$row['score'] . "</td>";
        echo "<td>" .$row['distance'] . "</td>";
        echo "<td>" .$row['User'] . "</td>";
    }

    echo "</table>";
}
?>

Upvotes: 0

Views: 11066

Answers (2)

Barmar
Barmar

Reputation: 780798

mysql_fetch_array just returns one row of the results, not all the rows. Your foreach loop is just looping over the columns in the first row of results.

If you want to process all the results, you should write:

while ($rc = mysql_fetch_array($results_course))

Then inside your loop, you use $rc['course_num'] to get the course from that row.

But I don't understand why you need that first loop at all. You're JOINing the hc_courses and hc_entries tables in the first query inside the loop. Why don't you just use that same query, but without the c.course_num = $rc condition, so it gets all courses at once instead of doing them one course at a time? Loop over those results, and start a new table every time the course number changes.

Here's the query for that:

SELECT c.course_name as course,
       e.distance as distance,
       e.score as score,
       e.time as time,
       e.user as User,
       c.course_num as course_num
FROM hc_entries e
JOIN hc_course c ON e.course=c.course_num
WHERE e.vehicle=$varVeh
ORDER BY course, score DESC

The PHP then looks like:

$last_course = null;

while ($row = mysql_fetch_assoc($results_HiScores) {
    if ($row['course_num'] !== $last_course) {
        // New course number, start a new table
        if ($last_course !== null) {
            // Close out last table, if any
            echo '</table>';
        }
        $last_course = $row['course_num'];
        echo "<table><tr><th>Best Scores for ".$vehName['Veh_name']."</th> </tr></table>";
        echo "<table border='1'>";
        echo "<tr><th>Course</th><th>Score</th><th>Distance</th><th>Player</th><th>Time</th></tr>";
    }
    echo "<tr>";  
    echo "<td>" .$row['course'] . "</td>";
    echo "<td>" .$row['score'] . "</td>";
    echo "<td>" .$row['distance'] . "</td>";
    echo "<td>" .$row['User'] . "</td>";
    echo "</tr>";
}
if ($last_course !== null) {
    echo "</table>";
}

And you shouldn't do the hc_vehicle_type query inside the loop. It doesn't use any variables that change during the loop, it's just looking up the name of $_POST['Veh_num']. Just do it once and reuse the result inside the loop.

Upvotes: 1

Jason Heo
Jason Heo

Reputation: 10236

try:

foreach(mysql_fetch_array($results_course, MYSQL_ASSOC) as $rc)
{
    ....
}

if second argument is not given, MYSQL_BOTH is assumed, so array has column name, and column number 0,1...,n . so you traversed result set twice (column name and column number)

RTMF : https://www.php.net/manual/en/function.mysql-fetch-array.php

and The print_r() gives Resource ID #5 error this is not an error!. return value of mysql_query() is result set. it's just a Resource of PHP internal structure.

Upvotes: 0

Related Questions