use Encode; use CGI; use DBI; my $dbname="nanamizuki"; my $dbhost="your.database.host.addr"; my $dbuser="db user id"; my $dbpass="b password"; my $encstr = "utf-8"; my $cgi = CGI->new(); my $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$dbhost",$dbuser,$dbpass) or die "cannot connect database."; my $sth1 = $dbh->prepare('SELECT t1.id ,t1.event_date ,t2.place ,t3.venue FROM events t1 left outer join places t2 on( t1.place_id = t2.id) left outer join venues t3 on( t1.venue_id = t3.id) WHERE tour_id = ? ORDER BY event_date, id'); my $sth2 = $dbh->prepare('SELECT t2.song_title FROM setlists t1 LEFT OUTER JOIN songs t2 ON( t1.song_id = t2.id) WHERE event_id = ? AND list_type = ? ORDER BY t1.order_index'); my $event_id; my $event_date; my $place; my $venue; my $song_title; my $cgi_id = $cgi->param('id'); $dbh->{AutoCommit}=0; print $cgi->header({charset=>'utf-8'}); print $cgi->start_html( {title=>encode($encstr,decode('euc-jp','公演情報')), charset=>'utf-8'} ); print $cgi->start_table({border=>1}); ############ 開催場所、会場情報展開 print $cgi->start_Tr(); $sth1->bind_param( 1, $cgi_id); $sth1->execute(); $sth1->bind_col( 1, \$event_id ); $sth1->bind_col( 2, \$event_date ); $sth1->bind_col( 3, \$place ); $sth1->bind_col( 4, \$venue ); while( $sth1->fetchrow_hashref() != undef ) { $event_id = encode($encstr, $event_id ); $event_date = encode($encstr, $event_date); $place = encode($encstr, $place ); $venue = encode($encstr, $venue ); print $cgi->td( {nowrap=>'nowrap'}, $cgi->p( encode($encstr, decode('euc-jp','公演日:')).$event_date ), $cgi->p( encode($encstr, decode('euc-jp','開催地:')).$place ), $cgi->p( encode($encstr, decode('euc-jp',' 会場:')).$venue ) ); } $dbh->commit(); print $cgi->end_Tr(); ############ セットリストの展開 print $cgi->start_Tr(); $sth1->bind_param( 1, $cgi_id); $sth1->execute(); $sth1->bind_col( 1, \$event_id ); $sth1->bind_col( 2, \$event_date ); $sth1->bind_col( 3, \$place ); $sth1->bind_col( 4, \$venue ); while( $sth1->fetchrow_hashref() != undef ) { $event_id = encode($encstr, $event_id); print $cgi->start_td({valign=>'top'}); ## セットリスト $sth2->bind_param( 1, $event_id); $sth2->bind_param( 2, 1 ); $sth2->execute(); $sth2->bind_col( 1, \$song_title ); print $cgi->p({class=>"list"}, encode($encstr, decode('euc-jp','セットリスト'))); print $cgi->start_ol(); while( $sth2->fetchrow_hashref() != undef ) { $song_title = encode($encstr, $song_title); print $cgi->li({}, $song_title); } print $cgi->end_ol(); $dbh->commit(); ## アンコール $sth2->bind_param( 1, $event_id); $sth2->bind_param( 2, 2 ); $sth2->execute(); $sth2->bind_col( 1, \$song_title ); print $cgi->p({class=>"list"}, encode($encstr, decode('euc-jp','アンコール'))); print $cgi->start_ol(); while( $sth2->fetchrow_hashref() != undef ) { $song_title = encode($encstr, $song_title); print $cgi->li({}, $song_title); } print $cgi->end_ol(); $dbh->commit(); ## Wアンコール $sth2->bind_param( 1, $event_id); $sth2->bind_param( 2, 3 ); $sth2->execute(); $sth2->bind_col( 1, \$song_title ); print $cgi->p({class=>"list"}, encode($encstr, decode('euc-jp','Wアンコール'))); print $cgi->start_ol(); while( $sth2->fetchrow_hashref() != undef ) { $song_title = encode($encstr, $song_title); print $cgi->li({}, $song_title); } print $cgi->end_ol(); $dbh->commit(); } $sth1->finish(); $sth2->finish(); $dbh->commit(); print $cgi->end_td(); print $cgi->end_Tr(); print $cgi->end_table(); print $cgi->end_html(); $dbh->disconnect();