[RESOLVED] fetching all records from mysql within a specific criteria

[raj_2006]

Hi all,

I have a field in job_list table that is salary whose datatype is varchar(100)

Now the data in the job_list table are:

salary

10,000
20,000
30,000
40,000
50,000

Now my sql is very simple

$sql="select * from job_list where salary>=10,000 and salary<=50,000";

If i run this query then zero rows are found but if i do this

$sql="select * from job_list where salary>=10,000 and salary<=40,000";

then "4 rows found"

Why the 5th row is not displaying if i select max and min salary.I could found any solid logic behind it but this is very much strange.

Please suggest me......Thanks....Raj

[mudelta]

hello,

it is strange.

Could you try it with between?


SELECT *
FROM job_list
WHERE salary BETWEEN 10,000 AND 50,000

Another question:

Why is the salary column a varchar and not numeric?

[raj_2006]

really its strange

I have tried with BETWEEN but didnt worked.I have made salary as varchar not int cause if someone try to enter comma then he can

like 10,000 not 10000

....Raj

[mudelta]

but i guess that's what causes the problem.

[raj_2006]

I have tried without commas too...but still its not showing all the records

i mean i have edited the salary values in the table by removing the comma....and then tried the sql by

$sql="select * from job_list where salary>=10000 and salary<=50000";

but not working

[mudelta]

did you change the type from varchar to int?

[chazzy]

what type is salary?

edit: nevermind. read the thread thoughout next time :-)

the problem is you're using varchar, as mu pointed out already. You should make it a numeric type, and at the app level strip out the ",".

edit2: also, just to point out the weird searching you're seeing is probably due to the way mysql is handling the values. it's most likely treating "40,000" as "forty..." and "10,000" as "ten..." and when you try to find things > ten but < forty since f < t it returns none.

[raj_2006]

Hi

Thanks to all for your suggestion.Its working.

I changed the datatype from varchar to int and removed the commas from the input value and it worked.

Thanks again.... :) ......Luv......Raj