Nested mysql_query() - Resets first result's pointer?

Question

I have a friend who's using FluxBB and he has just installed a modification for this forum software. The viewtopic.php file has a while-loop which does a mysql_fetch_assoc() on a result object. Inside of this loop there's a second mysql_fetch_assoc() on a second result object.

The reason for the nested while loop is that there are many posts, and each posts HasMany thank-you's.

Here's some code to better illustrate what I mean:

$result = mysql_query("some-query");

while($cur_post = mysql_fetch_assoc($result)) {
    // For every post, execute this second query
    $secondResult = mysql_query("some-query that uses the $cur_post id");
    while($thank_you = mysql_fetch_assoc($secondResult)) {
        // Display the thank_you
    }
}

The problem is that the outer-most while loop stops after just one iteration when using the second mysql_query. It does work if the first query is run again, which is a ridiculously dirty hack that works fine with OFFSET.

To me it seems that when the second mysql query is run, whatever $result is pointing at is invalidated. However, I barely know PHP so that's why I've come to SO with this problem.

How would you go about solving this issue? Is it correct that the $result pointer is affected by the second mysql query?

Answer 1

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

Youll have to grab all your posts (or a batch of them) and store the ids or whatever other data you need into an array or object and then iterate over it making the appropriate query and displaying output.

Or you could use some sql like this:

SELECT thank_you.* FROM thank_you, post WHERE post.id = thank_you.post_id ORDER BY thank_you.post_id;

Of course with this you lose grouping youd have to add some extra logic to build the proper presentation structures based on if the post_id has changed within the loop.