Jump to content
JackW

Query two tables?

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

Share this post


Link to post
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.

Share this post


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

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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

  • Like 1

Share this post


Link to post
Share on other sites

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.