desoto0311 Posted September 9, 2009 Share Posted September 9, 2009 I have a sql query in php to return zip codes within a 'radius' (of sorts, lol) of +/- 15. (ie search in 65650 and it returns 65635 - 65616) The problem is the query, when searching for zip codes that start with '0' (zero) ie 01126 will also return zip codes starting with 11 (ie 11264) which is nowhere near the other locations. The row in the table is den_postalcode and is set as numerical. Here are my queries: $sql = 'SELECT * FROM tbl_dentists 'WHERE '.$countrycode.$city.$postalcode ORDER BY RAND() LIMIT 30 '; Second query: $postalcode = ' AND den_postalcode < "'.($postal+15).'" AND den_postalcode >"'.($postal-15).'"'; 'SELECT * FROM tbl_dentists 'WHERE '.$countrycode.$postalcode ORDER BY RAND() LIMIT 10 '; Soooo... I'm hoping this is enough info, but why is the returned result for zero's also including non-zeros? (BTW, since the zip/postals are also in Canada/elsewhere, the zip code can't be 'forced' using a 5 digit return.) Thanks in advance! -D Link to comment https://forums.phpfreaks.com/topic/173723-mysql-search-results-with-zero/ Share on other sites More sharing options...
kratsg Posted September 10, 2009 Share Posted September 10, 2009 Use the between operator, that's what it's there for! http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between Link to comment https://forums.phpfreaks.com/topic/173723-mysql-search-results-with-zero/#findComment-915765 Share on other sites More sharing options...
desoto0311 Posted September 10, 2009 Author Share Posted September 10, 2009 Not sure how the between operator would work for the first query? I can see how it could work for the second (thanks) but again, not so much the first, lol. Link to comment https://forums.phpfreaks.com/topic/173723-mysql-search-results-with-zero/#findComment-916178 Share on other sites More sharing options...
desoto0311 Posted September 10, 2009 Author Share Posted September 10, 2009 One other comment. I just double-checked and actually my den_postalcode row (where my zip codes are stored in the mySql db) are actually set as varchar. This is even more confusing then. Shouldn't the results simply be output basically as a string? Why would it search within a varchar row for results that don't start with the leading zero? Link to comment https://forums.phpfreaks.com/topic/173723-mysql-search-results-with-zero/#findComment-916190 Share on other sites More sharing options...
kratsg Posted September 10, 2009 Share Posted September 10, 2009 For integers you can set ZEROFILL on (check out the manual), which means that if you have an INT(5) and insert 222 you will get 00222, but for varchar this won't work, you'll have to format the string in PHP first. Link to comment https://forums.phpfreaks.com/topic/173723-mysql-search-results-with-zero/#findComment-916344 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.