Jump to content

Archived

This topic is now archived and is closed to further replies.

quasiman

table join

Recommended Posts

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:

[code=php:0]
$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 />";
}
[/code]

Share this post


Link to post
Share on other sites
Are you sure that the field names are right?
Are you sure that there´s any reccord?

Share this post


Link to post
Share on other sites
Hi

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

[code]SELECT mail.mailID, mail_attachments.message, mail_attachments.name FROM mail LEFT JOIN mail_attachments ON mail.mailID = mail_attachments.message;[/code]

cheers,
tdw

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites
And what's missing from these 4 records?

Share this post


Link to post
Share on other sites
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 [code]"WHERE mail_attachments.message = $current_record";  [/code]

Thanks!

Share this post


Link to post
Share on other sites

×

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.