Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

In my SQL I have a table called members, a column called referral in member, says who referred who so:
NAME--REFERRAL
Jack--
Bob---Jack
Joe---Bob
Henry-Joe
Gabe--Jack
Pete--Gabe
Tim---Gabe

[download]
I need to display a list on three levels so, say I gave you jack, and I wanted his referrals. 
Jacks Referrals:
Level 1:
 -Bob
 -Gabe
Level 2:
 -Joe
 -Pete
 -Tim
Level 3:
 -Henry

[download]
$sth = $dbh->do("SELECT referral FROM members WHERE username='Jack'");

[download]
And just go from there. This is how I want to have it though, have it like this. 
@ref = "Bob|Gabe",
"Joe|Pete|Tim",
"Henry";

[download]
I can't find a way to do this

Replies are listed 'Best First'.
Re: Building a List
by UnderMine (Friar) on Nov 23, 2002 at 15:33 UTC
    SQL ... 
    create table test_members (
username varchar(20),
referral varchar(20));

insert into test_members values ('Jack',null);
insert into test_members values ('Bob','Jack');
insert into test_members values ('Joe','Bob');
insert into test_members values ('Henry','Joe');
insert into test_members values ('Gabe','Jack');
insert into test_members values ('Pete','Gabe');
insert into test_members values ('Tim','Gabe');

    [download]
    Perl... 
    use strict;
use DBI;

my $database='db';
my $username='user';
my $password='pass';

my $dbh=DBI->connect('DBI:mysql:database='.$database,
                     $username,$password);
print 'dbh error' if (!$dbh);

my @ref = ();
my @names = ('Jack');
my $base_sql=q{SELECT username FROM test_members WHERE referral };
while (scalar(@names)) {
  my $sql=$base_sql.($#names?'in ('.join(',',map('?',@names)).')':'=?'
+);
  print "$sql\n";
  my $sth=$dbh->prepare($sql);
  print 'sth error' if (!$sth);
  my $rv=$sth->execute(@names);
  print 'rv error' if (!$rv);
  my $res = $sth->fetchall_arrayref();
  @names=map($res->[$_][0],0..$#$res);
  push @ref, join '|',@names if (scalar(@names));
}

print join(',',@ref),"\n";

print "finished\n";

    [download]
    Should do the job

    Hope it helps
    UnderMine

    Update: username and referral were swaped around.. think referral should be called referrer

[reply]
[d/l]
[select]
      When I run it @refer contains nothing?
[reply]
        did you check @ref?

        UnderMine

[reply]
Re: Building a List
by pg (Canon) on Nov 23, 2002 at 18:34 UTC
    You can try this data structure, it implements a directional map in a OO style:

    Referals.pm
package Referals;

use Data::Dumper;

use strict;

sub new {
    shift;
    my $self = {};
    $self->{NAME} = shift;
    $self->{REFERALS} = [];
    bless $self;
    return $self;
}

sub add_referal {
    my $self = shift;
    push @{$self->{REFERALS}}, shift;
}

sub display {
    my $self = shift;
    print "Name: $self->{NAME}\nReferals:\n";
    print Dumper $self->{REFERALS};
}

1;

Referals.pl:
use Referals;

my $Bob = new Referals("Bob");
my $Joe = new Referals("Joe");
my $Jack = new Referals("Jack");
my $Henry = new Referals("Henry");
my $Gabe = new Referals("Gabe");
my $Pete = new Referals("Pete");
my $Tim = new Referals("Tim");

$Jack->add_referal($Bob);
$Bob->add_referal($Joe);
$Joe->add_referal($Henry);
$Jack->add_referal($Gabe);
$Gabe->add_referal($Pete);
$Gabe->add_referal($Tim);

$Jack->display;

    [download]
[reply]
[d/l]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom