php mysqli object search and sort

Question

Using php 5.3 and mysqli I return a result set from a query that just has usernames, something like

$query_username = "SELECT username FROM some_table WHERE param = 1";
$username =  $mysqliObject->query($query_username);

while($row_username = $username->fetch_object()){
print "<br>Username: $row_username->username";
}

All fine, but here is my problem, there are repeated usernames, and I don't know which names are going to be in the query before hand, could be bob, sue, james. Or it could be tom, dick, harry, tom. What I need to do is print out each username and how many times it shows up in this object. For very strange reasons I CANNOT use neat stuff like group by and count(*) in the query(don't ask it is truly weird). So my question is, what is the fastest way to loop through the returned object(or associative array if need be) to get each unique name and how many times it appears. Thanks for your help and I apologize if this is a freshman CS question, I'm self taught and always filling in the gaps!

MrCode · Accepted Answer · 2012-04-27 06:18:17Z

1

If you really must do it on the PHP side instead of using a GROUP BY clause:

while($row_username = $username->fetch_object())
{
    if(isset($usernames[$row_username['username']]))
    {
        $usernames[$row_username['username']]++;
    }
    else
    {
        $usernames[$row_username['username']] = 1;
    }
}

asort($usernames);
// use ksort() to sort by username instead of the count

// print out the usernames
foreach($usernames as $username => $count)
{
    echo $username . ", count: " . $count;
}

answered Apr 27, 2012 at 6:18

MrCode

64.6k10 gold badges95 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vaene Over a year ago

Perfect, worked great! My only comment is to use fetch_array() instead of fetch_object() for your example. Thanks, not only for the code but the education on arrays!

VolkerK · Accepted Answer · 2012-04-27 06:14:57Z

1

e.g.

$users = array();
while( false!==($row=$result->fetch_array()) ){
    if ( isset($users[$row['username']]) ) {
        $users[$row['username']] += 1;
    }
    else {
        $users[$row['username']] = 1;
    }
}
asort($users);

answered Apr 27, 2012 at 6:14

VolkerK

96.3k20 gold badges169 silver badges232 bronze badges

Collectives™ on Stack Overflow

php mysqli object search and sort

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related