Query and Output

[Sarcoth]

My query is working the way I want it, but I'm not sure I'm displaying the output in the most efficient way. Although, it is displaying exactly the way I want.

PHP Code:

/* The usergroups that will appear on the roster */
$usergroupids = $permissions['rostermembers'];                    // the string = 40,41,42,43
$displaygroups = array();
$displaygroups = explode(',',$usergroupids);                    // convert to array

/* More selective on which fields to view from the user table */
$user_table = "user.userid, user.usergroupid, user.membergroupids, user.username, user.joindate, user.lastactivity, user.avatarid ";

/* Create the query for viewing data */
$query = "SELECT $user_table, userfield.* ";
$query .= "FROM " . TABLE_PREFIX . "user AS user ";
$query .= "LEFT JOIN " . TABLE_PREFIX . "userfield AS userfield ";
$query .= "ON user.userid = userfield.userid ";
$query .= "WHERE (user.usergroupid IN ($usergroupids) ";
$query .= "OR FIND_IN_SET(" . implode(', user.membergroupids) OR FIND_IN_SET(', $displaygroups) . ", user.membergroupids)) ";

/* Now let's run the query */
$result = $db->query_read_slave($query);

$rowcount = mysql_num_rows($result);
printf("Rows:  %d<br \>", $rowcount);

foreach($displaygroups AS $groupid) {
    printf("Group:  %s<br \>", $groupid);
    while($roster = $db->fetch_array($result)) {
        $membergroupids = explode(',',$roster['membergroupids']);
        if ($groupid == $roster['usergroupid'] || in_array($groupid, $membergroupids)) {
            printf("Username:  %s<br \>", $roster['username']);
            $charinfo['count']++;
        }
        unset($membergroupids);
    }
    mysql_data_seek($result, 0);
} 


Output
Rows: 7
Group: 40
Username: Name1
Group: 41
Group: 42
Username: Name2
Username: Name3
Username: Name4
Username: Name5
Username: Name6
Username: Name7
Group: 43

The way the output is being displayed right now, it is going through the query multiple times. The number of times is equal to how many groups there are. This won't be much of a problem if there are only 4 groups and the total number of people is low. But, I'm concerned if there are 10 groups and over 200 (or more) users. Any suggestions on a better way to display the data?

Thanks in advance for any suggestions.

[kh99]

If you're asking how it could be done without using mysql_data_seek(), you could build an array of arrays, index the "top level" one by usergroupid, and each element of that would be another array containing any data you need (or just store the row array). Then when you're done you could use a loop similar to what you have, but go through the array you built.

If you're asking if that's a better idea than what you have, I don't know. My gut feeling is that unless your query is very big, it won't make much difference. But I'm not sure.

Edit: Oh, actually if you add an "ORDER BY" clause to your query, you should be able to produce the same output with one pass through the data, you just have to save the previous groupid so you can output a new header when it changes.

[Sarcoth]

@kh99 - Thanks Kevin.

I thought about building a multidimensional array at first, but I'm not sure how to do the top level key. I'm sure that would be the usergroupids (in this case they are 40,41,42,43). I think the problem I would have with that is I would have to perform multiple queries. One for each usergroupids. Not hard to do, but I always thought it was best to do that once. That brings up my primary question, is it more efficient? Will it be slower or faster than what I have above when we get into 500+ or 1000+ records? The plus side would be that the output would go a lot smoother, no replication. The downside would be 1 query per usergroupids. Unless I'm mistaken?

I'm not sure about the ORDER BY. I would have to order by user.usergroupid, but what if the user is in one of the membergroupids instead?

This query checks to see if the user is in either the usergroupid or one of the membergroupids.

[kh99]

Oh, right, I see now. So I guess a user could be displayed in two sections if they happened to be in more than one of those groups. So I guess you're right, ORDER BY wouldn't help.

But I think you can do the array thing with one query. Something like:

while($roster = $db->fetch_array($result)) {
    $ids = explode(',',$roster['membergroupids']);
    $ids[] = $roster['usergroupid'];
    foreach ($ids AS $id) {
        $rostergroups[$id][] = &$roster;
    }
}  

foreach($displaygroups AS $groupid) {
    printf("Group:  %s<br \>", $groupid);
    if (!is_array($rostergroups[$groupid])) continue;
    foreach ($rostergroups[$groupid] AS &$roster) {
        printf("Username:  %s<br \>", $roster['username']);
        $charinfo['count']++;
    }
}

