Multiple table/field select in SQL statement?

I'm trying to pull data out of three different tables with different fields, but don't really know how to compile the below code into one big SQL statement.  Any ideas with what I've included below?

Everything works fine right now, by the way, but I want it to be displayed a little cleaner.


CODE:
====
<?php
 

$connection = mysql_connect("localhost","matt","");
mysql_select_db("test",$connection);
 
$query_privmsgs = "SELECT distinct privmsgs_subject,privmsgs_from_userid,privmsgs_to_userid, FROM phpbb_privmsgs ORDER BY privmsgs_id DESC"; // Get newest messages
$result_privmsgs = mysql_query($query_privmsgs) or die ("Error in
query_privmsgs: " . mysql_error());
while ($row_privmsgs = mysql_fetch_array($result_privmsgs))
{
     
     echo "<br><b>Subject</b> : " . $row_privmsgs['privmsgs_subject'];
     echo "<br>From userid : " . $row_privmsgs['privmsgs_from_userid'];
     echo "<br>To userid : " . $row_privmsgs['privmsgs_to_userid'];
     echo "<br>";
}


$query_text = "SELECT distinct privmsgs_text FROM phpbb_privmsgs_text ORDER BY privmsgs_text_id DESC"; //Get newest messages
$result_text = mysql_query($query_text) or die ("Error in query_text: " .
mysql_error());
while ($row_text = mysql_fetch_array($result_text))
{
     
     echo "<br><b>Text</b> : " . $row_text['privmsgs_text'];
     echo "<br>";
}

$query_text = "SELECT distinct username FROM phpbb_users"; //See whos who
$result_text = mysql_query($query_text) or die ("Error in query_text: " .
mysql_error());
while ($row_text = mysql_fetch_array($result_text))
{
     
     echo "<br><b>From</b> : " . $row_text['username'];
     echo "<br><br>";
}

?>
mattybrighAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

snoyes_jwCommented:
SELECT DISTINCT
    privmsgs_subject,
    privmsgs_from_userid,
    privmsgs_to_userid,
    privmsgs_text,
    from_user.username AS from_user_name,
    to_user.username AS to_user_name
FROM
    phpbb_privmsgs
    JOIN phpbb_users AS from_user ON (privmsgs_from_userid = from_user_name.userId)
    JOIN phpbb_users AS to_user ON (privmsgs_to_userid = to_user_name.userId)
ORDER BY
    privmsgs_id DESC
0
mattybrighAuthor Commented:
Hmmm...I get this errror:

Error in query_privmsgs: Unknown column 'privmsgs_text' in 'field list'

Here's my full code
============

<?php
 

$connection = mysql_connect("localhost","matt","");
mysql_select_db("test",$connection);

$query_privmsgs = "SELECT DISTINCT
    privmsgs_subject,
    privmsgs_from_userid,
    privmsgs_to_userid,
    privmsgs_text,
    from_user.username AS from_user_name,
    to_user.username AS to_user_name
FROM
    phpbb_privmsgs
    JOIN phpbb_users AS from_user ON (privmsgs_from_userid = from_user_name.userId)
    JOIN phpbb_users AS to_user ON (privmsgs_to_userid = to_user_name.userId)
ORDER BY
    privmsgs_id DESC"; // Get newest messages
$result_privmsgs = mysql_query($query_privmsgs) or die ("Error in
query_privmsgs: " . mysql_error());
while ($row_privmsgs = mysql_fetch_array($result_privmsgs))
{
     
     echo "<br><b>Subject</b> : " . $row_privmsgs['privmsgs_subject'];
     echo "<br>From userid : " . $row_privmsgs['privmsgs_from_userid'];
     echo "<br>To userid : " . $row_privmsgs['privmsgs_to_userid'];
     echo "<br><b>Text</b> : " . $row_text['privmsgs_text'];
     echo "<br><b>From</b> : " . $row_text['username'];
     echo "<br>";
}

?>
0
snoyes_jwCommented:
Oh, guess the text is in a separate table.  You'll need to add that table to the FROM clause, joining it to the phpbb_privmsgs table with whatever id field is appropriate.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

mattybrighAuthor Commented:
Ok, I'll give it a shot but it sounds pretty advanced.  If you have the time, could you maybe post that here as well (just so I have something to fall back on)?
0
snoyes_jwCommented:
Which fields match up in phpbb_privmsgs and phpbb_privmsgs_text?
0
mattybrighAuthor Commented:
In phpbb_privmsgs the fields are:
privmsgs_subject
privmsgs_from_userid
privmsgs_to_userid

In phpbb_privmsgs_text the only field I want to display is:
privmsgs_text

And then phpbb_users the only field I want to display is:
username
0
snoyes_jwCommented:
I need to know what fields to use to connect phpbb_privmsgs with phpbb_privmsgs_text.  Doesn't matter if you want to display those fields or not.
0
mattybrighAuthor Commented:
Ok, I just checked the database and here are the fields in PHPBB_PRIVMSGS
privmsgs_id
privmsgs_type
privmsgs_subject
privmsgs_from_userid
privmsgs_to_userid

Here are all the fields in PHPBB_PRIVMSGS_TEXT
privmsgs_text_id
privmsgs_bbcode_uid
privmsgs_text

Essentially the PHPBB_PRIVMSGS contains the header, subject and the frame of the message.  The PHPBB_PRIVMSGS_TEXT contains the actual message (text).

Does that help any?
0
snoyes_jwCommented:
Is there some other table that joins these two tables?  The question basically is, how do you know which messages go with which text?
0
mattybrighAuthor Commented:
I don't think so.  This goes back to my original post (first post on this page) - everything works fine and displays everything I want it to, I just wanted to simplify things.
0
DataSmartsCommented:
I had to install phpbb on one of my sites to get the correct database schema, but I believe the following should get you what you want simply:

<?php

$query_privmsgs = "SELECT * FROM phpbb_privmsgs JOIN phpbb_privmsgs_text ON (phpbb_privmsgs.privmsgs_id = phpbb_privmsgs_text.privmsgs_text_id) JOIN phpbb_users ON (phpbb_privmsgs.privmsgs_from_userid = phpbb_users.user_id ) ORDER BY phpbb_privmsgs.privmsgs_id DESC"; // Get newest messages
$result_privmsgs = mysql_query($query_privmsgs); //Add Die statement as you like

$tdcount = 1;
$numtd = 5; // number of cells per row


// show content



print"
<h3>Recent Private Messages</h3>
<table border=1 width='600' style='border-color: #b0c0d0; font-size:10pt;'>
      <tr>
            <td>Subject</td>
            <td>From ID</td>
            <td>To ID</td>
            <td>Text</td>
            <td>From User</td>
      </tr>";
while($row = mysql_fetch_array($result)) {

$privmsgs_subject = mysql_result($result, 0, 'privmsgs_subject');
$privmsgs_from_userid = mysql_result($result, 0, 'privmsgs_from_userid');
$privmsgs_to_userid = mysql_result($result, 0, 'privmsgs_to_userid');
$privmsgs_text = mysql_result($result, 0, 'privmsgs_text');
$phpbb_username = mysql_result($result, 0, 'username');

print"<tr>
            <td>$privmsgs_subject</td>
            <td>$rivmsgs_from_userid</td>
            <td>$privmsgs_to_userid</td>
            <td>$privmsgs_text</td>
            <td>$phpbb_username</td>
      </tr>"; // display as you like

      if ($tdcount == $numtd) {  echo "</tr>";
      $tdcount = 1;
      } else {
      $tdcount++;
      }
}

// time to close up our table
if ($tdcount!= 1) {
      while ($tdcount <= $numtd) {
      echo "<td>&nbsp;</td>";
      $tdcount++;
      }
      echo "</tr>";
}

?>
0
DataSmartsCommented:
Dump the SQL SELECT statement into phpmyadmin to make sure it is pulling what you want.  As I had no private messages sent on the new install, it pulled zero records...
0
DataSmartsCommented:
Sheesh...and dont forget to add the connection strings to the top of the file!
0
mattybrighAuthor Commented:
Everything looks fine, though it's throwing an error:

Notice: Undefined variable: result in c:\inetpub\wwwroot\phpbb\phpbb2\admin\privmsgs.php on line 34

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in c:\inetpub\wwwroot\phpbb\phpbb2\admin\privmsgs.php on line 34


Line 34: while($row = mysql_fetch_array($result)) {


Any ideas?  And thanks for everyone's help!
0
DataSmartsCommented:
What variable gets set at line 34?
0
DataSmartsCommented:
Oh nevermind...

Change line 34 to from this:

while($row = mysql_fetch_array($result)) {

to this:

while($row = mysql_fetch_array($result_privmsgs)) {
0
mattybrighAuthor Commented:
Parse error: parse error, unexpected $end in c:\inetpub\wwwroot\phpbb\phpbb2\admin\privmsgs.php on line 70

Line 70:
?>

From the research I've done it looks like the error is a missing { or } but I don't know where!
0
DataSmartsCommented:
Would you mind posting the full code here and I'll look for it?
0
mattybrighAuthor Commented:
No problem


CODE
====
<?php

$connection = mysql_connect("localhost","matt","");

mysql_select_db("test",$connection);

$query_privmsgs = "SELECT * FROM phpbb_privmsgs JOIN phpbb_privmsgs_text ON (phpbb_privmsgs.privmsgs_id = phpbb_privmsgs_text.privmsgs_text_id) JOIN phpbb_users ON (phpbb_privmsgs.privmsgs_from_userid = phpbb_users.user_id ) ORDER BY phpbb_privmsgs.privmsgs_id DESC"; // Get newest messages
$result_privmsgs = mysql_query($query_privmsgs); //Add Die statement as you like

$tdcount = 1;
$numtd = 5; // number of cells per row


// show content



print"
<h3>Recent Private Messages</h3>
<table border=1 width='600' style='border-color: #b0c0d0; font-size:10pt;'>
     <tr>
          <td>Subject</td>
          <td>From ID</td>
          <td>To ID</td>
          <td>Text</td>
          <td>From User</td>
     </tr>";
      
 while($row = mysql_fetch_array($result_privmsgs)) {

{

$privmsgs_subject = mysql_result($result, 0, 'privmsgs_subject');
$privmsgs_from_userid = mysql_result($result, 0, 'privmsgs_from_userid');
$privmsgs_to_userid = mysql_result($result, 0, 'privmsgs_to_userid');
$privmsgs_text = mysql_result($result, 0, 'privmsgs_text');
$phpbb_username = mysql_result($result, 0, 'username');

print"<tr>
          <td>$privmsgs_subject</td>
          <td>$rivmsgs_from_userid</td>
          <td>$privmsgs_to_userid</td>
          <td>$privmsgs_text</td>
          <td>$phpbb_username</td>
     </tr>"; // display as you like

     if ($tdcount == $numtd) {  echo "</tr>";
     $tdcount = 1;
     } else {
     $tdcount++;
     }
}

// time to close up our table
if ($tdcount!= 1) {
     while ($tdcount <= $numtd) {
     echo "<td>&nbsp;</td>";
     $tdcount++;
     }
     echo "</tr>";
}


?>
0
DataSmartsCommented:
<?php

$connection = mysql_connect("localhost","matt","");

mysql_select_db("test",$connection);

$query_privmsgs = "SELECT * FROM phpbb_privmsgs JOIN phpbb_privmsgs_text ON (phpbb_privmsgs.privmsgs_id = phpbb_privmsgs_text.privmsgs_text_id) JOIN phpbb_users ON (phpbb_privmsgs.privmsgs_from_userid = phpbb_users.user_id ) ORDER BY phpbb_privmsgs.privmsgs_id DESC"; // Get newest messages
$result_privmsgs = mysql_query($query_privmsgs); //Add Die statement as you like

$tdcount = 1;
$numtd = 5; // number of cells per row


// show content



print"
<h3>Recent Private Messages</h3>
<table border=1 width='600' style='border-color: #b0c0d0; font-size:10pt;'>
     <tr>
          <td>Subject</td>
          <td>From ID</td>
          <td>To ID</td>
          <td>Text</td>
          <td>From User</td>
     </tr>";
     
 while($row = mysql_fetch_array($result_privmsgs)) {


$privmsgs_subject = mysql_result($result, 0, 'privmsgs_subject');
$privmsgs_from_userid = mysql_result($result, 0, 'privmsgs_from_userid');
$privmsgs_to_userid = mysql_result($result, 0, 'privmsgs_to_userid');
$privmsgs_text = mysql_result($result, 0, 'privmsgs_text');
$phpbb_username = mysql_result($result, 0, 'username');

print"<tr>
          <td>$privmsgs_subject</td>
          <td>$rivmsgs_from_userid</td>
          <td>$privmsgs_to_userid</td>
          <td>$privmsgs_text</td>
          <td>$phpbb_username</td>
     </tr>"; // display as you like

     if ($tdcount == $numtd) {  echo "</tr>";
     $tdcount = 1;
     } else {
     $tdcount++;
     }
}

// time to close up our table
if ($tdcount!= 1) {
     while ($tdcount <= $numtd) {
     echo "<td>&nbsp;</td>";
     $tdcount++;
     }
     echo "</tr>";
}


?>
0
mattybrighAuthor Commented:
Now I'm getting these errors on lines 32-36

Notice: Undefined variable: result in c:\inetpub\wwwroot\phpbb\phpbb2\admin\privmsgs.php on line 32

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in c:\inetpub\wwwroot\phpbb\phpbb2\admin\privmsgs.php on line 32
0
DataSmartsCommented:
I really am sorry....I'm used to doing this with my own peculiar quirks and rushing through this..

$privmsgs_subject = mysql_result($result_privmsgs, 0, 'privmsgs_subject');
$privmsgs_from_userid = mysql_result($result_privmsgs, 0, 'privmsgs_from_userid');
$privmsgs_to_userid = mysql_result($result_privmsgs, 0, 'privmsgs_to_userid');
$privmsgs_text = mysql_result($result_privmsgs, 0, 'privmsgs_text');
$phpbb_username = mysql_result($result_privmsgs, 0, 'username');
0
mattybrighAuthor Commented:
Not a problem.  This seems to work the best, but still getting an error and it lists several thousand rows (and there's only two PM's in the DB)!

Fatal error: Maximum execution time of 30 seconds exceeded in c:\inetpub\wwwroot\phpbb\phpbb2\admin\privmsgs.php on line 44
0
DataSmartsCommented:
Can you post a link?  Did you post the JOIN statement to phpmyadmin?  How many rows did it show (If you did)?
0
mattybrighAuthor Commented:
I don't have access to PHP Myadmin here at work and I'm testing it on a local server here at the office (I installed PHPBB on our server).  I'll be using it on my site and can post a link after work when I get home.

It's saying line 44 is the problem and here's that:
</tr>"; // display as you like

Also, doesn't look like we closed the table, where would we do that?

0
DataSmartsCommented:
Replace the snippet here....what version of PHP are you using as well?  And, just out of curiosotiy, why do you want to read people's private messages?  I own a fairly large sports message board (collegefanatics.com/boards) and so I'm interested....


print"<tr>
          <td>$privmsgs_subject</td>
          <td>$rivmsgs_from_userid</td>
          <td>$privmsgs_to_userid</td>
          <td>$privmsgs_text</td>
          <td>$phpbb_username</td>"; // display as you like

     if ($tdcount == $numtd) {  echo "</tr>";
     $tdcount = 1;
     } else {
     $tdcount++;
     }
}

// time to close up our table
if ($tdcount != 1) {
     while ($tdcount <= $numtd) {
     echo "<td>&nbsp;</td>";
     $tdcount++;
     }
     echo "</tr></table>";
}


?>
0
mattybrighAuthor Commented:
Not sure what version of PHP I have here at work, I think the one on my site is 3 something.  There's actually a mod that's really easy to let you read PM's, but this is a way around that (you have to be an admin to use the mod and I can show you where to get it if you like).

I'm playing in an online game and I want to see if others are plotting against me!  And I'm hosting the game at my site and I guess I could just open up the database and check, but I can't access the DB at work so...that's why.

This new snippet is doing the same thing, so I'm not sure what's wrong...

Fatal error: Maximum execution time of 30 seconds exceeded in c:\inetpub\wwwroot\phpbb\phpbb2\admin\privmsgs.php on line 29


Line 29:
while($row = mysql_fetch_array($result_privmsgs)) {
0
DataSmartsCommented:
You really need to dump the SELECT query into SQL and see what it pulls...

SELECT * FROM phpbb_privmsgs JOIN phpbb_privmsgs_text ON (phpbb_privmsgs.privmsgs_id = phpbb_privmsgs_text.privmsgs_text_id) JOIN phpbb_users ON (phpbb_privmsgs.privmsgs_from_userid = phpbb_users.user_id ) ORDER BY phpbb_privmsgs.privmsgs_id DESC

My expectation is that we need to further refine the ON statement
0
mattybrighAuthor Commented:
I just tested it in MySQL and it pulls everything fine (well, two of everything so might want to make it distinct) but it pulls both PM's just fine.
0
DataSmartsCommented:
How about echoing ONLY the number of rows:

$numrows = mysql_num_rows($result_privmsgs);

print"#numrows";

Make sure you comment out the While loop and place this after

$result_privmsgs = mysql_query($query_privmsgs); //Add Die statement as you like

0
mattybrighAuthor Commented:
ya lost me there.  you might have to repost the code.  Or is all of that done in mysql?
0
DataSmartsCommented:
Run as a seperate file:

<?php

$connection = mysql_connect("localhost","matt","");

mysql_select_db("test",$connection);

$query_privmsgs = "SELECT * FROM phpbb_privmsgs JOIN phpbb_privmsgs_text ON (phpbb_privmsgs.privmsgs_id = phpbb_privmsgs_text.privmsgs_text_id) JOIN phpbb_users ON (phpbb_privmsgs.privmsgs_from_userid = phpbb_users.user_id ) ORDER BY phpbb_privmsgs.privmsgs_id DESC"; // Get newest messages
$result_privmsgs = mysql_query($query_privmsgs); //Add Die statement as you like
$numrows = mysql_num_rows($result_privmsgs);

print"#numrows";
?>
0
mattybrighAuthor Commented:
It doesn't give a number, it's just a white page with:

#numrows
0
DataSmartsCommented:
print"$numrows";
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.