CODEWITHSUNDEEP
phpmysqlcakephp
DANLEE
DANLEE

Reputation: 453

cakephp SQL HELP! where?

I am trying to perform an SQL update in CakePHP. Here is my code:

$sql = "
  UPDATE carts 
  SET
    qty = ".$this->data['Cart']['qty'].",
    process = 'UnPaid'
  WHERE ct_session_id = '".$this->data['Cart']['ct_session_id']."'
    AND product_id = '".$this->passedArgs['pd_id']."'
    AND key = '".$this->Session->read('Cart.key', $newCartkey)."'
";      
$this->Cart->query($sql);

I get this error:

SQL Error: 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 'key = 'bwfgkxms'' at line 3

The query my code produces is:

UPDATE carts
SET
  qty = 111,
  process = 'UnPaid'
WHERE ct_session_id = '3254430f577669bb8ecdb8b8aadf1b96'
  AND product_id = '51'
  AND key = 'bwfgkxms'

Upvotes: 1

Views: 150

Answers (2)

Dave
Dave

Reputation: 29131

It would greatly benefit you to get in the habit of using the CakePHP conventions. There are many benefits to doing so, and when you do things like hand-write a query that could easily be done with CakePHP, you're making it more difficult on yourself as well as opening yourself up for many issues / security concerns in the future.

The CakePHP way (with all the benefits included in following conventions):

$this->Cart->updateAll(
    array(
        'Cart.qty' => $this->data['Cart']['qty'],
        'Cart.process' => 'UnPaid'),
    array(
       'Cart.ct_session_id' => $this->data['Cart']['ct_session_id'],
       'Cart.product_id' => $this->passedArgs['pd_id'],
       'Cart.key' => $this->Session->read('Cart.key', $newCartkey)
    )
);

More details on updating your data (and/or saving your data in general): http://book.cakephp.org/2.0/en/models/saving-your-data.html#model-updateall-array-fields-array-conditions

Upvotes: 0

nickb
nickb

Reputation: 59699

key is a reserved word in MySQL, you need to surround it with backticks in the column name.

$sql = "
 UPDATE carts 
 SET qty = ".$this->data['Cart']['qty'].", process = 'UnPaid'
 WHERE ct_session_id = '".$this->data['Cart']['ct_session_id']."'
   AND product_id = '".$this->passedArgs['pd_id']."'
   AND `key` = '".$this->Session->read('Cart.key', $newCartkey)."'
";

Upvotes: 8

Related Questions