Jump to content

Query two tables?


JackW

Recommended Posts

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 ' &nbsp; ';
echo $message_array['school'];
echo ' &nbsp; ';
echo $message_array['name'];
echo ' &nbsp; ';
echo $message_array['post_day'];
echo ' &nbsp; ';
echo $message_array['reply_day'];
echo ' &nbsp; ';
echo $message_array['subject'];
echo ' &nbsp; ';
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

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

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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...