troubleshooting Question

Modify mysql statement in php code

Avatar of majdolyan
majdolyanFlag for Saudi Arabia asked on
PHPDatabases
13 Comments1 Solution496 ViewsLast Modified:
The attached PHP code display all users of a mysql table, I want to make a modification and sort displayed names automatically by "item_amt". This is not a column name but it's a calculated value using Count function.

There are two tables involved here: Ad (for ads details, including userid for each ad) & User (for user details).

Any help is appreciated?
if(!defined('SITE_PATH')) die('Access denied');
 
function Members_List_main(){
global $db,$MOD_CFG,$DATE_TIME_FMT; // all globals here
 
//nav stuff starts
 
 $whitelist = array(
   'search_by' => array(
     'type' => 'alpha',
   ),
   'sort_by' => array(
     'type' => 'alpha',
   ),
   'a_d' => array(
     'type' => 'option',
     'options' => array('asc', 'desc'),
   ),
   'off' => array(
     'type' => 'int',
   ),
   'q' => array(
     'type' => 'string',
   ),
 );
 
 $clean = esFilter($_GET, $whitelist);
 
if(!$clean['off'] || $_SERVER['REQUEST_METHOD']=='POST') $clean['off'] = 0;
if(!$clean['a_d']) $clean['a_d'] = 'asc';
 
$num = $clean['off']*$MOD_CFG['DISP_ROWS'];
 
$ql = 'SELECT COUNT(*) FROM '.TBL_USER.' WHERE 1';
 
 $sel_search_by = array(
   'username' => '{Username}',
 );
 
$add_url = '';
 
if($_GET['s_res']) {
  
  if($clean['search_by'] && $sel_search_by[$clean['search_by']] && $clean['q']) {
    $ql.=' AND '.$clean['search_by']." LIKE '%".addcslashes($db->quote($clean['q']), '%_')."%'";
  }
  if($clean['sort_by']) {
    $ql.=' ORDER BY "'.$clean['sort_by'].'" '.$clean['a_d'];
  }
  $add_url='&s_res=1';
  if($clean['search_by'] && $clean['q']) $add_url.='&search_by='.$clean['search_by'].'&q='.urlencode($clean['q']);
  if($clean['sort_by']) $add_url.='&sort_by='.$clean['sort_by'].'&a_d='.$clean['a_d'];
}
 
$numrecs = $db->one_data($ql);
 
$lower = $num+1;
if($lower>$numrecs) $lower = 0;
$upper = $num+$MOD_CFG['DISP_ROWS'];
if($upper>$numrecs) $upper = $numrecs;
 
$ql = str_replace('COUNT(*)','id,username,UNIX_TIMESTAMP(regdate) AS regdate',$ql);
if($MOD_CFG['DISP_ROWS']) $ql.=" LIMIT $num,".$MOD_CFG['DISP_ROWS'];
 
$tpl = &new HawkTpl;
$tpl->InitArray('row');
 
$O_UFS = &Factory::singleton('FieldsSet', '{user_fields}');
 
$res = $db->query($ql);
 
if(mysql_num_rows($res)>0){
 
  $A_UID = array();
  
  while($v = mysql_fetch_assoc($res)) $A_UID[] = $v['id'];
  
  $A_ONLINE = IsUserOnline($A_UID);
  $A_ITEMS = $db->two_col_assoc('SELECT userid,COUNT(*) FROM '.TBL_AD.' WHERE userid IN('.implode(',',$A_UID).') GROUP BY userid');
  
  mysql_data_seek($res,0);
  
  while($v = mysql_fetch_assoc($res)){
    /*
    $v = $O_UFS->FillCustomFieldsArray($v); 
    $O_UFS->AdjustCustomFields($v);
    */
    $v['num'] = ++$num;
    $v['online'] = $A_ONLINE[$v['id']];
    $v['item_amt'] = $A_ITEMS[$v['id']];
    if(!$v['item_amt']) $v['item_amt'] = 0;
    $v['regdate'] = strftime($DATE_TIME_FMT,$v['regdate']);
    $tpl->AddCell('row',$v);
  }
}
 
$tpl_v = compact('numrecs','lower','upper');
$tpl_v['nav'] = GetNavigation(HW_MOD_URL,$MOD_CFG['DISP_ROWS'],10,$numrecs,$clean['off'],$add_url);
//nav stuff ends
 
 $tpl_v['search_by'] = GetAdmSel($sel_search_by, $clean['search_by']);
 
 $a_v = array(
   'username'=>'{Username}',
   'regdate'=>'{Date_Reg}',   
 
 
 );
 
 $tpl_v['sort_by'] = GetAdmSel($a_v, $clean['sort_by']);
 
 $a_v = array(
  'asc'=>'{Ascendant}',
  'desc'=>'{Descendant}',
 );
 
 $tpl_v['a_d'] = GetAdmSel($a_v, $clean['a_d']);
 $tpl_v['q'] = html_esc($clean['q']);
 
return $tpl->Parse(HW_MOD_TPL.'members.htm',$tpl_v);
 
 
}
 
if(!function_exists('GetAdmSel')){
 
function GetAdmSel($a_v,$s_cur = ''){
  $s = '';
  if($a_v) foreach($a_v as $k=>$v){
    $s.='<option'.($k==$s_cur?' selected':'').' value="'.$k.'">'.$v;
  }
  return $s;
}
 
}
 
?>
ASKER CERTIFIED SOLUTION
bleach77

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros