MySQL操作程序十二(MySQL的数据生成HTML)
返回
<![CDATA[
# make_type.pl,新版本
# main_type1==>HTML化
use strict;
use DBI;
use HTML::Template;
my(%t,$n,@fld,@rec,$pref,$template,@loop);
print "This is make_type.pl.\n";
$t{title}{type1} = 'Equipment Types';
$template = HTML::Template->new(filename => "sample.htm");
@loop = ();
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
#
$t{htmfile} = 'type1.htm';
$t{table1} = 'main_type1';
$t{inputf} = 'main_type1.txt';
# Read main_type1.txt
open(IN,"$t{inputf}") or die "Can't open the file $t{inputf}\n";
while(<IN>){
next if $. == 1;
chop;
@fld = split(/==>/);
push(@{ $t{list0} },$fld[3]);
$t{html_id}{$fld[3]} = $fld[2];
if ( $fld[4] ) {
$t{html_2id}{$fld[3]} = $fld[4];
}
}
close(IN);
# Read data from database
$t{sth} = $$pref{dbh}->prepare("SELECT id,name FROM $t{table1}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{id1} = $rec[0];
$t{name1} = $rec[1];
# $t{name1} =~ s/\s+$//;
$t{name1} = uc($t{name1});
push(@{ $t{list1} },$t{name1});
if ( $t{name_id}{$t{name1}} ) {
print "ERROR,$rec[0],==>,$t{name_id}{$t{name1}}\n";
exit;
} else {
$t{id1} = sprintf("%06d",$t{id1});
$t{tb1} = 'a' . $t{id1};
$t{name_id}{$t{name1}} = $t{tb1};
}
}
$t{sth}->finish;
@{ $t{list} } = (@{ $t{list0} },@{ $t{list1} });
@{ $t{list} } = sort @{ $t{list} };
# 重複項目を削除
my(%seen,$item);
%seen = ();
foreach $item (@{ $t{list} }) {
push(@{ $t{list2} },$item) unless $seen{$item}++;
}
$template->param(title => $t{title}{type1});
# SAVE
open(OUT,">$t{inputf}");
#open(OUT,">tmp1.txt");
print OUT "Filename=$t{inputf}\n";
$t{NO} = 0;
for $n ( 1 .. $#{ $t{list2} } ) {
print OUT $t{table1};
print OUT '==>';
if ( $t{name_id}{$t{list2}[$n]} ) {
$t{tb1} = $t{name_id}{$t{list2}[$n]};
} else {
$t{tb1} = 'a000000';
}
$t{e1} = $t{list2}[$n];
print OUT $t{tb1};
print OUT '==>';
if ($t{html_id}{$t{e1}}) {
$t{h1} = 1;
} elsif ( $t{html_2id}{$t{e1}} ) {
$t{h1} = 1;
} else {
$t{h1} = 0;
}
print OUT $t{h1};
print OUT '==>';
print OUT $t{list2}[$n];
if ( $t{html_2id}{$t{e1}} ) {
print OUT '==>';
print OUT $t{html_2id}{$t{e1}};
$t{html1} = $t{html_2id}{$t{e1}} . '.htm';
$t{e1} = '<a href=./partshtm/' . $t{html1} . '>' . $t{e1} . '</a>';
}
print OUT "\n";
if ( $t{html_id}{$t{e1}} ) {
$t{html1} = $t{name_id}{$t{e1}} . '.htm';
$t{e1} = '<a href=./partshtm/' . $t{html1} . '>' . $t{e1} . '</a>';
}
my %row = (
NO => $n,
E1 => $t{e1}
);
push(@loop, \%row);
}
close(OUT);
$template->param(std_loop => \@loop);
open(OUT,">$t{htmfile}");
print OUT $template->output;
close(OUT);
print "The output file is $t{htmfile}\n";
$$pref{dbh}->disconnect;
]]>
<![CDATA[
# make_type.pl,旧版本
# main_type1==>HTML化
use strict;
use DBI;
use HTML::Template;
my(%t,$n,@fld,@rec,$pref,$template,@loop);
print "This is make_type.pl.\n";
$t{title}{type1} = 'Equipment Types';
$template = HTML::Template->new(filename => "sample.htm");
@loop = ();
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
#
$t{htmfile} = 'type1.htm';
$t{table1} = 'main_type1';
$t{inputf} = 'main_type1.txt';
# Read main_type1.txt
open(IN,"$t{inputf}") or die "Can't open the file $t{inputf}\n";
while(<IN>){
next if $. == 1;
@fld = split(/==>/);
chop($fld[3]);
push(@{ $t{list0} },$fld[3]);
$t{html_id}{$fld[3]} = $fld[2];
}
close(IN);
# Read data from database
$t{sth} = $$pref{dbh}->prepare("SELECT id,name FROM $t{table1}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{id1} = $rec[0];
$t{name1} = $rec[1];
# $t{name1} =~ s/\s+$//;
$t{name1} = uc($t{name1});
push(@{ $t{list1} },$t{name1});
if ( $t{name_id}{$t{name1}} ) {
print "ERROR,$rec[0],==>,$t{name_id}{$t{name1}}\n";
exit;
} else {
$t{id1} = sprintf("%06d",$t{id1});
$t{tb1} = 'a' . $t{id1};
$t{name_id}{$t{name1}} = $t{tb1};
}
}
$t{sth}->finish;
@{ $t{list} } = (@{ $t{list0} },@{ $t{list1} });
@{ $t{list} } = sort @{ $t{list} };
# 重複項目を削除
my(%seen,$item);
%seen = ();
foreach $item (@{ $t{list} }) {
push(@{ $t{list2} },$item) unless $seen{$item}++;
}
$template->param(title => $t{title}{type1});
# SAVE
open(OUT,">$t{inputf}");
print OUT "Filename=$t{inputf}\n";
$t{NO} = 0;
for $n ( 1 .. $#{ $t{list2} } ) {
print OUT $t{table1};
print OUT '==>';
if ( $t{name_id}{$t{list2}[$n]} ) {
$t{tb1} = $t{name_id}{$t{list2}[$n]};
} else {
$t{tb1} = 'a000000';
}
$t{e1} = $t{list2}[$n];
print OUT $t{tb1};
print OUT '==>';
if ($t{html_id}{$t{e1}}) {
$t{h1} = 1;
} else {
$t{h1} = 0;
}
print OUT $t{h1};
print OUT '==>';
print OUT $t{list2}[$n];
print OUT "\n";
if ( $t{html_id}{$t{e1}} ) {
$t{html1} = $t{name_id}{$t{e1}} . '.htm';
$t{e1} = '<a href=' . $t{html1} . '>' . $t{e1} . '</a>';
}
my %row = (
NO => $n,
E1 => $t{e1}
);
push(@loop, \%row);
}
close(OUT);
$template->param(std_loop => \@loop);
open(OUT,">$t{htmfile}");
print OUT $template->output;
close(OUT);
print "The output file is $t{htmfile}\n";
$$pref{dbh}->disconnect;
exit;
]]>
<![CDATA[
# make_hp.pl
# main_name1,main_maker1==>HTML化
use strict;
use DBI;
use HTML::Template;
my(%t,$n,@fld,@rec,$pref,$template,@loop);
$t{title}{name1} = 'Main Ship Equipments';
$t{title}{maker1} = 'Main Marine Manufacturers';
$template = HTML::Template->new(filename => "sample.htm");
@loop = ();
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
print "Please input the name(name1,maker1)=";
chop($t{root1}=<STDIN>);
$t{htmfile} = $t{root1} . '.htm';
$t{table1} = 'main_' . $t{root1};
$t{inputf} = $t{table1} . '.txt';
# Read the data file
open(IN,"$t{inputf}") or die "Can't open the file $t{inputf}\n";
while(<IN>){
next if $. == 1;
@fld = split(/==>/);
chop($fld[1]);
push(@{ $t{list0} },$fld[1]);
}
close(IN);
# Read data from database
$t{sth} = $$pref{dbh}->prepare("SELECT name FROM $t{table1}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{name1} = $rec[0];
# $t{name1} =~ s/\s+$//;
$t{name1} = uc($t{name1});
push(@{ $t{list1} },$t{name1});
}
$t{sth}->finish;
@{ $t{list} } = (@{ $t{list0} },@{ $t{list1} });
@{ $t{list} } = sort @{ $t{list} };
# 重複項目を削除
my(%seen,$item);
%seen = ();
foreach $item (@{ $t{list} }) {
push(@{ $t{list2} },$item) unless $seen{$item}++;
}
$template->param(title => $t{title}{$t{root1}});
open(OUT,">$t{inputf}");
print OUT "Filename=$t{inputf}\n";
$t{NO} = 0;
for $n ( 1 .. $#{ $t{list2} } ) {
print OUT $t{table1};
print OUT '==>';
print OUT $t{list2}[$n];
print OUT "\n";
my %row = (
NO => $n,
E1 => $t{list2}[$n]
);
push(@loop, \%row);
}
close(OUT);
$template->param(std_loop => \@loop);
open(OUT,">$t{htmfile}");
print OUT $template->output;
close(OUT);
print "The output file is $t{htmfile}\n";
$$pref{dbh}->disconnect;
exit;
]]>
返回