Help with zip radius search

[siamsam]

I downloaded a zip code radius search script and I actually got it to work, somewhat.  I attached the include class script and the demo. This works...

<?php 
$zip = "-1";
if (isset($_GET['zip'])) {
$zip = (get_magic_quotes_gpc()) ? $_GET['zip'] : addslashes($_GET['zip']);
}
mysql_select_db($database_connDB1, $connDB1) or die(mysql_error());
$z = new zipcode_class;
$zips = $z->get_zips_in_range($zip, 10, _ZIPS_SORT_BY_DISTANCE_ASC, true); 

if ($zips === false) echo 'Error: '.$z->last_error;
else { 
foreach ($zips as $key => $value) {
      echo "Zip code <b>$key</b> is <b>$value</b> miles away from <b>'$zip'</b>.<br />";
   }
}
?>

It returns all the zips within the range of the url variable within 10 miles. The problem I am having is, I need to use those values and compare it to matching advertisers' zips in the db. I have searched on how to do this and I just end up with a ton of errors. Here is my sql (which obviously doesn't work):

mysql_select_db($database_connDB2, $connDB2);
$query_getCat = ("SELECT subcategories.subcategory, subcategories.cat_id, advertisers.adv_id, advertisers.zip_code, couponinfo.coupon_id FROM subcategories, advertisers, couponinfo WHERE (subcategories.subcat_id = advertisers.subcat_id OR subcategories.subcat_id = advertisers.subcat_id2) AND (advertisers.adv_id = couponinfo.adv_id) AND (advertisers.zip_code IN ('$zips')) ORDER BY subcategories.subcategory");
$getCat = mysql_query($query_getCat, $connDB2) or die(mysql_error());

I have tested the sql query with a constant zip code and it returns what I want it to return, but I cannot seem to get the result of the first into the second. I have a feeling it is because I am not pulling the right array from the include class script? And of course that I am new to all this...

 

Would really appreciate the help! Thanks.

 

 

[attachment deleted by admin]

[joel24]

is get_zips_in_range() returning any values?

try put in a print_r($zips) which will print all the values in the $zips array.

i.e.

<?php
$zip = "-1";
if (isset($_GET['zip'])) {
   $zip = (get_magic_quotes_gpc()) ? $_GET['zip'] : addslashes($_GET['zip']);
}
mysql_select_db($database_connDB1, $connDB1) or die(mysql_error());
$z = new zipcode_class;
$zips = $z->get_zips_in_range($zip, 10, _ZIPS_SORT_BY_DISTANCE_ASC, true);

if ($zips === false) echo 'Error: '.$z->last_error;
else {
foreach ($zips as $key => $value) {
      echo "Zip code <b>$key</b> is <b>$value</b> miles away from <b>'$zip'</b>.<br />";
   }
}
?>

 

and the zipcode class you're using requires a database table "zip_code" filled with zip codes. Have you got this database table setup?

[siamsam]

yep, the database is set up and the get_zips_in_range returns all the values without a problem. I echoed them out to the top of the page to make sure that part was working. It returns all the zips and the distance from the zip variable in the url. I just can't figure out how to search within those results in my query. It may be that I am trying to compare too much in the WHERE statement, but even still - it doesn't recognize $zips as holding any values. I am lost - but it seems like I just need a bridge between the two that I am completely missing.

[joel24]

the mysql IN statement works like so

 SELECT * FROM table WHERE id IN (12, 16, 13) 

 

in your case you need to organise the zip codes like so (zip1, zip2, zip3) so that MySQL can interpret it.

you could use a foreach loop and write all the zips into a string like (zip1, zip2, zip3, zip4, etc etc). i.e. expanding on your code posted before

<?php
$zip = "-1";
if (isset($_GET['zip'])) {
   $zip = (get_magic_quotes_gpc()) ? $_GET['zip'] : addslashes($_GET['zip']);
}
mysql_select_db($database_connDB1, $connDB1) or die(mysql_error());
$z = new zipcode_class;
$zips = $z->get_zips_in_range($zip, 10, _ZIPS_SORT_BY_DISTANCE_ASC, true);

$sqlZips = '';

if ($zips === false) echo 'Error: '.$z->last_error;
else {
foreach ($zips as $key => $value) {
      //SQL STRING YOU CAN REMOVE single quote around $value if values are all numbers
$sqlZips .= "'$value', ";
   }
}

