PHP mysql search multiple tables using a keyword

Question

I have three tables in my database which are:

messages topics comments 

Each of these tables has two fields called 'content' and 'title'. I want to be able to use 'Like' in my sql statement to look at 'messages.content', 'messages.title', 'topics.content', 'topics.title', 'comments.content' and 'comments.title' using a keyword.

So far, my query is able to find results from only one table:

mysql_query("SELECT * FROM messages  WHERE content LIKE '%" . $keyword . "%'  OR title LIKE '%" . $keyword ."%'"); 

I am also wondering, once I get the results from multiple tables, how can I tell what result is from what table?

Any help would be greatly appreciated!

Answer 1

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

So, you are getting result from all of the three tables, and you can identify which row came from which table by looking at its type value.

Answer 2

What you are probably looking for is the UNION command:

SELECT id, 'messages' as 'table' FROM messages    WHERE content LIKE '%keyword%'      OR title LIKE '%keyword%' UNION SELECT id, 'topics' as 'table' FROM topics   WHERE content LIKE '%keyword%'      OR title LIKE '%keyword%' UNION SELECT id, 'comments' as 'table' FROM comments   WHERE content LIKE '%keyword%'      OR title LIKE '%keyword%'