(Yii)CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation:

Question

I'm trying to execute this sql query but an exception occurs...I tried the query in PhpMyAdmin and it worked ok.

<?php
        $criteria=new CDbCriteria;
        $criteria->condition='select min(purchase_order_id) from purchase_order where purchase_order_id >'.$model->purchase_order_id;
        $nextfield = \POrder::model()->model()->findAll($criteria);
           ?>

The exception is, 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 'select min(purchase_order_id) from purchase_order where purchase_order_id >65' at line 1. The SQL statement executed was: SELECT * FROM purchase_order t WHERE select min(purchase_order_id) from purchase_order where purchase_order_id >65

It was a wrong post sorry...But still I think it's not the where clauses. — Pedi
– Pedi, Commented Oct 15, 2014 at 6:01
I believe the idea is that $criteria->condition is used to build the where clause, not specify the entire query. — StuartLC
– StuartLC, Commented Oct 15, 2014 at 6:03

Willem Renzema · Accepted Answer · 2014-10-15 06:19:48Z

If you just want the result of the query, do the following:

Yii::app()->db->createCommand("
SELECT MIN(purchase_order_id) FROM purchase_order
WHERE purchase_order_id > :purchase_order_id
")->queryScalar(array('purchase_order_id'=>$model->purchase_order_id'));

That will return exactly the single value that you would get from phpmyadmin or mysql workbench.

If you want the model record which corresponds to that purchase_order_id, then you can do the following:

$criteria=new CDbCriteria;
$criteria->addCondition("t.purchase_order_id = (
SELECT MIN(purchase_order_id)
FROM purchase_order
WHERE purchase_order_id > :previous_purchase_order_id)");
$criteria->params['previous_purchase_order_id'] = $model->purchase_order_id;
$nextfield = \POrder::model()->model()->find($criteria);

I'm assuming that there is only one such model, so I changed the findAll to find.

Collectives™ on Stack Overflow

(Yii)CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation:

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related