我一直在尝试根据分数获得用户的百分位排名。 我可以在phpmyadmin中得到结果 - 但似乎无法在php中正确获取代码。
这是我到目前为止所拥有的....
$db=JFactory::getDBO();
$session =& JFactory::getSession();
$user =& JFactory::getUser();
$userId = $user->id;
$rank = "SELECT rank, cb_cscore,(SELECT 100*(rank / count(*)) FROM `e72jm_comprofiler`,(SELECT @rownum :=0)r WHERE cb_cscore IS NOT NULL) as percentage FROM (SELECT @rownum := @rownum +1 AS rank, `cb_cscore` , `user_id` FROM `e72jm_comprofiler`,(SELECT @row :=0) r WHERE cb_cscore IS NOT NULL AND user_id = $userId LIMIT 0,1";
$db->setQuery( $rank );
$db->query();
但是 - 我一直卡在如何加载结果上......
意思是如何从查询中加载结果.....
任何帮助将不胜感激...
$rank = "SELECT rank, cb_cscore,(SELECT 100*(rank / count(*)) as percentile FROM `e72jm_comprofiler`,(SELECT @rownum :=0)r WHERE cb_cscore IS NOT NULL) as percentage FROM (SELECT @rownum := @rownum +1 AS rank, `cb_cscore` , `user_id` FROM `e72jm_comprofiler`,(SELECT @row :=0) r WHERE cb_cscore IS NOT NULL AND user_id = $userId LIMIT 0,1";
然后,只需像其他列一样将其作为"百分位数"列访问即可。
> 首先,您没有在查询中正确定义数据库表。您需要使用前缀 #__
,因此在您的情况下,它将是#__comprofiler
而不是e72jm_comprofiler
其次,您不需要使用 &
运算符,因此请将=& JFactory
更改为= JFactory
第三,删除将数据插入数据库或更新数据库时使用的$db->query();
然后,您需要使用$rows = $db->loadObjectList();
定义结果
最后,您可以使用foreach
循环显示结果,因为它将是一个数组。
最终代码:
$db = JFactory::getDBO();
$session = JFactory::getSession();
$user = JFactory::getUser();
$userId = $user->id;
$rank = "SELECT rank, cb_cscore,(SELECT 100*(rank / count(*)) FROM `#__comprofiler`,(SELECT @rownum :=0)r WHERE cb_cscore IS NOT NULL) as percentage FROM (SELECT @rownum := @rownum +1 AS rank, `cb_cscore` , `user_id` FROM `#__comprofiler`,(SELECT @row :=0) r WHERE cb_cscore IS NOT NULL AND user_id = $userId LIMIT 0,1";
$db->setQuery($rank);
$rows = $db->loadObjectList();
foreach ($rows as $row) {
echo $row->rank;
echo $row->cb_cscore;
}
我还建议您查看在数据库查询中使用Joomla 2.5编码标准:
http://docs.joomla.org/Selecting_data_using_JDatabase
希望这有帮助
试试这个,
$db=JFactory::getDBO();
$session =& JFactory::getSession();
$user =& JFactory::getUser();
$userId = $user->id;
$rank = "SELECT rank, cb_cscore,
(SELECT 100*(rank / count(*)) FROM `e72jm_comprofiler`,
(SELECT @rownum :=0)r WHERE cb_cscore IS NOT NULL) as percentage
FROM (SELECT @rownum := @rownum +1 AS rank, `cb_cscore` , `user_id` FROM `e72jm_comprofiler`,
(SELECT @row :=0) r WHERE cb_cscore IS NOT NULL)
AND user_id = $userId LIMIT 0,1";
$db->setQuery( $rank );
$db->query();
$res = $db->loadAssocList();//if the result is single row you can use $db->loadAssoc();
echo "<pre/>";
print_r($res);