JackW Posted July 30, 2019 Share Posted July 30, 2019 I have a MySQL data base with two tables. (questions then answers?) I need to query the second table to find related results from a query on the first table. Here is what I have, How do I get it to combine the two queries? <?php $db = @mysqli_connect('localhost', 'unsabook', 'Password', 'unsabook'); if (!$db) { echo "Error: " . mysqli_connect_error(); exit(); } $rs_message = $db->query ("SELECT * FROM `messages` ORDER BY `message_ID` ASC LIMIT 0,200"); while ( $message_array = $rs_message->fetch_assoc() ) { echo '<div class="left10">'; echo $message_array['message_ID']; echo ' '; echo $message_array['name']; echo ' '; echo $message_array['subject']; echo ' <br>'; echo $message_array['message']; $message_ID = $message_array['message_ID']; echo '</div>'; } // 2nd table is below $rs_replies = $db->query ("SELECT * FROM `replies` WHERE `message_ID` = '".$message_ID."' ORDER BY `reply_ID` DESC"); while($reply_array = mysqli_fetch_assoc($rs_replies)) { echo '<div class="left10">'; echo $reply_array['reply_ID']; echo ' '; echo $reply_array['name']; echo ' '; echo $reply_array['message_ID']; echo ' '; echo $reply_array['reply_day']; echo ' '; echo $reply_array['message']; echo '</div>'; } $db->close(); ?> Quote Link to comment Share on other sites More sharing options...
benanamen Posted July 30, 2019 Share Posted July 30, 2019 The manual is always a good place to start. Quote Link to comment Share on other sites More sharing options...
Barand Posted July 30, 2019 Share Posted July 30, 2019 You mention questions and answers tables in the question and the code is about messages and replies!? Are the two even related? If you want help with a query it's always better to provide the structures of the relevant tables. Please post them. Don't loop through one query result and use that to query another table within the loop. That is very heavy on time and resources. Instead use a single query with a join to get all the data you need from both tables with a single query. Quote Link to comment Share on other sites More sharing options...
JackW Posted July 30, 2019 Author Share Posted July 30, 2019 3 minutes ago, benanamen said: The manual is always a good place to start. Duh! I have looked there and cannot understand what I am reading. Quote Link to comment Share on other sites More sharing options...
Psycho Posted July 30, 2019 Share Posted July 30, 2019 Yes, you should combine them - never run queries in loops. I would suggest modifying the names of your fields in those two tables. Both tables have fields called "name" and "message". It is possible to run a JOINed query on those two tables, but you will need to create aliases - otherwise it is ambiguous when trying to reference "name" in the result set. E.g. in the SELECT condition you could do this to add aliases SELECT m.message_ID, m.name as messageName, m.subject, m.message, r.reply_ID, r.name as replyName, r.reply_day, r.message as reply But, why go to the trouble of using aliases when it would make more sense to rename those fields to be unique? Using "message_ID" in both tables makes sense since is a primary/foreign key relationship. Anyway, here is how you could combine those queries. SELECT m.message_ID, m.name as messageName, m.subject, m.message, r.reply_ID, r.name as replyName, r.reply_day, r.message as reply FROM `messages` m JOIN `replies` r ON m.message_ID = r.message_ID ORDER BY `message_ID` ASC, `reply_ID` DESC LIMIT 0,200 Quote Link to comment Share on other sites More sharing options...
JackW Posted July 30, 2019 Author Share Posted July 30, 2019 6 minutes ago, Barand said: You mention questions and answers tables in the question and the code is about messages and replies!? Are the two even related? If you want help with a query it's always better to provide the structures of the relevant tables. Please post them. Don't loop through one query result and use that to query another table within the loop. That is very heavy on time and resources. Instead use a single query with a join to get all the data you need from both tables with a single query. You got me. The code is not for questions it is messages then the second table will be for comments. For each message there may be numerous (comments) replies. Tables below. table messages message_ID int 6 school var 10 name var 60 post_day var 50 reply_day var 50 message Med_Txt subject var 240 table replies reply_ID int 6 name var 60 message_ID var 20 reply_day var 50 message Med_Txt Quote Link to comment Share on other sites More sharing options...
Barand Posted July 30, 2019 Share Posted July 30, 2019 Psycho has already given you the JOIN solution so saves me doing it. A couple of questions though, Why is there a reply_day column in the message table (when it's clearly reply-related). Do the reply_day and post_day columns contain dates? Quote Link to comment Share on other sites More sharing options...
Techbriefers Posted July 31, 2019 Share Posted July 31, 2019 You can select from both the table then refine the result by loop. Select using join. SELECT m.columns..., r.columns... FROM `messages` AS m LEFT JOIN `replies` AS r ON m.message_ID = r.message_ID ORDER BY m.message_ID ASC, r.reply_ID DESC LIMIT 0,200 Quote Link to comment Share on other sites More sharing options...
Barand Posted July 31, 2019 Share Posted July 31, 2019 @Techbriefers - Brilliant! The way you managed to repeat what Psyco posted 14 hours earlier, removing all structure and readability and fitting the query into a single line, shows sheer genius. 1 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.