Welcome Guest, Not a member yet? Register   Sign In
Active Record LIKE on join query
#1

Is it possible to use LIKE on a JOIN query with Active Record?
In this application $temp is a free text search string. I wish to count the number of records containing the search text in any of the fields of the join query.

Code:
$temp = $this->input->post('text', TRUE);
if($temp > ''){
   $this->db->like('tbl_users.usr_Lastname',  $temp, 'both');
   $this->db->or_like('tbl_groups.grp_Area', $temp, 'both');
   $this->db->or_like('tbl_systems.sys_Location', $temp, 'both');
   // etc., etc.,
}
// Record count
$jTableResult['TotalRecordCount'] = $this->db->count_all_results('tbl_systems');   // <-- What goes here?????
$this->db->select('
tbl_users.usr_Lastname,
tbl_groups.grp_Area,
tbl_systems.sys_Location,
               etc.,
', FALSE)
->from('tbl_users')
->join('tbl_groupmembers', 'tbl_groupmembers.gm_UserID = tbl_users.usr_UserID', 'inner')
->join('tbl_groups', 'tbl_groups.grp_ID = tbl_groupmembers.gm_GroupID', 'inner')
->join('tbl_rights', 'tbl_rights.r_GroupID = tbl_groups.grp_ID', 'inner')
->join('tbl_systems', 'tbl_systems.sys_ID = tbl_rights.r_SystemID', 'inner')
;

Many thanks.
Reply
#2

I may be a little confused, but you should be able to perform your query, then count the number of rows returned in that query using num_rows():

PHP Code:
$temp $this->input->post('text'TRUE);

if (
$temp !== '') {
   $this->db->like('tbl_users.usr_Lastname',  $temp'both');
   $this->db->or_like('tbl_groups.grp_Area'$temp'both');
   $this->db->or_like('tbl_systems.sys_Location'$temp'both');
   // etc., etc.,
}
$this->db->select(
    array(
        'tbl_users.usr_Lastname',
        'tbl_groups.grp_Area',
        'tbl_systems.sys_Location',
        // etc.,
    ), 
    FALSE
)
         ->join('tbl_groupmembers''tbl_groupmembers.gm_UserID = tbl_users.usr_UserID''inner')
         ->join('tbl_groups''tbl_groups.grp_ID = tbl_groupmembers.gm_GroupID''inner')
         ->join('tbl_rights''tbl_rights.r_GroupID = tbl_groups.grp_ID''inner')
         ->join('tbl_systems''tbl_systems.sys_ID = tbl_rights.r_SystemID''inner');

$query $this->db->get('tbl_users');

// Record count
$jTableResult['TotalRecordCount'] = $query->num_rows(); 
Reply
#3

Thanks for that, mwhitney. I figured it out after I'd posed the question. The trick, as you have done in your sample code is to pass an empty string to the count_all_results function. I couldn't respond immediately as I needed the nod from the moderator before I could edit again.

To anyone else: set up your like and or_like statements. Set up your from and join statements. Use count_all_results with no parameters.

Code:
$temp = $this->input->post('text', TRUE);
if($temp > ''){
$this->db->like('tbl_users.usr_Lastname',  $temp, 'both');
$this->db->or_like('tbl_groups.grp_Area', $temp, 'both');
$this->db->or_like('tbl_systems.sys_Location', $temp, 'both');
etc., etc.,
}

// Record count
$this->db->from('tbl_users')
->join('tbl_groupmembers', 'tbl_groupmembers.gm_UserID = tbl_users.usr_UserID', 'inner')
->join('tbl_groups', 'tbl_groups.grp_ID = tbl_groupmembers.gm_GroupID', 'inner')
->join('tbl_rights', 'tbl_rights.r_GroupID = tbl_groups.grp_ID', 'inner')
->join('tbl_systems', 'tbl_systems.sys_ID = tbl_rights.r_SystemID', 'inner');
$jTableResult['TotalRecordCount'] = $this->db->count_all_results(''); // <---- Pass empty string to function.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB