bbPress

Info

• 3 posts
• 2 voices
• Started 4 years ago by wittmania
• Latest reply from Sam Bauers
• This topic is not resolved

Tags

• mysql
• sorting

• Posts in this topic

Single/Double digit sorting errors in mysql query

1. • 
   • wittmania
   • Member

   • Posted 4 years ago #

   I am working on adding a "Most Viewed Topics" list to my bb-Topic-Views plugin, and I'm getting hung up on one little thing. This is the code I am using to pull the topic ID and # of views from the topicmeta table:

   $most_viewed = (array) $bbdb->get_results("SELECT topic_id, meta_value FROM $bbdb->topicmeta WHERE meta_key='views' ORDER BY meta_value DESC");

   I then use array_slice to shorten the list to the number of posts the user wants to have listed. The only problem is that the results are sorted out of order.

   For instance, a post with 5 views would come before a post with 40, because 5 is higher than 4. In other words, any post with a single-digit number of views is sorted above posts with a double-digit number of views.

   Has anyone ever had this problem? Any ideas how to fix it?
2. • 
   • wittmania
   • Member

   • Posted 4 years ago #

   Answered my own questions. Thanks to plenty of googling, I learned that I needed to use the cast() function to consider the values as numbers instead of as text strings.

   This is the code I ended up with:
   $most_viewed = (array) $bbdb->get_results("SELECT topic_id, meta_value FROM $bbdb->topicmeta WHERE meta_key='views' ORDER BY cast(meta_value as UNSIGNED) DESC");

   The cast($column as $type) function forces the query to treat the meta_value values as unsigned integers. Nifty, yes?
3. • 
   • Sam Bauers
   • Key Master

   • Posted 4 years ago #

   Yes, nifty. I was going to suggest a PHP solution, but that's much neater.

4. You must log in to post.