Gossamer Forum
Home : Products : Gossamer Links : Development, Plugins and Globals :

Global: List where the users come from?

Quote Reply
Global: List where the users come from?
Hi Andy,
think there is no one else, who can help me with this one :-)

I know how to export the city a user comes, from gcommunity to glinks
Code:
<%Plugins::Auth_Community::get_profile%>
<%prof_city%>

Do you have a global, to show a list of the cities where the most users come from?
Something like:
There are
20 Users from Hamburg
19 Users from Munich
17 Users from Berlin

Thanks
Matthias

Matthias
gpaed.de
Quote Reply
Re: [Matthias70] Global: List where the users come from? In reply to
Hi,

This should do it. It won't sort them by the amount of users per city, but it does get all the "unique" cities, and the number of users in them.

Code:
sub {

use lib '/path/to/community/private/lib';

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

my $sth = $NEWDB->table('comm_users')->select( ['DISTINCT(prof_city)'] ) || die $GT::SQL::error;

my @loop;
while (my $hit = $sth->fetchrow_hashref) {
if (length($hit->{prof_city}) < 1) { next; }
my $tmp;
$tmp->{City} = $hit->{prof_city};
$tmp->{count} = $NEWDB->table('comm_users')->count( { prof_city => $tmp->{City} } );
push @loop, $tmp;
}

return { city_loop_count => \@loop }

}

Call with:

Code:
<%global_name%>
<%loop city_loop_count%>
<%City%> total- <%count%> <br />
<%endloop%>

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] Global: List where the users come from? In reply to
Hi Andy,
thanks I get an output, but totally unsorted.
Is there a way to sort this list?
And is there a way to hide cities with 1 entry (there are some typos or fantasy cities I don't want to show)

Thanks
Matthias

Matthias
gpaed.de
Quote Reply
Re: [Matthias70] Global: List where the users come from? In reply to
HI,

I'm just too darn nice Wink

I had to rewrite the whole thing - but it all tests ok my end:

Code:
sub {

my $ignore;
$ignore->{'Place'} = 1;
$ignore->{'Test'} = 1;


use lib '/home/user/domain.com/secure_data/lib';

my $NEWDB = new GT::SQL (
def_path => '/home/user/domain.com/secure_data/defs',
cache => 0,
debug => 0,
subclass => 0
);

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

my @cities;
while (my ($s, $c) = $sth->fetchrow_array) {
if (length($s) < 2) { next; }
if ($c < 2) { next; }
if ($ignore->{$s}) { next; }
push @cities, { Name => $s, count => $c }
}

return { city_loop_count => \@cities }

}

Call it in the same way as before.

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!

Last edited by:

Andy: Dec 23, 2007, 4:13 AM
Quote Reply
Re: [Andy] Global: List where the users come from? In reply to
Hi Andy,
I get the following error
Code:
Unable to compile 'user_cities': syntax error at (eval 28) line 24, near ")) " syntax error at (eval 28) line 28, near "} return" Global symbol "@cities" requires explicit package name at (eval 28) line 28.

Matthias

Matthias
gpaed.de
Quote Reply
Re: [Matthias70] Global: List where the users come from? In reply to
Oops, try the modified version.

Code:
if ($ignore->{$s})) { next; }

should have been:

Code:
if ($ignore->{$s}) { next; }

(there was an extra ) )

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] Global: List where the users come from? In reply to
Ok now I have an output sorted by number of cities
but there is a
Unknown Tag: 'City'
and the cut off <2 does not work. There are still entries with 1 city.

Matthias

Matthias
gpaed.de
Quote Reply
Re: [Matthias70] Global: List where the users come from? In reply to
Hi,

Try this:

Code:
<%global_name%>
<%loop city_loop_count%>
<%Name%> total- <%count%> <br />
<%endloop%>

Quote:
and the cut off <2 does not work. There are still entries with 1 city.

Mmm.. weird - you got a URL I can see this happening? the length($s) < 2 bit should work fine.

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] Global: List where the users come from? In reply to
Hi Andy,
I don't mean the length of the city name, but the number of cities.
Cities with only one entry should not be shown.

testing site
www.gpaed.de/g-topdatei

Matthias

Matthias
gpaed.de
Quote Reply
Re: [Matthias70] Global: List where the users come from? In reply to
Hi,

Ah, ok =)

In that case, give the above modified version a go.

I've just added in:

Code:
if ($c < 2) { next; }

That should do the trick =)

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] Global: List where the users come from? In reply to
Hi Andy,
works perfect, thanks a lot.
I paypaled you not only for this one, but for the support in the last weeks
Matthias

Matthias
gpaed.de
Quote Reply
Re: [Matthias70] Global: List where the users come from? In reply to
Hi,

Thanks 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: [Andy] Global: List where the users come from? In reply to
Hi Andy,
I modified this global to list other fields from my gcommunity user table. Works fine for many other fields Smile

At the moment I'm trying to show a list with the age of my users.
The age of the users
31 years (50 users)
37 years (48 users)
29 years (37 users)

