Merge two sql queries / php functions into one?

I have two functions which work separately perfectly as two separate forms

The first; (as no one will know what lattitude and longitude their zip code is) changes a zip code into two coordinates (longitute and latitude).

I have a second function performs a search using lattitude and longitude to find stores that are within a certain area.

Both work great but I can't work out how to join the two functions into one. With the second using the results from the first

here is what i have

Function one - on its own page where a form passes the varible zip

function getCoords($zip)
{
	
	echo '<h2>stores</h2><p>you searched for '.$zip.'</p>';
	$db_conn = db_connect(); // database connection

	// turn long and lat into zipcode
    $query = "SELECT latitude, longitude
	FROM zipcodes
	WHERE '".$zip."' = zip";
}

PHP:

Function two - again on its own page where a form passes the varibles longitude & latitude

     $query = "SELECT store_id, store_name, store_address, store_zipcode,
   3963 * acos(cos(radians(90-latitude ))*cos(radians(90-'".$latitude."'))+sin(radians(90-latitude ))
	* sin(radians(90-'".$latitude."'))
	*cos(radians(longitude- '".$longitude."')) 
	) AS distance
	FROM stores, zipcodes
	WHERE stores.store_zipcode = zipcodes.zip
	HAVING (distance < '".$area."')
	ORDER BY distance asc";
}

PHP:

Can this be done? Thanks

 

You can store the values of lat. and long. in php variables,(which you got from DB by zip code). Then call the second function from within the first one.
Make sure that php variables you use are named same as they appear in second query.

 

The best way to do it would be using a UNION ALL

google that...

 

It looks to me your first function is redundant since you are already doing a JOIN in your second query. Hmm, try bypassing the first function and use this query for your second query:

$query = "SELECT store_id, store_name, store_address, store_zipcode,
3963 * acos(cos(radians(90-latitude ))*cos(radians(90-latitude))+sin(radians(90-latitude ))
* sin(radians(90-latitude))*cos(radians(longitude-longitude)) ) AS distance FROM stores, zipcodes
WHERE stores.store_zipcode = zipcodes.zip AND '".$zip."' = zipcodes.zip HAVING (distance < '".$area."') ORDER BY distance asc";

Code (markup):

 

Thanks Rosytoes,

but i tried using your code and somethings not right. The zip is passed to the form but no results are returned.

function getCoords($zip)
{
     
	
echo "<h1>getDistance!</h1>";
	echo '<h2>stores</h2><p>you searched from '.$zip.'</p>';
	$db_conn = db_connect(); // database connection
   
$question = "SELECT store_id, store_name, store_address, store_zipcode, 3963 * acos(cos(radians(90-latitude ))*cos(radians(90-latitude))+sin(radians(90-latitude ))
* sin(radians(90-latitude))*cos(radians(longitude-longitude)) ) AS distance FROM stores, zipcodes
WHERE stores.store_zipcode = zipcodes.zip AND '".$zip."' = zipcodes.zip HAVING (distance < '".$area."') ORDER BY distance asc";
	
	$answer=mysql_query($question) or die(mysql_error());

echo "<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\" width=\"100%\" ><tr>";
	
	// display results
	while ($array = mysql_fetch_array($answer)) {
     	$store_name = $array['store_name'];	
	    $store_zipcode = $array['store_zipcode'];
		$distance = $array['distance'];
		$distance = round($distance);
		$store_address = $array['store_address'];

   echo "$distance";
}

echo "</table>";
// if no results are found display message
$counst=mysql_num_rows($answer) ;
if($counts==0){
echo "There are no stores within the area searched. Try widening your search.";
}
	}

PHP:

 

thanks for trying guys.

jamus, thanks for the form! just need to nail the query now!

 

how about replacing '".$zip."' and '".$area."' with just '$zip' and '$area'. Oh and is $area actually declared?

 

WHERE stores.store_zipcode = zipcodes.zip AND '$zip' = zipcodes.zip HAVING (distance < '$area') ORDER BY distance asc";

PHP:

I get no results using this. hmmmm

 

is $area being passed into the function?

 

I can see it is being passed.

im using

	echo '<h2>stores</h2><p>you searched from '.$zip.' within '.$area.' </p>';

