Codeigniter 3 use of LIKE results in an SQL related error

4036 views mysql
3

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 '!'

My search method looks like this:

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

Where is my mistake?

answered question

$this->db->like('*', $expression); what is * ?

@sagi it is all columns

This is not possible in SQL. * is used in select list

1 Answer

3

You need to use column/expression instead of *:

SELECT * FROM `posts` WHERE * LIKE '%expression%' ESCAPE '!'
=>
SELECT * FROM `posts` WHERE column_name LIKE '%expression%' ESCAPE '!';

I need to seelct from all columns in the posts table

Then you could use something like(be aware of poor performance because CONCAT and LIKE '%exp' make it nonSARGable.

SELECT * FROM `posts` 
WHERE CONCAT(col1,col2, col3, ...)  LIKE '%expression%' ESCAPE '!'

To avoid potential problems with CONCAT you should add separator:

SELECT * FROM `posts` 
WHERE CONCAT(col1,'^',col2,'^',col3, ...)  LIKE '%expression%' ESCAPE '!'

posted this

Have an answer?

JD

Please login first before posting an answer.