function getLastPublishedByCat($status = array(_SSECTION_STATUS_PUBLISHED))
{
$ret = array();
$itemclause = "";
if (!smartsection_userIsAdmin()) {
$smartsectionPermHandler =& xoops_getmodulehandler('permission', 'smartsection');
$items = $smartsectionPermHandler->getGrantedItems('item_read');
$itemclause = " AND itemid IN (" . implode(',', $items) . ")";
}
$sql = "CREATE TEMPORARY TABLE tmp (categoryid INT(8) UNSIGNED NOT NULL,datesub int(11) DEFAULT '0' NOT NULL);";
$sql2 = " LOCK TABLES " . $this->db->prefix('smartsection_items') . " READ;";
$sql3 = " INSERT INTO tmp SELECT categoryid, MAX(datesub) FROM " . $this->db->prefix('smartsection_items') . " WHERE status IN (" . implode(',', $status) . ") {$itemclause} GROUP BY categoryid;";
$sql4 = " SELECT " . $this->db->prefix('smartsection_items') . ".categoryid, itemid, title, short_url, uid, " . $this->db->prefix('smartsection_items') . ".datesub FROM " . $this->db->prefix('smartsection_items') . ", tmp\n\t WHERE " . $this->db->prefix('smartsection_items') . ".categoryid=tmp.categoryid AND " . $this->db->prefix('smartsection_items') . ".datesub=tmp.datesub;";
/*
//Old implementation
$sql = "SELECT categoryid, itemid, question, uid, MAX(datesub) AS datesub FROM ".$this->db->prefix("smartitem_item")."
WHERE status IN (". implode(',', $status).")";
$sql .= " GROUP BY categoryid";
*/
$this->db->queryF($sql);
$this->db->queryF($sql2);
$this->db->queryF($sql3);
$result = $this->db->query($sql4);
$error = $this->db->error();
$this->db->queryF("UNLOCK TABLES;");
$this->db->queryF("DROP TABLE tmp;");
if (!$result) {
trigger_error("Error in getLastPublishedByCat SQL: " . $error);
return $ret;
}
while ($row = $this->db->fetchArray($result)) {
$item = new SmartsectionItem();
$item->assignVars($row);
$ret[$row['categoryid']] =& $item;
unset($item);
}
return $ret;
}
