Here's the info on the "weirdness" of between: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com VamVan wrote: > Hey, > > For ranges you can also use "Between" in the mysql queries. > > SELECT * FROM table WHERE Type= "Attainable" AND Min LIKE $var can be > written as > > Select * from table where between min and max > > Just remember that "between" acts a bit wierd with dates or else Jim's > solution would be perfect for you. > > Thanks > > > > On Wed, Jul 23, 2008 at 7:58 AM, Jim Lucas <lists@xxxxxxxxx> wrote: > > >> Aslan wrote: >> >> >>> Hey there, >>> >>> I have a range of records that represent different faults and different >>> symptoms that I want to pull out of the database, and to find the records >>> that are the closest within each range. >>> >>> I am currently doing it with a barrage of if statements, but I am sure >>> that this could be done faster and far more elegantly by using SQL >>> >>> I have a range of conditions eg >>> Attainable rates: >>> 0-500 KB/sec is very poor >>> 500 - 1000 is marginal >>> 1000- 3000 KB/sec is good >>> >>> So the database may look like: >>> Type|Min|Max|Value >>> Attainable|0|500|" This rate is very poor" >>> >>> and then SQL could go something like >>> >>> SELECT * FROM table WHERE Type= "Attainable" AND Min LIKE $var >>> >>> >>> >> You're close, try this >> >> SELECT * >> FROM table >> WHERE Type = "Attainable" >> AND Min <= $var >> AND Max >= $var >> >> >> as long as your min and max do not overlap from row to row, you should only >> get one result. Make sure in your data that you have no overlap. >> >> Row 1 = 0 - 499 >> Row 2 = 500 - 999 >> Row 3 = 1000 - 1499 >> >> >> >>> But that wouldn't work quite right I don't think. >>> >>> But where it can get a bit more hairy is that I want to have a whole range >>> of variables that are input from an entry table, and then it just finds the >>> the vars that are the closest to what is searching for all the vars. The >>> closest code I have seen doing something similar is where there it is >>> finding if an IP is in a certain range. >>> >>> Does that make sense? feel free to email me if you need more explanation. >>> >>> It is kind of like a multi variable search engine, that is finding the >>> root cause of the symptoms that are the very best fit given the >>> multi-variables... >>> >>> Thanks heaps for any assistance, >>> Aslan. >>> >>> >>> >>> >> -- >> Jim Lucas >> >> "Some men are born to greatness, some achieve greatness, >> and some have greatness thrust upon them." >> >> Twelfth Night, Act II, Scene V >> by William Shakespeare >> >> >> >> -- >> PHP General Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> >> >> > >
