SQL Query help

General PHP Help
#1

Hi. I’m in need of some help. I’m trying to make my php query work for 2 types of searches when searching a map database. I.m executing this by url.

This is what I have now:
$query = “SELECT *, ( 3959 * acos( cos( radians($latitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians($longitude) ) + sin( radians($latitude) ) * sin( radians( latitude ) ) ) ) AS distance FROM UNA HAVING distance < 30 ORDER BY distance LIMIT 0 , 50”;

I call this search by using
http://example.com/mapdata.php?latitude=30.549&longitude=-90.419

This works fine. But I would also be able to search by a zip code function, such as:
http://example.com/mapdata.php?zip=70401

How can I add Zip=$zip to my original code so I can use either url? I tried using the OR function and couldn’t get it to work. I’m a newb so any help is appreciated!

#2

Do you want that your query by zip code returns all zip codes with set distance from given zip code? If so, you can first query latitude and longitude for this zip code, then do the same query what you have now:
[php]<?php
if(isset($_REQUEST[‘zip’]){
$query = “SELECT latitude,longitude from UNA WHERE Zip=”.$zip;
$r = mysql_query($query);
if(mysql_num_rows($r)){
$f = mysql_fetch_array($r);
$latitude = $f[‘latitude’];
$longitude = $f[‘longitude’];
}
}

$query = “SELECT *, ( 3959 * acos( cos( radians($latitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians($longitude) ) + sin( radians($latitude) ) * sin( radians( latitude ) ) ) ) AS distance FROM UNA HAVING distance < 30 ORDER BY distance LIMIT 0 , 50”;

// the rest of your current code goes here

?>[/php]

#3

I tried using this code an it gave me error Parse error: syntax error, unexpected ‘{’ in …

So will this code allow me to search by lat and long, or have the choice to search by zip code and show all the lat and long near that zip code?

Thanks for the help!

#4

I believe i got it working! It was just missing a “)” if(isset($_REQUEST[‘zip’])){

Thank!!!

#5

Having one more problem. When I do a search result for a zip that doesn’t exist, I need it to return no results. Below is what I have so far

[php]
// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
die ('Can’t use db : ’ . mysql_error());
}

if(isset($_REQUEST[‘zip’])){
$query = “SELECT latitude,longitude from UNA WHERE Zip=”.$zip;
$r = mysql_query($query);
if(mysql_num_rows($r)){
$f = mysql_fetch_array($r);
$latitude = $f[‘latitude’];
$longitude = $f[‘longitude’];
}
}

$query = “SELECT *, ( 3959 * acos( cos( radians($latitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians($longitude) ) + sin( radians($latitude) ) * sin( radians( latitude ) ) ) ) AS distance FROM UNA HAVING distance < 30 ORDER BY distance LIMIT 0 , 50”;

$result = mysql_query($query);
if (!$result) {
die('Invalid query: ’ . mysql_error());
}

[/php]

#6

Here I’ve added all kind of validation for your script :slight_smile:
[php]if(isset($_REQUEST[‘zip’])){
$query = “SELECT latitude,longitude from UNA WHERE Zip=”.$_REQUEST[‘zip’];
$r = mysql_query($query);
if(mysql_num_rows($r)){
$f = mysql_fetch_array($r);
$latitude = $f[‘latitude’];
$longitude = $f[‘longitude’];
}
else{ // zip not found
die('Invalid zip code: '.$_REQUEST[‘zip’]);
}
}
elseif(!isset($_REQUEST[‘latitude’]) or !isset($_REQUEST[‘longitude’])){ // no zip, no coordinates
die(‘Please specify zip code OR latitude and longitude’);
}

$query = “SELECT *, ( 3959 * acos( cos( radians($latitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians($longitude) ) + sin( radians($latitude) ) * sin( radians( latitude ) ) ) ) AS distance FROM UNA HAVING distance < 30 ORDER BY distance LIMIT 0 , 50”;

$result = mysql_query($query);
if (!$result) {
die('Invalid query: ’ . mysql_error());
}[/php]

#7

Hi! Thanks for all the help. The only issue I’m running into is, I am parsing this information into XML. So when there is no zip code match, it doesn’t do anything, so the xml file still exists, but just no results?

#8

I know this probably looks a mess, but it’s close to what I’m trying to do. :-X

[php]
if(isset($_REQUEST[‘zip’])){
$query = “SELECT latitude,longitude from UNA WHERE Zip=”.$_REQUEST[‘zip’];
$r = mysql_query($query);
if(mysql_num_rows($r)){
$f = mysql_fetch_array($r);
$latitude = $f[‘latitude’];
$longitude = $f[‘longitude’];
$result = mysql_query($query);
}
}

if(isset($_REQUEST[‘latitude,longitude’])){
$query = “SELECT *, ( 3959 * acos( cos( radians($latitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians($longitude) ) + sin( radians($latitude) ) * sin( radians( latitude ) ) ) ) AS distance FROM UNA HAVING distance < 30 ORDER BY distance LIMIT 0 , 50”;
$result = mysql_query($query);
}
[/php]

#9

Is what I’m trying to do even possible? Having 2 choices of queries? Would the OR statement be useful in any way?

Sponsor our Newsletter | Privacy Policy | Terms of Service