Jump to content

Query two tables?


JackW

Recommended Posts

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();
?>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.