How do structure this query?

[poleposters]

I have bought some zipcode data which lists  the zipcodes and the zipcodes immediately adjoining.

 

I'm trying to integrate it with my search script but am stuck for ideas on how to do it.

 

My current search script only returns the zipcode entered into the searchbox.This query is simple enough

 

SELECT business_listing FROM table WHERE zipcode=$searchterm

 

$searchterm contains the string typed into the searchbox.

 

The zipcode table I have purchased is structured like this.

2000,5,2007,2008,2009,2010,2011

2006,4,2008,2037,2042,2050
2007,4,2000,2008,2009,2037
2008,8,2000,2006,2007,2010,2016,2037,2042,2043
2009,3,2000,2007,2037
2010,6,2000,2008,2011,2016,2021,2027
2011,3,2000,2010,2027

 

 

The columns are zipcode,number of adjacent zipcodes,adjacent zipcode one, adjacent zipcode two ,etc.

 

Also please note , I have asked this question of people before and they have suggested I use Lat/Long zipcode tables. However that would return zipcodes seperated by a body of water, and this does not suit my purpose.

 

Basically I want to be able to SELECT business_listing FROM table WHERE zipcode=zip1,zip2,zip3,zip4

 

The main problem I see is that each zipcode has a different number of adjoining zipcodes. so I cant just SELECT adjoining zipcode one,two three FROM table WHERE zipcode=2003

 

Can anyone help with some ideas on how it could be done?

 

 

 

[gluck]

This what I understand:

Zipcode had a number of adjacent zipcodes and you want to display results in all of the adjacent zipcodes as well.

One of doing in is creating an adjacent zipcode table so you will on entry fro each adjacent zipcode.

 

Ex: 78238 has 2 adjacent codes 78237 and 78239.

 

zipcode adj_zipcode

78238  78237

78238  78239

 

and then you can fire a select like this

SELECT business_listing FROM table WHERE zipcode in (select zipcode, adj_zipcode from  adjacent_zipcode where zipcode =$searchterm);

 

OR

 

another quick method code be dump the entire zipcode row in a field

 

ex: 2000,5,2007,2008,2009,2010,2011

 

zipcode=2000 add another field zipcode_adj=2000,5,2007,2008,2009,2010,2011

 

then simply fire this query

SELECT business_listing FROM table WHERE zipcode in (substr(6, strlen(zipcode_adj),zipcode_adj))

 

in 2000,5,2007,2008,2009,2010,2011 substr(6, strlen(zipcode_adj),zipcode_adj) should return this:2007,2008,2009,2010,2011

 

 

 

 

 

 

[poleposters]

Great.The first method is definitely the easiest . Although I don't completely understand the second method.

 

The problem is the table I'm being supplied is structured like this.

 

2000,5,2007,2008,2009,2010,2011

 

Is there an automated way of changing the tables structure so that it looks like this.

 

2000,2007

2000,2008

2000,2009

2000,2010

2000,2011

 

Otherwise I'd have to re-enter the data manually. And with 30 000+ records it would take a while!.

 

Thank you for your help so far.

 

 

 

 

[Barand]

create the initial array in my code using file() function

 

<?php 
$zips = array (
    '2000,5,2007,2008,2009,2010,2011',
    '2006,4,2008,2037,2042,2050',
    '2007,4,2000,2008,2009,2037',
    '2008,8,2000,2006,2007,2010,2016,2037,2042,2043',
    '2009,3,2000,2007,2037',
    '2010,6,2000,2008,2011,2016,2021,2027',
    '2011,3,2000,2010,2027'
);

foreach ($zips as $line)
{
    $arr = explode(',', trim($line) );
    
    $zip = $arr[0];
    $adjArr = array_slice ($arr, 2);
    foreach ($adjArr as $adj)
    {
        $sql = "INSERT INTO adjacent_zips (zip, adj) VALUES ('$zip', '$adj')";
        echo $sql, '<br>';
    }
    
}

?>