Counting custom fields in PHP

[GrBear]

Here's the solution I can up with that works.

PHP Code:

$array = array();
$dbquery = $DB_site->query("SELECT field5 FROM " . TABLE_PREFIX . "userfield"); 

while ($queryrow = $DB_site->fetch_array($dbquery))
    {
        $array[$queryrow['field5']]++;
    }
reset($array);
while (list($key, $val) = each($array))
    {
                    $f5stat .= $val . " = " . $key;
    } 


Thanks for all the help folks, it was appreciated!

[rake]

Keep in mind, you'll be running through a table that can be a couple of thousand rows large, depending on the number of members on your board..

You should use the solution provided by MarcoH64

[GrBear]

Good point. It would make sense that it would be quicker for the DB engine to tabulate the totals rather than have PHP parse each row individually from the DB.

I'll give it a shot.

Thanks!

[GrBear]

I had to modify my the code of course for the new SELECT, and by tweaking the SQL statement further, I was able offload the sorting to the DB since I wanted the results displayed by the highest total to the lowest.

PHP Code:

$dbquery = $DB_site->query("SELECT field5, COUNT(field5) AS total FROM " . TABLE_PREFIX . "userfield GROUP BY field5 ORDER BY total DESC"); 

while ($queryrow = $DB_site->fetch_array($dbquery))
    {
        $f5stat .= $queryrow['total'] . " " . $queryrow['field5'];
    } 


There was a bit of extra html layout code in there as well, but for easier readability I left it out.

Much simpler execution to the problem. Again, many thanks! :up:

[Marco van Herwaarden]

Sometimes the solution to something that seems complicated can be real simple.

[pshadow]

this was great. I tried to do the same thing and it works perfect. thanks.

I'm wondering how to make 2 edits though:
1. exclude counting and displaying the empty fields. currently shows 500 blank.
2. not really related but, it displays whats in the field5 fine, how do I get the original field number from the select menus of the custom field? i.e. the ID that is used in the search => getall&field5=3 rather than getall&field5=orange

[Andreas]

1) WHERE field5 != ''
2) SELECT data from profilefield WHERE profilefieldid=5;
Then unserialize data and use the value returned by the other query as an index into this array.

[pshadow]

thanks. first part worked perfect. I'm new to the whole query stuff so not sure what part 2 entails with the unserialize and using other query. I'll have to play around and with that one.

thanks for the quick response

[Andreas]

PHP Code:

$field5 = $DB_site->query_first("SELECT data FROM " . TABLE_PREFIX . "profilefield WHERE profilefieldid = 5");
$fieldvalues = unserialize($field5['data']);

$dbquery = $DB_site->query("SELECT field5, COUNT(field5) AS total FROM " . TABLE_PREFIX . "userfield WHERE field5 != '' GROUP BY field5 ORDER BY total DESC");

while ($queryrow = $DB_site->fetch_array($dbquery))
    {
        $f5stat .= $queryrow['total'] . " " . $fieldvalues[$queryrow['field5']];
    } 


[pshadow]

hmm.. doesn't seem to return anything? no errors though? just count number and blank. nothing for the fieldvalues.