Re: multiple perl-mysql queries...

From the results you want, i'd just use DBIx::XHTML_Table and be done with it. But since that doesn't answer the question, here's more.

First, you have two completely different queries that produce two completely different results ... how can you expect the same loop to process both? Drop the for loop and process the first query first, the second query second. Also, you may want to experiment with a HandleError since you have your function bail_out:

my $dbh = DBI->connect(
   'connect string','user','pass',
   {RaiseError=>1, HandleError => \&bail_out},
};
[download]

Then bail_out will be called for you when errors occur. Much nicer.

UPDATE:
I really wanted you to refer to the source for DBIx::XHTML_Table, but that is an awful lot to digest for such a simple task. If you prepare the query first, then you can access the names of the columns without having to issue 2 queries — just one. Here is a quicky that uses CGI.pm to generate all HTML:

my $sth = $dbh->prepare("select * from foo");
$sth->execute;

print table(
   Tr [
      th($sth->{NAME}),
      map {map td($_), @$_} $sth->fetchall_arrayref
      
   ]
);
[download]

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)

Comment on Re: multiple perl-mysql queries... Select or Download Code

Replies are listed 'Best First'.
Re: Re: multiple perl-mysql queries... by kiat (Vicar) on Dec 04, 2003 at 14:47 UTC
Thanks, jeffa! Does HandleError work with multiple error message types (e.g. Error connecting to database, Error retrieving records, etc)? In other words, for each error type, I want print out the appropriate error message when an error is encountered.	[reply]
3Re: multiple perl-myslq queries... by jeffa (Bishop) on Dec 04, 2003 at 14:54 UTC
Just check the docs! :) "HandleError" (code ref, inherited) The "HandleError" attribute can be used to provide your own alternative behaviour in case of errors. If set to a reference to a subroutine then that subroutine is called when an error is detected (at the same point that "RaiseError" and "PrintError" are handled). The subroutine is called with three parameters: the error message string that "RaiseError" and "PrintError" would use, the DBI handle being used, and the first value being returned by the method that failed (typically undef). If the subroutine returns a false value then the "RaiseError" and/or "PrintError" attributes are checked and acted upon as normal. 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]
Re: Re: multiple perl-mysql queries... by kiat (Vicar) on Dec 04, 2003 at 15:52 UTC
Thanks for your update, jeffa :) I experimented with your code but I find it rather difficult to understand especially with the double map. I would like to print out the names of the columns, just like your code does (names of columns, followed by columns' values). How can I do that with my code using fetcharray_row()?	[reply]
3Re: multiple perl-mysql queries... by jeffa (Bishop) on Dec 04, 2003 at 18:30 UTC
Using your original code, it would look something like this: `my $sth = $dbh->prepare("select * from foo"); $sth->execute; print qq~<table>\n~; print qq~<tr>\n~; foreach (@{$sth->{NAME}}) { print qq~<th>$_</th>\n~; } print qq~</tr>\n~; while (my @ary = $sth->fetchrow_array) { print qq~<tr valign="top">\n~; foreach (@ary) { $_ = defined($_) ? $_ : ' '; print qq~<td>$_</td>\n~; } print qq~</tr>\n~; } print qq~</table>\n~;` [download] But if you haven't tried DBIx::XHTML_Table yet, please do. 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]