0

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

Improve this question
5
  • lt.php.net/mysql_data_seek Commented Mar 28, 2012 at 18:17
  • 1
    stackoverflow.com/questions/5749711/… Commented Mar 28, 2012 at 18:25
  • You can call it as many times as you want, but you'll only get results if you're passing in a valid query result handle, and that result still has unfetched rows available. Commented Mar 28, 2012 at 18:35
  • What part of the information here is unclear? php.net/manual/en/function.mysql-data-seek.php Commented Mar 28, 2012 at 18:59
  • i guess i am not clear what the second argument is... when i set it to 0 to clear the pointer to the first row, i get this error: Offset 0 is invalid for MySQL result index 8 (or the query data is unbuffered Commented Mar 28, 2012 at 19:00

2 Answers 2

Reset to default
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.

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

Comments

0

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.

Improve this answer

9 Comments

what if you have a second query that you need to mysql_fetch_assoc for in the same script?
Sorry to take over your answer, but @ina changed the question so it wasn't correct (anymore). Hope you don't mind. :-)
the second query does indeed return records! the second query is run inside its own loop. sorry, i've been staring at this thing for hours now and it's driving me nuts!
sorry - i meant, if i were to shell into mysql, the second query returns records... but somehow the php isn't pulling through...
@ina I feel your pain. SO you have tried to echo the second query and copy/pasted it in shell and you got results? If yes: what does mysql_error() say?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.