Select order help

select order

[chrissim]

hi guys,

i'm unable to retrieve data based on the select order highest hits using the query below

SELECT * FROM uniquehits where date = '$time' And Hits > 2 ORDER BY date

eg it should list them in order from the highest to lowest hits

1. abx.com 260 29-05-2010
2. abcd.com 120 29-05-2010
3. 123.com 100 29-05-2010
4. 12a.com 90 29-05-2010

But Query the select function above it was retrieve in this order below but not from the top highest hits

1.123.com 100 29-05-2010
2.12a.com 90 29-05-2010
3.abc.com 260 29-05-2010
4.abcd.com 120 29-05-2010

[lavazza]

Instead of:
SELECT * FROM uniquehits where date = '$time' And Hits > 2 ORDER BY date

Try:
SELECT * FROM uniquehits where date = '$time' And Hits > 2 ORDER BY Hits

[chrissim]

hi,

Yes it did tried that before but it doesn't work and worst still the highest hits like abc.com 260 29-05-2010 was not retrieve at all

[lavazza]

it doesn't work at all

I can't guess what that means...

Do you get an error message?
Do you get a <BLANK> space where you expect to see query results?

-------

What happens if you try
SELECT Hits FROM uniquehits where date = '$time' And Hits > 2;
?

[chrissim]

it can retrieve data but not in order from highest to lower hits

[lavazza]

In creating the uniquehits table, have you explicitly declared the data_type for Hits as an integer?

[chrissim]

yes that's right, table hits created as INT(11) UNSIGNED NOT NULL DEFAULT 0

[Tommybs]

Have you tried

select * from uniquehits where `hits` > 2 order by `hits` DESC

[chrissim]

hi Tommybs,

i did tried the query as you suggested but i still can't get the correct order that i want. The fact that if i ignored the date = $time in my select query it will list all the hits that generate from my previous date.The main point i just want them to retrieve based on my today date in right order from highest to lowest hits

[Tommybs]

select * from uniquehits where `hits` > 2 AND `date` = '$time' order by `hits` DESC

[Tommybs]

Just out of curiosity have you tried running the query in phpmyadmin or something similar actually replacing the $time variable with an actual value to check that, that is working. I can't see anything obviously wrong but it's always best to eliminate all possibilites

[lavazza]

What do you get from
echo '<p> My time is: ' . $time . '</p>' ;
?

[chrissim]

hi lavazza

Echo as My time is: 30-05-2010

[chrissim]

hi Tommybs

Yeah ...my man that's work great :) Everything turned out in right order from highest to lowest hits.
Thanks man and everyones who give me support to my issue here

select * from uniquehits where `hits` > 2 AND `date` = '$time' order by `hits` DESC 

[topr8]

FYI date is a reserved word and you shouldn't use it as a column name