[RESOLVED] query not working

boombanguk

for some strange reason this query is not working

SELECT *
FROM websites
ORDER BY viewed DESC
LIMIT 0 , 30

even when I do the query inside phpmyadmin, it still doesn't give the correct result (theres 3 entries, with viewed equalling 8,10,0 respectively going by ID). The correct result should be 10,8,0 but it keeps giving me 8,10,0

any ideas?

Piranha

What type is viewed? If it is a char/varchar then it is correct. Change it to an int to get the result you expect.

nemonoman

Piranha wrote:
.Change it to an into to get the result you expect.

He meant to say: change it to an INT (integer datatype)

Alternatively create a number from the VARCHAR to order your results:

ORDER BY viewed*1 DESC

boombanguk

its a varchar(6)

still giving me the wrong result 🙁

Piranha

What do you store in the column, is it only numbers?

boombanguk

yes only numbers.

Weird thing is, is that I have 2 tables. The other table (called news) allows the same query and works fine. Even though when I hit browse in phpmyadmin the viewed field shows numbers, could it be that they are actually in there as strings? but it still shows them as numbers?

boombanguk

I just put the numbers in the columns with phpmyadmin, so they are definately in there as numbers.

Piranha

I think that you don't understand the difference between the string "10" and the integer 10. The string "10" is not a number, it is a string containing 2 signs, "1" and "0". The integer 10 is a number. In a string it compares the first sign, orders by that, then compages the next sign, order by that, and so on. So the string "10" is lower than the string "8". When it is a int it compares according the the actual number, then 8 is lower than 10.

But to solve your problem. If you only will store numbers you should use some datatype that stores numbers. Not only will it work with order, it will take less space as well.

boombanguk

yes you are right, I changed it to a INT and its worked, odd thing is though, that the other table also uses a varchar but gives the correct result. The other thing is to me because I come from an actionscript background, an INT type is a var that holds values up to 255. But in PHP its greater right?

Piranha

I don't know for sure how big an INT is in PHP, just make a few checks to find out. But it is way bigger than 255, I would guess that it is at least between -4 billion and +4 billion.

cgraz

Set your INT field to be UNSIGNED if you're not going to have any negative values. This will give you some more space.

Standard Int: -2,147,483,648 to 2,147,483,647
Unsigned: 0 to 4,294,967,295

You can also use SMALLINT, MEDIUMINT, OR BIGINT depending on your requirements. SMALLINT only uses up half the storage space, but can only hold 65,535 (if UNSIGNED)