0

I am trying to build an application in PHP PDO. I need my queries to be very generic i.e. a same query should run in any DBMS, how can I achieve this ?

For example,

MySQL - select * from emp limit 10,20;

and

MS SQL - select * from (select *, ROW_NUMBER() as cnt from emp)x where cnt between 10 and 20 (something like this)

How can I achieve both of these in in PDO using generic code. SO that i just have to change dbms (and drivers) and then rest of the things work fine.

Please help.

Thanks !

Andrey Korneyev
  • 26,353
  • 15
  • 70
  • 71
Mandar
  • 1
  • 2

1 Answers1

0

SQL Server as you know has a completely different approach to 'limiting', and in particular SQL Server has no notion of the 'range' ability that MySQL offers, to do this you need to wrap the query in a sub-select.

So... you're probably looking at some ORM/query builder so that you can notionally write queries and set limits/ranges and then this will need some method to generate a MySQL or SQL Server version of the query.

Here is an example of the query you want SQL Server style ;) Row Offset in SQL Server

A good approach is using something like Doctrine and it's Query Language - http://doctrine.readthedocs.org/en/latest/en/manual/dql-doctrine-query-language.html

Community
  • 1
  • 1
Brian
  • 8,418
  • 2
  • 25
  • 32
  • Thanks Brian for the help. I read PDO is a abstraction layer, hence thought it will have provision for such kind of a query. – Mandar Apr 10 '15 at 11:59
  • PDO is definatley the way to go with PHP and database interaction, but a range query in SQL Server is as you know a bit of a hack at best, so some other layer like doctrine or you're own ORM/query building tool will be needed on top and PDO will do the actuall execution of generated queries :) – Brian Apr 10 '15 at 13:55