Gossamer Forum: Products: Gossamer Links: Development, Plugins and Globals: Global: List where the users come from?

Dec 23, 2007, 2:54 AM

Matthias70

Enthusiast (826 posts)

Dec 23, 2007, 2:54 AM

Post #1 of 22

Shortcut

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

Dec 23, 2007, 3:23 AM

Andy

Veteran / Moderator (17298 posts)

Dec 23, 2007, 3:23 AM

Post #2 of 22

Shortcut

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

IMPORTANT: I've now moved to ultranerds.co.uk, and the .com will no longer work!
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package (plugins total "value" $3,325 & rising, for just $350)| GLinks ULTRA Package PRO (plugins total "value" $5,625 & rising, for just $500)
Support Forum | Links SQL Plugins | DMOZ Dumps | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Compare our different Plugin packages *new* Free CSS Templates

Dec 23, 2007, 3:39 AM

Matthias70

Enthusiast (826 posts)

Dec 23, 2007, 3:39 AM

Post #3 of 22

Shortcut

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

Dec 23, 2007, 3:47 AM

Andy

Veteran / Moderator (17298 posts)

Dec 23, 2007, 3:47 AM

Post #4 of 22

Shortcut

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

Last edited by:

Andy: Dec 23, 2007, 4:13 AM

Dec 23, 2007, 3:52 AM

Matthias70

Enthusiast (826 posts)

Dec 23, 2007, 3:52 AM

Post #5 of 22

Shortcut

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

Dec 23, 2007, 3:58 AM

Andy

Veteran / Moderator (17298 posts)

Dec 23, 2007, 3:58 AM

Post #6 of 22

Shortcut

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

Dec 23, 2007, 4:03 AM

Matthias70

Enthusiast (826 posts)

Dec 23, 2007, 4:03 AM

Post #7 of 22

Shortcut

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

Dec 23, 2007, 4:05 AM

Andy

Veteran / Moderator (17298 posts)

Dec 23, 2007, 4:05 AM

Post #8 of 22

Shortcut

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

Dec 23, 2007, 4:09 AM

Matthias70

Enthusiast (826 posts)

Dec 23, 2007, 4:09 AM

Post #9 of 22

Shortcut

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

Dec 23, 2007, 4:14 AM

Andy

Veteran / Moderator (17298 posts)

Dec 23, 2007, 4:14 AM

Post #10 of 22

Shortcut

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

Dec 23, 2007, 4:18 AM

Matthias70

Enthusiast (826 posts)

Dec 23, 2007, 4:18 AM

Post #11 of 22

Shortcut

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

Dec 23, 2007, 4:19 AM

Andy

Veteran / Moderator (17298 posts)

Dec 23, 2007, 4:19 AM

Post #12 of 22

Shortcut

Re: [Matthias70] Global: List where the users come from? In reply to

Hi,

Thanks Smile

Cheers

Andy (mod)
andy@ultranerds.co.uk

Dec 23, 2007, 10:57 AM

Matthias70

Enthusiast (826 posts)

Dec 23, 2007, 10:57 AM

Post #13 of 22

Shortcut

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

Dec 25, 2007, 12:33 PM

Matthias70

Enthusiast (826 posts)

Dec 25, 2007, 12:33 PM

Post #14 of 22

Shortcut

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

Dec 26, 2007, 12:40 AM

Andy

Veteran / Moderator (17298 posts)

Dec 26, 2007, 12:40 AM

Post #15 of 22

Shortcut

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

Dec 29, 2007, 12:14 PM

Matthias70

Enthusiast (826 posts)

Dec 29, 2007, 12:14 PM

Post #16 of 22

Shortcut

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

Dec 29, 2007, 12:33 PM

Andy

Veteran / Moderator (17298 posts)

Dec 29, 2007, 12:33 PM

Post #17 of 22

Shortcut

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

Dec 31, 2007, 6:25 AM

Matthias70

Enthusiast (826 posts)

Dec 31, 2007, 6:25 AM

Post #18 of 22

Shortcut

Re: [Andy] Global: List where the users come from? In reply to

Andy wrote:

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

Dec 31, 2007, 6:30 AM

Andy

Veteran / Moderator (17298 posts)

Dec 31, 2007, 6:30 AM

Post #19 of 22

Shortcut

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

Mar 23, 2008, 6:57 AM

Matthias70

Enthusiast (826 posts)

Mar 23, 2008, 6:57 AM

Post #20 of 22

Shortcut

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

Mar 23, 2008, 7:30 AM

Andy

Veteran / Moderator (17298 posts)

Mar 23, 2008, 7:30 AM

Post #21 of 22

Shortcut

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

Mar 23, 2008, 8:37 AM

Matthias70

Enthusiast (826 posts)

Mar 23, 2008, 8:37 AM

Post #22 of 22

Shortcut

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