table join

[quasiman]

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]

[eric1235711]

Are you sure that the field names are right?
Are you sure that there´s any reccord?

[thedarkwinter]

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

[fenway]

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.

[quasiman]

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.

[fenway]

And what's missing from these 4 records?

[quasiman]

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!