Searching Across Multiple Tables: Handling LIKE Queries in PHP and MySQL
Searching across multiple tables with a single keyword is a common requirement when building search features for content-heavy applications. The core challenge here isn't just writing the SQL; it's managing the result set so your application logic knows exactly which table each row originated from. Your current query successfully searches messages but stops there because you haven't combined the other tables into the same execution context.
To perform a search across messages, topics, and comments simultaneously, you must use a UNION statement. This operator combines the result sets of two or more SELECT statements into a single result set. Since your schema specifies that all three tables share identical column names (content and title), this structure is ideal for a UNION.
Here is how to construct the query:
SELECT 'messages' AS source_table, content, title
FROM messages
WHERE content LIKE '%keyword%' OR title LIKE '%keyword%'
UNION ALL
SELECT 'topics' AS source_table, content, title
FROM topics
WHERE content LIKE '%keyword%' OR title LIKE '%keyword%'
UNION ALL
SELECT 'comments' AS source_table, content, title
FROM comments
WHERE content LIKE '%keyword%' OR title LIKE '%keyword%';
Notice the addition of source_table in each sub-query. This is a crucial step to answer your second question: "how can I tell what result is from what table?" By explicitly selecting a string literal (e.g., 'messages') as an alias, you inject metadata directly into the result set. When you fetch these results in PHP, every row will now have that first column indicating its origin.
Using UNION ALL is generally preferred over UNION in this specific scenario because UNION attempts to remove duplicate rows based on all selected columns. Since your goal is to find content regardless of where it appears, removing duplicates might hide valid results if the same text exists in both a message and a comment. UNION ALL preserves every match, which you can filter or handle later in your application code.
Performance Considerations
While UNION allows you to search multiple tables at once, be aware of the performance implications. MySQL must execute three separate queries internally and then merge the results. As your dataset grows, this approach can become slower than a single query with an index on one table. If performance becomes a bottleneck, consider consolidating data into a single searchable table or implementing full-text search engines like Elasticsearch.
Another potential pitfall involves special characters in keywords. The LIKE operator is sensitive to percent signs (%) and underscores (_). If a user searches for a string containing these characters without proper escaping, your query could break or return incorrect results. Always ensure $keyword is sanitized before injecting it into the query string.
Modernizing with Laravel
If you are working within the Laravel ecosystem, writing raw UNION queries is often unnecessary and can obscure the data flow. Laravel provides robust tools to handle multi-table searches without sacrificing readability or safety. You can leverage Eloquent's whereHasIn or simply run a raw query using the database facade with parameter binding to prevent SQL injection.
For instance, you could construct the query dynamically by mapping your tables and columns, then executing it via DB::select(). This ensures that even if you add a fourth table later, the logic remains consistent and secure. If you need more advanced search capabilities like ranking by relevance or handling stop-words, consider integrating Laravel Scout with an external index service.
Ultimately, using UNION with an explicit source identifier is the most direct way to solve this problem in vanilla PHP and MySQL. It keeps the logic transparent while giving your application exactly the metadata it needs to present results correctly to the user.