php - Codeigniter 更新函数中的两个不同的表名

Question

基本信息代码点火器:3，PHP 5

我目前正在玩一个更新查询的多表更新。我的SQL如下-

UPDATE users as a, u_basicinfos as b
SET 
    a.first_name = 'XYI',
    b.Address = 'USA',
    b.PortalURL = NULL
WHERE a.id = b.UserId AND b.InfosId = 1

以上查询绝对适用于 heidisql 或 phpmyadmin sql 控制台。

我正尝试在 codeigniter 中创建一个 active records 查询，如下所示-

$this->db->where('a.id = b.UserId');
$this->db->where('b.Infosid', $infoid);

$tblname = 'users AS a, u_basicinfos AS b';
// may be problem with this format of two tables name

$query = $this->db->update($tblname, $data);

注意:我在 Controller 中分配了 $data 数组:

$data = array (
    'a.first_name' => $this->input->post('first_name'),
    'b.Address' => $this->input->post('Address'),
    'b.NewportalURL' => $this->input->post('PortalURL')
);

现在，我正在努力解决以下错误。

Table 'finalportal.users as a, u_basicinfos' doesn't exist

UPDATE `users AS a, u_basicinfos` AS `b` SET `a`.`first_name` = 'XYI',
`b`.`Address` = 'USA', `b`.`PortalURL` = NULL

我需要一些想法。让我知道我做错了什么？谢谢

Answer 1

尝试在查询中使用它

$this->db->where('b.Infosid', $infoid);
$tblname = 'users a JOIN u_basicinfos b on a.id = b.UserId';
$query = $this->db->update($tblname, $data);