Multiple While Loops within a While Loop?

Question

Total newbie trying to learn... thanks for all the help so far!

UPDATE - updated code below - Thanks Phill

I'm now trying to get the relevant horse information from a table horses which I can join to race_form with the horse_name field. I want to then display the information for each horse in the race below the while($racecard) info. Does this make it a bit clearer?

I thought I could just do another query and then a while loop with mysql_fetch_array below the one I have already and that would display it and then move onto the next race, but that apparently doesn't work...?!

I'm wondering if you can run 2 while loops after each other inside a while loop? I am working on a horse racing formguide - I can display each race list, but underneath I want to display individual horse details on each horse in the race. Anyway if you look at this page you can see what I'm trying to do:

http://tasmanianracing.com/superform/formguide.php?meetingID=21042011LAUN&Submit=View+Form

Problem is, any time I put a second while loop after the race list, it won't show up. I'm trying to run a query to get the horse details from my horse table and then run a while for each horse in the race, but nothing shows up.

I hope this is clear enough ... my snippet of code is below - please let me know if I've missed out something important:

echo "<table width='990' border='1' cellspacing='2' cellpadding='2'>";

$racedetails = mysql_query("SELECT * 
    FROM race_info
    WHERE meetingID = ('" . $safemeetingID . "')");

while($row = mysql_fetch_array($racedetails))
{
    echo "<tr><td>Race " . $row['race_number'] . " at " . $row['start_time'] . " " . $row['class'] . " over " . $row['distance'] . "m</td></tr>";

    $racecard = mysql_query("SELECT *
        FROM race_form
        INNER JOIN race_info ON race_form.raceID = race_info.raceID
        WHERE race_form.raceID = ('" . $row['raceID'] . "')");

    while($row = mysql_fetch_array($racecard))
    {
        echo "<tr><td>" . $row['number'] . "</td><td>" . $row['past10'] . "</td><td>" . $row['horse_name'] . "</td></tr>";
    }

    echo "</table><br>";

    echo "<br>I wish I could put in some horse form here...<br>";

    echo "<table width='990' border='1' cellspacing='2' cellpadding='2'>";

}

echo "</table>";

You can stack loops 1000 deep if you want, it just takes a starts to take a LONG time to execute the full outer loop. — josh.trow, Apr 20 '11 at 13:03
Nested loops are not uncommon. But make sure there is a exit condition for all the loops and like @Calum said, to ensure to do it in an optimized way to prevent a timeout. — Ranhiru Jude Cooray, Apr 20 '11 at 13:04
I'm not sure exactly what problem you're having, but I wouldn't be surprised if it were related to your reuse of the variable `$row` in each of your while loops... Why don't you try using a different variable for each loop? — jswolf19, Apr 20 '11 at 13:06
OK thanks - I'm sure there's a better way but my skills are so rudimentary that this is all I could come up with! — Tristan Heffernan, Apr 20 '11 at 13:15
**PRO TIP:** It is a really bad idea to put a select query inside of a loop. You should try to select all of your information outside of the loop and then manipulate. — afuzzyllama, Apr 20 '11 at 14:07

score 4 · Answer 1 · answered Apr 20 '11 at 13:03

4

You'll probably need to change the names of some of the $row variables, they may interfere with each other.

For example:

while($row_races = mysql_fetch_array($totalraces)){
while($row_details = mysql_fetch_array($racedetails)){
while($row_card = mysql_fetch_array($racecard)){

answered Apr 20 '11 at 13:03

Calum

5,308
1
22
27

1

Thanks ... I'm so new I didn't realise I could name each row variable differently - I thought it had to be that to call the row ... lol – Tristan Heffernan Apr 20 '11 at 13:14
Nope no success. When I put my 2nd while loop in where I have the Testing Testing stuff nothing shows up – Tristan Heffernan Apr 20 '11 at 13:23

score 1 · Answer 2 · answered Apr 20 '11 at 13:03

1

I theory you could and in practice you can, but a while in a while in a for in a while seems a little bit over the top...

I'm sure we can help you make it more efficient if you explain what it is you're trying to do.

answered Apr 20 '11 at 13:03

Rudie

52,220
42
131
173

OK I will try and explain. I have 5 tables - race_meetings which has a record on the meeting ie date track etc, race_info which has a record on each race ie distance, class time, race_form which has a record for each runner in an upcoming race with jockey barrier weight etc, horses which contains each horses details and race_results which is not used here ... I want to display for an upcoming meeting, race 1 info, a list of runners in race 1 below, and more details for each horse in that race below that. Then the same for race 2 and so on ... is that clearer? – Tristan Heffernan Apr 20 '11 at 13:10
Maybe there is a better way to optimize your query using more joins? You may need to nest the loops, as this is common in db queries for 2-3 queries, but more than that seems excessive. – David Houde Apr 20 '11 at 13:21
Do you mean I should do one big query joining all my tables prior to the while/for loops and then call on it in the loops? – Tristan Heffernan Apr 20 '11 at 13:24
Not 1 query, but `JOIN`s might be smart. Also smart would be to take the queries outside the loops, because queries are expensive. Right now you'll need 1*5*5 queries. You can optimize that to 1+5+5 queries (give or take). – Rudie Apr 20 '11 at 13:27
The data structure would help a lot: where are the foreign keys and how are they related? Also: if you specify your question, please do that in the original location (you can edit), so you can use more styles, have more space and it's always 'up there'. – Rudie Apr 20 '11 at 13:28

Phill Pafford · Accepted Answer · 2011-04-20T13:45:40.947

I think you can get rid of one of your queries:

The first query gets the number of races by selecting a COUNT, This returns one record with the count value.

// This returns one record with the count
$total_races    = "SELECT COUNT(race_number) AS totalraces 
                   FROM race_info WHERE meetingID = ('".$safemeetingID."') ";

Next you iterate over the the same records as the rows returned for the race details are the same as the count.

// This looks to return the record(s) with the race details
$race_details   = "SELECT * FROM race_info 
                    WHERE meetingID = ('" . $safemeetingID . "')";

I think you can just use this to get the desired results: (I agree to rename the $row variable(s) to something descriptive for each while loop)

$racedetails = mysql_query("SELECT *
FROM race_info
WHERE meetingID = ('" . $safemeetingID . "')");

while($details_row = mysql_fetch_array($racedetails))
{
    echo "<tr><td>Race " . $details_row['race_number'] . " at " . $details_row['start_time'] . " " . $details_row['class'] . " over " . $details_row['distance'] . "m</td></tr>";

    $racecard = mysql_query("SELECT *
    FROM race_form
    INNER JOIN race_info ON race_form.raceID = race_info.raceID
    WHERE race_form.raceID = ('" . $details_row['raceID'] . "')");

    while($rc_row = mysql_fetch_array($racecard))
    {
    echo "<tr><td>" . $rc_row['number'] . "</td><td>" . $rc_row['past10'] . "</td><td>" . $rc_row['horse_name'] . "</td></tr>";
    }

    echo "</table><br>";

    echo "Testing<br>Testing<br>I wish I could put in some horse form here...<br>";

    echo "<table width='990' border='1' cellspacing='2' cellpadding='2'>";

}

NOT TESTED/PSEUDO CODE

"SELECT * 
FROM horses AS h, 
INNER JOIN race_info  AS ri ON race_form.raceID = race_info.raceID
WHERE horse_name IN (
    SELECT horse_name
    FROM race_form AS srf
    WHERE h.horse_name = srf.horse_name
)
AND race_form.raceID = ('" . $details_row['raceID'] . "')"

The idea is to join the two queries into one, I know this is not the correct syntax but it might give you an idea on how to go about it.

Or you can do another query while loop for the horse names

$racedetails = mysql_query("SELECT *
FROM race_info
WHERE meetingID = ('" . $safemeetingID . "')");

while($details_row = mysql_fetch_array($racedetails))
{
    echo "<tr><td>Race " . $details_row['race_number'] . " at " . $details_row['start_time'] . " " . $details_row['class'] . " over " . $details_row['distance'] . "m</td></tr>";

    $racecard = mysql_query("SELECT *
    FROM race_form
    INNER JOIN race_info ON race_form.raceID = race_info.raceID
    WHERE race_form.raceID = ('" . $details_row['raceID'] . "')");

    while($rc_row = mysql_fetch_array($racecard))
    {
        echo "<tr><td>" . $rc_row['number'] . "</td><td>" . $rc_row['past10'] . "</td><td>" . $rc_row['horse_name'] . "</td></tr>";

        $horses = mysql_query("SELECT *
        FROM horses
        WHERE horse_name = ('" . $rc_row['horse_name'] . "')");

        while($horse_row = mysql_fetch_array($horses))
        {
            // echo horse details here
        }
    }

    echo "</table><br>";

    echo "Testing<br>Testing<br>I wish I could put in some horse form here...<br>";

    echo "<table width='990' border='1' cellspacing='2' cellpadding='2'>";

}

Thanks this does the same job in a better way. Now my only question is how to display the horse details of each horse listed in the racecard, below each race, if that makes sense? It means grabbing the info of each horse from the horse table - both horses and race_form tables have a horse_name field to join them — Tristan Heffernan, Apr 20 '11 at 13:34
Hey Phill, that last code almost does it, but it puts the $horses info above the $racedetails info, not below it. — Tristan Heffernan, Apr 20 '11 at 13:59
I'll tick this as answered because you've pretty much got me there cheers — Tristan Heffernan, Apr 20 '11 at 22:23

Multiple While Loops within a While Loop?

3 Answers3

Linked