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:

search()

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



The relevant table fields are:

Where is my mistake?

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

2 Answers
2

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:

system/database/DB_query_builder.php

This :

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



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


Was changed to this:

case 'before':
$v = "'%$v'"; // <- The fix
break;



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.

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.

CONCAT

LIKE '%exp'

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


To avoid potential problems with CONCAT you should add separator:

CONCAT

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


CONCAT(col1,'^',col2,'^',col3, ...)

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy