How to Combine Multiple Table Searches in PHP with MySQL and a Keyword?

Combining Multiple Table Searches in PHP with MySQL and a Keyword

You have a database with three tables: messages, topics, and comments. Each table contains two fields, 'content' and 'title'. Your goal is to perform a single search query that searches through all six fields (messages.content, messages.title, topics.content, topics.title, comments.content, comments.title) using a specified keyword.

To accomplish this, modify your query to use multiple UNION statements. Each UNION statement will search a specific table using the LIKE operator for the keyword within the 'content' and 'title' fields. The following updated query combines all three searches:

$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);

This query uses the UNION operator to combine the results of each SELECT statement into a single result set. The added 'type' field indicates which table the row originated from.

"msg" = messages table
"topic" = topics table
"comment" = comments table

The above is the detailed content of How to Combine Multiple Table Searches in PHP with MySQL and a Keyword?. For more information, please follow other related articles on the PHP Chinese website!