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

Hey

Sorry, a newbie type of question, but somehow I never gotten this to work when I tried. Basically I want to retrieve all values from a MySQL database and print it out in a table. The below coding works and displays all the values I want it to display. However, It uses fetchrow_hash, which retrieves the first row values of the table and then calls a while statement using a fetchrow_array, which retrieves all the values except the first row. How can I make this code so it gives it a bit more efficiency by not having to call both fetchrow_hash and array? Isn't there a way just to do something like a while (%hash = $sth->fetchrow_array) to retrieve all the values at once? I tried this but the my script died on me and seemed to slow my server down dramatically? ahh~ I just have a feeling that this is such a simple newbie mistake that I'm doing wrong. It seems like the fetchrow-hash is retrieves ALL the mysql db results (including column names that are not listed in my SELECT statement?) and therefore theirs room for improving efficiency.

print <<EOF;
Company Info:<br>
<table cellpadding="2" cellspacing="0" border="0" width="100%">
    <tr>
        <td>ID</td>
        <td>Name</td>
        <td>Company</td>
        <td>Country</td>
        <td>File</td>
        <td>Summary</td>
        <td>Date Submitted</td>
    </tr>
EOF
#retrieve business plans
$ci_blah = "mypassword";
use DBI;

      $dbh_m = DBI->connect('DBI:mysql:companies','myusername',$ci_bla
+h) or die "Couldn't connect to database: " . DBI->errstr;

         $sql_m = "SELECT id,firstname,lastname,country,file_name,summ
+ary,date FROM companies_db";

         $sth_m = $dbh_m->prepare($sql_m) or die "preparing: ",$dbh_m-
+>errstr;
         $sth_m->execute or die "executing: ", $dbh_m->errstr;
         $results = $sth_m->fetchrow_hashref;
print "    <tr>";
print "        <td>$results->{'id'}</td>";
print "        <td>$results->{'firstname'} $results{'lastname'}</td>";
print "        <td>$results->{'company'}</td>";
print "        <td>$results->{'country'}</td>";
print "        <td>$results->{'file_name'}</td>";
print "        <td>$results->{'summary'}</td>";
print "        <td>$results->{'date'}</td>";
print "    </tr>";

while (@data = 
    $sth_m->fetchrow_array())  # keep fetching until 
                             # there's nothing left
{
print "    <tr>";
print "        <td>$data[0]</td>";
print "        <td>$data[1] $data[2]</td>";
print "        <td>$data[3]</td>";
print "        <td>$data[4]</td>";
print "        <td>$data[5]</td>";
print "        <td>$data[6]</td>";
print "        <td>$data[7]</td>";
print "    </tr>";
}
      $sth_m->finish;
     $dbh_m->disconnect;

print "</table>";

[download]


Anthony

Replies are listed 'Best First'.
Re: Fetchrow_hash/array question with DBI/Perl
by dbwiz (Curate) on Mar 20, 2004 at 21:15 UTC

    It isn't clear what you want to achieve.

    If you want a list of arrays, then use 

    my $lol = $sth_m->fetchall_arrayref()

    [download]
    . If you want a list of hashes, then use 
    my $loh = $sth_m->fetchall_arrayref({});

    [download]
    (notice the empty hash ref passed as an argument).

    In both cases, you get an array ref containing either hashes or arrays,a nd you can loop through the results.

    Have a look at DBI Recipes for some more examples and explanations.

    As a side note, you are using $results->{'company'}, but there is no 'company' column in your query.

[reply]
[d/l]
[select]
      haha, yeah I did that to see how exactly the fetchrow_hashref worked. Even though I left it out of the sql query, it stil retrieved the value, which led me to think that fetchrow_hashref may retrieve all values in the table?

      Thanks, I got the script working with your example etcshadow. I guess I never knew about the "fetchall_hashref", instead I used fetchrow_hashref haha.

      Anthony
[reply]
Re: Fetchrow_hash/array question with DBI/Perl
by etcshadow (Priest) on Mar 20, 2004 at 21:09 UTC
    First, there's no need to call fetchrow_hashref before calling fetchrow_array. Second, you could just call fetchall_arrayref and just iterate over the results. (That will return you a referrence to an array of arrayrefs) 
    $sth_m->execute or die "executing: ", $dbh_m->errstr;

