PHP and MySQL looking up zip code in a range

[Lostnode]

Ok, my subject line may not be exactly what I am looking for and I am not even sure what the subject should be, so let me explain what I am trying to do.

 

My application is as such that you enter in a zip code and it returns information, however my hard copy has a range of zip codes, in this case, equaling a zone, I.E. 90076-90210 is zone A, now instead of having the following in the database:

 

ID   ZIP        ZONE
1    90076    a
2    90077    a
3    90078    a
...
...
134   90210   a

 

Could I make it:

 

ID   SZIP        EZIP        ZONE
1    90076      90210     a

 

and though my PHP/mySQL callout compare the variable to, and find that is ZIP is between SZIP and EZIP to output a?

 

As there are a couple od thousand zip codes I am trying to minimize my database to be a bit smaller, but am not sure how to code it.  Normally I would make the database have all 99000 entries and pull from the database where ZIP = ZIP, but again I am trying to save my self some time...

 

Could I make it somehow that if ZIP <= SZIP and >= EZIP output ZONE?

 

Does this make any sense?  My PHP Coding is unfortunately lacking, I have only been programming it heavily for the last 5 months and it has been basic stuff.  Any help would be appreciated.

[Lostnode]

Ok, I found this in a search, which is the opposite of what I want

 

SELECT * FROM table WHERE field >= $num1 AND field <= $num2

 

Now is it possible to make it look like this?

 

SELECT * FROM table WHERE $field >= EZIP AND $field <= SZIP

or if that causes an syntax error

SELECT * FROM table WHERE EZIP <= $field AND SZIP >= $field

 

 

I have not tested it yet as I am still weighing my options, I would like to compact my DB a little but do not want to short out my possibilities.

[Pikachu2000]

SELECT `field` FROM `table` WHERE `field` BETWEEN value1 AND value2

[Lostnode]

I appologize, but that seems to be the reverse of what I need... What I am trying to do is (using the same coding as yours)

 

SELECT `field` FROM `table` WHERE `value` BETWEEN field1 AND field2

Which I do not believe is possible which is why I am asking how to do it, this is what I want to have happen:

 

If I type in zip code 90123 it looks at the database and sees that the value is between 90076 (DB field SZIP) and 90210 (DB field EZIP) and there for when I pull the data zone is A

 

SELECT `ZONE` FROM `table` WHERE `$value` BETWEEN SZIP AND EZIP

which I know to be wrong.

[Pikachu2000]

Oh, I apparently misunderstood. In that case, your probably better to leave it as is. 99000 records in a table is still very small.

[Lostnode]

Well that kinda sucks, I was hoping to make it a but shorter as most of the data is being inputted by hand into a CSV file to be imported...

Is there no way to compare a single input to see if its between two values in two separate fields?  Even if it takes a little bit more coding I don't care

[Lostnode]

Well, found my own answer after taking to a few buddies of mine and pulling out an old MySQL book off the shelf.

 

The answer to my dilemma was actually quite simple and I even answered it with a question I asked a little earlier:

 

Ok, I found this in a search, which is the opposite of what I want

 

SELECT * FROM table WHERE field >= $num1 AND field <= $num2

 

Now is it possible to make it look like this?

 

SELECT * FROM table WHERE $field >= EZIP AND $field <= SZIP

or if that causes an syntax error

SELECT * FROM table WHERE EZIP <= $field AND SZIP >= $field

 

 

I have not tested it yet as I am still weighing my options, I would like to compact my DB a little but do not want to short out my possibilities.

 

ANd Voila I was right,

 

SELECT * FROM table WHERE szip <= $zip AND ezip >= $zip

 

Works like a charm.