Why this SQL syntax error?

Asked

Viewed 142 times

0

$termos = (explode('/', implode('/', $_GET['tipo'])));
print_r($termos);

Array
(
    [0] => CASA
    [1] => CASA EM CONDOMINIO
    [2] => COBERTURA
)

I have a syntax error in this SQL that makes loops to add as many terms are needed that are captured inside array above:

$sql = array('0');
foreach($termos as $termo){
    $sql[] = 'CATEGORIA LIKE %'.$termo.'%';
}

$sql = 'SELECT * FROM imovel WHERE ' .implode(' OR ', $sql);

Syntax error or access Violation: 1064

I can’t figure out which syntax error is alerted.

mysql sql
  • 1

    Didn’t forget to put single quotes on the term of LIKE? Like: CATEGORIA LIKE '%CASA%'.

  • Echo this SQL string. This array(0), for example, would not be producing something like WHERE 0 OR CATEGORIA LIKE...?

  • @Brunoaugusto, this one WHERE 0 OR ... works. Here is an Sqlfiddle: http://sqlfiddle.com/#! 2/1c439/3

  • Link did not open as expected.

  • Jeez, it’s out of the air... When you come back you’ll see how it works.

2 answers

4


Your script is generating an SQL with the clause LIKE without the simple quotation marks.

Something like:

SELECT *
FROM imovel
WHERE 0
OR CATEGORIA LIKE %CASA%
OR CATEGORIA LIKE %CASA EM CONDOMINIO%
OR CATEGORIA LIKE %COBERTURA%

Which is not valid according to the operator’s documentation LIKE of .

The solution is to include the quotation marks in the construction of your Array, as:

foreach($termos as $termo){
    $sql[] = 'CATEGORIA LIKE \'%'.$termo.'%\'';
}

//ou

foreach($termos as $termo){
    $sql[] = "CATEGORIA LIKE '%".$termo."%'";
}

  • I realized that to include the quotes, you need to give backslash, good to know this, I ended up getting another way but its application is correct. Thank you.

1

I got the syntax error right and the code went like this:

$sql = array('0');
foreach($words as $word){
    $sql[] = "CATEGORIA LIKE '%{$word}%'";
}

$sql    = 'SELECT * FROM imovel WHERE ' .implode(' OR ', $sql);
$sql   .= ' ORDER BY IMO_CODIGO DESC LIMIT '.$inicio. ', '. $limite;

With this code it is possible to recover terms from arrays, be as many and make a query one by one to have the database return. Example:

Facade colors:

Array
(
    [0] => VERDE
    [1] => VERMELHO
    [2] => AZUL
    [3] => AMARELO
    [4] => BRANCO
    [5] => PRETO
    [6] => CINZA
)

Case this array is the result of a checkbox for example, this statement returns the facades with the selected colors.

Thank you Sopt.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.