Re: Questions about finding ranges

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

 



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