PHP mysql使用关键字搜索多个表 [英] PHP mysql search multiple tables using a keyword

问题描述

我的数据库中有三个表格：

 信息
主题
评论
  

这些表格中的每一个都有两个名为'content'和'title'的字段。我希望能够在我的sql语句中使用'Like'来查看'messages.content'，'messages.title'，'topics.content'，'topics.title'，'comments.content'和'comments。 title''使用关键字。

到目前为止，我的查询只能从一个表中找到结果：

  mysql_query（SELECT * FROM messages 
 WHERE content LIKE'％。$ keyword。％'
 OR title title LIKE'％。$ keyword。％ ）; 
  

我也想知道，一旦我从多个表中得到结果，我怎么能告诉结果是什么从什么桌子？

任何帮助都将不胜感激！

$ query =（SELECT content，title，'msg'as type FROM messages WHERE content LIKE'％。
$ keyword。％'OR title LIKE'％。$ keyword。 ％'）
UNION
（选择内容，标题，'主题'作为类型FROM主题WHERE内容LIKE'％'。
$关键字。％'或标题LIKE'％。 $关键字。％）
UNION
（选择内容，标题，'comment'作为类型FROM注释WHERE内容LIKE'％。
$关键字。％'OR title LIKE' ％。$ keyword。％'）;

mysql_query（$ query）;


因此，您可以从所有三个表中获得结果，并且您可以识别哪一行来自该表通过查看类型值。

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.

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!

解决方案

$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 type value.

这篇关于PHP mysql使用关键字搜索多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！