Originally posted by HotDog
[b]This is actually a pretty good question, and I don’t know. But if PHP does query the whole database and store it in memory before selecting rows $stop to $start, then every time you say mysql_query("SELECT * FROM db") you are doing the same thing and run into the same problem of having to query the whole database, right??
If the information stays in the database until PHP actually calls any data, then this is a moot point. But if not, then a more effecient way would be to come up with a WHERE statement to limit the database query. But as I have stated before, I don’t know how query ROW sequence without some sort of refference data. If I could say WHERE ROWS BETWEEN 5 AND 10 that would be great!!
Like I said, if someone else has found a SQL work around for this, I’d love to know it. I think this would be important to know how to do since MySQL is the only database (that I know of) that uses the LIMIT feature. So if there is another way of doing it in a SQL statement, or at least be able to limit you query on the database, it would enable other databases to emulate the LIMIT feature.
But once again, if PHP doesn’t hold the whole table query in memory and only selects the rows as they are actually called, then it really shouldn’t make any difference, right??
<Edited by HotDog on 01-31-2001 at 03:53 PM> [/B]
Well, I know the database does do tons of work behind the scenes to keep it working, and I’m pretty sure PHP does some trickery as well.
For instance, if I do “SELECT * FROM post” on our 1.2 million row post table PHP immediately DIES without spitting out a single row. I can only attribute this to some internal buffer in PHP getting filled up as it queries the rows (even before I execute the fetch_array( … ) function) – perhaps it is the memory cap set in the ini file? In any rate, the database has to set up temporary tables and such in memory to handle the result set, so any time you can limit it that would be a good thing. The “LIMIT x, y” syntax in MySQL is non-standard SQL which is why it only works in MySQL. I know MS-SQL server has similar syntax (also non-standard), as well as Oracle and Sybase.
For your “BETWEEN( x, y )” problem I can only suggest this:
Every time you delete a record, reload all of the data into your table, essentially removing the “holes” in the system (as long as you don’t mind the ID being reset that is). The way to do this (if Access supports SELECT INTO) would be (GO is equivalent to ; at the end of MySQL statements):
SELECT *
INTO temp_messages
FROM messages
GO
DELETE messages
GO
INSERT INTO messages
SELECT *
FROM temp_messages
GO
If Access does not support the SELECT INTO SQL extension, then you will have to do something like:
CREATE TABLE temp_messages ( blah )
GO
INSERT INTO temp_messages
SELECT * FROM messages
GO
DELETE messages
GO
INSERT INTO messages
SELECT *
FROM temp_messages
GO
That way you can be sure that you are grabbing the correct amount with the BETWEEN( … ) clause.
Or, if you don’t want to mess with re-numbering them, you can take a look at odbc_fetch_row( … ); in the PHP manual (http://www.php.net/manual/en/function.odbc-fetch-row.php) to see how that works, but I think because the ODBC drivers aren’t API calls directly to the SQL server like the other PHP database functions are, you might still have the same database result set overhead as before.
