Both Mysql and Mssql compatibility in Yii

Hi Everyone,

I have been searching in this forum and google, but couldn’t find the answer. If you know it, please reply.

There is a simple MySQL query:

SELECT table_id FROM my_table WHERE (my_column='my_value') 

ORDER BY my_something DESC LIMIT 0, 20

In MS-SQL, it is something like this:

SELECT TOP 20 table_id FROM my_table WHERE (my_column='my_value') 

ORDER BY my_something DESC

The question: Is there a way to write a query that will work under both MSSQL (i am using PHPPDO driver) and MYSQL? I want to loop throught the selected rows.

Thank you,


I am not completely sure because I never used it, but the Query Builder should be able to do that. I think it should look something like:

$query = Yii::app()->db->createCommand()



    ->where('mycolumn=:myvalue', array(':myvalue'=>$var))

    ->order('my_something desc')



However if you have to work with MySQL and MSSQL connection at the same time - I am not sure if you can change the database layer at run time. You may check the API if you need this.

Not really surprising, because if you use Yii and it’s functionality, you wouldn’t be having that problem as it’s abstracted away. :)

Did you try it?

As Tropi pointed out, you can use the query builder, but why not start with using regular Models?