Incorrect Counts Pulled

[Niki]

I went to add a counter for "Males, Females and Unspecified". 

Upon doing such the page is now counting but the only two that are being counted correctly is Male and Unspecified.

Females count as two when there is only one. And unspecified, while showing the correct amount puts a negative sign in front of the count.

This is the code:

PHP Code:

    $num_male = $db->fetch_field($db->simple_select("userfields", "COUNT(ufid) as count", "fid3='Male'"), "count");
    $num_female = $db->fetch_field($db->simple_select("userfields", "COUNT(ufid) as count", "fid3='Female'"), "count");
    $num_unspecified = $stats_cache['numusers'] - $num_male - $num_female; 


Code:

        <table border="0" cellspacing="'.$theme['borderwidth'].'" cellpadding="'.$theme['tablespace'].'" class="tborder" style="text-align: center;">
             <tbody>
                <tr>
                    <td class="tcat" width="33.3%">{$num_male} Males</td>
                    <td class="tcat" width="33.3%">{$num_female} Females</td>
                    <td class="tcat" width="33.3%">{$num_unspecified} Unspecified</td>
                </tr>
            </tbody>
        </table>

I think I've figured out the possible issue but I do not know a way to fix this or how to pull just specifics.

This seems to be pulling the count of Females/Males for the entire community instead of just this usergroup (hence why females is coming as a negative number, I'm believing since some accounts aren't completely filled out yet)! How would I go about just pulling for this usergroup (#8)?

I believe I'd need to gather the data from usergroup 8 but I'm unsure how to do that to then gather the above information from just that group.

[Diogo Parrinha]

Hi,

To get data from a certain group only you'll need to join with the users table and add a new condition to the where clause, in which you specify that usergroup=8.

As for the counts, please check the table directly from PHPMyAdmin to confirm they are OK first.

Regards,
Diogo

[Niki]

Does it matter where I add in the where clause? I'm running into an error and unsure if I am just putting it on the wrong spot for this or if I just messed up completely because I have no idea what I'm doing.

New code:

PHP Code:

// Usergroup Number Counts
$num_male = $db->fetch_field($db->simple_select("userfields", "WHERE usergroup IN ('4')", "COUNT(ufid) as count", "fid3='Male'"), "count");
$num_female = $db->fetch_field($db->simple_select("userfields", "WHERE usergroup IN ('4')", "COUNT(ufid) as count", "fid3='Female'"), "count");
$num_other = $db->fetch_field($db->simple_select("userfields", "WHERE usergroup IN ('4')", "COUNT(ufid) as count", "fid3='Other'"), "count"); 


Error Message:

MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE usergroup IN ('4') FROM mybbvo_userfields WHERE COUNT(ufid) as count' at line 1
Query:
SELECT WHERE usergroup IN ('4') FROM mybbvo_userfields WHERE COUNT(ufid) as count

[Diogo Parrinha]

Hi,

What you are doing is incorrect.

simple_select('TABLE_HERE', 'FIELDS_TO_GET', 'WHERE_CLAUSE', array(options)

What you had initially was right.
Example:

PHP Code:

$num_male = $db->fetch_field($db->simple_select("userfields", "COUNT(ufid) as total_males", "fid3='Male'"), "total_males"); 


[Niki]

But my original code counts all, not just the ones in usergroup 4. Would I buy the where clause before “fid3=‘Male’”)

[Diogo Parrinha]

Hi,

As I said in a post above, to check for a specific usergroup you need to do a JOIN query between that table and the 'users' table.

There are places all over the MyBB code with JOIN queries. If you search for 'userfields' in all files, you'll probably even find something very close to what you're trying to do.

Regards,
Diogo

[Niki]

Thank you! I missed that part, I'm not the best with PHP and I'm honestly guessing with everything I write and didn't quite understand that you literally meant "join with the users table" lol.

I'll search around for these things. Thanks for the replies and help.

[Diogo Parrinha]

Hi,

It's an SQL JOIN query

Regards,
Diogo

[Niki]

Hi,

It's an SQL JOIN query

Regards,
Diogo

PHP Code:

$num_male = $db->fetch_field( $db->query( "SELECT COUNT(*) as numberofmales FROM ".TABLE_PREFIX."users WHERE usergroup = 16 ", "fid5='Male'"), 'numberofmales' );
$num_female = $db->fetch_field( $db->query( "SELECT COUNT(*) as numberoffemales FROM ".TABLE_PREFIX."users WHERE usergroup = 16 ", "fid5='Female'"), 'numberoffemales' ); 


So I’ve gotten to here. It works but the number of males and females is the same when it should be 2 females and 0 males.
No error anymore but it’s still wrong. Any tips?

[Diogo Parrinha]

Hi,

You are not doing a JOIN. You are running the following query:

Code:

"SELECT COUNT(*) as numberofmales FROM ".TABLE_PREFIX."users WHERE usergroup = 16 ", "fid5='Male'"

And then you pass a second parameter which I don't know what it is and I don't know if it even exists (you need to check the query() function):

Code:

"fid5='Male'"

But that's not what you want.

You'd want somemthing like:

Code:

SELECT COUNT(*) as numberofmales
FROM ".TABLE_PREFIX."userfields uf
LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=u.ufid)
WHERE uf.fid5='Male' AND u.usergroup=16
GROUP BY u.fid5

I just woke up so that could be wrong somewhere but take this as an example.

Regards,
Diogo