using one table to subtract results from another

[scarhand]

ok i will try to explain this as easy as possible

 

lets say i have 2 tables

 

table 1 is called "posts" and contains these columns:

- id

- message

 

table 2 is called "opened_posts" and contains these columns:

- post_id

- reader_id

 

now lets say i want to get the results from "posts" for each row "opened_posts" does not contain the post_id.

 

i was thinking i could select all the data in "posts", create a while loop, and inside that loop select the data in "opened_posts" where the id = post_id, and if no results come up, keep looping until all rows have been gone through

 

but honestly, is there a more efficient way of doing this?

[pocobueno1388]

I'm not completely sure about this, but you can try it.

 

SELECT p.col1, p.col2
FROM posts p
INNER JOIN open_posts o
ON o.post_id = p.id

[fenway]

Do you want to find open posts or unread posts?

[scarhand]

Do you want to find open posts or unread posts?

 

find unread posts

[scarhand]

heres what ive got so far ($myid variable has already been set):

 

<?php

$openedpostsresult = mysql_query("SELECT * FROM opened_posts WHERE reader_id = '$myid'");

while ($oprow = mysql_fetch_array($openedpostsresult))
{
  $postid = $oprow['post_id'];

  $postsresult = mysql_query("SELECT * FROM posts WHERE id != '$postid'");

  while ($prow = mysql_fetch_array($postsresult))
  {
    $prow['message'] = $message;
  
    echo $message;
  }
}

?>

 

i havent tested this out yet but it should give you an idea of what im trying to do. is there a more efficient way of coding this?

[fenway]

Try this:

 

SELECT p.*
FROM posts AS p
LEFT JOIN open_posts AS o ON o.post_id = p.id
WHERE o.post_id IS NULL