compare varchar with number in sql query

Question

table

user(varchar40)      credit(varchar40)
u1                   400
u2                   500
u3                   600
u4                   650

and query

$sql=mysql_query("SELECT `user` FROM `tbl` WHERE `credit` < '8'");
for($i=0;$i<mysql_num_rows($sql);$i++)

{
echo mysql_result($sql,$i,0);
}

and result u1
u2
u3
u4

because credit is varchar only first number compare and return all user

how i do correct compare without change table ???

Answer 1

first, why are storing numbers as string? anyway, you can cast the value into number using CAST()

SELECT `user` FROM `tbl` WHERE CAST(`credit` AS SIGNED) < 8

if I were you, you should fix it to correct data type to avoid casting the column always just for comparison. To do this, alter the table and add extra column, an INTEGER column. UPDATE the table by casting the column credit into the new added column and drop column credit.