Both Mysql and Mssql compatibility in Yii

Yii 1.1 General Discussion
1

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,

Canadian

2

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()

    ->select('tableid')

    ->from('my_table')

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

    ->order('my_something desc')

    ->limit(20)

    ->queryRow();

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.

3

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?