return the result of a query and the total number of rows in a single function
- by csotelo
This is a question as might be focused on working in the best way, if there are other alternatives or is the only way:
Using Codeigniter ... I have the typical 2 functions of list records and show total number of records (using the page as an alternative). The problem is that they are rather large. Sample 2 functions in my model:
count Rows:
function get_all_count()
{
$this->db->select('u.id_user');
$this->db->from('user u');
if($this->session->userdata('detail') != '1')
{
$this->db->join('management m', 'm.id_user = u.id_user', 'inner');
$this->db->where('id_detail', $this->session->userdata('detail'));
if($this->session->userdata('management') === '1')
{
$this->db->or_where('detail', 1);
}
else
{
$this->db->where("id_profile IN (
SELECT
e2.id_profile
FROM profile e, profile e2, profile_path p, profile_path p2
WHERE e.id_profile = " . $this->session->userdata('profile') . "
AND p2.id_profile = e.id_profile
AND p.path LIKE(CONCAT(p2.path,'%'))
AND e2.id_profile = p.id_profile
)", NULL, FALSE);
$this->db->where('MD5(u.id_user) <>', $this->session->userdata('id_user'));
}
}
$this->db->where('u.id_user <>', 1);
$this->db->where('flag <>', 3);
$query = $this->db->get();
return $query->num_rows();
}
results per page
function get_all($limit, $offset, $sort = '')
{
$this->db->select('u.id_user, user, email, flag');
$this->db->from('user u');
if($this->session->userdata('detail') != '1')
{
$this->db->join('management m', 'm.id_user = u.id_user', 'inner');
$this->db->where('id_detail', $this->session->userdata('detail'));
if($this->session->userdata('management') === '1')
{
$this->db->or_where('detail', 1);
}
else
{
$this->db->where("id_profile IN (
SELECT
e2.id_profile
FROM profile e, profile e2, profile_path p, profile_path p2
WHERE e.id_profile = " . $this->session->userdata('profile') . "
AND p2.id_profile = e.id_profile
AND p.path LIKE(CONCAT(p2.path,'%'))
AND e2.id_profile = p.id_profile
)", NULL, FALSE);
$this->db->where('MD5(u.id_user) <>', $this->session->userdata('id_user'));
}
}
$this->db->where('u.id_user <>', 1);
$this->db->where('flag <>', 3);
if($sort) $this->db->order_by($sort);
$this->db->limit($limit, $offset);
$query = $this->db->get();
return $query->result();
}
You see, I repeat the most of the functions, the difference is that only the number of fields and management pages.
I wonder if there is any alternative to get as much results as the query in a single function. I have seen many tutorials, and all create 2 functions: one to count and another to show results ... Will there be more optimal?
