Member Avatar for MDGM

Hi,

Im having a wierd problem with my mysql query:

I have entries in my database with a rating (out of 10) and a timestamp, and I want to pull out the highest rated entry from the past 30 days. These are the entries I have:

Primary key ----------------- Rating ------------------ Timestamp
1                                3                    979818943
2                                6                    1232277417
3                                9                    2147483647
4                                10                  1232811754

This is my query:

$SecondsInOneMonth = 2629743;
$time = time() - $SecondsInOneMonth;

$result = mysql_query('SELECT * FROM images WHERE `timestamp` > "'.$time.'" ORDER BY ratingav DESC LIMIT 1');

The result from the query pulls out the item with primary key 3.

Any reason why?

Thanks in advance.

Max Mumford.

  • 1 Contributor
  • 1 Reply
  • 107 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by MDGM
Member Avatar for MDGM

****************SOLVED****************

silly mistake, the column `rating` was a varchar, and so when sorting by rating, it treated 10 as being lower than 9. Iv changed it to integer and it works fine.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.