Count Male/Female Specific Usergroup

[Niki]

I'm no where close to being knowledgeable with PHP, all the coding I have was thrown together by looking at a million and twelve different support threads.

I've also asked for help in a million and twelve different places and the closest I've gotten to any help was from Diogo Parrinha on their own personal forum. This person gave me a bump in the right direction but I still have no idea what I'm doing. I was told to do a SQL JOIN query. I've searched all the core files for something similar with userfields and still can't find what I'm needing, or it could be because I don't even know what I'm doing.


This code works but counts for all usergroups not just usergroup 4:

PHP Code:

// Usergroup Number Counts $num_male_pack1 = $db->fetch_field($db->simple_select("userfields", "COUNT(ufid) as count", "fid3='Male'"), "count"); $num_female_pack1 = $db->fetch_field($db->simple_select("userfields", "COUNT(ufid) as count", "fid3='Female'"), "count");

---

When I tried to add in the WHERE for usergroup this threw SQL errors:

PHP Code:

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

---

A shove in the right direction with possibly some examples and explanations would be absolutely wonderful if anyone had any.

I appreciate any help in advance.

Thanks!

[RateU]

The conditions should be placed at the 3rd arguments of the simple_select.

[Niki]

Placed it at the third and still receiving errors.

PHP Code:

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

---

Also tried

PHP Code:

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

---

[Niki]

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?

[RateU]

If there are more than one conditions, you should use AND or OR, not ,

For example (based on your query):

PHP Code:

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

---

[Niki]

(12-16-2017 10:13 PM)RateU Wrote:  If there are more than one conditions, you should use AND or OR, not ,

For example (based on your query):

PHP Code:

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

---

That unfortunately causes an error message.
“SQL Error:
1054 - Unknown column 'usergroup' in 'where clause'
Query:
SELECT COUNT(ufid) as count FROM mybbvo_userfields WHERE usergroup IN ('4') AND fid3='Male'”

(12-16-2017 09:13 AM)Niki Wrote:  

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?

^ The above actually works but I think it is counting users in the group instead of males/females based on profile field. So I do believe I’m getting closer but this is the closest I get.

From reading MyBB docs, the simple select being used in the previous discussions seems to not be able to be used for grabbing usergroups and stuff? Or maybe I read the doc wrong. So I went with this instead.

[RateU]

Yeah, because the usergroup is in users table. You need to join userfields table with users table.

Something like this:

PHP Code:

1 2 3 4 5 6 7 8

$NumMale = $db->fetch_field( $db->query(' SELECT COUNT(*) AS NumMale FROM '.TABLE_PREFIX.'users u LEFT JOIN '.TABLE_PREFIX.'userfields uf ON (u.uid=uf.ufid) WHERE u.usergroup=2 AND uf.fid3="Male" '),'NumMale' );

---

[Niki]

Okay that makes a lot more sense, I've been doing some reading up and your code makes twice the amount of sense that my code does. I wasn't quite for sure how to join users and userfields in one code.

Thanks so much! I'll get to testing it out asap! Much appreciated!


By chance, do you think you could direct me to a link or explain some how how I would add multiple usergroups to one query?

I've tried u.usergroup=(8,9) but the page won't load and I've searched google for different terms regarding arrays and usergroups and queries and nothing has come up helpful.

[RateU]

SQL Code

u.usergroup IN (8,9)

---

Note that beside the ufid, there is no column indexed in userfields table.