(I haven't tried this at all, so it could be wrong).

[Sarcoth]

@kh99 - I'll test it out and let you know. Thank you.

--------------- Added [DATE]1371565419[/DATE] at [TIME]1371565419[/TIME] ---------------

Output
Rows: 8
Group: 40
Username:
Group: 41
Group: 42
Username:
Username:
Username:
Username:
Username:
Username:
Username:
Group: 43

It is displaying the groups and the correct number of users per group, so that is good. Only problem is it is not displaying the usernames. I'm dissecting it now and will try to figure out where it is going wrong.

--------------- Added [DATE]1371569057[/DATE] at [TIME]1371569057[/TIME] ---------------

Your code:

PHP Code:

while($roster = $db->fetch_array($result)) {
    $ids = explode(',',$roster['membergroupids']);
    $ids[] = $roster['usergroupid'];
    foreach ($ids AS $id) {
        $rostergroups[$id][] = &$roster;
    }
} 


1) I made a quick change inside the foreach statement:

PHP Code:

if (in_array($id, $displaygroups)) {
    $rostergroups[$id][] = &$roster;    //storing user data
} 


That change forces it to only store the important groups I want to display on the roster. Cuts down on what is stored in the array.

But, The way the data is being stored in the array seems weird to me. It goes through each group (user and member) that the user belongs to. That's good. And thanks for showing me that, I never thought to just add the usergroupid to the membergroupids array; awesome.

Anyhow, this was somehow changing all the data stored each time to be the exact same. Check this out.

ID: 42
Array ( [42] => Array ( [0] => Array ( [userid] => 15 [usergroupid] => 42 [membergroupids] => 37 [username] => LordMaus [field43] => Mmaus ) ) )
-----Next-----
ID: 42
Array ( [42] => Array ( [0] => Array ( [userid] => 75 [usergroupid] => 42 [membergroupids] => 37 [username] => Maggie [field43] => Maggie ) [1] => Array ( [userid] => 75 [usergroupid] => 42 [membergroupids] => 37 [username] => Maggie [field43] => Maggie ) ) )
-----Next-----
ID: 42
Array ( [42] => Array ( [0] => Array ( [userid] => 258 [usergroupid] => 42 [membergroupids] => 37 [username] => Pyway [field43] => Enno ) [1] => Array ( [userid] => 258 [usergroupid] => 42 [membergroupids] => 37 [username] => Pyway [field43] => Enno ) [2] => Array ( [userid] => 258 [usergroupid] => 42 [membergroupids] => 37 [username] => Pyway [field43] => Enno ) ) )
-----Next-----
ID: 42
Array ( [42] => Array ( [0] => Array ( [userid] => 537 [usergroupid] => 42 [membergroupids] => 37 [username] => Ranis [field43] => Qinshi ) [1] => Array ( [userid] => 537 [usergroupid] => 42 [membergroupids] => 37 [username] => Ranis [field43] => Qinshi ) [2] => Array ( [userid] => 537 [usergroupid] => 42 [membergroupids] => 37 [username] => Ranis [field43] => Qinshi ) [3] => Array ( [userid] => 537 [usergroupid] => 42 [membergroupids] => 37 [username] => Ranis [field43] => Qinshi ) ) )
-----Next-----
ID: 42
Array ( [42] => Array ( [0] => Array ( [userid] => 20 [usergroupid] => 42 [membergroupids] => 37 [username] => Rraven [field43] => Rraven ) [1] => Array ( [userid] => 20 [usergroupid] => 42 [membergroupids] => 37 [username] => Rraven [field43] => Rraven ) [2] => Array ( [userid] => 20 [usergroupid] => 42 [membergroupids] => 37 [username] => Rraven [field43] => Rraven ) [3] => Array ( [userid] => 20 [usergroupid] => 42 [membergroupids] => 37 [username] => Rraven [field43] => Rraven ) [4] => Array ( [userid] => 20 [usergroupid] => 42 [membergroupids] => 37 [username] => Rraven [field43] => Rraven ) ) )
-----Next-----
ID: 40
Array ( [42] => Array ( [0] => Array ( [userid] => 2 [usergroupid] => 6 [membergroupids] => 22,33,37,40,49 [username] => Sarcoth [field43] => Sarcoth ) [1] => Array ( [userid] => 2 [usergroupid] => 6 [membergroupids] => 22,33,37,40,49 [username] => Sarcoth [field43] => Sarcoth ) [2] => Array ( [userid] => 2 [usergroupid] => 6 [membergroupids] => 22,33,37,40,49 [username] => Sarcoth [field43] => Sarcoth ) [3] => Array ( [userid] => 2 [usergroupid] => 6 [membergroupids] => 22,33,37,40,49 [username] => Sarcoth [field43] => Sarcoth ) [4] => Array ( [userid] => 2 [usergroupid] => 6 [membergroupids] => 22,33,37,40,49 [username] => Sarcoth [field43] => Sarcoth ) ) [40] => Array ( [0] => Array ( [userid] => 2 [usergroupid] => 6 [membergroupids] => 22,33,37,40,49 [username] => Sarcoth [field43] => Sarcoth ) ) )
-----Next-----
ID: 42
Array ( [42] => Array ( [0] => Array ( [userid] => 289 [usergroupid] => 42 [membergroupids] => 37 [username] => Warconan [field43] => WARKRZY ) [1] => Array ( [userid] => 289 [usergroupid] => 42 [membergroupids] => 37 [username] => Warconan [field43] => WARKRZY ) [2] => Array ( [userid] => 289 [usergroupid] => 42 [membergroupids] => 37 [username] => Warconan [field43] => WARKRZY ) [3] => Array ( [userid] => 289 [usergroupid] => 42 [membergroupids] => 37 [username] => Warconan [field43] => WARKRZY ) [4] => Array ( [userid] => 289 [usergroupid] => 42 [membergroupids] => 37 [username] => Warconan [field43] => WARKRZY ) [5] => Array ( [userid] => 289 [usergroupid] => 42 [membergroupids] => 37 [username] => Warconan [field43] => WARKRZY ) ) [40] => Array ( [0] => Array ( [userid] => 289 [usergroupid] => 42 [membergroupids] => 37 [username] => Warconan [field43] => WARKRZY ) ) )
-----Next-----
ID: 42
Array ( [42] => Array ( [0] => Array ( [userid] => 153 [usergroupid] => 42 [membergroupids] => 37 [username] => Wulvar [field43] => Wulvar ) [1] => Array ( [userid] => 153 [usergroupid] => 42 [membergroupids] => 37 [username] => Wulvar [field43] => Wulvar ) [2] => Array ( [userid] => 153 [usergroupid] => 42 [membergroupids] => 37 [username] => Wulvar [field43] => Wulvar ) [3] => Array ( [userid] => 153 [usergroupid] => 42 [membergroupids] => 37 [username] => Wulvar [field43] => Wulvar ) [4] => Array ( [userid] => 153 [usergroupid] => 42 [membergroupids] => 37 [username] => Wulvar [field43] => Wulvar ) [5] => Array ( [userid] => 153 [usergroupid] => 42 [membergroupids] => 37 [username] => Wulvar [field43] => Wulvar ) [6] => Array ( [userid] => 153 [usergroupid] => 42 [membergroupids] => 37 [username] => Wulvar [field43] => Wulvar ) ) [40] => Array ( [0] => Array ( [userid] => 153 [usergroupid] => 42 [membergroupids] => 37 [username] => Wulvar [field43] => Wulvar ) ) )

