JackW Posted July 29, 2019 Share Posted July 29, 2019 I have a MySQL data base with two tables. 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['school']; echo ' '; echo $message_array['name']; echo ' '; echo $message_array['post_day']; echo ' '; echo $message_array['reply_day']; echo ' '; echo $message_array['subject']; echo ' '; 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(); ?> Link to comment Share on other sites More sharing options...
justsomeguy Posted July 29, 2019 Share Posted July 29, 2019 If replies are only nested one level deep then using a join would let you get everything with a single query. Otherwise you need to put the second query in the loop for the first, and that's going to cause problems with scaling if you need to run 200+ queries to show that page. Link to comment Share on other sites More sharing options...
JackW Posted July 30, 2019 Author Share Posted July 30, 2019 1 hour ago, justsomeguy said: If replies are only nested one level deep then using a join would let you get everything with a single query. Otherwise you need to put the second query in the loop for the first, and that's going to cause problems with scaling if you need to run 200+ queries to show that page. I think I need to put a second query in the loop. I had the below code in a previous version of php and it worked fine. Can't seem to make it work now in the updated version. What do I need to do differently? The below code only gets one line from the first table and non from the second table. <?php $db = @mysqli_connect('localhost', 'unsabook', 'Yearbook#1964', 'unsabook'); if (!$db) { echo "Error: " . mysqli_connect_error(); exit(); } $rs_message = $db->query ("SELECT * FROM `messages` ORDER BY `message_ID` DESC LIMIT 0,200"); while ( $message_array = $rs_message->fetch_assoc() ) { echo '<div>'; echo $message_array['message_ID']; echo 'Subject: <b>'; echo $message_array['subject']; echo '</b>'; echo ' Submitted by '; echo $message_array['name']; echo '<br>'; echo $message_array['message']; echo '<br></div>'; $rs_replies = mysql_query("SELECT * FROM replies WHERE message_ID = '" . $message_array['message_ID'] . "' ORDER BY `reply_ID` DESC"); while($replies_array = mysql_fetch_assoc($rs_replies)) { echo '<div class="indent">'; echo $replies_array['reply_ID']; echo '<b>Reply</b> '; echo 'Submitted by '; echo $replies_array['name']; echo '<br>'; echo $replies_array['message']; echo '</div>'; } } $db->close(); ?> Link to comment Share on other sites More sharing options...
justsomeguy Posted July 30, 2019 Share Posted July 30, 2019 You can use a single query with a join, you only need to use recursion if the replies can be nested under each other. If it only shows one record from the database then there's probably an error. mysql_query does not show errors from MySQL, you need to check and display them yourself. The entire mysql extension is also removed in the current version of PHP, I would suggest switching to PDO or mysqli. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now