quasiman Posted October 3, 2006 Share Posted October 3, 2006 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] Quote Link to comment Share on other sites More sharing options...
eric1235711 Posted October 3, 2006 Share Posted October 3, 2006 Are you sure that the field names are right?Are you sure that there´s any reccord? Quote Link to comment Share on other sites More sharing options...
thedarkwinter Posted October 3, 2006 Share Posted October 3, 2006 Himaybe 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 Quote Link to comment Share on other sites More sharing options...
fenway Posted October 3, 2006 Share Posted October 3, 2006 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. Quote Link to comment Share on other sites More sharing options...
quasiman Posted October 3, 2006 Author Share Posted October 3, 2006 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:2233I 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted October 3, 2006 Share Posted October 3, 2006 And what's missing from these 4 records? Quote Link to comment Share on other sites More sharing options...
quasiman Posted October 3, 2006 Author Share Posted October 3, 2006 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! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.