Jump to content

query'ing two tables


Craig Hopson

Recommended Posts

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

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

SELECT * FROM files WHERE email_id IN (SELECT id FROM emails WHERE subject = '$id' ORDER BY date DESC LIMIT 5) 

Edited by birbal
Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...