//now SQL should work with IN command
//this is from what you posted earlier
//just modified (advertisers.zip_code IN ($sqlZips))
mysql_select_db($database_connDB2, $connDB2);
$query_getCat = ("SELECT subcategories.subcategory, subcategories.cat_id, advertisers.adv_id, advertisers.zip_code, couponinfo.coupon_id FROM subcategories, advertisers, couponinfo WHERE (subcategories.subcat_id = advertisers.subcat_id OR subcategories.subcat_id = advertisers.subcat_id2) AND (advertisers.adv_id = couponinfo.adv_id) AND (advertisers.zip_code IN ($sqlZips)) ORDER BY subcategories.subcategory");
$getCat = mysql_query($query_getCat, $connDB2) or die(mysql_error());

//now do something with the query.
?>

 

[siamsam]

I tried adding in what you posted, but now the page fails to display completely - no error message or anything, just a blank page. Odd. Any other ideas?

[joel24]

did you edit that code to do something with the database query results... at the end where I wrote "now do something with the query."?

 

You can put this at the end and echo all the results, it will only echo all the rows returned from the database, what you do with the information returned from database is up to you.

$i = 0;
while ($row=mysql_fetch_array($getCat))
{
echo "<b>Row $i.</b><br/>";
$i++;
print_r($row);
}

[siamsam]

I did, I am having it echoing out in a do while loop in a table on the page. If I put an actual zip code in the WHERE IN clause I get all the results I am looking for, so I know my query is ok. I just can't get it to pass the values of the zips_in_range query to a variable that I can use in the IN clause. This is driving me nuts, I am sure it is just some simple 2-3 lines of code needed to get those values...I just can't figure out where to get them. Where you able to look at the zips.class script? I know it is in there somewhere, but since it is filled with other functions, I can't tell what to use! Is the result of the zips_in_range query $r, $return, $details or something I am completely missing? Uggghhh...

[joel24]

yes I looked at the zipcode class file,

 

In the file, the line which structures the details returned is

$return[str_pad($row[0], 5, "0", STR_PAD_LEFT)] = round($dist, $this->decimals);

in the "function get_zips_in_range(".

 

It is returning an array with an index of the zip with "0" added infront. i.e. 2343 would be $array[02343], and then the distance as the value.

 

If you just want the zips and not the distance, I would change this to

$return[] = $row[0];

Then the function will return an array with all your zips in range, then to use it in mysql you will have to implode that array like so

$z = new zipcode_class;
$zips = $z->get_zips_in_range($zip, 10, _ZIPS_SORT_BY_DISTANCE_ASC, true);

//for sql, implode with ', ' to seperate zips.
$sqlZIPS = implode(", ", $zips);

//now do search with that
mysql_select_db($database_connDB2, $connDB2);
$query_getCat = ("SELECT subcategories.subcategory, subcategories.cat_id, advertisers.adv_id, advertisers.zip_code, couponinfo.coupon_id FROM subcategories, advertisers, couponinfo WHERE (subcategories.subcat_id = advertisers.subcat_id OR subcategories.subcat_id = advertisers.subcat_id2) AND (advertisers.adv_id = couponinfo.adv_id) AND (advertisers.zip_code IN ($sqlZIPS)) ORDER BY subcategories.subcategory");
$getCat = mysql_query($query_getCat, $connDB2) or die(mysql_error());

[siamsam]

Ok - that worked. joel24 rocks!

 

Thanks so much for your help. The only problem is...I don't want to display the distance, but I wanted to sort the results by distance - showing the results for the data in the closest zips first. If I take distance out, I can't sort it that way?

 

Definitely getting closer though. I am starting to really not like zip codes...

[joel24]

use the sort function

 

$z = new zipcode_class;
$zips = $z->get_zips_in_range($zip, 10, _ZIPS_SORT_BY_DISTANCE_ASC, true);

//sort the array
sort($zips);

//for sql, implode with ', ' to seperate zips.
$sqlZIPS = implode(", ", $zips);

//now do search with that
mysql_select_db($database_connDB2, $connDB2);
$query_getCat = ("SELECT subcategories.subcategory, subcategories.cat_id, advertisers.adv_id, advertisers.zip_code, couponinfo.coupon_id FROM subcategories, advertisers, couponinfo WHERE (subcategories.subcat_id = advertisers.subcat_id OR subcategories.subcat_id = advertisers.subcat_id2) AND (advertisers.adv_id = couponinfo.adv_id) AND (advertisers.zip_code IN ($sqlZIPS)) ORDER BY subcategories.subcategory");
$getCat = mysql_query($query_getCat, $connDB2) or die(mysql_error());

[siamsam]

It didn't like that one! - this is what I got.

 

Warning: sort() expects parameter 1 to be array, boolean given in C:\vhosts\mysite\home.php on line 44

 

Warning: implode() [function.implode]: Invalid arguments passed in C:\vhosts\mysite\home.php on line 46

Error:

 

This is getting so frustrating. Do I have to do something like break apart the array, query the zip_code IN ($sqlZIPS) and then put it back into an array with the distance to sort? 