my $table = $sth_m->fetchall_arrayref;
foreach my $row (@$table) {
    print "    <tr>";
    print "        <td>$row->[0]</td>";
    print "        <td>$row->[1] $row->[2]</td>";
    print "        <td>$row->[3]</td>";
    print "        <td>$row->[4]</td>";
    print "        <td>$row->[5]</td>";
    print "        <td>$row->[6]</td>";
    print "        <td>$row->[7]</td>";
    print "    </tr>";
}

    [download]
    ------------
:Wq
Not an editor command: Wq

    [download]
[reply]
[d/l]
[select]
Re: Fetchrow_hash/array question with DBI/Perl
by jeffa (Bishop) on Mar 21, 2004 at 17:32 UTC

    Personally, i strive to avoid print HTML one line at time with a simple print statement. The main reason being that you have quoting issues to worry about, and they simply make your code ugly. HERE DOCS can help, but i am going to show you 3 other ways to do this, each a bit different.

    Version 1: CGI.pm 

    use strict;
use warnings;
use Data::Dumper;

use DBI;
use CGI::Pretty qw(:standard);

my $dbh = DBI->connect(
    qw(DBI:vendor:database:host user pass),
    {RaiseError=>1},
);

my $sth = $dbh->selectall_arrayref('
   SELECT firstname,lastname,login,email FROM student
');

print header,start_html, table(
   Tr(
      map th($_), ('First Name', 'Last Name',qw(Login Email)),
   ),
   map Tr(map td($_),@$_), @$sth
);

    [download]
    We didn't concatenate First and Last name though ...

    Version 2: Template Toolkit 

    use DBI;
use CGI qw(header);
use Template;

my $dbh = DBI->connect( ... );

my $students = $dbh->selectall_arrayref('
   SELECT firstname,lastname,login,email FROM student
',{Slice => {}});

print header;
my $tt = Template->new;
$tt->process(\*DATA, {students => $students}) || die $tt->error();

__DATA__
<table>
   <tr>
      <th>Name</th> <th>Login</th> <th>Email</th>
   </tr>
   [% FOREACH student = students %]
   <tr>
      <td>[% student.lastname %], [% student.firstname %]</td>
      <td>[% student.login %]</td>
      <td>
         <a href="mailto:[% student.email %]">[% student.email %]</a>
      </td>
   </tr>
   [% END %]
</table>

    [download]

    Version 3: DBIx::XHTML_Table ... the most inflexible solution, but i still use it from time to time. 

    use DBIx::XHTML_Table;
use CGI qw(header);

print header, DBIx::XHTML_Table
   ->new(qw(DBI:vendor:database:host user pass))
   ->exec_query('SELECT firstname,lastname,login,email FROM student')
   ->map_cell(sub {my$e=shift;qq|<a href="mailto:$e">$e</a>|},'email')
   ->output
;

    [download]
    Again, didn't concantenate First and Last name, and while i probably could do this with DBIx::XHTML_Table via jumping through hoops of fire ... i would be better off in the long run using Version 2. However, sometimes you do want to keep Last and First name separated -- easy searches and sorts. YMWV, but i highly recommend Version 2.

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
[d/l]
[select]
Re: Fetchrow_hash/array question with DBI/Perl
by pbeckingham (Parson) on Mar 21, 2004 at 01:30 UTC

    I would replace the multiple print statements: 

    print "    <tr>";
print "        <td>$results->{'id'}</td>";
print "        <td>$results->{'firstname'} $results{'lastname'}</td>";
print "        <td>$results->{'company'}</td>";
print "        <td>$results->{'country'}</td>";
print "        <td>$results->{'file_name'}</td>";
print "        <td>$results->{'summary'}</td>";
print "        <td>$results->{'date'}</td>";
print "    </tr>";

    [download]
    with the following, which only calls print once: 
    print "    <tr>",
      "        <td>$results->{'id'}</td>",
      "        <td>$results->{'firstname'} $results{'lastname'}</td>",
      "        <td>$results->{'company'}</td>",
      "        <td>$results->{'country'}</td>",
      "        <td>$results->{'file_name'}</td>",
      "        <td>$results->{'summary'}</td>",
      "        <td>$results->{'date'}</td>",
      "    </tr>";

    [download]
    although it doesn't answer your question.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom