Gossamer Forum
Quote Reply
Show user gender
Hi Andy,
I have this global to show the gender on my site

Code:
sub {

use lib '/www/community/private/lib';

my $NEWDB = new GT::SQL (
def_path => '/www/community/private/defs',
cache => 0,
debug => 0,
subclass => 0
);

my $db = $NEWDB->table('comm_users');
$db->select_options( "GROUP BY prof_gender","ORDER BY gender_count DESC");
my $sth = $db->select("prof_gender", "COUNT(*) as gender_count") or die "Query Error: $GT::SQL::error";

my @genders;
while (my ($s, $c) = $sth->fetchrow_array) {

push @genders, { Name => $s, count => $c }
}

return { gender_loop_count => \@genders }

}


Code:
<%user_gender%>

<ul>
<%loop gender_loop_count%>
<li><%Name%> - <%count%></li>
<%endloop%>
</ul>


I have two problems with this global
It prints out

0 - 1115
1 - 791

But instead 0 and 1 there should be female and male
and
it would be great if it shows not the number but the percentage
just like this
female - 58 %
male - 42 %

Difficult for me, but perhaps easy for you

Thanks

Matthias
gpaed.de

Last edited by:

Matthias70: Apr 28, 2008, 2:50 PM
Quote Reply
Re: [Matthias70] Show user gender In reply to
Hi,

Making it show the "Male" and "Female" bit shouldn't be hard .. try this global (shouldn't need to edit your template codes at all);

Code:
sub {

use lib '/www/community/private/lib';

my $NEWDB = new GT::SQL (
def_path => '/www/community/private/defs',
cache => 0,
debug => 0,
subclass => 0
);

my $db = $NEWDB->table('comm_users');
$db->select_options( "GROUP BY prof_gender","ORDER BY gender_count DESC");
my $sth = $db->select("prof_gender", "COUNT(*) as gender_count") or die "Query Error: $GT::SQL::error";

my @genders;
while (my ($s, $c) = $sth->fetchrow_array) {
if ($s == 1) { $s = 'Male' } else { $s = 'Female' }
push @genders, { Name => $s, count => $c }
}

return { gender_loop_count => \@genders }

}

Regarding:

Quote:
it would be great if it shows not the number but the percentage
just like this
female - 58 %
male - 42 %

Thats not so simple as to do with a one-liner - so afraid that will have to wait for now, until I've got a bit more free time Smile

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Matthias70] Show user gender In reply to
Hi,

Ok, found a bit of time to look at this for you :P

Try this:

Code:
sub {

use lib '/www/community/private/lib';

my $NEWDB = new GT::SQL (
def_path => '/www/community/private/defs',
cache => 0,
debug => 0,
subclass => 0
);

my $db = $NEWDB->table('comm_users');

# get total of those wth a value in prof_gender
my $total = $db->count( GT::SQL::Condition->new('prof_gender','LIKE','%') ) || 0;

$db->select_options( "GROUP BY prof_gender","ORDER BY gender_count DESC");
my $sth = $db->select("prof_gender", "COUNT(*) as gender_count") or die "Query Error: $GT::SQL::error";

my @genders;
while (my ($s, $c) = $sth->fetchrow_array) {
if ($s == 1) { $s = 'Male' } else { $s = 'Female' }

# VALUE * 100 / OVERALL
my $percentage = $c * 100 / $total;

push @genders, { Name => $s, count => $c, percentage => $percentage }
}

return { gender_loop_count => \@genders }

}

Then, in the loop - you can use <%percentage%>

Hopefully it works (and the math of how to get the percentage is right <G>)

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Show user gender In reply to
Quote:
my $total = $db->count( GT::SQL::Condition->new('prof_gender','LIKE','%') ) || 0;

Not sure why you are using a wildcard. It is redundant. If you are counting where prof_gender is anything then you may as well just do.....

Code:
my $total = $db->count();
Quote Reply
Re: [Wychwood] Show user gender In reply to
Hi,

Not everyone enters their Gender Wink

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Show user gender In reply to
Not sure what point you are making. My example does the same as yours. It's just shorter.
Quote Reply
Re: [Wychwood] Show user gender In reply to
Hi,

He only wants to work out based on the number of people who've entered their Gender.

i.e

Male - 1000
Female - 500
Not specified - 100

There's not point work out the percentages based on 1600 results, cos the percentages won't match up otherwise ;)

Would show something like:

Male: 62.5%
Female : 31.25%

...but we need:


Male: 66.6%
Female : 33.3%

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Show user gender In reply to
Well ok, but your example doesn't do that anyway. To do that you need to count all rows (like I showed) and then count up the total of rows that have a value of 0, 1 and then whatever non-specified is...NULL or empty.

Then find the percentage.

$male_percent = ($total_male_count / $total_rows) * 100;
$female_percent = ($total_female_count / $total_rows) * 100;
$other_percent = ($total_other_count / $total_rows) * 100;
Quote Reply
Re: [Andy] Show user gender In reply to
Hi Andy and Wychwood,
thanks for your help.

Andy your code works perfect.
The output is a little bit too exact Wink
Can I round it up or off???

Code:
Female - 58.5212375458836 %
Male - 41.4787624541164 %


The field prof_gender is set to "Not Null - Yes" so every user has a gender in my database.
So the code works with Wychwood line, too.
Code:
my $total = $db->count();

Matthias
gpaed.de

Last edited by:

Matthias70: Apr 29, 2008, 8:32 AM
Quote Reply
Re: [Matthias70] Show user gender In reply to
Hi,

No problem Tongue

Change:

Code:
my $percentage = $c * 100 / $total;

...to:

Code:
my $percentage = sprintf("%.2f",$c * 100 / $total);

That should do it :)

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Show user gender In reply to
Andy wrote:
Hi,

No problem Tongue

Code:
my $percentage = sprintf("%.2f",$c * 100 / $total);

Hi Andy,
This one is exact enough for me Smile
Code:
my $percentage = sprintf("%.0f",$c * 100 / $total);

Thanks for your help

Matthias
gpaed.de