Lostnode Posted February 5, 2011 Share Posted February 5, 2011 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. Quote Link to comment Share on other sites More sharing options...
Lostnode Posted February 5, 2011 Author Share Posted February 5, 2011 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. Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted February 5, 2011 Share Posted February 5, 2011 SELECT `field` FROM `table` WHERE `field` BETWEEN value1 AND value2 Quote Link to comment Share on other sites More sharing options...
Lostnode Posted February 5, 2011 Author Share Posted February 5, 2011 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. Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted February 5, 2011 Share Posted February 5, 2011 Oh, I apparently misunderstood. In that case, your probably better to leave it as is. 99000 records in a table is still very small. Quote Link to comment Share on other sites More sharing options...
Lostnode Posted February 7, 2011 Author Share Posted February 7, 2011 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 Quote Link to comment Share on other sites More sharing options...
Lostnode Posted February 9, 2011 Author Share Posted February 9, 2011 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.