Excluding IDs from SELECT

[Pawn]

My application displays posts based on currentness and popularity, in two distinct lists.

 

Users can also vote on posts, after which they are hidden. The post IDs that users have voted on are stored in a cookie.

 

I need to construct a concise query that prevents "popular" posts and posts that have been voted on from appearing within the "new" list. In my current system, the used values are explicitly excluded from appearing in the query with some very inelegant code.

 

$num_posts = 5;
$sql = "SELECT * FROM posts ORDER BY popularity LIMIT ".$num_posts;
$query = mysql_query($sql);
$excluded_ids = array()
while($row = mysql_fetch_assoc($query)) {
    // ...do some stuff...
    $excluded_ids[] = $row['post_id'];
}
if(!empty($_COOKIE['voted_on'])) {
   // example value "12|19|23|26"
   foreach(explode("|", $_COOKIE['voted_on']) as $val)) {
        $excluded_ids[] = $val;
   }
}
$sql = SELECT * FROM posts WHERE post_id <>";
$num_excluded_ids = count($excluded_ids);
$i = 0;
foreach($excluded_ids as $val) {
    $i++;
    $sql .= $val;
    if($i <= $num_excluded_ids) {
        $sql .= " AND post_id <> ";
    }
}
$sql .= " ORDER BY date_posted LIMIT ".$num_posts;
$query = mysql_query($sql) // ... 

This method is ugly and probably very slow. What would be a better approach?

 

Thanks in advance for anything you can suggest.

[n3r0x]

Something like this should work..

 

#using MySQL function => http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_not-in

#using PHP function => http://se.php.net/manual/en/function.implode.php

$num_posts = 5;
$excluded_ids=0;
if(!empty($_COOKIE['voted_on']))
     $excluded_ids = implode(",",$_COOKIE['voted_on']);


$sql = "SELECT * FROM posts WHERE post_id NOT IN(".$excluded_ids.") ORDER BY popularity LIMIT ".$num_posts;
$query = mysql_query($sql);
/*
Do what you want to do below here..
*/

 

NOTE: Didn´t have time to test this...

[Pawn]

Thanks n3r0x, that looks like it'll do the trick.

[BlueSkyIS]

some of us like to quote every value in sql. here is one way to do that:

 

$excluded_ids = implode("','",$_COOKIE['voted_on']); // added single quotes on each side of comma

$sql = "SELECT * FROM posts WHERE post_id NOT IN('".$excluded_ids."') ORDER BY popularity LIMIT ".$num_posts; // added single quotes before and after $excluded_ids

[Pawn]

Thanks for the tip, BlueSky!