[joel24]

hmm that would mean the function is no longer passing back an array because I didn't look at the function carefully enough and was trying to pass a constant to the function instead of an integer

I had another look at and the zipclass defines some constants for sorting, and you have to call a constant in the function by using the respective value / integer.

i.e.

// constants for passing $sort to get_zips_in_range()
define('_ZIPS_SORT_BY_DISTANCE_ASC', 1);
define('_ZIPS_SORT_BY_DISTANCE_DESC', 2);
define('_ZIPS_SORT_BY_ZIP_ASC', 3);
define('_ZIPS_SORT_BY_ZIP_DESC', 4);

 

$zips = $z->get_zips_in_range($zip, 10, 1, true); would be sort by distance, ascending.

$zips = $z->get_zips_in_range($zip, 10, 2, true); would be sort by distance descending,

$zips = $z->get_zips_in_range($zip, 10, 3, true); would be sort by zip ascending

$zips = $z->get_zips_in_range($zip, 10, 4, true); would be sort by zip descending

 

$z = new zipcode_class;
//this will sort zips by distance, ascending.
$zips = $z->get_zips_in_range($zip, 10, 1, true);

//for sql, implode with ', ' to seperate zips.
$sqlZIPS = implode(", ", $zips);

//now do search with that
mysql_select_db($database_connDB2, $connDB2);
$query_getCat = ("SELECT subcategories.subcategory, subcategories.cat_id, advertisers.adv_id, advertisers.zip_code, couponinfo.coupon_id FROM subcategories, advertisers, couponinfo WHERE (subcategories.subcat_id = advertisers.subcat_id OR subcategories.subcat_id = advertisers.subcat_id2) AND (advertisers.adv_id = couponinfo.adv_id) AND (advertisers.zip_code IN ($sqlZIPS)) ORDER BY subcategories.subcategory");
$getCat = mysql_query($query_getCat, $connDB2) or die(mysql_error());

[siamsam]

Unfortunately that didn't work either, the problem is we took it out of the array with $return[] = $row[0]. It is only returning one value, the zip. Is there a way to take out the resulting zips from the first query, then use them in the next query and then associate them back with the corresponding distance after the results of the 2nd query? Or do I have to query it all over again since it is a calculated result?

 

My head hurts...lol...and I am sure you are sick of helping me by now! I really do appreciate it! Especially since you are the only one in this forum who has attempted to tackled this problem.

[joel24]

hmm

try this, and tell me what is echoed.

$z = new zipcode_class;
//this will sort zips by distance, ascending.
$zips = $z->get_zips_in_range($zip, 10, 1, true);

//for sql, implode with ', ' to seperate zips.
$sqlZIPS = implode(", ", $zips);

print_r($zips);
echo "<br/><br/>" . $sqlZIPS . "<br/><br/>";

//now do search with that
mysql_select_db($database_connDB2, $connDB2);
exit("SELECT subcategories.subcategory, subcategories.cat_id, advertisers.adv_id, advertisers.zip_code, couponinfo.coupon_id FROM subcategories, advertisers, couponinfo WHERE (subcategories.subcat_id = advertisers.subcat_id OR subcategories.subcat_id = advertisers.subcat_id2) AND (advertisers.adv_id = couponinfo.adv_id) AND (advertisers.zip_code IN ($sqlZIPS)) ORDER BY subcategories.subcategory");

[siamsam]

This is what I got (keeping the zip.class as the original):

 

Array ( [19067] => 0 [08625] => 1.18 [08677] => 1.25 [08611] => 1.48 [08645] => 1.75 [08646] => 1.83 [08647] => 1.85 [08605] => 1.91 [08666] => 1.91 [08604] => 1.94 [08601] => 1.97 [08603] => 2 [08608] => 2.01 [08695] => 2.03 [08607] => 2.08 [08609] => 2.6 [08629] => 2.76 [08610] => 3.08 [08618] => 3.1 [19054] => 3.2 [08638] => 4.14 [19030] => 4.33 [19055] => 4.79 [08505] => 4.8 [19058] => 5.09 [08619] => 5.09 [08554] => 5.38 [08518] => 5.75 [08620] => 5.87 [19057] => 5.96 [08628] => 5.96 [19059] => 6.14 [08690] => 6.17 [08648] => 6.28 [08515] => 6.83 [19056] => 6.86 [19007] => 7.36 [19049] => 7.99 [08691] => 8.19 [19048] => 8.24 [08650] => 8.37 [08560] => 8.53 [08022] => 8.93 [18977] => 9.17 [08016] => 9.2 [19047] => 9.29 [08534] => 9.74 [19021] => 9.75 [18940] => 9.83 )

 

