Query two tables?

[JackW]

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 ' &nbsp; ';
echo $message_array['name'];
echo ' &nbsp; ';
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 ' &nbsp; ';
echo $reply_array['name'];
echo ' &nbsp; ';
echo $reply_array['message_ID'];
echo ' &nbsp; ';
echo $reply_array['reply_day'];
echo ' &nbsp; ';
echo $reply_array['message'];
echo '</div>';
}
 
 $db->close();
?>

[benanamen]

The manual is always a good place to start.

[Barand]

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.

[JackW]

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.

[Psycho]

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

[JackW]

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

[Barand]

Psycho has already given you the JOIN solution so saves me doing it.

A couple of questions though,

1. Why is there a reply_day column in the message table (when it's clearly reply-related).
2. Do the reply_day and post_day columns contain dates?

[Techbriefers]

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

[Barand]

@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.