SQL LIMIT problem with odbc (MS_ACCESS)

There’s definitely a way of limiting results using ODBC - I’ve seen it somewhere before but I cant for the life of me track it down :frowning:

Originally posted by MattR

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?

What if you were to use a some sort of odbc_prepare to setup what you wanted to query first, and once you have all the parameters set, do the execution. Would you still run into the same problem??

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.

I’ll give this a try and let you know how it works. My only question is wouldn’t this tie up the db server while it recalculated the ID numbers. And if so, what would be the time frame with a long table (1-2 million rows, not that my humble message board will get that big). To so this I suppose I would need to lock the table until the update is complete, right.

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.
[/B]

I actually used this function in my working example above. I have it in a FOR loop to count through the rows setting up a $start and $stop variable for the row selection, then passing a $start+10 variable on to the next page to where to start counting the next set of rows. It works great, but if PHP loads the whole query into memory before selecting anything and executing anything, then with a large row table it would bogg down and crash out. But if php doesn’t do this, or if we can work around it, why not just do it that way?