Craig Hopson Posted July 16, 2012 Share Posted July 16, 2012 hi guys i really need some help with this i just cant get my head round it i have two tables emails and filesEMAILS has id, from, subject, body, dateFILES has id, email_id, filename, mime, subject i need to display the email body and IF it had a picture attached to the email it will have the details logged in files so here's what i have so far $result = mysql_query("SELECT * FROM emails WHERE subject = '$id' ORDER BY date DESC LIMIT 2");echo date('H:i:s', time());echo '<br>';while($row = mysql_fetch_array($result)) {$result2 = mysql_query("SELECT * FROM files WHERE subject = '$id' ORDER BY id DESC LIMIT 2");while($row2 = mysql_fetch_array($result2)) { if($row2['email_id'] == $row['id']) { echo '<img src="uploads/'.$id.'/thumbnail/'.$row2['filename'].'" style="border:0px">'; echo $row['from']; echo $row['body']; echo '<br>'; } else { echo '<img src="images/avatar.png" width="100px" style="border:0px">'; echo $row['from']; echo $row['body']; echo '<br>'; }}} but this gives 4 outputs same 2 emails duplicated 1 with pic 1 without Link to comment Share on other sites More sharing options...
justsomeguy Posted July 16, 2012 Share Posted July 16, 2012 You can use a join to get information from multiple tables. http://www.w3schools.com/sql/sql_join.asp Link to comment Share on other sites More sharing options...
Craig Hopson Posted July 16, 2012 Author Share Posted July 16, 2012 yeah i looked at that didnt understand but i got it working like this $result = mysql_query("SELECT * FROM emails WHERE subject = '$id' ORDER BY date DESC LIMIT 5");while($row = mysql_fetch_array($result)) {$result2 = mysql_query("SELECT * FROM files WHERE email_id = '$row[id]'");while($row2 = mysql_fetch_array($result2)) { $IMAGE = 'uploads/'.$id.'/thumbnail/'.$row2['filename']; $IMAGEID = $row2['email_id'];}if($IMAGEID == $row['id']) { echo '<img src="'.$IMAGE.'" width="100px" style="border:0px">';} else { echo '<img src="images/avatar.png" width="100px" style="border:0px">';}echo $row['from'];echo $row['body'];echo '<br>';} Link to comment Share on other sites More sharing options...
birbal Posted July 17, 2012 Share Posted July 17, 2012 (edited) SELECT * FROM files WHERE email_id IN (SELECT id FROM emails WHERE subject = '$id' ORDER BY date DESC LIMIT 5) Edited July 17, 2012 by birbal Link to comment Share on other sites More sharing options...
Craig Hopson Posted July 17, 2012 Author Share Posted July 17, 2012 thanks sooooo much looks so easy when its shown Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now