Why this SQL syntax error?

Question

Why this SQL syntax error?

Navigation

#1 by (4 votes)
#2 by (1 votes)

0

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

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

I'm having a syntax error in this SQL that makes loops to add as many terms as needed that are captured within 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 not figure out what the syntax error was.

mysql sql

asked by anonymous 04.10.2014 / 18:03

2 answers

1

I was able to correct the syntax error and the code looks 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 retrieve terms of arrays , whatever they may be and make a query one by one to get the database return. Example:

Facade colors:

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

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

Thank you SOpt.

04.10.2014 / 18:28

Return of content by cURL [closed] How to check WHERE clause with apostrophe in a DELETE operation?

score 4 · Accepted Answer

Your script is generating a SQL with the LIKE clause without the single quotes.

Something like:

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

What 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 , such as:

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

//ou

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