CODEWITHSUNDEEP
phpmysqlyii
Tirta John
Tirta John

Reputation: 27

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

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?

Upvotes: 0

Views: 2135

Answers (3)

MrSoundless
MrSoundless

Reputation: 1384

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

Upvotes: 3

Anri
Anri

Reputation: 1693

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

Upvotes: 0

Zander Rootman
Zander Rootman

Reputation: 2208

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}' ...

Upvotes: 0

Related Questions