mysql: `id` not in ($ids)

[notset]

Hello,

It always returns all results - even if idis in the $ids array.
Where is the mistake?

 $sql = "SELECT `id` FROM `views` WHERE `user_id` = '".$user_id."'";
$result = mysql_query($sql) OR exit(mysql_error());
$ids = '';
if (mysql_num_rows($result) > 0) {
  while ($row = mysql_fetch_array($result)) {
   $ids[] = $row['id'];
  }
}
$sql = "SELECT `id`,`name`,`url`,`img`,`interval` FROM `ads` WHERE `id` NOT IN ('".$ids."')";
$result = mysql_query($sql) OR exit(mysql_error());

[devinemke]

$sql = 'SELECT * FROM ads WHERE id NOT IN(' . implode(',', $ids) . ')';

however if you are using mySQL 4.1.x or higher you can (and should) do this all in a single query:

$sql = 'SELECT * FROM ads WHERE id NOT IN(SELECT id FROM views WHERE user_id = ' . $user_id . ')';

[notset]

ok, then my full query:

$sql = "SELECT `id`,`name`,`url`,`img`,`interval` FROM `ads` WHERE `type` = '".$ad_type."' AND `id` NOT IN(SELECT `id` FROM `views` WHERE `user_id` = '".$user_id."') AND `batch` > 0 AND `suspended` < 1 AND `amount` > `views` ORDER BY RAND() LIMIT ".$limit;

Returns an error:

You have an error in your SQL syntax near 'SELECT id FROM views WHERE user_id = '<...skiped...>') A' at line 1

[notset]

Maybe somebody could help me? 🙁

Where is the problem?

NOT IN(SELECT id FROM views WHERE user_id = '".$user_id."') ?

[laserlight]

Print out the final SQL statement generated and have a look yourself.

[notset]

$sql returns:

SELECT id,name,url,img,interval FROM ads WHERE type = 'text' AND id NOT IN(SELECT id FROM views WHERE user_id = '75ab71e6ed9e02a13c54127027bf128c') AND batch > 0 AND suspended < 1 AND amount > views ORDER BY RAND() LIMIT 1