Re: Questions about finding ranges

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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
>>
>>
>>     
>
>   

[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux