Hello There, Guest! › Register

MySubscriptions 2.2 released.
Looking for a custom plugin for your site? Ask here for a quote.

Incorrect Counts Pulled
#1

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.
Reply
#2

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
To download our paid plugins and receive support you must be a paid subscriber. Click here for more information.
Reply
#3

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:
Quote: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
Reply
#4

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"); 
To download our paid plugins and receive support you must be a paid subscriber. Click here for more information.
Reply
#5

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

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
To download our paid plugins and receive support you must be a paid subscriber. Click here for more information.
Reply
#7

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. Smile
Reply
#8

Hi,

It's an SQL JOIN query Smile

Regards,
Diogo
To download our paid plugins and receive support you must be a paid subscriber. Click here for more information.
Reply




Users browsing this thread: 1 Guest(s)