How to Perform a Keyword-Based Multi-Table Search in PHP and MySQL?

PHP MySQL: Multi-Table Search with a Keyword

Finding results from multiple tables in a MySQL database using a keyword can be a challenge. In this article, we address the problem of searching across 'messages.content', 'messages.title', 'topics.content', 'topics.title', 'comments.content', and 'comments.title' from three separate tables ('messages', 'topics', and 'comments') using the 'LIKE' operator.

Query for Multi-Table Search:

To perform a search across multiple tables, we can utilize the UNION operator. The following query combines the results from each table:

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

Identifying Table Origin of Results:

Once the results are obtained, we need a way to determine the table from which each row originates. For this, we have added a 'type' column to each row, indicating the table name ('msg' for 'messages', 'topic' for 'topics', and 'comment' for 'comments'). After executing the query, the results can be accessed and sorted based on the 'type' column to isolate the rows from specific tables.

Example Usage:

Suppose we have a keyword 'example' and want to find all matching results. The multi-table search will return all rows where the keyword appears in the 'content' or 'title' fields of any of the three tables. We can then use the 'type' column to group and display the results separately for each table.

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