PHP:

which displays it fine.

the query is failing still

 

I think I know why, your first query returns a pair of values from a zip code. Your second query returns a set of values within an area. My query made them the same, therefore you are getting no results. Not sure if you can merge them into one single query now.

 

well i want to use the pair of values returned "FROM" the first query "IN" the second query to do the search.

Could I not do two separate queries in two separate functions? The first then the second with values being passed between them?

 

Your thread title was "Merge two sql queries / php functions into one?", so I thought you wanted to merge 2 queries into one query in one function. You can certainly merge 2 queries in 2 functions into 2 queries in 1 function, That's easy enough to do. I'll get back to you later if you still need help.

 

Sorry to be a pain! but im going round in circles!

could show me how to merge the two queries into one function?

 

This is weird. I replied this afternoon and now the post is nowhere to be seen. I'll have another go tomorrow.

 

SELECT stores.store_id
     , stores.store_name
     , stores.store_address
     , stores.store_zipcode
     , 3963 * acos(cos(radians(90-zipcodes.latitude)) 
                 * cos(radians(90-startzip.latitude))
                 + sin(radians(90-zipcodes.latitude)) 
                 * sin(radians(90-startzip.latitude))
                 * cos(radians(zipcodes.longitude
                             - startzip.latitude)) 
                  ) AS distance 
  FROM (
       SELECT latitude
            , longitude 
         FROM zipcodes 
        WHERE $zip = zip
       ) as startzip 
cross
  join stores
inner
  join zipcodes 
    on zipcodes.zip = stores.store_zipcode 
HAVING distance < 1000 
ORDER 
    BY distance asc

PHP:

The distances seem WAY too high now though.

Not allowed to add links - sorry

 

Take 2.
You will need to pass the variable $area into the function.

function getCoords($zip,$area)
{
echo '<h2>stores</h2><p>you searched for '.$zip.'</p>';
$db_conn = db_connect(); // database connection

// turn long and lat into zipcode
$query = "SELECT latitude, longitude FROM zipcodes WHERE zip='$zip'";
$result=mysql_query($query) or die(mysql_error());
while ($r = mysql_fetch_array($result)) { $lat=$r[0]; $long=$r[1]; }

$query = "SELECT store_id, store_name, store_address, store_zipcode, 3963 * acos(cos(radians(90-latitude ))*cos(radians(90-'$lat'))+sin(radians(90-latitude )) * sin(radians(90-'$lat')) *cos(radians(longitude- '$long')) ) AS distance FROM stores, zipcodes WHERE stores.store_zipcode = zipcodes.zip HAVING (distance < '$area') ORDER BY distance asc";
$result=mysql_query($query) or die(mysql_error());
if (!mysql_num_rows($result)) {
// display results here
}
else echo "There are no stores within the area searched. Try widening your search.";
}

Code (markup):

 

The first part works
im using
echo "lat = $lat <br>";
echo "long = $long <br>";
just before the second query just to show they are being returned

In the second query I fixed the distance to 2000

HAVING (distance < 2000) ORDER BY distance asc";

PHP:

but no results are being displayed

// display results here


	echo "<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\" width=\"100%\" ><tr>";
	
	// display results
	while ($list = mysql_fetch_array($result)) {
     	$store_name = $list['store_name'];
	    $store_zip = $list['store_zipcode'];
		$distance = $list['distance'];
		$distance = round($distance);
		$store_address = $list['store_address'];

   echo "<td colspan=\"3\" align=\"left\"><strong>$store_name</strong></td>";
   echo "<td align=\"right\">$distance miles away</td></tr>";
   echo "<tr><td colspan=\"3\" align=\"left\" width=\"130px\">$store_address $store_zip $store_zip_2</td>";
   echo "<td align=\"left\"><a href=\"http://www.multimap.com/map/browse.cgi?client=public&search_result=&pc=$store_zip$store_zip_2\" target=\"_blank\">View Map</a></td>";
   echo "<tr><td align=\"left\">&nbsp;</td><tr>";   
	echo "</tr>";
}
echo "</table>";
}
}

PHP:

 

could you echo the 2nd query and put it here?

 

where? Sorry, how do you mean?
Really appreciate you looking at this!

so close I can smell it lol