PHP Select Statement MySql Syntax

[dpalame]

Hello,

 

I have a select statement that works great:

 

"SELECT *, ( 3959 * acos( cos( radians($lat) ) * cos( radians( loc_LAT_centroid ) ) * cos( radians( loc_LONG_centroid ) - radians($lon) ) + sin( radians($lat) ) * sin( radians( loc_LAT_centroid ) ) ) ) AS distance FROM allStores  HAVING distance <= '$miles'  ORDER BY distance"

 

I want to add one more variable to check in the table.  Something like WHERE name LIKE '%$name%'  Can't figure out where to put it in the select statement.  Any help would be appreciated.  Thank you.

 

Damon

[AyKay47]

"SELECT *, ( 3959 * acos( cos( radians($lat) ) * cos( radians( loc_LAT_centroid ) ) * cos( radians( loc_LONG_centroid ) - radians($lon) ) + sin( radians($lat) ) * sin( radians( loc_LAT_centroid ) ) ) ) FROM allStores AS distance WHERE distance <= '$miles' AND name LIKE "%$name%" ORDER BY distance"

[dpalame]

Thank you but that code is not returning any rows.  The $name variable is an array.  Is that what is messing it up?

[dpalame]

I tried the code without the extra variable and it doesn't work:

 

"SELECT *, ( 3959 * acos( cos( radians($lat) ) * cos( radians( loc_LAT_centroid ) ) * cos( radians( loc_LONG_centroid ) - radians($lon) ) + sin( radians($lat) ) * sin( radians( loc_LAT_centroid ) ) ) ) FROM allStores AS distance WHERE distance <= '$miles'  ORDER BY distance"

[TeNDoLLA]

Apparently you can't use an array as value for SQL. You could create the extra part for the sql query manually and then add it to the SQL-clause.

e.g.

$name = array('test', 'test2', 'test3');
$sqlExtra = "AND (";
foreach ($name as $val)
{
$sqlExtra .= "name LIKE '%$val%' OR ";
}
$sqlExtra = substr($sqlExtra, 0, -4) . ')';
var_dump($sqlExtra); // Outputs AND (name LIKE '%test%' OR name LIKE '%test2%' OR name LIKE '%test3%')

 

$sql = "SELECT *, ( 3959 * acos( cos( radians($lat) ) * cos( radians( loc_LAT_centroid ) ) * cos( radians( loc_LONG_centroid ) - radians($lon) ) + sin( radians($lat) ) * sin( radians( loc_LAT_centroid ) ) ) ) FROM allStores AS distance WHERE distance <= '$miles'  $sqlExtra ORDER BY distance"

[dpalame]

Thanks everyone for your help.  Used the following syntax and it works great:

 

$sqlExtra = "(";

foreach ($at2 as $val)

{

$sqlExtra .= "cat_primary LIKE '%$val%' OR ";

}

$sqlExtra = substr($sqlExtra, 0, -4) . ')';

// var_dump($sqlExtra); // Outputs AND (name LIKE '%test%' OR name LIKE '%test2%' OR name LIKE '%test3%')

 

 

$query = "SELECT *, ( 3959 * acos( cos( radians($lat) ) * cos( radians( loc_LAT_centroid ) ) * cos( radians( loc_LONG_centroid ) - radians($lon) ) + sin( radians($lat) ) * sin( radians( loc_LAT_centroid ) ) ) ) AS distance FROM allStores  WHERE $sqlExtra HAVING distance <= '$miles'  ORDER BY distance";