PHP double while loop, second loop not iterating more than once

Question

so I'm working on a "product listing", and the idea is to have the name of the product and then a drop down list of colours derived from the database. The problem is I can only get the "colours" while loop to iterate the once, I get the first product name and the colours in the drop down menu the once but not subsequently, the help I'd like is, how can I change this code to make it do what I need, I've been trying for a week and I could really use some help.

    $product_query = mysql_query('SELECT * FROM products WHERE id > 0');
$colour_query = mysql_query('SELECT * FROM product_colours WHERE id > 0');


while ($get_product_rows = mysql_fetch_assoc($product_query)){
echo $get_product_rows['name'];
    echo "<br \>";

    if ($get_product_rows['has_colour'] == '1'){
        while ($get_colour_row = mysql_fetch_assoc($colour_query)){
            // Drop down box population goes here.


        }
    }
}

If anyone can help I would appreciate it. From Grant M.

I'm not certain of this and don't have time to test, but I think trying to simultaneously run two mysql queries like that is unpredictable. Instead, you could do the while loops to convert the queries into arrays one at a time, and then do a nested foreach loop. — kingcoyote
– kingcoyote, Commented Mar 16, 2012 at 0:23
can you do var_dump(mysql_fetch_assoc($color_query)) and update your question with the output? — F21
– F21, Commented Mar 16, 2012 at 0:24
@kingcoyote Why would it be unpredictable? He has got two different queries on two different tables, also he is executing his queries before the while loops. — steveoh
– steveoh, Commented Mar 16, 2012 at 0:31
@steveoh It's a vague memory, but I recall the pointers getting mixed up with each other, where the nested mysql_fetch_assoc conflicts with the outer one. So instead of going 1A, 1B, 1C, 2A, 2B, 2C, it goes 1A, 2B, 3C or something like that. — kingcoyote
– kingcoyote, Commented Mar 16, 2012 at 0:38
Do the colours vary by product? If so, joining products to product_colours might be more efficient (depending on a lot of factors like database link latency, number of rows in each result set...) — James Holwell
– James Holwell, Commented Mar 16, 2012 at 0:40

kba · Accepted Answer · 2012-03-16 00:28:22Z

3

The way mysql_fetch_assoc() works is that it has an internal pointer, and every time you run the method, you get the next item and the pointer is moved one time. If there was no pointer, how would the while loop otherwise ever terminate? It would just keep pulling out colours, over and over.

The way to resolve this is to reset the point in each iteration. This can be done using mysql_data_seek().

$product_query = mysql_query('SELECT * FROM products WHERE id > 0');
$colour_query = mysql_query('SELECT * FROM product_colours WHERE id > 0');

while ($get_product_rows = mysql_fetch_assoc($product_query)) {
    echo $get_product_rows['name'];
    echo "<br />";

    if ($get_product_rows['has_colour'] == '1') {
        while ($get_colour_row = mysql_fetch_assoc($colour_query)) {
            // Drop down box population goes here. 
        }
    }
    mysql_data_seek($colour_query, 0);
}

answered Mar 16, 2012 at 0:28

kba

19.5k6 gold badges65 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Grant Mackenzie Over a year ago

I tried your addition and I ended up with the second while loop iterating quite a few times, seems to have iterated based on the amount of returned rows from the query, however, inserting a "break" in the loop sorted that. Thanks very much for your help people.

kba Over a year ago

@GrantMackenzie You're welcome. If this solved your problem, remember to mark it as an answer by clicking the V to the left.

James Holwell · Accepted Answer · 2012-03-16 00:35:24Z

@Kristian Antonsen is quite right - once you've read the rows once you are at 'the end' unless you rewind to the beginning of the result set.

Alternatively to the other answers posted, because the colours aren't dependent on the product - get them once and then reuse them in memory.

// get the colours once
$colour_query = mysql_query('SELECT * FROM product_colours WHERE id > 0');
$colours = array();
while ($get_colour_row = mysql_fetch_assoc($colour_query)) {
  array_push($colours, $get_colour_row['colour']);
}

// loop through each product
$product_query = mysql_query('SELECT * FROM products WHERE id > 0');
while ($get_product_rows = mysql_fetch_assoc($product_query)) {
    echo $get_product_rows['name'];
    echo "<br />\n";

    if ($get_product_rows['has_colour'] == '1'){
        foreach ($colours as $colour) {
            echo $colour . "<br />\n";
        }
    }
}

Ry- · Accepted Answer · 2012-03-16 00:28:30Z

The problem is that the second time around, the pointer is already at the end of the record for $colour_query; it will continue to return null because it doesn't know that the nested loop is complete.

You can solve this problem by resetting the pointer using mysql_data_seek:

$product_query = mysql_query('SELECT * FROM products WHERE id > 0');
$colour_query = mysql_query('SELECT * FROM product_colours WHERE id > 0');

while ($get_product_rows = mysql_fetch_assoc($product_query)) {
    echo $get_product_rows['name'];
    echo "<br />";

    if ($get_product_rows['has_colour'] == '1') {
        while ($get_colour_row = mysql_fetch_assoc($colour_query)) {
            // Drop down box population goes here.
        }

        // Reset the pointer:
        mysql_data_seek($colour_query, 0);
    }
}

Also, your <br \> is wrong; it should be <br />.

Collectives™ on Stack Overflow

PHP double while loop, second loop not iterating more than once

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related