具有活动记录和or_like的CodeIgniter [英] CodeIgniter with Active Records and or_like
问题描述
我有一个管理面板，其中包含所有从名为 users的数据库表中获得的所有用户信息的视图。
I have an admin panel with a view for all the users info gotten from a Database table named "users".
有一个搜索框，我使用了活动记录来获取数据。但这似乎有问题，我无法弄清楚。我很确定它在 or_like 部分中，但是错误使我感到困惑。
There's a search box and I use Active records to fetch the data. But there seems to be a problem with that and I can't figure it out. I'm pretty sure it's in the or_like part but the error confuses me.
这是我进行的查询传递一个变量 $ data 从搜索框中发布信息：
Here is the query I make passing one variable $data posting the info from the search box :
public function get($data) {
$this->db->select('id, email, firstname, lastname, usertype, ts_created, ts_last_login, position');
if (isset($data['query']) && $data['query'] != '') {
$fields = json_decode($data['fields'], true);
$this->db->like($fields[0], $data['query']);
foreach ($fields as $field) {
$this->db->or_like($field, $data['query']);
}
}
$this->db->limit($data['limit'], $data['start']);
if (isset($data['sort'])) {
$sort = json_decode($data['sort'], true);
$this->db->order_by($sort[0]['property'], $sort[0]['direction']);
}
$query = $this->db->get('users');
由于它是对现有程序的某种重构，因此我不得不添加一个新功能-'groups'这不是用户表的一部分，所以我添加了键['groups'] =>，并使用值循环了查询结果：
Because it's somewhat a refactoring of an existing program, I had to add a new feature - 'groups' which is not part of the users table so I add the key['groups']=>with values looping the query result :
$users = array();
foreach ($query->result_array() as $user) {
$users[] = array_merge($user, array('groups' =>''));
}
$count = sizeof($users);
//Adding groups id's as values to the group key
foreach ($this->getGroupsAndUsers() as $group) {
for ($i = 0; $i < $count; $i++) {
if ($users[$i]['id'] == $group['user_id']) {
//Begin check
if ($users[$i]['groups'] == '') {
$users[$i]['groups'] .= $group['group_id'];
} else {
$users[$i]['groups'] .= "," . $group['group_id'];
}
//End of check
}
}
}
$result = $users;
return $result;
并将最终数组传递给$ result变量，这应该是全部，但是当我尝试搜索某个用户，我会收到此错误：
And with passing the final array to the $result variable this should be all, but when I try to search a certain user I get this error:
<p>Error Number: 1054</p><p>Unknown column 'groups' in 'where clause'</p><p>SELECT `id`, `email`, `firstname`, `lastname`, `usertype`, `ts_created`, `ts_last_login`, `position`
FROM (`users`)
WHERE `firstname` LIKE '%stefan%'
OR `firstname` LIKE '%stefan%'
OR `lastname` LIKE '%stefan%'
OR `email` LIKE '%stefan%'
OR `position` LIKE '%stefan%'
OR `groups` LIKE '%stefan%'
它说 groups是未知列，并且从select中丢失了，但是我不确定这是真正的问题，因为我实际上看到了这些组，并在管理面板中有一个用户界面。另外，作为一个MySQL菜鸟，我想得到这样的查询有点奇怪：
it says that groups is unknown column and it's missing from the select but I'm not sure it's the real problem cause I actually see the groups and have a UI in the admin panel for it. Also, being a MySQL noob, I think it's kind of strange to get a query like this:
WHERE `firstname` LIKE '%stefan%'
OR `firstname` LIKE '%stefan%'
OR `lastname` LIKE '%stefan%'
OR `email` LIKE '%stefan%'
OR `position` LIKE '%stefan%'
OR `groups` LIKE '%stefan%'
我的意思是，这是 or_like 的正常行为以获得相同的值吗？
I mean, is this the normal behaviour of or_like to get the same value?
最后，因为我真的迷失了方向，如果有人可以帮助或建议可能导致此问题的原因，我们将不胜感激。
And at the end, because I'm really lost here, if anyone can help or suggest what could cause this problem would be really appreciated.
推荐答案
我在ellislab论坛 https://ellislab.com/forums/上找到了针对类似代码生成器和or_like问题的答案viewthread / 185983 /
而不是使用
I found the answer for codeigniter like and or_like problem at ellislab forum https://ellislab.com/forums/viewthread/185983/ Instead of using
$this->db->like('location', $your_string);
使用：
$this->db->where('location LIKE', '%'.$your_string.'%');
和 or_where ，而不是 or_like 。
这篇关于具有活动记录和or_like的CodeIgniter的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
