Page 1 of 1

Database result resource - detail?

Posted: Mon Jun 17, 2002 4:34 am
by lipowg
When use a database connection to execute a select statement and it succeeds in returning the result set, what exactly has it returned to PHP?

If I execute a command like
$result=mysql_query("Select * FROM USERS");

then I can fetch one row at a time from $result - for example with:
$row=mysql_fetch_array($result);

And each time I issue the fetch command I will get the next row, and there are functions to move the pointer or reset back to the beginning.

My question: Is the entire result in PHP memory? Or is the result set a temporary table on the database server? In other words does the PHP result set just point to the database? Or can I close the connection and still continue to get the fetch rows from result?

Also I assume that even if it is a pointer it is a static result set. That is if someone updates or deletes one of the records my select statement retrieved, that this will not be reflected in my result set $result.

OK - I know this was not put in the most succinct manner possible; but I wanted to be clear on what I already knew, what I question was, and what I knew that might not be so.

Posted: Mon Jun 17, 2002 4:46 am
by mikeq
My question: Is the entire result in PHP memory? Or is the result set a temporary table on the database server? In other words does the PHP result set just point to the database? Or can I close the connection and still continue to get the fetch rows from result?
Does it really matter, database connections end when your script ends (as long as you are not using persistent connections)
Also I assume that even if it is a pointer it is a static result set. That is if someone updates or deletes one of the records my select statement retrieved, that this will not be reflected in my result set $result.
Correct.

Re: Database result resource - detail?

Posted: Mon Jun 17, 2002 5:27 am
by twigletmac
lipowg wrote:Or can I close the connection and still continue to get the fetch rows from result?
The easiest way to find stuff like this out is to test it. Open a connection, run a query, close the connection and then see if you can access the results (quick answer - yes you can).

As for why you would want to close the database connection before the end of the script, well if you've finished using it why keep it open?

Mac

Posted: Mon Jun 17, 2002 8:08 am
by jason
My question: Is the entire result in PHP memory? Or is the result set a temporary table on the database server? In other words does the PHP result set just point to the database? Or can I close the connection and still continue to get the fetch rows from result?
It depends on the Database. IIRC, Oracle starts returns its results as soon as it finds them, where as MySQL first gets the results, and then returns them.

But no, you can't close the connection to the database and THEN get the results back, you must first retrieve the results.

Posted: Mon Jun 17, 2002 11:11 am
by lipowg
You are right - it was late at night, and I was tired. I should have thought of testing it. But until I phrased the question for the forum my question was not clear enough to test.

Anyway thanks all - that answers the part of my question most important to me. The main point that the results resource is static is the critical point; that makes writing optimistic locking classes pretty straightforward.