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.
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:
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
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\"> </td><tr>"; echo "</tr>"; } echo "</table>"; } } PHP: