Jump to content


Photo

table join


  • Please log in to reply
6 replies to this topic

#1 quasiman

quasiman
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts
  • LocationPortland, Oregon

Posted 03 October 2006 - 01:00 AM

Joining two tables - mail and mail_attachments.  I want to display all records where mail_attachments.message = mail.mailID.

I'm not getting any errors, but it's returning everything in the mail_attachments.name column, not just the records where my fields (above) are equal:

$query = "SELECT mail.mailID, mail_attachments.message, mail_attachments.name ".
 "FROM mail, mail_attachments ".
	"WHERE mail_attachments.message = mail.mailID"; 
	 
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
	echo $row['name'];
	echo "<br />";
}


#2 eric1235711

eric1235711
  • Members
  • PipPipPip
  • Advanced Member
  • 107 posts
  • LocationSão Paulo - Brazil

Posted 03 October 2006 - 01:05 PM

Are you sure that the field names are right?
Are you sure that there´s any reccord?
Programming is like building your own universe...
But let me ask you something:
Your world... It´s cool? Or it sucks?

#3 thedarkwinter

thedarkwinter
  • Members
  • PipPipPip
  • Advanced Member
  • 191 posts
  • LocationLondon

Posted 03 October 2006 - 01:09 PM

Hi

maybe you need to use a JOIN command in the sql: try something like

SELECT mail.mailID, mail_attachments.message, mail_attachments.name FROM mail LEFT JOIN mail_attachments ON mail.mailID = mail_attachments.message;

cheers,
tdw
Remember - if you don't figure it out yourself, you'll probably forget it tomorrow :)

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 October 2006 - 02:56 PM

Well, yes, you should use proper JOIN syntax as per the above... but I don't see why your initial query would behave the way you describe.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 quasiman

quasiman
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts
  • LocationPortland, Oregon

Posted 03 October 2006 - 04:20 PM

eric1235711:
yes I'm sure there are records, and I have the names right.

thedarkwinter:
I tried your LEFT JOIN and I'm getting the same results.  For messages 2 and 3 there were two attachments each, so in the mail_attachments table there are four records.  I changed the row echo to display the attachment message number, my result then is:
2
2
3
3

I wonder if there's a script somewhere else on the page that's affecting this.  Is that possible?  I don't think so, but I can't see why it's doing this otherwise.

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 October 2006 - 04:39 PM

And what's missing from these 4 records?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 quasiman

quasiman
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts
  • LocationPortland, Oregon

Posted 03 October 2006 - 04:55 PM

oh wait....duh.  ;)  I'm asking it to display messages where they match, so of course that's what it's doing.  I need to say
"WHERE mail_attachments.message = $current_record"; 

Thanks!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users