使用 foreach 进行动态表选择
我使用 kohana 并且我有这个模型来使用 PDO 从数据库检索搜索结果：
class Model_Crud extends Model_Database {
private $tables=array('articles','comments','pages');
public function get_search_results()
{
$query = DB::query(Database::SELECT, 'SELECT * FROM :table WHERE ( title LIKE :search OR body LIKE :search OR tag LIKE :search)');
$query->param(':search', $_POST['search'] );
$query->bind(':table', $table );
foreach($this->tables as $table)
{
//echo $query;
$result[] = $query->execute();
}
return $result;
}
}
这不会工作，因为 sql 语句的最终形式将是这样的：
SELECT * FROM 'articles' WHERE ( title LIKE 'a random string' OR body LIKE 'a random string' OR tag LIKE 'a random string')
并且自然会失败，因为文章应该超出 '
这样的事情可以做吗？ 或者我需要编写 3 个不同的查询，每个表一个？
I use kohana and i have this model to retrieve search results from database using PDO:
class Model_Crud extends Model_Database {
private $tables=array('articles','comments','pages');
public function get_search_results()
{
$query = DB::query(Database::SELECT, 'SELECT * FROM :table WHERE ( title LIKE :search OR body LIKE :search OR tag LIKE :search)');
$query->param(':search', $_POST['search'] );
$query->bind(':table', $table );
foreach($this->tables as $table)
{
//echo $query;
$result[] = $query->execute();
}
return $result;
}
}
This wont work cause the sql statement will be like this in its final form:
SELECT * FROM 'articles' WHERE ( title LIKE 'a random string' OR body LIKE 'a random string' OR tag LIKE 'a random string')
and naturally it fails since articles should be out of '
Can something like this done?
or i need to write 3 different queries, one for each table?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
看一下 Database_Query 类，如果不为每个表创建单独的查询，似乎无法完成您所追求的任务。
您可以使用转换
:table的set_table方法来扩展 Database_Query 类。更好的是，您可以稍微抽象一下概念，并添加新方法来转换不需要清理的参数。查看 Database_Query::compile 了解它是如何完成的。 （一点也不难。）
Taking a look at the Database_Query class, it doesn't seem that what you are after can be done without creating separate queries for each table.
You could extend the Database_Query class with a
set_tablemethod that translates:table.Better yet, you could abstract the concept a little and add new methods for translating parameters that are not to be sanitized. Take a look at Database_Query::compile to get an idea of how it's done. (It's not difficult at all.)
是的，只需将表名直接放入字符串中，而不是作为参数：
通常这不是一个好主意，因为 SQL 注入，但由于表列表已编码到您的程序中，因此您不必担心关于这个例子。
Yes, just put the table name into the string directly instead of as a parameter:
Normally this isn't a good idea because of SQL injection, but since the list of tables is coded into your program, you don't really have to worry about that in this case.