CDbCommand failed to execute the SQL statement: SQLSTATE[42000]:

Question

I'm trying to get the time range on my yii app.

This is my model :

public function timeRange($attribute, $params) {

        $criteria = new CDbCriteria;

        $criteria->addCondition(
                "WHERE ('jam_keluar' > '{$this->jam_masuk}' OR 'jam_masuk' < '{$this->jam_keluar}')".
                "OR ('jam_masuk' < '{$this->jam_keluar}' AND 'jam_keluar' > '{$this->jam_keluar}')"
            );

        $record = self::model()->exists($criteria);
        if(!empty($record)) {
            $this->addError('jd_id', 'Item already exists within range.');
            return false;
        }

    }

But i'm getting the following error :

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 'WHERE ('jam_keluar' > 03:00:00' OR 'jam_masuk' < '12:00:00')OR ('jam_masuk' < '1' at line 1. The SQL statement executed was: SELECT 1 FROM `a_jadwal` `t` WHERE WHERE ('jam_keluar' > 03:00:00' OR 'jam_masuk' < '12:00:00')OR ('jam_masuk' < '12:00:00' AND 'jam_keluar' > '12:00:00') LIMIT 1

Anyone?

MrSoundless · Accepted Answer · 2014-05-14 13:06:08Z

You need to remove 'WHERE' from within your addCondition because addCondition will handle that for you. Also escape all your values with quotes but do not put quotes around your column names.

You should use CDbCriteria::compare or at least use addCondition with params for this to avoid SQL injection:

// Use the solution with CDbCriteria::addCondition
$criteria->addCondition(
    "WHERE (jam_keluar > :jam_masuk OR jam_masuk < :jam_keluar)".
    "OR (jam_masuk < :jam_keluar AND jam_keluar > :jam_keluar)",
    array(
        ':jam_masuk' => $this->jam_masuk,
        ':jam_keluar' => $this->jam_keluar
    )
);

// Or use the solution with CDbCriteria::compare
$criteria->compare('jam_keluar', '>'.$this->jam_masuk, false);
$criteria->compare('jam_masuk', '<'.$this->jam_keluar, false, 'OR');
$criteria->compare('jam_masuk', '<'.$this->jam_keluar, false, 'OR');
$criteria->compare('jam_keluar', '>'.$this->jam_keluar, false);

Zander Rootman · Accepted Answer · 2014-05-14 12:40:08Z

0

There's a single quote missing.

$criteria->addCondition(
            "WHERE ('jam_keluar' > '{$this->jam_masuk}' OR 'jam_masuk' < '{$this->jam_keluar}')".
            "OR ('jam_masuk' < '{$this->jam_keluar}' AND 'jam_keluar' > '{$this->jam_keluar}')"
        );

Here:

"WHERE ('jam_keluar' > {$this->jam_masuk}' ...

answered May 14, 2014 at 12:40

Zander Rootman

2,2082 gold badges19 silver badges29 bronze badges

Comments

Anri · Accepted Answer · 2014-05-14 12:40:26Z

0

public function timeRange($attribute, $params) {

$criteria = new CDbCriteria;

$criteria->addCondition(
        "WHERE ('jam_keluar' > '{$this->jam_masuk}' OR 'jam_masuk' < '{$this->jam_keluar}')".
        "OR ('jam_masuk' < '{$this->jam_keluar}' AND 'jam_keluar' > '{$this->jam_keluar}')"
    );

$record = self::model()->exists($criteria);
if(!empty($record)) {
    $this->addError('jd_id', 'Item already exists within range.');
    return false;
}
else {
    return true;
}

}

you have missed ' inside query

answered May 14, 2014 at 12:40

Anri

1,7032 gold badges18 silver badges36 bronze badges

Collectives™ on Stack Overflow

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]:

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related