Selecting by Zip by first 5 only.

raouleduke

I am trying to select records by zip, they are stored like this:
56510-56511-56514-56519. My problem is is that some of the zipcodes in the database are ten digit, can I use a wild card with the IN STATEMENT, or maybe just select the first 5 digits.

$selectedzip1 = str_replace("-", "\",\"", $selectedzip);  

$selectedzipstr = implode("','", $selectedzip1); 


  $query = "UPDATE TABLE SET ACTIVE = '1' WHERE CLOSEDATE > '$date' ";   

  if($selectzip =='1'){
  $query.=" AND MAKE IN (\"".$selectedzipstr."\")"; 
  }

Thanks for any help.

paulnaj

Hi,

You could just truncate all the elements of $selectedzip using
substr().

Paul 🙂

raouleduke

Then could I still use the IN STATEMENT, I think then I would have to break them out and do them indiviually.

Weedpacket

I don't have to deal with ZIP codes, so maybe I'm missing something here, but it looks like with "56510-56511-56514-56519" you're storing four ZIP codes in one field?!

raouleduke

More than four, that was just an example, so I have to break them apart and use them for the criteria in the Select statement.

Weedpacket

Originally posted by raouleduke
More than four, that was just an example

Bad. Very bad. For one thing, it makes searching for ZIP codes a lot more difficult.

I presume each record has a unique ID? Add a new table with record_id and ZIP fields, and add a new record in that table for every ZIP code. So that if your example ZIPs were for record 2134,

SELECT record_id,zip from address_zips Where record_id=2134

record_id | zip
----------+----
2134      | 56510
2134      | 56511
2134      | 56514
2134      | 56519