Adding to this

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

my @ref = ();
$username = $mem_info[2];
my @names = $username;
my $base_sql=q{SELECT username FROM members WHERE referral };

while (scalar(@names)) {
  my $sql=$base_sql.($#names?'in ('.join(',',map('?',@names)).')':'=?'
+);
  my $sth=$dbh->prepare($sql);
  my $rv=$sth->execute(@names);
  my $res = $sth->fetchall_arrayref();
  @names=map($res->[$_][0],0..$#$res);
  push @ref, join '|',@names if (scalar(@names));
  last if $#ref == 2 ;
}
[download]

That will give me usernames in @ref like example:

This is what I now want to do

I wnat it do add that additional information, its a column in my database called status. How could I add this?

my $base_sql=q{SELECT username,status FROM members WHERE
[download]

Comment on Adding to this Select or Download Code

Replies are listed 'Best First'.
Re: Adding to this by atcroft (Abbot) on Nov 24, 2002 at 17:05 UTC
In response to the first question, regarding going from @ref="name\|name\|..." to @ref="name=member\|name=member\|...", look to the map() statement following your fetchall_arrayref call. @ref is being built by doing a join() using the pipe character("\|") to join the values in @names, and @names is being pieced together from the contents of the array referred to by $res. Is a map() statement best there, or would a loop actually be clearer, such as: `# # Untested, off-the-cuff code, # probably w/errors in the reference notation # foreach my $response (@{$res}) { push(@names, $response->[0] . '=' . $response->[1]); }` [download] You at least get the idea-it is perhaps a few lines longer, but somewhat clearer, and thus easier for you to update later. Don't know if that would do it, but it may at least give you an idea on how to handle it. I hope that helps, and I look forward to the responses of other, more knowledgable monks who may can provide additional (better?) ideas. Update: Corrected $response[1] in code segment to read $response->[1]. (Still unsure of the code, though.)	[reply] [d/l]
Re: Adding to this by Ido (Hermit) on Nov 24, 2002 at 20:12 UTC
I'm not sure I fully understnad what you're trying to do and what exactly the problem is. (saying what you have and what you want to have is like saying "I want to get from point A to point B. Can you show me the path?", and of course, between point A and point B, there's an infinite number of paths.) But I will try my best shot. So..as far as I understand, the problem is you can't contain the additional data you want in the final result array (@ref) in the array you use to build it (@names) because @names is later used to grab the next results. Hence, we should build @ref directly from $res. Here's how I would write the code: my @ref; my @names = $username = $mem_info[2]; #Scalar assignment returns the a +ssigned value, you can use it. my $base_sql=q{SELECT username,status FROM members WHERE referral }; while (@names) { #No scalar() needed - boolean context is just a speci +al kind of scalar context. my $sql=$base_sql.($#names?'in ('.join(',',map('?',@names)).')':'=?' +); my $sth=$dbh->prepare($sql); my $rv=$sth->execute(@names); my $res = $sth->fetchall_arrayref(); @names=map($_->[0],@$res); #Use map to map by each value of the arra +y, instead of by indices. push @ref, join '\|',map("$_->[0]=$_->[1]",@$res) if @$res; #@names c +ontains as many values as @$res does. last if $#ref == 2 ; } [download] hth..	[reply] [d/l]