php - UpdateAll 在 CakePHP 中不起作用

Question

我编写了一个查询来编辑与 ID 相关的特定 ROW。我正在使用 UPDATEALL() 函数来更新我的记录。

这是 CakePHP 中使用的通用语法，我也在下面使用相同的语法

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

我的代码看起来像这样。

$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'] 从 URL 获取当我运行编辑功能时，它会抛出错误。

此外，当回显 $_GET['user_id'] 和 $_GET['inv_id'] 时，结果不为空。所以我在这里发送任何空数据。

我对传递的值得到了完美的响应，如下所示:

$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>";

为了确保这是我在打印上述查询的数组时得到的结果

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] => d-higgins@mac.com
                    [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/
                )

        )

)

这是我的错误屏幕错误:SQLSTATE[42000]:语法错误或访问冲突:1064 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解要使用的正确语法

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` = John-Appleseed@mac.com, `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)

我的语法有什么问题吗？

Answer 1

生成的 SQL 似乎有一些错误，例如它包含

`Invoicecustomer`.`user_id` = nitin

而 nitin 应该位于引号之间。如果 CakePHP 生成此 SQL(显然确实如此)，则数据库中的字段似乎未正确定义。例如，通常 user_id 是 INT 或 bigINT)，但您将其用作 CHAR，在这种情况下，您的数据库模型应该与之匹配。否则，CakePHP 可能会生成错误的查询，就像现在一样。

实际上，如果我是正确的，似乎您所有的表列都被定义为 INT，而不是您想要存储在其中的数据类型。