CODEWITHSUNDEEP
phpmysql
Dumindu Madushanka
Dumindu Madushanka

Reputation: 504

MySQL LIKE query not working

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));

Upvotes: 2

Views: 2785

Answers (4)

manowar_manowar
manowar_manowar

Reputation: 1150

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

You must use ' (single quotes)

Upvotes: 4

OscarGarcia
OscarGarcia

Reputation: 2114

Please, take care of this:

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.

Upvotes: 2

bcesars
bcesars

Reputation: 1012

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:

Upvotes: 2

Michael Scola
Michael Scola

Reputation: 84

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

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

Upvotes: 0

Related Questions