LIMIT the result set of an ODBC query in PHP
Okay, this is one that I constantly forget. When querying an MS Access database from a PHP script via an ODBC connection, you often want to limit the result set instead of receiving the entire dataset. In MySQL you’d use the LIMIT clause in the query, ie. SELECT * FROM TableName LIMIT 10;. This won’t work with an MS Access database, however.
However, if you try that with an ODBC connection you will like receive something along the lines of the following error:
SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ‘TableName LIMIT 10’., SQL state 37000 in SQLExecDirect
It’s a lovely error message, too bad it’s almost completely uninformative. It turns out that the error is because MS Access SQL doesn’t support the LIMIT clause.
The answer, is that MS Access SQL doesn’t use the LIMIT clause, it instead uses the TOP specifier. So the above query would be re-written for MS Access as SELECT TOP 10 * FROM TableName; which will return the first 10 rows from TableName including all columns, just like our previous MySQL statement would have.
As with the LIMIT clause, TOP can be used in conjunction with any JOIN‘s, ORDER BY‘s that are also in your SQL query.
There are no comments on this entry.
There are no trackbacks on this entry.