UpdateAll not working in CakePHP

I have a query written to Edit a particular ROW with respect to ID. I'm using a UPDATEALL() function to update my record.

this is the general syntax used in CakePHP, also I'm using this same syntax below

$this->Ticket->updateAll(
    array('Ticket.status' => "'closed'"),
    array('Ticket.customer_id' => 453)
);

My code however looks like this.

$this->Invoicecustomers->updateAll(
array(
'user_id'=> $_GET['user_id'],
'inv_id' => $_GET['inv_id']
),array(
'Invoicecustomers.user_id'=> $_GET['user_id'],
'Invoicecustomers.inv_id' => $_GET['inv_id'])
);

$_GET['user_id'] & $_GET['inv_id'] are fetched from the URL It throws me an error when I run the edit function.

Also when Echoed $_GET['user_id'] & $_GET['inv_id'] , results are not empty. SO I'm sending any null data here.

I get a perfect response for the values passed like this:

$this->loadModel('Invoicecustomer');
        $cust = $this->Invoicecustomer->find('all',array(
        'conditions' => array('Invoicecustomer.inv_id' => $_GET['inv_id'])
        ));
        /*$log = $this->Invoicecustomer->getDataSource()->getLog(false, false);
        debug($log);*/
        echo "we are getting User_id as <strong>".$cust[0]['Invoicecustomer']['user_id'] . "</strong> and inv_id as <strong>". $cust[0]['Invoicecustomer']['inv_id']."</strong>";

To make sure this is what I get when I print array for the above query

Array
(
    [0] => Array
        (
            [Invoicecustomer] => Array
                (
                    [id] => 34
                    [user_id] => abc
                    [group_id] => 346027119
                    [address1] => Corte Madera
                    [address2] => CA
                    [address3] => 94925
                    [comment] => invoice default comment
                    [cust_name] => Daniel Higgins
                    [date1] => 2013-08-06 19:41:11
                    [email] => [email protected]
                    [inv_id] => 1030
                    [tax_include] => NO
                    [tax_rate] => 5.00
                    [datastatus] => Admin
                    [mobile] => 555-478-7672
                    [sentstatus] => Not Sent
                    [status] => Unpaid
                    [street] => 332 Laguna Street
                    [paidamount] => 0.00
                    [balanceamount] => 0.00
                    [voidstatus] => unpaid
                    [signature] => nitin236090907.jpg
                    [imagepath] => http://192.168.1.100/cakephp_new/app/webroot/img/Invoicecustomer/346027119/abc/
                )

        )

)

It is my ERROR SCREEN Error: 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

SQL Query: 
        UPDATE `cakephpdb`.`invoicecustomers` AS `Invoicecustomer`  SET `Invoicecustomer`.`user_id` = nitin, `Invoicecustomer`.`group_id` = 346027119, `Invoicecustomer`.`address1` = Atlanta, `Invoicecustomer`.`address2` = GA, `Invoicecustomer`.`address3` = 30303, `Invoicecustomer`.`comment` = invoice default comment, `Invoicecustomer`.`cust_name` = nitin ksagar13, `Invoicecustomer`.`email` = [email protected], `Invoicecustomer`.`inv_id` = 1030, `Invoicecustomer`.`tax_include` = NO, `Invoicecustomer`.`tax_rate` = 5.00, `Invoicecustomer`.`datastatus` = Admin, `Invoicecustomer`.`mobile` = 888-555-5512, `Invoicecustomer`.`sentstatus` = Not Sent, `Invoicecustomer`.`status` = Unpaid, `Invoicecustomer`.`street` = 3494 Kuhl Avenue, `Invoicecustomer`.`paidamount` = 0.00, `Invoicecustomer`.`balanceamount` = 0.00, `Invoicecustomer`.`voidstatus` = unpaid, `Invoicecustomer`.`signature` = nitin885659375.jpg, `Invoicecustomer`.`imagepath` = http://192.168.1.100/cakephp_new/app/webroot/img/Invoicecustomer/346027119/nitin/  WHERE `Invoicecustomer`.`user_id` = 'nitin' AND `Invoicecustomer`.`group_id` = '346027119' AND `Invoicecustomer`.`inv_id` = '1030'  

Notice: 
    If you want to customize this error message, create app/View/Errors/pdo_error.ctp

Stack Trace

    CORE/Cake/Model/Datasource/DboSource.php line 460 → PDOStatement->execute(array)
    CORE/Cake/Model/Datasource/DboSource.php line 426 → DboSource->_execute(string, array)
    CORE/Cake/Model/Datasource/Database/Mysql.php line 379 → DboSource->execute(string)
    CORE/Cake/Model/Model.php line 2372 → Mysql->update(AppModel, array, null, array)
    APP/Controller/InvoicecustomersController.php line 164 → Model->updateAll(array, array)
    [internal function] → InvoicecustomersController->editInvoiceCustomer()
    CORE/Cake/Controller/Controller.php line 486 → ReflectionMethod->invokeArgs(InvoicecustomersController, array)
    CORE/Cake/Routing/Dispatcher.php line 187 → Controller->invokeAction(CakeRequest)
    CORE/Cake/Routing/Dispatcher.php line 162 → Dispatcher->_invoke(InvoicecustomersController, CakeRequest, CakeResponse)
    APP/webroot/index.php line 109 → Dispatcher->dispatch(CakeRequest, CakeResponse)

Is my syntax wrong in any way.?

Upvotes: 0

Answers (2)

Indrajeet Singh

Reputation: 2989

Try...
$user_id = $_GET['user_id'];
$this->Invoicecustomers->updateAll(
array(
'user_id'=> "'$user_id'",
'inv_id' => $_GET['inv_id']
),array(
'Invoicecustomers.user_id'=> "'$user_id'",
'Invoicecustomers.inv_id' => $_GET['inv_id'])
);

Upvotes: 0

Maarten

Reputation: 21

The resulting SQL seems to have some errors, for example it contains

`Invoicecustomer`.`user_id` = nitin

While nitin should be between qoutes. If CakePHP generates this SQL (which it apparently does), it would seem that the fields in your database are not correctly defined. For example, normally user_id would be an INT or bigINT), but you use it as a CHAR, in which case your database model should match that. Otherwise CakePHP might generate the wrong query, as it does now.

Actually, if I'm correct, it seems all your table columns are defined as INT, instead of as the kind of data you want to store in them.

Upvotes: 1

UpdateAll not working in CakePHP

Answers (2)

Related Questions