Skip to content

LIMIT the result set of an ODBC query in PHP

Posted by Nathan Giesbrecht on July 15, 2014 in Snippets, Tips, Web Development

The Problem

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

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.

Spread The Love, Share Our Article

  • Delicious
  • Digg
  • Newsvine
  • RSS
  • StumbleUpon
  • Technorati
  • Twitter

Related Posts

Comments

There are no comments on this entry.

Trackbacks

There are no trackbacks on this entry.

Add a Comment

Required

Required

Optional


framework-break