count() in showgroups.php

[NickPR]

How would one go about counting the sub parts of the array? i've tried various count() variations but can't seem to crack it i keep getting a figure for the total number in the array at told level (i.e. the number of usergroups).

the reason i ask is because i have a modified version of showgroups which acts as a rollcall and want to show the total number of members belonging to each class (not forum group).

heres the snippit *cough* of code which refers to the building of my array:

PHP Code:

//  Start fetch members 
$users = $DB_site->query("
    SELECT DISTINCT
         $magelofieldselect $surnamefieldselect user.username, user.userrankid, user.activestatus, userclass.title, user.userraceid, user.userid, user.usergenderid, user.userclassid,
         user.usergroupid
        FROM user
        LEFT JOIN usergender ON (usergender.usergenderid = user.usergenderid)
        LEFT JOIN userclass ON (userclass.userclassid = user.userclassid)
        LEFT JOIN userfield ON (userfield.userid = user.userid)
        LEFT JOIN usergroup ON (usergroup.usergroupid = user.usergroupid)
        WHERE usergroup.rollcall = 1 AND user.userclassid != 0 ORDER BY userclassid, username ASC
    ");

// Count totals
$totalactive = $DB_site->query_first("
                SELECT COUNT(*) AS users
                FROM " . TABLE_PREFIX . "user AS user
                LEFT JOIN " . TABLE_PREFIX . "usergroup ON (usergroup.usergroupid = user.usergroupid)
                WHERE usergroup.rollcall=1 AND user.userclassid != 0 AND user.activestatus  !=0
        ");
                
$totalinactive = $DB_site->query_first("
                SELECT COUNT(*) AS users
                FROM " . TABLE_PREFIX . "user AS user
                LEFT JOIN " . TABLE_PREFIX . "usergroup ON (usergroup.usergroupid = user.usergroupid)
                WHERE usergroup.rollcall=1 AND user.userclassid != 0 AND user.activestatus =0
        ");
        
$totalall = $DB_site->query_first("
                SELECT COUNT(*) AS users
                FROM " . TABLE_PREFIX . "user AS user
                LEFT JOIN " . TABLE_PREFIX . "usergroup ON (usergroup.usergroupid = user.usergroupid)
                WHERE usergroup.rollcall=1 AND user.userclassid != 0
        ");
                

while ($user = $DB_site->fetch_array($users)) {
    if (($smodcount++ % 2) == 0) {
        $backcolor = "#94CDF8";
    } else {
        $backcolor = "#6699CC";
    }
    
    process_userinfo();
    
    $userclassid = $user['userclassid'];
    $getuserstatus=$DB_site->query_first("SELECT title FROM useractive WHERE useractiveid=$user[activestatus]");
    $getuserrace=$DB_site->query_first("SELECT title FROM userrace WHERE userraceid=$user[userraceid]");
    $getusergroup=$DB_site->query_first("SELECT title FROM usergroup WHERE usergroupid=$user[usergroupid]");
    $getuserrank=$DB_site->query_first("SELECT title FROM userrank WHERE userrankid=$user[userrankid]");
    $getusergender=$DB_site->query_first("SELECT title FROM usergender WHERE usergenderid=$user[usergenderid]");
                    
    if (!$grouptitle[$usergroupid]) {
            $grouptitle[$userclassid] = $user['title'];
    }
    eval("\$groupinfo[$userclassid] .= \"".fetch_template("rollcall_bit2")."\";");

}

if (is_array($groupinfo)) {
    while(list($key, $val) = each($groupinfo)) {
        
        $classname = $grouptitle["$key"];
        if (substr($classname,-1)!="s") {
            $classname.="s";
        }
        $result = count($classname);
        $adminbits = $val;
        eval("\$groupbits .= \"".fetch_template("rollcall_group2")."\";");
        
    }

}

// build forum jump
construct_forum_jump();
eval('print_output("' . fetch_template('rollcall2') . '");'); 


[Andreas]

Seems like I don't understand anything ^.^
What do you want to count?

[NickPR]

want to count the sub totals of each class:

heres a working example of my page : http://www.mortalis.com/?mainDiv=/forums/testrc.php

Thanks

[Andreas]

OK ... these things like Bards, Beastlords, etc come from table userclass which is linked to table user by userclassid.

And you want to know how many members are in each class, right?

Then use this query

[sql]
select userclassid, count(userclassid) as total from user group by userclassid
[/sql]

[NickPR]

thx KirbyDE. I think this will work if i could only use it properly :P

I first assigned it to a var: $result = $DB_site->query_first("select userclassid, count(userclassid) as total from user group by userclassid");

and then it just showed as 'Array' in each case of $result in the output. So i modified the string in the html template to call $result[total] but now it outputs what appears to be the total of all users (506).

Where am i going wrong ?

heres the modified code:

PHP Code:

process_userinfo();
    
    $userclassid = $user['userclassid'];
    $getuserstatus=$DB_site->query_first("SELECT title FROM useractive WHERE useractiveid=$user[activestatus]");
    $getuserrace=$DB_site->query_first("SELECT title FROM userrace WHERE userraceid=$user[userraceid]");
    $getusergroup=$DB_site->query_first("SELECT title FROM usergroup WHERE usergroupid=$user[usergroupid]");
    $getuserrank=$DB_site->query_first("SELECT title FROM userrank WHERE userrankid=$user[userrankid]");
    $getusergender=$DB_site->query_first("SELECT title FROM usergender WHERE usergenderid=$user[usergenderid]");
                    
    if (!$grouptitle[$usergroupid]) {
            $grouptitle[$userclassid] = $user['title'];
    }
    eval("\$groupinfo[$userclassid] .= \"".fetch_template("rollcall_bit2")."\";");

}

if (is_array($groupinfo)) {
    while(list($key, $val) = each($groupinfo)) {
        $result = $DB_site->query_first("select userclassid, count(userclassid) as total from user group by userclassid");
        $classname = $grouptitle["$key"];
        if (substr($classname,-1)!="s") {
            $classname.="s";
        }
        
        
        $adminbits = $val;
        eval("\$groupbits .= \"".fetch_template("rollcall_group2")."\";");
        
    }

} 


[Andreas]

query_first will only give you the first result row.

If this is what you want you could use
[sql]
select count(*) from user where userclassid=$groupinfo[userclassid]
[/sql]

But I wouldn't do this, as it would cause one additional query for each class.
With the query I posted before you can retrieve the information you want (how many users are in each class) with one query.

Therefore you should use smth. like this:

PHP Code:

$classes = $DB_Site->query("SELECT userclassid, COUNT(userclassid) AS total FROM " . TABLE_PREFIX . "user BROUP BY userclassid");
while ($class = $DB_Site->fetch_array($classes))
  echo "There are $class[total] members in class id $class[userclassid]"; 


Btw: I think your whole code needs some major rework as it seems like you are executing 5 additional queries per user (!).

PHP Code:

$getuserstatus=$DB_site->query_first("SELECT title FROM useractive WHERE useractiveid=$user[activestatus]");
$getuserrace=$DB_site->query_first("SELECT title FROM userrace WHERE userraceid=$user[userraceid]");
$getusergroup=$DB_site->query_first("SELECT title FROM usergroup WHERE usergroupid=$user[usergroupid]");
$getuserrank=$DB_site->query_first("SELECT title FROM userrank WHERE userrankid=$user[userrankid]");
$getusergender=$DB_site->query_first("SELECT title FROM usergender WHERE usergenderid=$user[usergenderid]"); 


I think this is not necessary. You can have all those fields in the first query and thus being returned in array $user:

PHP Code:

$users = $DB_site->query("SELECT $magelofieldselect $surnamefieldselect user.username, user.userrankid, user.activestatus, userclass.title, user.userraceid, user.userid, user.usergenderid, user.userclassid, user.usergroupid,
                          useractive.title AS userstatus, userrace.title AS userrace, userrank.title AS userrank, usergender.title AS usergender, usergroup.title AS usergroup
                          FROM " . TABLE_PREFIX . "user AS user
                          LEFT JOIN " . TABLE_PREFIX . "useractive AS useractive ON (useractive.ueractiveid = user.activestatus)
                          LEFT JOIN " . TABLE_PREFIX . "userrace AS userrace ON (userrace.userraceid = user.userraceid)
                          LEFT JOIN " . TABLE_PREFIX . "userrank AS userrake ON (userrank.userrankid = user.userrankid)
                          LEFT JOIN " . TABLE_PREFIX . "usergender AS usergender ON (usergender.usergenderid = user.usergenderid)
                          LEFT JOIN " . TABLE_PREFIX . "userclass AS userclass ON (userclass.userclassid = user.userclassid)
                          LEFT JOIN " . TABLE_PREFIX . "userfield AS userfield ON (userfield.userid = user.userid)
                          LEFT JOIN " . TABLE_PREFIX . "usergroup AS usergroup ON (usergroup.usergroupid = user.usergroupid)
                          WHERE usergroup.rollcall = 1 AND user.userclassid != 0 ORDER BY user.userclassid, user.username ASC
                         ");