Remove last three chars from field and compare to IN list

[bkeep]

I am basiclly trying to modify an existing zip code range lookup class I have gotten off the internet which has been working pretty flawlessly for US based zips for some time. to utilize it for some UK Post Codes.

So some of the data in the zip field may be UK post_codes AB105HY, SW345HY etc the database I have does not have complete post codes it only has the county or area code such as AB10, AB11, SW34 etc..

is there an easy way like using an IN statement to get only the first 3 or 4 characters in the zip field within my results fields?

Something like this is what I am thinking but of course doesn't work

$zip_list = "AB10,AB11,AB25,AB24,AB16,AB12,AB15,AB22,AB13,AB14";
$addOnSQL.="RIGHT(zip, -3) IN ('" . $zip_list . "')";

Any help would be appreciated
Regards,
Brandon

[rocknbil]

So you have AB10, AB11, SW34 in your database.

The search/submitted data is like AB105HY, SW345HY.

Wouldn't it be more simple to trim the last three characters off the search data and do an equality comparison?

[bkeep]

I have a table for listings which has a field which can contain the full uk post code so either 6 or 7 chars which can be searched on as a single point so the zip field in the listings table can have AB10SYX or whatever it may be

the information the user is going to enter for their search will most likely be a full post code AB10 SYX I am currently trimming that and search to get a radius of all areas which is the list of AB10,AB11,AB25,AB24,AB16,AB12,AB15,AB22,AB13,AB14. This data comes from another table post_codes and county, lat, lon, etc with a truncated post code.

here is some of the code after the POST comes in the range would be 5 10 25 etc as is needed for a radius search

if (!empty($_POST["start_zip"])) {
$zip = new zipcode_class;
//UK based site can be 6 or 7 alphanumeric
$zip->units = "k"; //set as kilometers
$zip->db_table = "post_code"; //set as post_code table for UK
$zip_code = substr($_POST["start_zip"], 0, -3);

//US based site is always 5 digits
//$zip->units = "m"; //set as miles
//$zip->db_table = "zip_code"; //set as zip_code table for US
//$zip_code = $_POST["start_zip"];

if (!empty($addOnSQL))
$addOnSQL.=" AND ";
if (!empty($_POST["zip_range"]))
$zips = $zip->get_zips_in_range($zip_code, $_POST["zip_range"], _ZIPS_SORT_BY_DISTANCE_ASC, true);
//print_r($zips);
if (empty($zips)) {
$addOnSQL.="zip = '" . $_POST["start_zip"] . "'";
} else {
// this fetches all the zip codes in the $zips variable
$zipcodes = array_keys($zips); 
$zip_list = implode(",",$zipcodes);

//$addOnSQL.="zip IN (" . $zip_list . ")";
$addOnSQL.="right(zip, -3) IN ('" . $zip_list . "')";
//mid(@text, 1, length(@text)-3)
}
}

Regards,
Brandon

[bkeep]

well I guess there is no good way only the ugly way ;) a foreach and the mySQL LIKE clause

// this fetches all the zip codes in the $zips variable
$zipcodes = array_keys($zips); 
//print_r($zipcodes);
if (!empty($addOnSQL)) {
$addOnSQL.=" AND ";
}
$zip_count = count($zipcodes);
$c=0;
foreach ($zipcodes as $onezip) {
//needs to be an OR
$addOnSQL.="zip LIKE '" . $onezip . "___'";
if ($c == $zip_count-1) {
break;
} else {
$addOnSQL.=" OR ";
}
++$c;
}

[coopster]

I must admit, I too have really struggled trying to figure out what you have for user input and what is in the database table. After that, I am uncertain as to what you want returned? Can you explain it in plain text (no code)?

[bkeep]

I guess it is a little convoluted. this may end up being a little long in the tooth but I will try and keep it as short as possible while giving an idea of the whole story.

I have an open source auto classifieds script which users can list autos and I have used a freely available zip code radius search for US based zip codes with much success over the years. I have recently been working on expanding that existing class with a list of UK post code data which is not the full UK post code archive of 1.7 million records but only about 3000 general area prefix codes.

So in general a user will login and post a listing of their vehicle and enter in the appropriate details including the zip/post code. In the US that would be something like 61032 as we all know it is always 5 digits. For UK post codes that could be an alphanumeric string from 6-7 characters long.

The table which contains the data for the radius search is separate from where the listing data the user posted is stored. This table has the zip, latatude and longitude which is how the radius is calculated. When a search is performed on the zip 61032 the radius functions find all zips within the specified range of x miles and return zips as an array; 61032, 61019, 61018 etc. I can take that array and create a string to use an IN clause -> zip in (61032, 61019, 61018). This works pretty well for finding items in the range.

