How can I display an entire MySQL table in Perl?

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

Comment on How can I display an entire MySQL table in Perl? Select or Download Code

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]
Re: Re: How can I display an entire MySQL table in Perl? by Trimbach (Curate) on Jan 02, 2002 at 04:07 UTC
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]
(jeffa) 2Re: How can I display an entire MySQL table in Perl? by jeffa (Bishop) on Jan 02, 2002 at 21:59 UTC
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]
Re: (jeffa) 2Re: How can I display an entire MySQL table in Perl? by ellem (Hermit) on Jan 04, 2002 at 02:01 UTC
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]
Re: Re: How can I display an entire MySQL table in Perl? by Anonymous Monk on Jan 31, 2002 at 09:41 UTC
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]
Re: Re: Re: How can I display an entire MySQL table in Perl? by ellem (Hermit) on Feb 01, 2002 at 06:59 UTC
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]
Re: Re: How can I display an entire MySQL table in Perl? by hakkr (Chaplain) on Jan 02, 2002 at 18:34 UTC
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]


Perl-Sensitive Sunglasses
	PerlMonks