CODEWITHSUNDEEP
phpmysqlcodeignitersql-likequery-builder
Gia Nebieridze
Gia Nebieridze

Reputation: 161

CodeIgniter LIKE query doesn't return result when input contains a space

I want search in mysql by name.

I have this code in search model

$this->db->limit($limit, $start);
$this->db->like("name",$by);    
$res = $this->db->get('walls');
if ($res->num_rows() > 0)
    return $res->result();

The result for $by = "megan"; is working, but when $by = "megan fox"; is not working.

I have a row in my table with a name column value of "megan fox";

I tried urldecode() but to no avail.

Upvotes: 1

Views: 1423

Answers (3)

Azam Alvi
Azam Alvi

Reputation: 7055

Use urldecode function to solve this Try this into your model

    $by= urldecode($by);

    $this->db->limit($limit, $start);
    $this->db->like("name",$by);    
    $res = $this->db->get('walls');
        if ($res->num_rows() > 0)
            return $res->result();

Upvotes: -1

Vassilis Barzokas
Vassilis Barzokas

Reputation: 3242

You can first split the $by variable into separate values with something like this:

$by = explode(" ", $by);

and then try to make the like clause like this

$this->db->like("name",$by[0]);
$this->db->or_like("name",$by[1]);

It will produce something like this

 WHERE name LIKE '%megan%' OR name LIKE '%fox%'

This assumes that you always pass 2 variables to the $by, seperated by space. You have to adjust it to make sure it works in every case, for example when there is only one variable passed to the $by you should do a check for it.

And please note that it will be fairly slower than splitting the name and the surname into two separate fields in the table and querying each of them for a specific name or surname. You should do this if you care for optimization.

Upvotes: 1

tomexsans
tomexsans

Reputation: 4527

 $this->db->limit($limit, $start);
        $this->db->like("name",$by);    
        $res = $this->db->get('walls');
            if ($res->num_rows() > 0)
                return $res->result();

You might want to add BOTH,LEFT,RIGHT on your like() clause example

$this->db->like("name",$by,'BOTH'); // left and right wild card %name%
$this->db->like("name",$by,'LEFT'); // left  wild card %name
$this->db->like("name",$by,'RIGHT'); // right wild card name%

You can read more at http://ellislab.com/codeigniter/user-guide/database/active_record.html

Upvotes: 3

Related Questions