Using DB::select() in Laravel with LIKE clause and a variable column name

Question

In the docs,

$results = DB::select('select * from users where id = ?', array(1));

The Problem

I have a variable $column and $value and I want them to search the database based on what column like this:

$results = DB::select('select * from users where ? LIKE "%?%"', array($column, $value));

But this throws an error:

SQLSTATE[42P18]: Indeterminate datatype: 7 ERROR: could not determine data type of parameter $2 (SQL: SELECT * FROM test WHERE refno LIKE '%te%') 

I tried hard-coding the value like this:

$results = DB::select('select * from users where ? LIKE "%te%"', array($column));

but it returns a blank array.

How do I do this? Please help.

EDIT:

The query is actually long (with multiple joins). So I prefer not to use the Query Builder style if possible. But if it's not possible, then I will just use Query Builder.

Info:

• Laravel v4.2
• PostgreSQL

Solution 1

It could be done in more Query Builder style, like that:

$results = DB::table('users')
    ->where($column, 'LIKE', '%' . $value . '%')
    ->get();

EDIT

The only reliable way how to do it with DB::select() is:

$results = DB::select("select * from users where ? LIKE '%?%'", array($column, $value));

It produces the right query, I checked it against the database, but also a blank array, or wrong results. Even if this method somehow worked, you still have to escape table and columns names manually, which is tedious and apparently does not work with ?. If you lets say had a $column named values this method would break, since values is a reserved word (at least in MySQL).

The Query Builder method is highly advised, because it also automatically adds SQL escapes to table and column names. Also it is is more portable across DB drivers. It also supports joins with ease. No need to use the method you wants.

Solution 2

the only way i find working

$phone = '%'.$phone.'%';

$seachbyphone = DB::select('SELECT * FROM user WHERE phoneno LIKE ?',[$phone]);

Comments

[Melvin]

Hi, @delmadord Thanks for your answer. Yes I could do that. Thanks. I edited my question.

[Melvin]

Thanks.. I realized there's no other way then.. I'll just have to do it with Query Builder style.. Thanks again.