Solved

Multiple table/field select in SQL statement?

Posted on 2006-06-21
34
259 Views
Last Modified: 2013-12-12
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>";
}

?>
0
Comment
Question by:mattybrigh
  • 15
  • 14
  • 5
34 Comments
 
LVL 33

Expert Comment

by:snoyes_jw
Comment Utility
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
 

Author Comment

by:mattybrigh
Comment Utility
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
 
LVL 33

Expert Comment

by:snoyes_jw
Comment Utility
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
 

Author Comment

by:mattybrigh
Comment Utility
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
 
LVL 33

Expert Comment

by:snoyes_jw
Comment Utility
Which fields match up in phpbb_privmsgs and phpbb_privmsgs_text?
0
 

Author Comment

by:mattybrigh
Comment Utility
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
 
LVL 33

Expert Comment

by:snoyes_jw
Comment Utility
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
 

Author Comment

by:mattybrigh
Comment Utility
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
 
LVL 33

Expert Comment

by:snoyes_jw
Comment Utility
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
 

Author Comment

by:mattybrigh
Comment Utility
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
 
LVL 2

Expert Comment

by:DataSmarts
Comment Utility
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
 
LVL 2

Expert Comment

by:DataSmarts
Comment Utility
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
 
LVL 2

Expert Comment

by:DataSmarts
Comment Utility
Sheesh...and dont forget to add the connection strings to the top of the file!
0
 

Author Comment

by:mattybrigh
Comment Utility
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
 
LVL 2

Expert Comment

by:DataSmarts
Comment Utility
What variable gets set at line 34?
0
 
LVL 2

Expert Comment

by:DataSmarts
Comment Utility
Oh nevermind...

Change line 34 to from this:

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

to this:

while($row = mysql_fetch_array($result_privmsgs)) {
0
 

Author Comment

by:mattybrigh
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Expert Comment

by:DataSmarts
Comment Utility
Would you mind posting the full code here and I'll look for it?
0
 

Author Comment

by:mattybrigh
Comment Utility
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
 
LVL 2

Expert Comment

by:DataSmarts
Comment Utility
<?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
 

Author Comment

by:mattybrigh
Comment Utility
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
 
LVL 2

Expert Comment

by:DataSmarts
Comment Utility
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
 

Author Comment

by:mattybrigh
Comment Utility
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
 
LVL 2

Expert Comment

by:DataSmarts
Comment Utility
Can you post a link?  Did you post the JOIN statement to phpmyadmin?  How many rows did it show (If you did)?
0
 

Author Comment

by:mattybrigh
Comment Utility
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
 
LVL 2

Expert Comment

by:DataSmarts
Comment Utility
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
 

Author Comment

by:mattybrigh
Comment Utility
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
 
LVL 2

Expert Comment

by:DataSmarts
Comment Utility
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
 

Author Comment

by:mattybrigh
Comment Utility
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
 
LVL 2

Expert Comment

by:DataSmarts
Comment Utility
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
 

Author Comment

by:mattybrigh
Comment Utility
ya lost me there.  you might have to repost the code.  Or is all of that done in mysql?
0
 
LVL 2

Expert Comment

by:DataSmarts
Comment Utility
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
 

Author Comment

by:mattybrigh
Comment Utility
It doesn't give a number, it's just a white page with:

#numrows
0
 
LVL 2

Accepted Solution

by:
DataSmarts earned 500 total points
Comment Utility
print"$numrows";
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now