I changed the pointer (&$roster) to just $roster and that seemed to fix that problem. Here is the last print_r of the $rostergroups array.

ID: 42
Array ( [42] => Array ( [0] => Array ( [userid] => 15 [usergroupid] => 42 [membergroupids] => 37 [username] => LordMaus [field43] => Mmaus ) [1] => Array ( [userid] => 75 [usergroupid] => 42 [membergroupids] => 37 [username] => Maggie [field43] => Maggie ) [2] => Array ( [userid] => 258 [usergroupid] => 42 [membergroupids] => 37 [username] => Pyway [field43] => Enno ) [3] => Array ( [userid] => 537 [usergroupid] => 42 [membergroupids] => 37 [username] => Ranis [field43] => Qinshi ) [4] => Array ( [userid] => 20 [usergroupid] => 42 [membergroupids] => 37 [username] => Rraven [field43] => Rraven ) [5] => Array ( [userid] => 289 [usergroupid] => 42 [membergroupids] => 37 [username] => Warconan [field43] => WARKRZY ) [6] => Array ( [userid] => 153 [usergroupid] => 42 [membergroupids] => 37 [username] => Wulvar [field43] => Wulvar ) ) [40] => Array ( [0] => Array ( [userid] => 2 [usergroupid] => 6 [membergroupids] => 22,33,37,40,49 [username] => Sarcoth [field43] => Sarcoth ) ) )

Once that was fixed, the data is printing out correctly now. Thanks kh99. Looks like the only problem was your first &$roster.

[kh99]

Originally Posted by Sarcoth

Looks like the only problem was your first &$roster.

Oh, yeah, I guess it copies over the existing array if you do that, and all the saved info ends up as the same (empty) array. Sorry about that.