Error in where with variable [closed]

Question

Error in where with variable [closed]

Navigation

#1 by (2 votes)

-1

$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), since $ num is not working in NOT IN, but is normal in echo, how to solve?

php

asked by anonymous 13.03.2017 / 01:31

1 answer

How to handle error when deleting a BD record, which has a foreign key, PHP Exercise: transform decimal to binary and find 1's

score 2 · Accepted Answer

This can be done without the need for PHP, if it is 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 NOT IN to have the id values of the subquery.

Explanations:

NOT IN supports both direct values and id NOT IN (1,2,3,4,5...) or also id NOT IN (SELECT id FROM tabela) , so 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 IN (and also NOT IN , ANY , SOME , ALL ).

In order to solve the problem of LIMIT we create a subquery, so we return to IN the previously filtered values, then:

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

Since every subquery needs a name, the x was added at the end, here I used as nome_da_subquery , it can be any value, it is only a name and is defined by as nome or nome . / p>