How to Perform a Keyword Search Across Multiple Tables in MySQL and Identify the Table of Origin?

Multiple Table Search in MySQL Using a Keyword

Question:

In a database with three tables (messages, topics, and comments), each containing 'content' and 'title' fields, how can we perform a LIKE search across all tables using a keyword? How do we distinguish the table of origin for each result?

Solution:

To search multiple tables using a keyword in PHP and MySQL, we can utilize a UNION query:

$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 unites the results from each table into a single output. The 'type' field added to each row serves as an identifier for the table it originated from. After executing this query, we can iterate through the results and determine the table of origin based on the 'type' value.

The above is the detailed content of How to Perform a Keyword Search Across Multiple Tables in MySQL and Identify the Table of Origin?. For more information, please follow other related articles on the PHP Chinese website!