[SOLVED] Query help

[dare87]

I have this query:

 

$query="SELECT LotNum, Carat, Color, Clarity, Cert, Meas, Polish, Sym, Price FROM selling_data WHERE Shape='$sp'";

if ($crm==$crx) {

$query .= " AND Color='$crm'";

} else {

$query .= " AND Color BETWEEN '$crx' AND '$crm'";

}



if ($clm==$clx) {

$query .= " AND Clarity = '$clm'";

} else {

$query .= " AND Clarity BETWEEN '$clm' AND '$clx'";

}

if ($ctm>0 && $ctx>0 && $ctm!=$ctx) {

$query .= " AND Carat BETWEEN '$ctm' AND '$ctx'";

} elseif ($ctm!='' && $ctm==$ctx) {

$query .= " AND Carat='$ctm'";

} elseif ($ctm=='' && $ctx>0) {

$query .= " AND Carat <='$ctx'";

}



if ($pm>0 && $px>0) {

$query .= " AND Price BETWEEN '$pm' AND '$px'";

} elseif ($pm=='' && $px>0) {

$query .= " AND Price BETWEEN '1.0' AND '$px'";

}

else { $query .= " AND Price > '1'";

}

$query .= " ORDER BY Price ASC, Carat, Clarity ";

 

The problem I am having is that Clarity needs to be a number, but in my db it is not. In by db it is

 

Flawless

VVS-1

VVS-2

VS-1

VS-2

SI-1

SI-2

SI-3

I-1

I-2

I-3

 

I need them to equal

 

Flawless = 1

VVS-1 = 2

VVS-2 = 3

VS-1 = 4

VS-2 = 5

SI-1 = 6

SI-2 = 7

SI-3 = 8

I-1 = 9

I-2 = 10

I-3 = 11

 

How can I do this without changing my db?

 

Thanks

[PFMaBiSmAd]

This should work with strings as well - http://www.eklekt.com/tutorials/mysql

[dare87]

I don't understand, could you give me an example please?

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field

[dare87]

I must be really stupid, I still don't get it. How does that return Flawless as 1? and VVS - 1 as 2? etc...

 

if ($clm==$clx) {

$query .= " AND Clarity = '$clm'";

} else {

$query .= " AND Clarity BETWEEN '$clm' AND '$clx'";

}

 

This is the part of the code that needs changing. I don't know what to do.

[fenway]

Try this:

 

SELECT FIELD( Clarity, 'Flawless', 'VVS-1', 'VVS-2', 'VS-1', 'VS-2', 'SI-1', 'SI-2', 'SI-3', 'I-1', 'I-2', 'I-3' )