Try to search with Full name using Codeigniter

Question

Here is my table like below.

Id  Firstname Lastname
1   Akhil     Mokariya
2   Harshit   joshi

When i search the result "Akhil Mokariya" it's give me null record but i need result like below.

1  Akhil Mokariya

My Query is like below.

$searcharray = array(
            'students.firstname' => $searchterm,
            'students.lastname' => $searchterm,
            'students.guardian_name' => $searchterm,
            'students.adhar_no' => $searchterm,
            'students.samagra_id' => $searchterm,
            'students.roll_no' => $searchterm,
            'students.admission_no' => $searchterm,
        );

        $this->db->select('classes.id AS `class_id`,students.id,classes.class,sections.id AS `section_id`,sections.section,students.id,students.admission_no , students.roll_no,students.admission_date,students.firstname,  students.lastname,students.image,    students.mobileno, students.email ,students.state ,   students.city , students.pincode ,     students.religion,     students.dob ,students.current_address,    students.permanent_address,IFNULL(students.category_id, 0) as `category_id`,IFNULL(categories.category, "") as `category`,      students.adhar_no,students.samagra_id,students.bank_account_no,students.bank_name, students.ifsc_code ,students.father_name , students.guardian_name , students.guardian_relation,students.guardian_phone,students.guardian_address,students.is_active ,students.created_at ,students.updated_at,students.gender,students.rte,student_session.session_id')->from('students');
        $this->db->join('student_session', 'student_session.student_id = students.id');
        $this->db->join('classes', 'student_session.class_id = classes.id');
        $this->db->join('sections', 'sections.id = student_session.section_id');
        $this->db->join('categories', 'students.category_id = categories.id', 'left');
        $this->db->where('student_session.session_id', $this->current_session);
        $this->db->group_start();
        $this->db->or_like($searcharray);
        $this->db->group_end();
        $this->db->order_by('students.id');
        // $this->db->limit('100');
        $query = $this->db->get();

Answer 1

I find in my own searches that I will check to see if the search is for a string that has a space, so I can split it and use it for first and last name:

if( strpos( $searchterm, ' ' ) !== FALSE )
    $search_parts = explode(' ', $searchterm);

Then, you may want to make sure that there are two parts:

if( count( $search_parts ) == 2 ){
    // you know you have two parts
}

So then you'd be able to use the parts in your array:

'students.firstname' => $search_parts[0],
'students.lastname' => $search_parts[1],

And if they may be out of order:

'students.firstname' => $search_parts[1],
'students.lastname' => $search_parts[0],

I am using a different way of creating a query, but to show you an example, I do this:

if( strpos( $search, ' ' ) !== FALSE )
{
    $search_parts = explode(' ', $search);

    $sql .= ' 
        OR (
            first_name LIKE "%' . $this->db->escape_like_str( current($search_parts) ) . '%" ESCAPE "!" AND  
            last_name LIKE "%' . $this->db->escape_like_str( end($search_parts) ). '%" ESCAPE "!"
        ) ';

    reset($search_parts);

    $sql .= ' 
        OR (
            last_name LIKE "%' . $this->db->escape_like_str( current($search_parts) ) . '%" ESCAPE "!" AND 
            first_name LIKE "%' . $this->db->escape_like_str( end($search_parts) ). '%" ESCAPE "!"
        ) 
    ';
}

Also, if you don't care about code portability, you should see this answer with CONCAT: Mysql Concat two columns while searching with LIKE

That ends up looking something like this:

// Check if search was input as full name
if( strpos( $search, ' ' ) !== FALSE )
{
    $sql .= ' 
        OR ( CONCAT( first_name, " ", last_name ) LIKE "%' . $this->db->escape_like_str( $search ) . '%" ESCAPE "!" )  
        OR ( CONCAT( last_name, " ", first_name ) LIKE "%' . $this->db->escape_like_str( $search ) . '%" ESCAPE "!" ) ';
}

So, in your case, I think you should be able to do this:

$searcharray = array(
    'students.firstname' => $searchterm,
    'students.lastname' => $searchterm,
    'students.guardian_name' => $searchterm,
    'students.adhar_no' => $searchterm,
    'students.samagra_id' => $searchterm,
    'students.roll_no' => $searchterm,
    'students.admission_no' => $searchterm,
    'CONCAT(students.firstname, " ", students.lastname)' => $searchterm,
    'CONCAT(students.lastname, " ", students.firstname)' => $searchterm
);