Update query in cakephp with more than one condition

Question

I have a query like:

Update `pools` set status='2' where `pid`='1' and `uid`='2'

How do I convert this query in cakephp? i.e. I want to pass that AND condition in query which should update row containing pid='1' and uid='2'.

Solution

UPDATEALL is all you need.

$this->Pool->updateAll(array('status'=>2), array('Pool.pid'=>1,'Pool.uid' => 2));

For more documentation.

http://book.cakephp.org/2.0/en/models/saving-your-data.html#model-updateall-array-fields-array-conditions

If you already have id for the row which you want to update then no need to use update cakephp is very much clever enough to understand it.

For example.

if you have Pool.id which is row of record then.

$this->data['Pool']['id'] = $pool_id;
$this->data['Pool']['uid'] = $pool_uid;
$this->data['Pool']['other_data'] = $pool_otherData;
$this->Pool->save($this->data['Pool']);

above will automatically save $pool_id row.

Comments

[rockfeather]

I did try and it worked like a charm! but, if I do modify it a little like this:$this->Poo->updateAll(array('receipt_no'=>'abc123'), array('Pool.id'=>9,'Pool.uid' => 23));It takes 'abc123' as a column name and gives error as unknown column

[Dipesh Parmar]

you must need to use Model.field_name like array('Pool.receipt_no'=>'abc123')...because cakephp query create table alias automatically as per model name... try this i m sure this is it.

[rockfeather]

Yest I did use ('Pool.receipt_no'=>'abc123'), but it takes abc123 as field name

[Dipesh Parmar]

may i see database table structure...its not possible to take value parameter as column must be something wrong..

[rockfeather]

CREATE TABLE IF NOT EXISTS pools ( id bigint(11) NOT NULL AUTO_INCREMENT, pool_id bigint(11) NOT NULL, uid varchar(20) COLLATE utf8_unicode_ci NOT NULL, receipt_no varchar(255) COLLATE utf8_unicode_ci NOT NULL,

[rockfeather]

No this is also not working same error it takes 'abc123' as field name

[Php Geek]

whats ur cakephp version ??

[rockfeather]

Ok Finally this worked:$this->Poolcontributor->updateAll(array('receipt_no'=‌​>"'abc123'"), array('Poolcontributor.pool_id'=>9,'Poolcontributor.user_id' => 23)); What I did is i converted 'abc123' in to "'abc123'". really a weird behaviour of cakephp.Thanks for the help!

[rockfeather]

Ok Finally this worked:$this->Poolcontributor->updateAll(array('receipt_no'=‌​>"'abc123'"), array('Poolcontributor.pool_id'=>9,'Poolcontributor.user_id' => 23)); What I did is i converted 'abc123' in to "'abc123'". really a weird behaviour of cakephp