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

Hi everyone!

Currently I am learning how to wed MySQL with Perl, and use it for database/internet use. To do this, I of course am using the DBI module. I understand how to search for an id field in Perl by using DBI, but I don't know how I can display a whole table from MySQL in a Perl script. Is there a way to do this?

Currently, my MySQL table has columns:
+----+----------+----------+-------+-----------------+
| id | nickname | password | socks | favorite_number |
+----+----------+----------+-------+-----------------+
|  1 | Cowlick  | salty    |     0 |               4 |
|  2 | andy     | bandy    |     3 |               7 |
+----+----------+----------+-------+-----------------+

[download]

Which is simply an example for a short tutorial site. Also the Perl script I am using is this:
#!/usr/bin/perl -w
use warnings;
use strict;
use DBI;

my $dsn = 'DBI:mysql:test:localhost';
my $db_user_name = '';
my $db_password = '';
my ($id, $password);
my $dbh = DBI->connect($dsn, $db_user_name, $db_password);

my $input_nickname = 'Cowlick';
my $input_password = 'salty';

my $sth = $dbh->prepare( qq/select id, password from users
                           where nickname = '$input_nickname'/);
$sth->execute();

($id, $password) = $sth->fetchrow_array();
$sth->finish();
if ($input_password eq $password)
{ 
  print "\nLogin Successful!\n";
  print "Here are the stats:\n";
  print "="x20,"\n";
  print "id = $id, nick=$input_nickname, pass = $password\n";
}

$dbh->disconnect();

[download]

So far this program does what I want it to, but when I try adding the line:
my $sth = $dbh->prepare( qq/select * from users/);

[download]

instead of the line:
my $sth = $dbh->prepare( qq/select id, password from users where nickn
+ame = '$input_nickname'/);

[download]

Basically my question is what the title says: Is there a way I can display all of the contents of that MySQL table in the Perl script? Any help on this subject would be much appreciated. I have found that learning how to use Perl with MySQL is actually fun, and somewhat easy. I just don't know how to this type of thing. Maybe I am not in the right frame of mind ... anyways, any help would be appreciated!

Andy Summers

Replies are listed 'Best First'.
(jeffa) Re: How can I display an entire MySQL table in Perl?
by jeffa (Bishop) on Jan 02, 2002 at 03:03 UTC
    There are a few ways to display an entire table. The idea is to iterate through the rows that are returned (a list), where each row contains a numbers of elements (another list). 
    # prepare the SQL
my $sth = $dbh->prepare('select * from table');

# execute
$sth->execute();

# fetch first row
my @row = $sth->fetchrow_array();

# print remaining rows 
while (my @row = $sth->fetch_array()) {
   print join(',', @row), "\n";
}

    [download]
    Here is a different way that uses my favorite DBI method, selectall_arrayref(): 
    my $res = $dbh->selectall_arrayref('select * from table');

foreach my $row (@$res) {
   print join(',', @$row), "\n";
}

# html-table style
print "<table>\n";
foreach my $row (@$res) {
   print "\t<tr>\n";
   print map { "\t\t<td>$_</td>\n" } @$row;
   print "\t</tr>\n";
}
print "</table>\n";

    [download]
    If you find yourself doing a lot of the last example, check out DBIx::XHTML_Table, but HTML::Template is still the better choice. Be sure and read HTML::Template Tutorial (which i _finally_ updated to make 'Y2K+2' compliant) for info on displaying database results with it.

    UPDATE:
    Trimbach++ for reminding what i have so long forgotten.

    Just to keep up with ease, here is how it's done in DBIx::XHTML_Table: 

    use DBIx::XHTML_Table;

my $table = DBIx::XHTML_Table->new(
  'DBI:mysql:database:host', 'user', 'pass')
) || die;

$table->exec_query('select * from table');
print $table->output();

    [download]
    And that includes adding the names of the database columns wrapped in <th> tags. You can also stack the method calls: 
    print DBIx::XHTML_Table
   ->new('DBI:mysql:database:host', 'user', 'pass')
   ->exec_query('select * from table')
   ->output();

    [download]
    Of course, if that's all that DBIx::XHTML_Table did, then i would argue that a better way would be to wrap the CGI way into a subroutine ... but, it does more ... ;)

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
F--F--F--F--F--F--F--F--
(the triplet paradiddle)
[reply]
[d/l]
[select]
      If all you want to do is dump the contents of a SELECT statement to an HTML table, use the Power of the CGI module. Instead of this (from jeffa): 
      # html-table style
