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!
$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.