Error in Where with variable

Question

$inter = $pdo->query("SELECT * FROM noticias WHERE status = 'ativo' AND fixo != 's' AND comentarios != 's' ORDER BY id DESC LIMIT 5");
                $i = 1;
                $num = '';
                while($in = $inter->fetch()) {
                    if($i == 1){               
                        $num .= "'".$in['id']."', ";               
                    }elseif($i == 2){               
                        $num .= "'".$in['id']."', ";             
                    }elseif($i == 3){               
                        $num .= "'".$in['id']."', "; 
                    }elseif($i == 4){               
                        $num .= "'".$in['id']."', "; 
                    }elseif($i == 5){               
                        $num .= "'".$in['id']."'"; 
                    }
                $noticias = $pdo->query("SELECT * FROM noticias WHERE status='ativo' AND cat_id='Notícias' AND fixo != 's' AND comentarios != 's' AND id NOT IN ( $num ) ORDER BY id DESC LIMIT 3");
                    echo $num;
                $i++;} 
                while ($not = $noticias->fetch(PDO::FETCH_ASSOC)) {

The error happens in Where AND id NOT IN ( $num ), as $num is not working in NOT IN, but is normal in echo, how to solve?

Answer 1

This can be done without the need for PHP, if you are Mysql you can simply use:

SELECT   * 
FROM     noticias 
WHERE    status='ativo' 
AND      cat_id='Notícias' 
AND      fixo != 's' 
AND      comentarios != 's' 
AND      id NOT IN 
         ( 
                SELECT id 
                FROM   ( 
                                SELECT   id 
                                FROM     noticias 
                                WHERE    status = 'ativo' 
                                AND      fixo != 's' 
                                AND      comentarios != 's' 
                                ORDER BY id DESC limit 5) x) 
ORDER BY id DESC 
LIMIT 3

This will cause the NOT IN has the values of id subquery.

---

Explanations:

The NOT IN supports both direct values and id NOT IN (1,2,3,4,5...) or also id NOT IN (SELECT id FROM tabela), soon this will work:

SELECT * FROM tabela WHERE alguma_coisa IN (SELECT id FROM tabela_dois);

However Mysql has its limitations and does not support the use of LIMIT within the IN (and also NOT IN, ANY, SOME, ALL).

In order to solve the problem of LIMIT we created a subquery, so we returned to the IN the pre-filtered values, so:

SELECT * FROM tabela WHERE alguma_coisa IN (
  SELECT id FROM (
    SELECT id FROM tabela_dois ORDER BY id LIMIT 10
  ) as nome_da_subquery
);

As every subquery needs a name has been added x in the end, here I used the as nome_da_subquery, it can be any value, it is only a name and is defined by as nome or nome.