Count Male/Female Specific Usergroup
Niki Offline
Junior Member
**
Posts: 29
Joined: Oct 2016
Post: #1
Count Male/Female Specific Usergroup
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!

12-11-2017 03:41 AM
Find all posts by this user Quote this message in a reply
RateU Offline
Administrator
*******
Posts: 2,350
Joined: Mar 2010
Post: #2
RE: Count Male/Female Specific Usergroup
The conditions should be placed at the 3rd arguments of the simple_select.

12-16-2017 02:34 AM
Find all posts by this user Quote this message in a reply
Niki Offline
Junior Member
**
Posts: 29
Joined: Oct 2016
Post: #3
RE: Count Male/Female Specific Usergroup
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");

12-16-2017 06:16 AM
Find all posts by this user Quote this message in a reply
Niki Offline
Junior Member
**
Posts: 29
Joined: Oct 2016
Post: #4
RE: Count Male/Female Specific Usergroup

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? Smile

(This post was last modified: 12-16-2017 09:14 AM by Niki.)
12-16-2017 09:13 AM
Find all posts by this user Quote this message in a reply
RateU Offline
Administrator
*******
Posts: 2,350
Joined: Mar 2010
Post: #5
RE: Count Male/Female Specific Usergroup
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");


12-16-2017 10:13 PM
Find all posts by this user Quote this message in a reply
Niki Offline
Junior Member
**
Posts: 29
Joined: Oct 2016
Post: #6
RE: Count Male/Female Specific Usergroup
(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? Smile

^ 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.
(This post was last modified: 12-17-2017 04:16 AM by Niki.)
12-17-2017 03:08 AM
Find all posts by this user Quote this message in a reply
RateU Offline
Administrator
*******
Posts: 2,350
Joined: Mar 2010
Post: #7
RE: Count Male/Female Specific Usergroup
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'
);


(This post was last modified: 12-17-2017 04:32 AM by RateU.)
12-17-2017 04:28 AM
Find all posts by this user Quote this message in a reply
Niki Offline
Junior Member
**
Posts: 29
Joined: Oct 2016
Post: #8
RE: Count Male/Female Specific Usergroup
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.
(This post was last modified: 12-18-2017 07:16 AM by Niki.)
12-18-2017 06:37 AM
Find all posts by this user Quote this message in a reply
RateU Offline
Administrator
*******
Posts: 2,350
Joined: Mar 2010
Post: #9
RE: Count Male/Female Specific Usergroup

SQL Code
u.usergroup IN (8,9)


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


(This post was last modified: 12-19-2017 02:22 AM by RateU.)
12-18-2017 11:26 PM
Find all posts by this user Quote this message in a reply

« Next Oldest | Next Newest »

 Standard Tools
Forum Jump: