MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Michael Johnson Date:July 9 2004 3:42pm
Subject:Re: Between Operator
View as plain text  
On Fri, 09 Jul 2004 11:38:05 -0400, Keith Ivey <keith@stripped>  
wrote:

> Craig Hoffman wrote:
>
>> This should pull up all the rock climbs that are in Yosemite, that are  
>> traditional style and are between the rating 5.5 to 5.10c.  Here is my  
>> query:
>>
>> "SELECT * FROM routes, users WHERE area='$area' AND style='$style'   
>> BETWEEN rating='[$rating1]' AND rating='[$rating2]' GROUP BY route  
>> ORDER BY rating ASC ";
>
>
> Not sure what those square brackets are doing there, but your main  
> problem is that MySQL has no way of knowing what order you think those  
> rating strings should be in.  The string '5.5' is greater than '5.10c',  
> and the number 5.5 is greater than 5.10.  I think you're going to need  
> to change the way you represent the ratings -- maybe something like  
> '5.05' and '5.10c' would work (depending on what other possibilities  
> are, and how the letters are supposed to affect sorting). Then  
> manipulate the strings to produce what you're used to when it comes time  
> to display them (that, or have two columns: one for display and one for  
> sorting).
>

You could also set up a linked table that holds the sorting and rating  
string information. You could go with either a two column or three column  
table. The only difference being that the three column table would have an  
auto increment primary key so the sorting column can be changed without  
affecting the linking. A simple join connects the sort and rating columns.

Michael

-- 
Michael Johnson < mjohnson@stripped >
Internet Application Programmer, Pitsco, Inc.
+++ Opinions are my own, not my employer's +++
Thread
Between OperatorCraig Hoffman9 Jul
  • Re: Between OperatorKeith Ivey9 Jul
    • Re: Between Operator(Michael Johnson)9 Jul
  • Re: Between Operator(Pete Harlan)9 Jul
    • Re: Between OperatorCraig Hoffman10 Jul
      • Re: Between Operatormos10 Jul
  • Re: Between OperatorMartin Gainty10 Jul