CODEWITHSUNDEEP
phpmysqlsqlphpmyadmin
Galadre
Galadre

Reputation: 619

SQL Query works fine in PHPMyAdmin but not with mysql_query

The following SQL query works perfectly fine when I run it with PHPMyAdmin but when I run it from a web page I get an error. I have looked around on StackOverflow for similar questions and have tried their suggestions but alas... Does anyone have an idea why it won't work on the web page?

The SQL query after variables have been put in (example):

SELECT place FROM locations WHERE (( 69.1 * ( latitude - 51.4700000 ) ) * ( 69.1 * (latitude - 51.4700000 ) )) + ( ( 69.1 * ( longitude - 5.5528000 ) * COS( 51.4700000 / 57.3 )) * ( 69.1 * ( longitude - 5.5528000 ) * COS( 51.4700000 / 57.3 ) ) ) < 976.5625 ORDER BY ((69.1 * ( latitude - 51.4700000 ) ) * ( 69.1 * ( latitude - 51.4700000 ) )) + ( ( 69.1 * ( longitude - 5.5528000 ) * COS( 51.4700000 / 57.3 ) ) * ( 69.1 * ( longitude - 5.5528000 ) * COS(51.4700000 / 57.3 ) ) ) ASC

The PHP ($radius and $start are defined a little before this):

$latitude = $start['latitude'];
$longitude = $start['longitude'];
$r = pow($radius, 2);
$inrange = "SELECT place FROM locations_2 WHERE ((69.1 * (latitude - $latitude)) * (69.1 * (latitude - $latitude))) + ((69.1 * (longitude - $longitude) * COS($latitude / 57.3)) * (69.1 * (longitude - $longitude) * COS($latitude / 57.3))) < $r ORDER BY ((69.1 * (latitude - $latitude)) * (69.1 * (latitude - $latitude))) + ((69.1 * (longitude - $longitude) * COS($latitude / 57.3)) * (69.1 * (longitude - $longitude) * COS($latitude / 57.3))) ASC";
$result = mysql_query($inrange) or die(mysql_error());

The error message I get is:

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 ')) * (69.1 * (`latitude` - ))) + ((69.1 * (`longitude` - ) * COS( / 57.3)) * (69' at line 1

What's even stranger is that it worked... I wrote it all down, just the way I'm presenting it to you now, and was happy to see it worked just fine. That was earlier today. Now an hour or so ago I tried it again and for some reason it stopped working. I truly don't remember changing a thing.

EDIT

Thanks everyone. I figured it out and it turned out I made a stupid mistake. It worked earlier today because all the users had their location set. Now, I have a few users that don't so while looping through them the longitude and latitude came up empty.

Upvotes: 0

Views: 298

Answers (2)

Afshin
Afshin

Reputation: 4215

if ((isset($start['latitude']))&&(isset($start['longitude']))){
$latitude = $start['latitude'];
$longitude = $start['longitude'];
$r = pow($radius, 2);
$inrange = "SELECT place FROM locations_2 WHERE ((69.1 * (latitude - $latitude)) * (69.1 *    (latitude - $latitude))) + ((69.1 * (longitude - $longitude) * COS($latitude / 57.3)) * (69.1 * (longitude - $longitude) * COS($latitude / 57.3))) < $r ORDER BY ((69.1 * (latitude - $latitude)) * (69.1 * (latitude - $latitude))) + ((69.1 * (longitude - $longitude) * COS($latitude / 57.3)) * (69.1 * (longitude - $longitude) * COS($latitude / 57.3))) ASC";
$result = mysql_query($inrange) or die(mysql_error());

}else
{
echo "error";
}

or

put $latitude,$latitude into {} in query 

$inrange = "SELECT place FROM locations_2 WHERE ((69.1 * (latitude - {$latitude})) * (69.1 *    (latitude - {$latitude}))) + ((69.1 * (longitude - {$longitude}) * COS({$latitude} / 57.3)) * (69.1 * (longitude - {$longitude}) * COS({$latitude} / 57.3))) < $r ORDER BY ((69.1 * (latitude - {$latitude})) * (69.1 * (latitude - {$latitude}))) + ((69.1 * (longitude - {$longitude}) * COS({$latitude} / 57.3)) * (69.1 * (longitude - {$longitude}) * COS($latitude / 57.3))) ASC";

Upvotes: 1

Erik
Erik

Reputation: 2264

From your error message it seems like $latitude and $longitude does not contain any values. You probably want to check that $start is properly set. This is all I can help you with since I do not know what $start is and how you're getting it.

Upvotes: 3

Related Questions