在 Laravel 中使用 DB::select() 和 LIKE 子句和变量列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25178502/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using DB::select() in Laravel with LIKE clause and a variable column name
提问by Melvin
In the docs,
在文档中，
$results = DB::select('select * from users where id = ?', array(1));
The Problem
问题
I have a variable $columnand $valueand I want them to search the database based on what column like this:
我有一个变量$column，$value我希望他们根据这样的列搜索数据库：
$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 (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 Builderstyle if possible. But if it's not possible, then I will just use Query Builder.
查询实际上很长（有多个连接）。所以如果可能的话，我宁愿不使用Query Builder样式。但如果不可能，那么我将只使用查询生成器。
Info:
信息：
- Laravel v4.2
- PostgreSQL
- Laravel v4.2
- PostgreSQL
回答by peter.babic
It could be done in more Query Builderstyle, like that:
它可以以更多的查询生成器样式完成，如下所示：
$results = DB::table('users')
->where($column, 'LIKE', '%' . $value . '%')
->get();
EDIT
编辑
The only reliable way how to do it with DB::select()is:
唯一可靠的方法DB::select()是：
$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 $columnnamed valuesthis method would break, since valuesis a reserved word (at least in MySQL).
它生成了正确的查询，我根据数据库检查了它，但也生成了一个空白数组，或者错误的结果。即使这种方法以某种方式起作用，您仍然必须手动转义表名和列名，这很乏味并且显然不适用于 ?。如果你说有一个$column命名values这个方法会中断，因为它values是一个保留字（至少在 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.
强烈建议使用 Query Builder 方法，因为它还会自动将 SQL 转义添加到表名和列名。此外，它在数据库驱动程序之间更具可移植性。它还支持轻松连接。无需使用您想要的方法。
回答by Jaime Ocares
try this:
尝试这个：
use App\User; //don't forget add this to header
使用 App\User; //不要忘记将其添加到标题中
$result = User::where('columnName', 'LIKE', "%$value%")->get();
$result = User::where('columnName', 'LIKE', "%$value%")->get();
回答by Lino
the only way i find working
我找到工作的唯一方法
$phone = '%'.$phone.'%';
$seachbyphone = DB::select('SELECT * FROM user WHERE phoneno LIKE ?',[$phone]);
