如何在 PHP 和 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.

以上是如何在 PHP 和 MySQL 中執行基於關鍵字的多表搜尋？的詳細內容。更多資訊請關注PHP中文網其他相關文章！