Menu
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!
651
You can use multiple tables in your single SQL query. The act of joining in MySQL refers to smashing two or more tables into a single table. You can use JOINS in the SELECT, UPDATE and DELETE statements to join the MySQL tables.
From multiple tables To retrieve information from more than one table, you need to join those tables together. This can be done using JOIN methods, or you can use a second SELECT statement inside your main SELECT query—a subquery.
To dynamically search all keywords, you can use the explode function to seperate all keywords; $queried = mysql_real_escape_string($_POST['query']); // always escape $keys = explode(" ",$queried); $sql = "SELECT * FROM links WHERE name LIKE '%$queried%' "; foreach($keys as $k){ $sql .
$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.
164
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%' If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
