- —————————————————–
查询数据
方法1：
$query = $this->db->get('mytable');
// Produces: SELECT * FROM mytable
第二和第三个参数允许你设置一个结果集每页纪录数(limit)和结果集的偏移(offset)
$query = $this->db->get('mytable', 10, 20);
// Produces: SELECT * FROM mytable LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax)
foreach ($query->result() as $row)
{
echo $row->title;
}
{
echo $row->title;
}
<span style="font-family: Courier New;">方法2</span>
$query = $this->db->get_where(‘mytable’, array(‘id’ => $id), $limit, $offset);
一个来自国外论坛的模糊查询+联合查询的例子
http://codeigniter.com/forums/viewthread/123755/#615985
1. Ask:I’m trying to create a search-function that searches through 3 tables at once. I’m not sure I’m approaching this in the right way:
$this->db->like('name', $search); // users table
$this->db->like('company', $search); // companies table
$this->db->like('company_adress', $search); // companies table
$this->db->like('interests', $search); // interests table
$this->db->from('users');
$this->db->join('companies', 'companies.user_id = users.id');
$this->db->join('interests', 'interests.user_id = users.id');
$q=$this->db->get();
$this->db->like('company', $search); // companies table
$this->db->like('company_adress', $search); // companies table
$this->db->like('interests', $search); // interests table
$this->db->from('users');
$this->db->join('companies', 'companies.user_id = users.id');
$this->db->join('interests', 'interests.user_id = users.id');
$q=$this->db->get();
One user can have multiple companies and multiple interests.
2. David: would try something like this:
$this->db->like('name', $search); // users table
$this->db->or_like('company', $search); // companies table
$this->db->or_like('company_adress', $search); // companies table
$this->db->or_like('interests', $search); // interests table
$this->db->from('users u');
$this->db->join('companies c', 'c.user_id = u.id', 'left');
$this->db->join('interests i', 'i.user_id = u.id', 'left');
$this->db->group_by('u.id'); // added a group_by
$q=$this->db->get();
$this->db->or_like('company', $search); // companies table
$this->db->or_like('company_adress', $search); // companies table
$this->db->or_like('interests', $search); // interests table
$this->db->from('users u');
$this->db->join('companies c', 'c.user_id = u.id', 'left');
$this->db->join('interests i', 'i.user_id = u.id', 'left');
$this->db->group_by('u.id'); // added a group_by
$q=$this->db->get();
the third parameter ‘left’ in the join argument makes sure that you can search for a user with no interests or companies.
3.Wow David, this works like a charm! Thanks so much!
As I understand it now, the “left” statement makes sure the table “users” is the main table in this query, no matter what’s in the other tables.