So where this kind of breaks down is the post code table I am using does not contain all 1.7 million UK post code records but only the basic area prefix. so instead of AB10SYX, and AB34FTW the data is only AB10, AB34 etc.

The same premise applies which is when a user searches for all of the vehicles inside a certain range the post code table will return an array of the areas within x miles. what is retuned is similar to AB10, AB34. This shortened prefix code is all I have to search against the full post code located in the listings table where the user data is stored.

So I have data like this AB10SYX, AB10HGF and need to be able to search out all items with AB10 as part of the zip field.

As in my example I wanted to be able to use something like the zip IN but instead of using the full value that is in the zip field remove the last three characters before comparing it to the prefix area list.

So please tell me that at least makes sense because if not my career as a creative writer is out the door;) Let me know if further clarification is needed or if that was already an overkill.

Thanks,
Brandon

[coopster]

Slightly overkill, didn't need the lat/lon search details. But the rest is making sense now. I was looking for this part:

User input on form: 
  zip: AB10SYX 
Value stored in database table column: 
  zip: AB10

And it still sounds like what I first read. Why not use the solution offered by rocknbil? Substring the first four characters of the incoming POST value for zip code and use that to query the database.

$zip_code = substr(trim($_POST["start_zip"]), 0, 4);

Don't forget to real_escape that $zip_code before using it in your query. It's still user-supplied data.

[bkeep]

That part already happens
$zip_code = substr($_POST["start_zip"], 0, -3); this info is then passed to grab the radius list from the postcode table using the zipcode class

$zips = $zip->get_zips_in_range($zip_code, $_POST["zip_range"], _ZIPS_SORT_BY_DISTANCE_ASC, true);

This returns an array of the post codes in a certain range ie a 10 mile radius. I am also using cleaned data so no worries there.

once this info is obtained I then take it and search the listings table which has the full post code value stored in it.

The LIKE search works but I can't see that as being very speedy it does work but I was just hoping for a little more elegant solution.

Best Regards,
Brandon

[coopster]

I didn't realize you were running two queries. I've always joined my tables and execute a single query when determining distance-based locations.

So, you are doing an equality comparison on the first table and the result set contains truncated zip codes within the radius range. Now you want to use them to SELECT from the classifieds table, which is the full zip again.

OK, time to tweak your query. I suppose you could use a SUBSTRING of the column value and do an equality comparison as opposed to a LIKE ...

WHERE SUBSTRING(zip, 0, 4) IN (<your comma separated list here>)

and another thought is to speed up your searches by created a new 4 character index on that column in your classifieds table:

ALTER TABLE classifieds ADD INDEX(zip(5));

[bkeep]

I don't think the substring option will work this is the query using that 0 records returned
SELECT * FROM listings WHERE SUBSTRING(zip, 0, 4) IN ('AB11,AB10,AB25,AB24,AB16,AB12,AB22,AB15,AB13') ORDER BY sold,id DESC LIMIT 0, 5

Using the zip IN 2 records returned
SELECT * FROM listings WHERE zip IN (61032,61013,61027,61067,61007,61018) ORDER BY sold,id DESC LIMIT 0, 5

and this is the query using the like way 2 records returns (correct)
SELECT * FROM listings WHERE zip LIKE 'AB11___' OR zip LIKE 'AB10___' OR zip LIKE 'AB25___' OR zip LIKE 'AB24___' OR zip LIKE 'AB16___' OR zip LIKE 'AB12___' OR zip LIKE 'AB22___' OR zip LIKE 'AB15___' OR zip LIKE 'AB13___' ORDER BY sold,id DESC LIMIT 0, 5

So I am not sure what is missing. Thanks for the idea on adding an index to the zip field it completely slipped my mind.
Best regards,
Brandon

[coopster]

('AB11,AB10,AB25,AB24,AB16,AB12,AB22,AB15,AB13')

Quotation marks are missing. You are passing one big long string as it stands right now. Note the difference:

('AB11,AB10,AB25,AB24,AB16,AB12,AB22,AB15,AB13') 
('AB11','AB10','AB25','AB24','AB16','AB12','AB22','AB15','AB13')

[bkeep]

Thanks, exactly right.

Only thing I had to change was the SUBSTRING to use 1 instead of 0
addOnSQL.="SUBSTRING(zip, 1, 4) IN ('" . $zip_list . "')";

One last question on this, is there a way to remove the last three chars? Sometimes it will not be a 4 character post code area ie pe1 instead of pe11 I know for sure(as sure as I can be ) I can always remove the last three chars and whatever the rest is is the area.

I appreciate your help coopster as always.
Regards,
Brandon

[bkeep]

I think I may have found my own answer
$addOnSQL.="left(zip, char_length(zip) -3) IN ('" . $zip_list . "')";

Regards,
Brandon