Error "check the manual that corresponds to your MySQL server version for the right syntax to use near on SQL LIKE" on php

Question

Can't seem to use this, there is always this error. Anyone can help?

Search Query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'WHERE des\n\t\t\tLIKE \'%Hgfddfhbb\'\' at line 10\n'

Version: 5.5.44

$search = $json['search'];

        $sql = 
        "SELECT des, lat, lng,
            ( 6371 * acos( cos( radians($lat) ) * 
            cos( radians( lat ) ) * 
            cos( radians( lng ) - radians($lng) ) + 
            sin( radians($lat) ) * 
            sin( radians( lat ) ) ) ) 
        AS distance 
        FROM problem 
        HAVING distance < 5
        WHERE des
        LIKE '%$search%'";

Please read the select query synax, in your query first you use HAVING clause then use WHERE clause. In place of that first write WHERE clause then writee HAVING clause. And one more thing without GROUP BY there is no meaning to use HAVING. — Kausha Mehta, Sep 12 '15 at 11:40

score 1 · Answer 1 · answered Sep 12 '15 at 08:49

if you're using aggregate functions then you must use HAVING otherwise WHERE works fine. as if you pay close attention to error

right syntax to use near \'WHERE

it says just before WHERE clause, and that's only HAVING.

Moreover, if you're intended to use HAVING with WHERE clause, you need to move HAVING after WHERE.

So your query will be look like

$search = $json['search'];

    $sql = 
    "SELECT des, lat, lng,
        ( 6371 * acos( cos( radians($lat) ) * 
        cos( radians( lat ) ) * 
        cos( radians( lng ) - radians($lng) ) + 
        sin( radians($lat) ) * 
        sin( radians( lat ) ) ) ) 
    AS distance 
    FROM problem 
    WHERE des
    LIKE '%$search%'
    HAVING distance < 5 ";

Hope this will work for you

Its works. thanks you so much but more one question. if I put like first, will it find the keyword first then calculate the distance? i wanted to find the distance then keywords which i think is faster. — phongyewtong, Sep 12 '15 at 09:09
you can't calculate distance first, as according to `query execution scheme` `WHERE` is always executed before `SELECT`. more about executions you can have a look here: http://stackoverflow.com/a/24128128/2558525 — Mubin, Sep 12 '15 at 09:22

score 0 · Answer 2 · answered Sep 12 '15 at 08:44

0

Looks like the are a character problem. Try adding addslashes() to $SQL

answered Sep 12 '15 at 08:44

Víctor

3,029
3
28
43

Error "check the manual that corresponds to your MySQL server version for the right syntax to use near on SQL LIKE" on php

2 Answers2