mySQL ORDER BY ASC works, DESC does not...
- by Ben
I've "integrated" SMF into Wordpress by querying the forum for a list of the most recent videos from a specific forum board and displaying them on the Wordpress homepage. However when I add the ORDER BY clause, the query (which I tested on other parts of the same page successfully), breaks.
To add to the mix, I am using the Auto Embed plugin to allow the videos to be played on the homepage, as well as using a jCarousel feature to rotate them. People were kind enough to help me here last time with the regexp to filter the video urls, I'm hoping for the same luck this time!
Here is the entire function (remove the DESC and it works...):
function SMF_getRecentVids($limit=10){
global $smf_settingsphp_d;
if(file_exists($smf_settingsphp_d)) include($smf_settingsphp_d);
include "AutoEmbed-1.4/AutoEmbed.class.php";
$AE = new AutoEmbed();
$connect = new wpdb($db_user,$db_passwd,$db_name,$db_server);
$connect->query("SET NAMES 'UTF8'");
$sql = SELECT m.subject, m.ID_MSG, m.body, m.ID_TOPIC, m.ID_BOARD, t.ID_FIRST_MSG
FROM {$db_prefix}messages AS m
LEFT JOIN {$db_prefix}topics AS t ON (m.ID_TOPIC = t.ID_TOPIC)
WHERE (m.ID_BOARD = 8)
ORDER BY t.ID_FIRST_MSG DESC";
$vids = $connect->get_results($sql);
$c = 0;
$content = "imageCarousel_itemList = [";
foreach ($vids as $vid) {
if ($c > $limit) continue;
//extract video code from body
$input = $vid->body;
$regexp = "/\b(?:(?:https?|ftp):\/\/|www\.)[-a-z0-9+&@#\/%?=~_|!:,.;]*[-a-z0-9+&@#\/%=~_|]/i";
if(preg_match_all($regexp, $input, $matches)) {
$AE->parseUrl($matches[0][0]);
$imageURL = $AE->getImageURL();
$AE->setWidth(290);
$AE->setHeight(240);
$content .= "{url: '".$AE->getEmbedCode()."', title: '".$vid->subject."', caption: '', description: ''},";
}
$c++;
}
$content .= "]";
echo $content;
$wpdb = new wpdb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
}