I can modify your global, but it makes no sense to list day, month and year. This list would be to long.
This list would make only sense, when days and months are cut off, and only the year would be compared and counted.

Is there a way to take only the years of the field prof_birthday?

Thanks
Matthias

Matthias
gpaed.de
Quote Reply
Re: [Matthias70] Global: List where the users come from? In reply to
Hi Andy
is it possible to use this
Code:
spliy /-/,
to get only the year from the date?

Matthias

Matthias
gpaed.de
Quote Reply
Re: [Matthias70] Global: List where the users come from? In reply to
HI,

Not quite as simple as that =)

You need something like this: (it WON'T sort by age, I tried - but couldn't get it working)

Code:
sub {

use GT::SQL;

use lib '/path/to/secure_data/lib';

my $NEWDB = new GT::SQL (
def_path => '/path/to/secure_data/defs',
cache => 0,
debug => 0,
subclass => 0
);

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

my $current = GT::Date::date_get();
my @current = split /-/, $current; # $current[0]

my $ages;
my $back;
while (my ($s, $c) = $sth->fetchrow_array) {

if ($s !~ /^(19|20)\d\d-/) { next; }

my @date = split /-/, $s; # yyyy-mm-dd

my $age = $current[0] - $date[0]; # age of person looking at

if ($ages->{$age}) { $ages->{$age} += 1; } else { $ages->{$age} = 1;}

}


my @entries;
map {
push @entries, { Age => $_, Count => $ages->{$_} }
} sort keys %$ages;


return { age_loop => \@entries }

}

Then call with:

Code:
<%global_name%>

<ul>
<%loop age_loop%>
<li><%Age%> - <%Count%></li>
<%endloop%>
</ul>

Hopefully thats ok Tongue

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] Global: List where the users come from? In reply to
Hi Andy,
do you see a way to sort it by number of same age?
like:
There are
50 user 35 years old
45 user 37 years old
34 user 42 years old
23 user 24 years old

Thanks
Matthias

Matthias
gpaed.de
Quote Reply
Re: [Matthias70] Global: List where the users come from? In reply to
Hi,

Afraid not. The only way I can think of, is having a script that populates a table (say comm_AgeCounts), and then you can do a simple query like:


SELECT COUNT(*) FRO comm_AgeCount ORDER BY Age DESC

..or similar :)

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] Global: List where the users come from? In reply to
Andy wrote:
Hi,

Afraid not. The only way I can think of, is having a script that populates a table (say comm_AgeCounts), and then you can do a simple query like:


SELECT COUNT(*) FRO comm_AgeCount ORDER BY Age DESC

..or similar :)

Cheers

Hi Andy,
I don't want to sort it by age but by the number of users!
Number of most users with the same age should be on top...

Thanks
Matthias

Matthias
gpaed.de
Quote Reply
Re: [Matthias70] Global: List where the users come from? In reply to
Hi,

Yeah, that still won't be possible with the above global :( (at least, I don't know how Tongue).

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] Global: List where the users come from? In reply to
Andy wrote:
HI,

Not quite as simple as that =)

You need something like this: (it WON'T sort by age, I tried - but couldn't get it working)

Code:
sub {

use GT::SQL;

use lib '/path/to/secure_data/lib';

my $NEWDB = new GT::SQL (
def_path => '/path/to/secure_data/defs',
cache => 0,
debug => 0,
subclass => 0
);

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

my $current = GT::Date::date_get();
my @current = split /-/, $current; # $current[0]

my $ages;
my $back;
while (my ($s, $c) = $sth->fetchrow_array) {

if ($s !~ /^(19|20)\d\d-/) { next; }

my @date = split /-/, $s; # yyyy-mm-dd

my $age = $current[0] - $date[0]; # age of person looking at

if ($ages->{$age}) { $ages->{$age} += 1; } else { $ages->{$age} = 1;}

}


my @entries;
map {
push @entries, { Age => $_, Count => $ages->{$_} }
} sort keys %$ages;


return { age_loop => \@entries }

}


Then call with:

Code:
<%global_name%>

<ul>
<%loop age_loop%>
<li><%Age%> - <%Count%></li>
<%endloop%>
</ul>

Hi Andy,
this global works fine, but I have some wrong entries in my database.
The global prints out this wrong user entries.
So I have users with the age of 108 and 3, 4 and 5 ;-)

Is there a way to print out only entries between the age of 18 and 80 ? to prevent wrong outputs?

Thanks
Matthias

Matthias
gpaed.de
Quote Reply
Re: [Matthias70] Global: List where the users come from? In reply to
Hi,

Try this.

Change:

Code:
my @entries;
map {
push @entries, { Age => $_, Count => $ages->{$_} }
} sort keys %$ages;

..to:

Code:
my @entries;
map {
if ($_ >= 18 && $_ <= 80) {
push @entries, { Age => $_, Count => $ages->{$_} }
}
} sort keys %$ages;

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] Global: List where the users come from? In reply to
Hi Andy,
that's it, works perfect.
Thanks
Matthias
www.gpaed.de

Matthias
gpaed.de