Yii1 equivalent of the Sql query

Question

I have this sql query

SELECT * FROM form_fields 
WHERE id NOT IN ("3", "1", "6") 
ORDER BY FIELD (id, "3" ,"1" ,"6")

which i want to convert to an yii1 query. So i tried like this

$SQL="SELECT * FROM form_fields WHERE id NOT IN {$sorted_array}  ORDER BY FIELD (id, $sorted_array)";
$connection=Yii::app()->db; 
$command=$connection->createCommand($SQL);
$rowCount=$command->execute(); 
$dataReader=$command->query();

where $sorted_array has value Array ( [0] => 3 [1] => 1 [2] => 6 )

It gave me an error

PHP notice

Array to string conversion

then i converted the $sorted_array to string like this

$string = implode(' ', $sorted_array);

and again executed the query

$SQL="SELECT * FROM form_fields WHERE id NOT IN {$string}  ORDER BY FIELD (id, $string)";
$connection=Yii::app()->db; 
$command=$connection->createCommand($SQL);
$rowCount=$command->execute(); 
$dataReader=$command->query();

Now i get another error

CDbException

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3 1 6 ORDER BY FIELD (id, 3 1 6)' at line 1. The SQL statement executed was: SELECT * FROM form_fields WHERE id NOT IN 3 1 6 ORDER BY FIELD (id, 3 1 6)

Instead of this

SELECT * FROM form_fields 
    WHERE id NOT IN ("3", "1", "6") 
    ORDER BY FIELD (id, "3" ,"1" ,"6")

my yii1 query produced this erroneous sql query

SELECT * FROM form_fields
WHERE id NOT IN 3 1 6 ORDER BY FIELD (id, 3 1 6)

Any help would be appreciated

Refer: http://stackoverflow.com/questions/20875758/how-to-put-sql-query-in-an-array-in-yii — Insane Skull, Jan 19 '16 at 05:03

score 1 · Accepted Answer · answered Jan 19 '16 at 08:41

1

The first parameter of implode should be a comma:

$string = implode(',', $sorted_array);

answered Jan 19 '16 at 08:41

topher

14,790
7
54
70

hi, it gave me CDbException CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3,1,6 ORDER BY FIELD (id, 3,1,6)' at line 1. The SQL statement executed was: SELECT * FROM form_fields WHERE id NOT IN 3,1,6 ORDER BY FIELD (id, 3,1,6) – Bloodhound Jan 20 '16 at 03:41
You forgot the brackets around the `IN` values. – topher Jan 20 '16 at 04:34

Alexander · Answer 2 · 2016-01-19T20:35:06.753

The better way is use QueryBuilder. It looks like

$result = Yii::app()->db->createCommand()
              ->select('field1, field2')
              ->from('table t')
              ->join('table2 t2', 't2.t_id = t.id')
              ->where('t.property = :property', [':property' => 1])
              ->queryRow();

It's more useful, because QueryBuilder use PDO-statements for parameters, and makes correct queries in SQL dialect which depends of your DBMS type.

It also supports array statements, like this:

->where(['in', 'field', [1,2,3]])

The result is:

... WHERE field IN (1, 2, 3) ...

And yes, it's still DAO, without ActiveRecord :)

Yii1 equivalent of the Sql query

2 Answers2