MySQL LIKE query not working

Question

I m using some advanced mysql query for search some data from the database. But when I added LIKE WHERE developer LIKE %Lanterra% to the query, it will not work.

working query.

$query = sprintf("SELECT *, ( 3959 * acos( cos( radians('%s') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( latitude ) ) ) ) AS distance FROM condo HAVING distance < '%s' ORDER BY distance LIMIT 0 , 2500",
    mysql_real_escape_string($center_lat),
    mysql_real_escape_string($center_lng),
    mysql_real_escape_string($center_lat),
    mysql_real_escape_string($radius));

When I added LIKE function.(not working)

$query = sprintf("SELECT *, ( 3959 * acos( cos( radians('%s') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( latitude ) ) ) ) AS distance FROM condo WHERE developer LIKE %Lanterra% HAVING distance < '%s' ORDER BY distance LIMIT 0 , 2500",
    mysql_real_escape_string($center_lat),
    mysql_real_escape_string($center_lng),
    mysql_real_escape_string($center_lat),
    mysql_real_escape_string($radius));

Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo). — Jay Blanchard, Mar 02 '15 at 17:33
Ok... can you see any kind of error reported from PHP or MySQL? — bcesars, Mar 02 '15 at 17:37
@dumidu. If not working, then there is an error PHP or MySQL should report. Please, include this line at the top of your php file `error_reporting(E_ALL); ini_set('display_errors', 1);`. Also, you can check your `php_error_log` and `mysql_error.log. — bcesars, Mar 02 '15 at 17:43
@bcesars yes. you are correct. There is an error. Warning: sprintf(): Too few arguments in C:\wamp\www\gmap\test.php on line 29 — Dumindu Madushanka, Mar 02 '15 at 17:51
@dumidu why u use printf function in sql query? and assign to $query? — ErasmoOliveira, Mar 02 '15 at 18:00
hmmm... Its because sprintf is recognizing `%Lanterra%` with `%` that can be declared and pass this value as placeholder. Try to escape then with this: `\%Lanterra\%` — bcesars, Mar 02 '15 at 18:01
Did you include single quotes? I'll encourage you to check `mysql_error.log` as well. — bcesars, Mar 02 '15 at 18:08
There are no error in mysql_error.log. I try these too. WHERE developer LIKE '\%Lanterra\%' — Dumindu Madushanka, Mar 02 '15 at 18:12
Really the error is % mark. Because when I removed % mark it shows no errors. How to escape that thing ? — Dumindu Madushanka, Mar 02 '15 at 18:22
Yes. The error is on % mark. when you use backslash PHP should recognize it inside the string as just `%`. — bcesars, Mar 02 '15 at 18:23
@dumidu. why are you printing an string and then put it in a variable? There something wrong in your code. `sprintf()` function is useful when you want to print something in user's browser, I don't think this will be useful in your SQL query. I'll post an answer and try to change your code like I do. — bcesars, Mar 02 '15 at 18:40
@bcesars Really. Even I didn't think about that. You are an amazing. I fixed it. I really need to rate you. — Dumindu Madushanka, Mar 02 '15 at 18:45
@dumidu. Good to know it works. Is posted an answer that could help you. =) — bcesars, Mar 02 '15 at 18:49

manowar_manowar · Answer 1 · 2022-03-06T10:24:05.020

4

SELECT * FROM `table` WHERE myfield like '%abc%';

You must use ' (single quotes)

edited Mar 06 '22 at 10:24

answered Mar 02 '15 at 17:31

manowar_manowar

1,198
2
15
32

FROM condo WHERE developer LIKE '%Lanterra%' I try. but it also not working. – Dumindu Madushanka Mar 02 '15 at 17:36
@ErasmoOliveira sorry. what you mean by "Manowar rules \m/" ? – Dumindu Madushanka Mar 02 '15 at 17:56

score 2 · Accepted Answer · answered Mar 02 '15 at 18:48

I don't think sprintf() function will work to include sql statement and the store into a variable.

Try something like this:

$query = "SELECT *, ( 3959 * acos( cos( 
radians('".mysql_real_escape_string($center_lat)."') ) * cos( radians( latitude ) ) 
* cos( radians( longitude ) - radians('".mysql_real_escape_string($center_lng)."') ) 
+ sin( radians('".mysql_real_escape_string($center_lat)."') ) 
* sin( radians( latitude ) ) ) ) AS distance FROM condo
 HAVING distance < '".mysql_real_escape_string($radius))."'
 ORDER BY distance LIMIT 0 , 2500";

And then you can execute your query.

But BECAREFUL with mysql_* .

As Jay said in comments is true:

"Consider using mysqli with prepared statements, or PDO with prepared statements, they're much safer."

OscarGarcia · Answer 3 · 2015-03-02T19:31:13.327

1

Please, take care of this:

Inside sprintf strinf format '%' characters must be escaped as '%%'.
Only char strings must be single quoted with mysql_real_escape_string.
mysql_* functions are deprecated, you must use prepared statements with mysqli or PDO:
- Prepared statements with mysqli.
- Prepared statements with PDO.

Nested query:

$query = sprintf("SELECT * FROM (SELECT *,
    ( 3959 * acos( cos( radians( %F ) ) * cos( radians( latitude ) ) *
    cos( radians( longitude ) - radians( %F ) ) + sin( radians( %F ) ) *
    sin( radians( latitude ) ) ) ) AS distance FROM condo WHERE
    developer LIKE '%%Lanterra%%') WHERE distance < %F
    ORDER BY distance LIMIT 0 , 2500",
    floatval($center_lat),
    floatval($center_lng),
    floatval($center_lat),
    floatval($radius)
);

Original query:

$query = sprintf("SELECT *, ( 3959 * acos( cos( radians( %F ) ) * cos( radians( latitude ) ) *
    cos( radians( longitude ) - radians( %F ) ) + sin( radians( %F ) ) *
    sin( radians( latitude ) ) ) ) AS distance FROM condo HAVING distance < %F
    WHERE developer LIKE '%%Lanterra%%' 
    ORDER BY distance LIMIT 0 , 2500",
        floatval($center_lat),
        floatval($center_lng),
        floatval($center_lat),
        floatval($radius)
);

Best regards.

edited Mar 02 '15 at 19:31

answered Mar 02 '15 at 18:22

OscarGarcia

1,895
16
16

What error? Could you write it? PS: Ahhh, sorry! distance couldn't be used in ORDER BY! wait a minute :) I'll write a nested statement. – OscarGarcia Mar 02 '15 at 18:27
Warning: sprintf(): Too few arguments in C:\wamp\www\gmap\test.php on line 30 – Dumindu Madushanka Mar 02 '15 at 18:31
Now both of them will escape sprintf. %Landerra% could be misinterpreted because %L could be a special char, so we need to write it as \%Landerra\%. Try now. – OscarGarcia Mar 02 '15 at 18:35
Yes. % mark is the problem. but '\%Lanterra\%' also shows same error. – Dumindu Madushanka Mar 02 '15 at 18:38
Sorry!! %% as single %! Hahaha. Try again, write %% instead \% or single %. – OscarGarcia Mar 02 '15 at 18:41
This type of problems could be easily avoided using prepared statements from mysqli_* functions (or object mysqli) or using PDO. Take a look: http://php.net/mysqli.prepare and http://php.net/pdo.prepare – OscarGarcia Mar 02 '15 at 18:48
You can still using sprintf, but remember not to quote numbers (only strings) and to escape % characters with double % (%%) inside format string. – OscarGarcia Mar 02 '15 at 18:50

score 0 · Answer 4 · answered Mar 02 '15 at 18:13

0

You can try instead double quotes " I've never used the LIKE keyword without them.

SELECT * FROM `db`.`table` WHERE `time` LIKE "%20150105%";

answered Mar 02 '15 at 18:13

Michael Scola

84
2

It is not this much simple. – Dumindu Madushanka Mar 02 '15 at 18:18

MySQL LIKE query not working

4 Answers4

Nested query:

Original query: