1

Right, this is a very odd issue that I'm wondering how i am going to explain it succinctly.

I have a WP plugin that records rows in a mysql table like so:

meta_id    post_id    meta_key    meta_value 
65387      605        _likes      9

Then on one of my pages I was running a query to select the most liked posts, ie. ORDER by meta_value DESC. Now i noticed on my site that whenever a post reached 10 it would not appear as top anymore and disappear from the query results. Odd.

I went to the database and (in PHPMyAdmin) ordered by meta_value and it did return 9 as the top results, none of the 10's appeared!?

One thing I thought it might be is the type of field (meta_value), these are the settings:

#   Column      Type        Collation           Attributes  Null    Default
4   meta_value  longtext    latin1_swedish_ci               Yes     NULL

Can anyone think of anything that might mean the ORDER BY is not working when the value is 10 or above!?

Thanks

benhowdle89
  • 36,900
  • 69
  • 202
  • 331

2 Answers2

2

If you can't change column datatype don't worry ... No need to do that

you need to just cast it's values in integers in ORDER BY clause.

user trick :

ORDER BY meta_value+0;

OR

ORDER BY CAST(meta_value as SIGNED);
1

Text fields don't order like number fields do. 9 is a higher value than 10 when descending (In text terms) as 10 starts with 1, and it's the 1 that it's working off, not the whole number, so 9 appears before 10.

BenOfTheNorth
  • 2,904
  • 1
  • 20
  • 46