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
  • 120 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by MDGM

Dani AI

Generated

Classic gotcha: numeric values stored as text sort lexicographically, not numerically. That makes strings like "10" compare as "1" vs "9", so a row with a value of 10 can appear lower than 9 when you ORDER BY the column. As discovered, the practical fix is to store ratings in a numeric column, but there are safe alternatives and steps to make the change without losing data.

Check for bad values first and back up the table. Example check (find non-numeric or empty ratings):

SELECT rating FROM images
WHERE rating REGEXP '[^0-9]' OR rating = '' OR rating IS NULL;

If values are clean, alter the column to a small numeric type appropriate for your range (ratings 0–10 fit in TINYINT UNSIGNED). Example:

ALTER TABLE images
MODIFY rating TINYINT UNSIGNED NOT NULL DEFAULT 0;

If changing the schema isn’t possible immediately, coerce the column at query time so sorting uses numeric behavior. Two common options:

ORDER BY CAST(rating AS UNSIGNED) DESC

or

ORDER BY rating+0 DESC

Note: casting in the query prevents use of an index on that column and can be slower on large tables. Converting the column to a numeric type is the long‑term solution for correctness and performance. For background on MySQL sorting and casting functions, see the MySQL documentation on sorting rows and CAST/CONVERT functions:

MySQL: Sorting rows
MySQL: CAST and CONVERT

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.