0, 1.18, 1.25, 1.48, 1.75, 1.83, 1.85, 1.91, 1.91, 1.94, 1.97, 2, 2.01, 2.03, 2.08, 2.6, 2.76, 3.08, 3.1, 3.2, 4.14, 4.33, 4.79, 4.8, 5.09, 5.09, 5.38, 5.75, 5.87, 5.96, 5.96, 6.14, 6.17, 6.28, 6.83, 6.86, 7.36, 7.99, 8.19, 8.24, 8.37, 8.53, 8.93, 9.17, 9.2, 9.29, 9.74, 9.75, 9.83

 

This is what I get when I change the result to $return[] = $row[0]:

 

Array ( [0] => 08016 [1] => 08022 [2] => 08505 [3] => 08515 [4] => 08518 [5] => 08534 [6] => 08554 [7] => 08560 [8] => 08601 [9] => 08603 [10] => 08604 [11] => 08605 [12] => 08607 [13] => 08608 [14] => 08609 [15] => 08610 [16] => 08611 [17] => 08618 [18] => 08619 [19] => 08620 [20] => 08625 [21] => 08628 [22] => 08629 [23] => 08638 [24] => 08645 [25] => 08646 [26] => 08647 [27] => 08648 [28] => 08650 [29] => 08666 [30] => 08677 [31] => 08690 [32] => 08691 [33] => 08695 [34] => 18940 [35] => 18977 [36] => 19007 [37] => 19021 [38] => 19030 [39] => 19047 [40] => 19048 [41] => 19049 [42] => 19054 [43] => 19055 [44] => 19056 [45] => 19057 [46] => 19058 [47] => 19059 [48] => 19067 )

 

08016, 08022, 08505, 08515, 08518, 08534, 08554, 08560, 08601, 08603, 08604, 08605, 08607, 08608, 08609, 08610, 08611, 08618, 08619, 08620, 08625, 08628, 08629, 08638, 08645, 08646, 08647, 08648, 08650, 08666, 08677, 08690, 08691, 08695, 18940, 18977, 19007, 19021, 19030, 19047, 19048, 19049, 19054, 19055, 19056, 19057, 19058, 19059, 19067

[joel24]

ok, ensure $return is '$return[] = $row[0]:", the other array is filled with distances.

And as you can see, they are sorted by distance, ascending.

 

Now this isn't returning any results in your query? what field type is the column storing the zips in the advertisers table?

[siamsam]

Taking the distance out of the return is just sorting the result ascending by zip code. When I run the sql statement, I get the results of all the advs in those zip codes, but the result set is displayed in random order and not by distance of the zip code.

 

I have two databases that I am querying - the zip code field in the zip db is zip_code VARCHAR(5) and the field in the adv db table is zip_code VARCHAR(5).

 

It seems like $return needs to keep the distances in the array in order to have the zips sorted.

 

Funny tidbit...I tried to find the guy who wrote this script and eventually tracked him down. Apparently he is now way beyond this programming stuff in life, but appreciates all that are still using the classes he wrote when he was living in his Toyota and had a lot of time on his hands to write and post scripts. lol! Won't see much help from him any time soon. 

 

[joel24]

hmm, this is true.

you'll have to leave the zipcode class as it was, with the distances.

Then use a foreach loop to create the SQL IN part,

And then since the zip field type in your database is Varchar, you should have this set to integer. an sql statement where the field is varchar will look for an exact match, and your zipclass adds a 0 infront of any zips which are less than 5 characters in length. If the field is of integer type it will ignore this 0 as 0120 is the same as 120, although a string "0120" is not the same as "120", hence if you leave the field type as varchar the sql query will not pick up zip code 1343 being IN (01343). The other option is to turn off the padding in the zipClass, I'd recommend you change your zip code field type for your table to Integer.

$z = new zipcode_class;
//this will sort zips by distance, ascending.
$zips = $z->get_zips_in_range($zip, 10, 1, true);

//for sql, implode with ', ' to seperate zips.

$sqlZIPS = '';
foreach ($zips as $key => $value) {
      //SQL STRING YOU CAN REMOVE single quote around $value if values are all numbers
$sqlZIPS .= "$key, ";
   }

//now do search with that
mysql_select_db($database_connDB2, $connDB2);
exit("SELECT subcategories.subcategory, subcategories.cat_id, advertisers.adv_id, advertisers.zip_code, couponinfo.coupon_id FROM subcategories, advertisers, couponinfo WHERE (subcategories.subcat_id = advertisers.subcat_id OR subcategories.subcat_id = advertisers.subcat_id2) AND (advertisers.adv_id = couponinfo.adv_id) AND (advertisers.zip_code IN ($sqlZIPS)) ORDER BY subcategories.subcategory");