PHP mysql 使用关键字搜索多个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6574564/
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
PHP mysql search multiple tables using a keyword
提问by Э?ad Дьdulя?мaи
I have three tables in my database which are:
我的数据库中有三个表，它们是：
messages
topics
comments
Each of these tables has two fields called 'content' and 'title'. I want to be able to use 'Like' in my sql statement to look at 'messages.content', 'messages.title', 'topics.content', 'topics.title', 'comments.content' and 'comments.title' using a keyword.
这些表中的每一个都有两个字段，称为“内容”和“标题”。我希望能够在我的 sql 语句中使用“Like”来查看“messages.content”、“messages.title”、“topics.content”、“topics.title”、“comments.content”和“comments”。 title' 使用关键字。
So far, my query is able to find results from only one table:
到目前为止，我的查询只能从一张表中找到结果：
mysql_query("SELECT * FROM messages
WHERE content LIKE '%" . $keyword . "%'
OR title LIKE '%" . $keyword ."%'");
I am also wondering, once I get the results from multiple tables, how can I tell what result is from what table?
我也想知道，一旦我从多个表中得到结果，我怎么知道结果来自哪个表？
Any help would be greatly appreciated!
任何帮助将不胜感激！
回答by MD Sayem Ahmed
$query = "(SELECT content, title, 'msg' as type FROM messages WHERE content LIKE '%" .
$keyword . "%' OR title LIKE '%" . $keyword ."%')
UNION
(SELECT content, title, 'topic' as type FROM topics WHERE content LIKE '%" .
$keyword . "%' OR title LIKE '%" . $keyword ."%')
UNION
(SELECT content, title, 'comment' as type FROM comments WHERE content LIKE '%" .
$keyword . "%' OR title LIKE '%" . $keyword ."%')";
mysql_query($query);
So, you are getting result from all of the three tables, and you can identify which row came from which table by looking at its typevalue.
因此，您将从所有三个表中获得结果，并且您可以通过查看其type值来确定哪一行来自哪个表。
回答by evan
What you are probably looking for is the UNION command:
您可能正在寻找的是 UNION 命令：
SELECT id, 'messages' as 'table' FROM messages
WHERE content LIKE '%keyword%'
OR title LIKE '%keyword%'
UNION
SELECT id, 'topics' as 'table' FROM topics
WHERE content LIKE '%keyword%'
OR title LIKE '%keyword%'
UNION
SELECT id, 'comments' as 'table' FROM comments
WHERE content LIKE '%keyword%'
OR title LIKE '%keyword%'
回答by Bruno Alano
Two search in other tables you use:
在您使用的其他表中进行两次搜索：
SELECT `categories`.`title`, `posts`.`title` WHERE `categories`.`title` LIKE {$a} OR `posts`.`title` LIKE {$a}
The CATEGORIESand POSTSare tables of your database.
该类别和职位是你的数据库的表中。
回答by Mahmudul Hasan
Html Search form:
Html 搜索表单：
<div class="header_top_right">
<form action="search.php" method="GET" class="search_form">
<input type="text" placeholder="Text to Search.." name="search">
<input type="submit" class="btn btn-default" value="">
</form>
</div>
Search.php:
搜索.php：
<?php
if (isset($_GET['search']) || !empty($_GET['search'])) {
$search = mysqli_real_escape_string($db->link, $fm->validation($_GET['search']));
}
else{
header("Location:404.php");
}
?>
<?php
$query = "SELECT * FROM news_post WHERE title LIKE '%$search%' OR body LIKE '%$search%' OR tags LIKE '%search%'";
$post = $db->select($query);
if ($post) {
while ($result = $post->fetch_assoc()) {
echo"Database data like, $result['title']";
}
}
else{
echo "result Not found";
}
include database.php in search.php
在 search.php 中包含 database.php
class Database{
public function select($query){
$result = $this->link->query($query) or die($this->link->error.__LINE__);
if($result->num_rows > 0){
return $result;
}
else {
return false;
}
}
}
$db = new Database();
回答by Mahmudul Hasan
class Database{
public function select($query){
$result = $this->link->query($query) or die($this->link->error.__LINE__);
if($result->num_rows > 0){
return $result;
} else {
return false;
}
}
}
$db = new Database();
$query = "SELECT * FROM post WHERE title LIKE '%$search%' OR body LIKE '%$search%'";
$post = $db->select($query);
if ($post) {
while ($result = $post->fetch_assoc()) {
echo"Database Table colum"
} else{
echo "Search result not Match";
}
}
