Skip to:
Content
Pages
Categories
Search
Top
Bottom

Single/Double digit sorting errors in mysql query

  • 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?

Viewing 2 replies - 1 through 2 (of 2 total)
  • 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?


    Sam Bauers
    Participant

    @sambauers

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

Viewing 2 replies - 1 through 2 (of 2 total)
  • You must be logged in to reply to this topic.
Skip to toolbar