SQL Less Than returns wrong results

[ukfusion]

I have an sql query which has several fields and no matter how hard i try i cant get the less than statement to work....i originally tried between but that wouldnt work either.

$yourpricesearch is the name of the drop box with in the search form with the price values of 5.00, 10.00, 15.00 etc...i did it this way as i wasn't sure if when i put the AND in when the value of $yourpricesearch was empty would cause problems so im only putting the statement in if $yourpricesearch has a value
my prices of the books are in the format 10.15 for example

if(!$yourpricesearch==""){$yourprice=" AND yourprice < '$yourpricesearch'";}
//Now we search for our search term, in the field the user specified 
$sql = "SELECT * FROM mytable_name WHERE status = 'active' AND isbn LIKE '%$isbnsearch%' AND title LIKE '%$keyword%' AND topic LIKE '%$topicsearch%' AND university LIKE '%$universitysearch%' $yourprice";

it still doesnt even seem to work if i put the less than statement directly into the sql query......also between statement didnt work.

im not sure if something is wrong in the statement?

sorry forgot to say how it returns wrong results.....at the momwent there are book prices ranging from 2.00 to 19.00 ish.....when you search for less than 20.00...they all show.....less than 15.00 they all show.....less than 10.00....none show...yet there are some with 5.00.....2.00 etc?

thanks

[Atli]

Hi.

Try looking at the query exactly like it is before it gets executed.
You can do that by simply printing the $sql variable in the code you posted.

It would also help to see the CREATE TABLE statement you used to create you table.

[ukfusion]

Hi,

I printed the query and this is what is print.....all looks fine to me.....when i search for less than 20.00 they all show even the 2.99 one...yes when i search < '10.00' nothing shows...not sure what the issue is?

SELECT * FROM iswapbooks_books WHERE status = 'active' AND isbn LIKE '%%' AND title LIKE '%%' AND topic LIKE '%%' AND university LIKE '%%' AND yourprice < '10.00'

the only thing i thought it could be was my bookid column is the primary key on auto increment and it is currently on 75 ish but theres only about 16 items in the table as i've deleted loads....could that be the issue...should i maybe reset my auto increment value...if so can i do this without deleting the items in the table.

Cheers

[Atli]

I can't see anything wrong with the query, although without knowing the exact structure I can't really say anything with any certainty.

What is the type of the "yourprice" column?
And what version of MySQL are you using?

@ukfusion
This shouldn't matter in the slightest.
Gaps in a auto_increment Primary Key column have no effect on the data in the table.

[ukfusion]

Hi...the yourprice field is VARchar...so this could me my problem...its mysql 4 however i can alter it to 5.....but i think i have to re-create the database...dont think it lets me just upgrade it...which could cause problems?

[Atli]

Ok, I see now.

The problem is that you are using incorrect types to store your data.

The price you are storing is a number, yet you are storing it in a VarChar field, which is a string type.
And to top that of, the value you use to compare it to is quoted, which means that even tho it looks like a number, MySQL considers it to be a string.

There are two probable solutions. Either should work, but doing both would be best.

#1
Your price field should be a numeric type. That way MySQL can actually evaluate it's value as a number and determine correctly whether or not it is actually higher or lower than the value you use to compare it with.

For monetary values, the DECIMAL type is usually best. It allows you to specify exactly how many whole numbers you want and how many fractions.

You could change that by simply doing:

ALTER TABLE `iswapbooks_books` 
    CHANGE `yourprice` (
        `yourprice` Decimal(7,2) Not Null
     );

Before you do this!.. make sure you back up any data you do not want to lose.
Even tho I highly doubt this will cause any damage to you data, there is always a remote possibility that something could go wrong when altering tables.

#2
The value you use to compare with the price is a string. This should be a number.

By that I mean: this part of your query:

yourprice < '10.00'

Because the 10.00 is wrapped in single-quotes, MySQL assumes it is a string rather than a number.
This can cause problems when comparing the value with actual numbers, or strings that look like numbers, as the two do not follow the same rules when compared.

To fix this, simply alter the query so it looks like this:

yourprice < 10.00