Using mysql_fetch_assoc twice in a single PHP script

Question

I usually try to minimize calls to MySQL where possible, but I've finally encountered the case where I have to do multiple calls to MySQL in a single script.

It looks like you can't use mysql_fetch_assoc twice in a single script(!).

It seems that mysql_data_seek is the solution, but I cannot seem to get it to work.

To be clear, I have two queries I need to make. The second query depends on results from the first... Here's the structure:

$result = mysql_query($query1);

while($row = mysql_fetch_assoc($result)){
 $pos = $row['position'];
}

mysql_free_result($result); // result freed per comment below. 

$query2 = ' '; //... dependent on $pos - in mysql shell this returns results! 
$result2 = mysql_query($query2) 

while($row = mysql_fetch_assoc($result2)){

 echo $row['id'];

}

What happens above is that the second while loop returns no results even though the query should have nontrivial rows.

---

Just to be sure: Is this how you clear the pointer from the previous result to be able to use mysql_fetch_assoc again?

mysql_data_seek($result,mysql_num_rows($result) - 1);

I'm not sure what to use as the second argument. Admittedly, I am not that clear on pointers, but it seems I should clear the pointer to 0. But I get this error:

Offset 0 is invalid for MySQL result index 8 (or the query data is unbuffered

Answer 1

Check your connection with mysql_error() and see if you're getting the "commands out of sync" error. If so, you need to call mysql_free_result() after completing the first query and before starting the second. You could also just call mysql_close() to close your database connection and then reopen a new one.

For more details, see this question.

Answer 2

OP changed the question, so see the edit

*Deleted the posted codes here**

EDIT

After your edited your question and made clear you have actually 2 resources it looks like there is something else wrong. You don't have to worry about pointer when you use two different resources to supply mysql_fetch_assoc(). The thing with mysql_fetch_assoc() is that it takes your param ($result) by reference.

Now to answer your question:

I usually try to minimize calls to MySQL where possible, but I've finally encountered the case where I have to do multiple calls to MySQL in a single script.

Nothing wrong with multiple SQL calls in one script. Although in general you should try to minimize the SQL calls (because they may hurt performance).

It looks like you can't use mysql_fetch_assoc twice in a single script(!).

Plain wrong. Ofc you can do it. As long as you note the above. However when you have two result sets this wouldn't be your problem.

It seems that mysql_data_seek is the solution, but I cannot seem to get it to work.

Again: this has nothing to do with it when you use two (different) result sets.

To be clear, I have two queries I need to make. The second query depends on results from the first.

This shouldn't be any problem at all. It looks like is something else wrong. Have you verified that the second query really is what you think it is? Are you sure there are records? Are you sure there aren't any (MySQL) errors. Do you have error reporting enabled? Have you tried printing out mysql_error()? To better be able to help you can you please provide your real code and not etc etc stuff? Maybe something else is going on.

Or maybe you are simply trying to run the second query inside the first loop. Which would be bad in so many ways.