Codeigniter联接表输出错误 [英] Codeigniter joined tables getting wrong output

问题描述

我有一个函数，可以从数据库中获取数据，并连接不同的表.这是方法:

I'm having an function which gets data from my database, and joins different tables. This is the method:

public function getCallcenterCall() {
    $this->db->select('bedrijf.*, status.status_naam, quickscan.datum_verzonden');
    $this->db->join('quickscan', 'bedrijf.id = quickscan.bedrijf_id');
    $this->db->join('status', 'bedrijf.status = status.status_id');
    $this->db->where('status', '0');

    $query = $this->db->get('bedrijf');

    return $query->num_rows() > 0 ? $query-> result_array() : FALSE;
}

在表状态下，我有3行:'id'，'status_id'，'status_naam'.在我看来，我输出了status_naam，但是在这里它出错了.

In the table status I got 3 rows: 'id', 'status_id', 'status_naam'. In my view, I output the status_naam, but here it gets wrong.

而不是给我属于'status_id=0'的'status_naam'；它给了我'status_naam'，其中'status_id=1'.

Instead of giving me the 'status_naam' that belongs to 'status_id=0'; it gives me the 'status_naam' where 'status_id=1'.

如果我尝试获取'status_id=1'的'status_naam'，也会发生同样的事情，那么它会给我'status_id=2'中的'status_naam'.

The same thing happens if I try to get the 'status_naam' for 'status_id=1' then it gives me the 'status_naam' from 'status_id=2'.

我做错了什么?预先感谢！

What am I doing wrong? Thanks in advance!

推荐答案

您的数据库设置是否使用外键约束?

Is your database setup to use foreign key constraints?

简单示例:

表格

create table `groups`(
`id` int(10) unsigned not null auto_increment primary key,
`name` varchar(30) not null,
`colour` varchar(7) not null,
`created_at` datetime not null,
`updated_at` datetime not null
 )engine=innodb;


create table `users`(
`id` int(10) unsigned not null auto_increment primary key,
`group_id` int(10) unsigned not null,
`permissions` varchar(255) not null default '{[u, r, d]}',
`created_at` datetime not null,
`updated_at` datetime not null,
index(group_id),
foreign key(group_id) references groups(id)
)engine=innodb;

原始SQL

select g.name, u.permissions from users as u
left join groups as g
on g.id = u.group_id
where id=1

如您所见，您在users表上设置了group_id的索引， 然后将其设置为我们的外键，并告诉它引用groups id.

As you can see you set an index of group_id on the users table, then set it as our foreign key and tell it to reference the groups id.

innodb引擎是必需的&这些行必须完全匹配.

innodb engine is required & the rows must match identically.

...希望有意义

这篇关于Codeigniter联接表输出错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！