Search code examples
mysqlcodeignitersql-likequery-builder

Codeigniter 3 use of db->like for multiple columns results in an SQL related error

I am working on a basic blog application in Codeigniter 3.1.8.

The search posts functionality gives me unexpected trouble: 

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '* LIKE '%expression%' ESCAPE '!'' at line 3

SELECT * FROM `posts` WHERE * LIKE '%expression%' ESCAPE '!'

The search() method in the Posts model looks like this:

public function search($expression) {
    $this->db->like('*', $expression);
    $query = $this->db->get('posts');
    return $query->result();
}

The relevant table fields are:

enter image description here

Where is my mistake?

Solution

  • In the new CodeIgniter release (3.1.9), there was a fix for an unwanted typo ... Inside system/database/DB_query_builder.php at line #973 I believe:

    This : case 'before': $v = "%'{$v}'"; // <- Check here break;

    Was changed to this: case 'before': $v = "'%{$v}'"; // <- The fix break;

    Try to do this change and see if this fixes your issue or not.

    EDITED

    After seeing your code, please don't use * as a field name, you have to specify what field is used for the search ... For instance, if you want to search in titles, descriptions and contents, you may proceed as this:

    $query = $this->db->like('title', $expression)
              ->or_like('description', $expression)
              ->or_like('content', $expression);

    Then the rest of your code. Give it a shot.