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?

[@wittmania Member 17 years, 8 months 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?

[@sambauers Participant 17 years, 8 months ago]

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