Re: [PHP] Questions about finding ranges
You guys are just awesome! Thanks already for the help I really appreciate it! My site is up on adslgeek.com/troubleshooter but I have a DNS problem that I have to fix over the weekend. :-( The scenario is an input dashboard, with various settings that measure the quality of an ADSL line. I have then mapped out all of the possible faults that I could think of that might fit those settings(did up a flowchart of questions that is huge). So for simplicity say the inputs are: - X Noise on line - X Distance from exchange - X Disconnections per day - Slow speed of x Then I have a list of all the possible scenarios eg 1) Disconnections caused by noisy line 2) Disconnections caused by distance 3) Slow due to noisey line 4) Slow due to distance So how I have currently done this is heaps of nested if statements, but that is going to get so unmanageable, and the speed is going to become a factor. What I had envisioned was input all the possible vars, and then SQL just finds the closest variable. It is kind of similar in logic to a search engine type script - if I had the strings "disconnections" and "noisey of 52" then it just finds the closest record. Does that make sense? Thanks heaps you guys have been so helpful. Cheers, Aslan Micah Gersten wrote: 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 <[EMAIL PROTECTED]> 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 WHEREType = "Attainable" ANDMin <= $var ANDMax >= $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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Questions about finding ranges
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 <[EMAIL PROTECTED]> 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 >> WHEREType = "Attainable" >> ANDMin <= $var >> ANDMax >= $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 >> >> >> > >
Re: [PHP] Questions about finding ranges
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 <[EMAIL PROTECTED]> 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 > WHEREType = "Attainable" > ANDMin <= $var > ANDMax >= $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 > >
Re: [PHP] Questions about finding ranges
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 WHEREType = "Attainable" ANDMin <= $var ANDMax >= $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