print "<table>\n";
foreach my $row (@$res) {
   print "\t<tr>\n";
   print map { "\t\t<td>$_</td>\n" } @$row;
   print "\t</tr>\n";
}
print "</table>\n";

      [download]
      I would do this: 
      use CGI qw(:standard *table);
use CGI::Pretty; # For pretty-printed HTML

print start_table;
print Tr(td($_)) foreach @$rows;
print end_table;

      [download]
      Yes, it's just that easy. Of course, the more general solution is using some sort of templating scheme, but in general this is a pretty sweet way of outputting dynamic table data on the fly.

      Gary Blackburn
      Trained Killer

[reply]
[d/l]
[select]
Re: How can I display an entire MySQL table in Perl?
by ellem (Hermit) on Jan 02, 2002 at 06:00 UTC
    I suggest that you RUN to your nearest tech book seller and purchase DuBois' MySQL and Perl for the Web

    You can get it here

    It is a an excellent reference and it is easy to read _and_ you'll learn stuff you didn't know about Perl, MySQL, CGI, DBI and life.

    I do not now nor have I ever worked for anyone related to this book, the publisher, or the website.
    --
    lmoran@wtsg.com
    There's more than one way to do it, just don't use my way.
[reply]
      kwoff reviewed that book quite favorably. Around the monastery, it is a good karma to link books to fatbrain.com, via the [isbn://] link. For example, MySQL and PERL for the Web You don't have to, of course. Looks like Bookpool is selling it for $10(US) less than Fatbrain, but Bookpool is out of stock right now ... going faster than hotcakes on the griddle!

      jeffa

      L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
F--F--F--F--F--F--F--F--
(the triplet paradiddle)
[reply]
[d/l]
        Fatbrain, huh? OK. I'll link to them but Bookpool has very good pricing. (I couldn't wait for my copy of MySQL & Perl -- I had to rush out and get it from the box they were unpacking it from at BN @ Union Square (and I spotted Winona Ryder that day too!))
        --
        lmoran@wtsg.com
        There's more than one way to do it, just don't use my way.
[reply]
      I am a very basic user of Perl and MySQl and I am having real problems in finding suitable examples of perl scripts to query a MYSQL database and output the results to a browser. Not knowing HTML does not help me much either.
      Is this book suitable for a beginner? , if not can you recommend anything that is. My only requirement (at the moment)is to do the following
      1) Create mysql table on remote server (done)
      2) Return data to browser (done but very basically, no formatting yet)
      3) Have a form on the browser screen that I can query the table in some sort of free format or using lookup options for some fields, i.e distinct subject etc

      Any pointers to a good resource to help me out

      TIA

      John
[reply]
        If you work your @$$ off and _do_ what the book tells you to: Yes. Yes you can learn this.
        You're not going to pick this book up and turn to page 80 and have it handed to you.
        And hey be easy on yourself, you posted here, you must know _some_ HTML.
        --
        ellem@optonline.net
        There's more than one way to do it, just don't use my way.
[reply]
Re: How can I display an entire MySQL table in Perl?
by jonjacobmoon (Pilgrim) on Jan 02, 2002 at 06:36 UTC

    These are all good suggests, but I have yet another way.

    I am not sure you said that you wanted to put the db table into an html table. Assuming that you simply wanted to get the data, extract in whole and then put it into an arbitrary html form or any form for that matter, you might try using fetchrow_hashref which I like because it allows you to preserve the column names as keys for easy access.

    This means that if the column names are unknown or you change tables you will be able to reuse your script and have access to the column names.

    I then use Template Toolkit as a template processor for displaying that data.

    (I do like the idea of doing it one fell swoop so I will look into DBIx::XHTML.)

    Edited by footpad, ~Wed Jan 2 07:11:39 2002 (GMT)

[reply]

      use hashes and arrays with an array of hash refs

      my @rows= ();
# an array reference 
 
while (my $row= $sth->fetchrow_hashref) {
    push (@rows, $row);
}
#you can then pass the array of hash refs straight to an
#HTML template loop construct or iterate over the array as
#below

foreach my $str (@$rows) {
    print $str->{'id'};
}

      [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom