MySQL操作程序三
返回
<![CDATA[
# 把enq2的ID输入到enq1中
use strict;
use DBI;
my(%t,$n,@fld,@rec);
# 连接数据库
$t{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$t{dbh} = DBI->connect($t{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$t{dbh}->do("SET NAMES utf8");
if(!$t{dbh}){
print "SQL read ERROR!\n";
exit;
}
# 取得enq2和enq1的对应关系
$t{sth} = $t{dbh}->prepare ("SELECT id,enq1id FROM enq2");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array )
{
$t{enq1}{$rec[0]} = $rec[1];
}
$t{sth}->finish;
for $n (keys %{ $t{enq1} } ) {
push(@{ $t{enq2}{$t{enq1}{$n}} },$n);
}
for $n ( keys %{ $t{enq2} } ) {
@{ $t{tmp} } = sort @{ $t{enq2}{$n} };
$t{enq2list} = join("=",@{ $t{tmp} });
$t{list}{$n} = $t{enq2list};
}
# 把数值代入enq1中
for $n ( keys %{ $t{list} } ) {
$t{value} = $t{list}{$n};
$t{sql} = 'UPDATE enq1 SET enq2s = "';
$t{sql} .= $t{value} . '" WHERE id = "' . $n . '";';
print "$t{sql}\n";
$t{dbh}->do($t{sql});
}
$t{dbh}->disconnect;
]]>
列出enq1 ID供选择(该部分已不用,保存下来做参考)
<![CDATA[
# 列出enq1 ID供选择
$t{sth} = $self->dbh->prepare("select id, ourref from enq1 ORDER BY id DESC");
$t{sth}->execute;
while (@rec = $t{sth}->fetchrow_array) {
$row_ref = (); # 这个初始化非常重要!
if ( $rec[0] == $t{enq1_id} ) {
$t{line1} = '<OPTION VALUE="' . $rec[0] . '" selected="selected">';
$t{line1} .= $rec[0] . '==>' . $rec[1] . '</OPTION>';
} else {
$t{line1} = '<OPTION VALUE="' . $rec[0] . '">';
$t{line1} .= $rec[0] . '==>' . $rec[1] . '</OPTION>';
}
$$row_ref{line1} = $t{line1};
push(@loop, $row_ref);
}
$t{sth}->finish;
$t{template}->param(LOOP => \@loop);
<tr bgcolor="lightcyan" align="center"><td>OURREF</td><td>
<TMPL_VAR NAME="enq1_id">==><TMPL_VAR NAME="ourref1">
<!-- 挑选enq1(OURREF) -->
<form action="" method="post">
<SELECT NAME="enq1_id">
<TMPL_LOOP NAME="LOOP">
<TMPL_VAR NAME="line1">
</TMPL_LOOP>
</SELECT>
<input type="submit" value="OURREF选择"><p>
<input type="hidden" name="id" value="<TMPL_VAR NAME="quo2_id">">
<input type="hidden" name="pat" value="select_enq1">
<input type="hidden" name="rm" value="modequo2">
</form>
<!-- 挑选enq1 -->
<form action="" method="post">
<input type=text name=word1 value="">
<input type="submit" value="OURREF検索"><p>
<input type="hidden" name="table" value="enq1">
<input type="hidden" name="table0" value="quo2">
<input type="hidden" name="item" value="enq1id">
<input type="hidden" name="id" value="<TMPL_VAR NAME="quo2_id">">
<input type="hidden" name="rm" value="modes_header">
</form>
</td></tr>
]]>
返回