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

I am new to perl and need help with mysql. I have a table in mysql that has 3 rows. The fields are name,phone1,phone2. 
use DBI;

$dsn = "DBI:mysql:database=numbers;host=localhost";
$dbh = DBI->connect($dsn, 'user', 'password');
$sth = $dbh->prepare("SELECT * FROM numbers");
$sth->execute;

[download]

this is when I run into issues at What I want to be able to do is create a loop that goes though each row one at a time then give me an array with the field names. 
  while (my $ref = $sth->SOMETING HERE) {
  print "The name field " . $ref[name];  
  print "The phone1 field " . $ref[phone1];
  print "The phone2 field " . $ref[phone2];
  }

[download]
It would then repeat that for each row in the table. Can this be done? Mike

2004-12-16 Janitored by Arunbear - replaced pre tags with code tags, as per Monastery guidelines

Replies are listed 'Best First'.
Re: Mysql Issues (newb)
by gaal (Parson) on Dec 16, 2004 at 19:38 UTC
    (Did you forget to use CODE tags?)

    You want fetchrow_hashref: 

    while (my $row = $sth->fetchrow_hashref) {
    print map { sprintf "The $_ field: $row->{$_}\n" }
            sort keys %$row;

    # or more simply: $row->{name}, $row->{phone1} etc.
}

    [download]
[reply]
[d/l]
[select]
Re: Mysql Issues (newb)
by nedals (Deacon) on Dec 16, 2004 at 20:16 UTC 
    use strict;
use DBI;

my $dsn = "DBI:mysql:database=numbers;host=localhost";
my $dbh = DBI->connect($dsn, 'user', 'password');
## Instead of using '*', use the column names. That way, if ever you A
+LTER the table this will still work.
my $sth = $dbh->prepare("SELECT name, phone1, phone2 FROM numbers");
$sth->execute();
$sth->bind_columns(\my($name,$phone1,$phone2));
while ($sth->fetch()) {
    print "The name field $name\n";  
    print "The phone1 field $phone1\n";
    print "The phone2 field $phone2\n";
}

    [download]
[reply]
[d/l]
      Generally the hash structure provides a more effective way to deal with the retrieved data.
      I'm still learning the basic of PERL, but I've been using hashes
      to deal the data retrieved from the database.
      So it would be interesting to use hashes instead of arrays. I really don't know if in your case the use of arrays is mandatory.
      but you could do, for example creating a hash like in:
      my %rpt = ( ) ;
      Then, building the hash with the information in the fields:
      while (my @row = $sthNames->fetchrow_array ()) {
    my $P = \%{$rpt{NAME}{$row[0]}};
    $P->{PHONE1} = $row[1];
    $P->{PHONE2} = $row[2];
}

      [download]

      And then, when going to use the information, just use the hash you created, like this:
      ...
foreach my $name (keys %{$rpt{NAME}}) {
    my $P = \%{$rpt{NAME}{$name}};
    $html .= qq {
         <TR>
               <TD>$name</TD>
               <TD>$p->{PHONE1}</TD>
               <TD>$P->{PHONE2}</TD>
             </TR>
    };
}

      [download]
[reply]
[d/l]
[select]
Re: Mysql Issues (newb)
by sasikumar (Monk) on Dec 17, 2004 at 06:55 UTC
    Hi,
    U Can Use Array tooo
    my @ref;
while ((@ref)=$sth->fetchrow_array) {
  print "The name field " . $ref[0];  
  print "The phone1 field " . $ref[1];
  print "The phone2 field " . $ref[2];
  }

    [download]

    Thanks
    Sasi Kumar.G
[reply]
[d/l]

Back to Seekers of Perl Wisdom