=**************************************
= Name: Generate HTML from MySQL
= Description:This will grab an entire table from a MySQL database and generate an html table from it
= By: RyMon
=
= Inputs:Arguments: database name, table name, html file to generate (will default to STDOUT), username, and password
=
= Returns:An HTML file, with one big table
=
= Assumes:Assumes you have the DBI::MySQL stuff installed and running at your site.
This will grap ALL the columns from a table, but it would be relatively easy to make it selective
=
= Side Effects:N/A
=
=This code is copyrighted and has= limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=197&lngWId=6=for details.=**************************************
#!perl
use DBI;
#Get arguments from command line
($db,$table,$out_file,$user,$pass)=@ARGV;
#Check for invalid info
unless($db && $table) {
print <<EOUsage;
Usage: $0 <database> <table> <out file> <user> <pass>
\tUnless you specify out file, STDOUT will be used.
\t(Sending an out file of '-' will generate the same result)
\tUser and Pass are optional.
EOUsage
exit();
}
#No out file if '-'
undef $out_file if($out_file eq '-');
#Connect to the DBI using our db, user, and pass
my $dbr=DBI->connect("dbi:mysql:$db",$user,$pass,{
PrintError=>0
}) or die "Can't connect to database; $DBI::errstr\n";
#Get all the rows from our table
my $sth=$dbr->prepare("SELECT * FROM $table")
or die "Can't prepare SQL statement: $DBI::errstr\n";
#Execute our command to get the rows
$sth->execute or die "Can't execute: $DBI::errstr\n";
#Open out_file
if($out_file) {
#Don't open file until now to avoid overwritting
# in case of errors
open OUT,">$out_file" or die "Cannot open $out_file for writting: $!\n";
}
#Header html data
$head=<<EOHead;
<html>
<head>
<title>Database Summary</title>
</head>
<body>
<h1>Data in $db:$table</h1>
<table border=1>
EOHead
out($head);
#Print a table row for each database row
my @row;
while(@row=$sth->fetchrow_array()) {
out("<tr><td>" . join("</td><td>",@row) . "</td></tr>\n");
}
#HTML tail info
$tail=<<EOTail;
</table>
</body>
</html>
EOTail
#Close out_file
if($out_file) {
close OUT;
}
#Disconnect from the database
$dbr->disconnect;
#Use this sub to make it easier when using file || STDOU
sub out {
my $data=shift;
if($out_file) {
print OUT $data;
} else {
print $data;
}
}
|
3/24/2008 1:22:49 PM: 
