数据库操作程序范例
戻る
如何判断empty table?(用count(*))
<![CDATA[
mysql> select * from a000010;
Empty set (0.03 sec)
mysql> select count(*) from a000010;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.01 sec)
]]>
UPDATE(修改)
<![CDATA[
$t{sql} = 'UPDATE ' . $t{table} . ' set ' . $t{item} . ' = "';
$t{sql} .= $t{GR} . '" where id = ' . $t{id};
$t{DO} = $self->dbh->do($t{sql});
]]>
INSERT(追加)
<![CDATA[
$sql = "INSERT INTO $table (";
for $n ( 0 .. ($#arr-1) ) {
$sql .= "$arr[$n],";
}
$sql .= "$arr[$#arr]) VALUES(";
for $n ( 0 .. ($#arr1-1) ) {
$sql .= "'" . $arr1[$n] . "',";
}
$sql .= "'" . "$arr1[$#arr1]')";
$insert = $self->dbh->do("$sql");
]]>
CREATE TABLE(追加新表格)
<![CDATA[
$t{ptable} = sprintf("%06d",$id);
$t{ptable} = 'a' . $t{ptable};
$t{sql} = 'DROP TABLE IF EXISTS ' . $t{ptable} . ';';
$self->dbh->do($t{sql});
$t{sql} = 'CREATE TABLE ' . $t{ptable};
$t{sql} .= ' (';
$t{sql} .= 'id INT AUTO_INCREMENT,';
$t{sql} .= 'name VARCHAR(50),';
$t{sql} .= 'code VARCHAR(50),';
$t{sql} .= 'group_id INT,';
$t{sql} .= 'dwg_id INT,';
$t{sql} .= 'price TEXT,';
$t{sql} .= 'oem TEXT,';
$t{sql} .= 'memo TEXT,';
$t{sql} .= 'PRIMARY KEY (id));';
$t{DO} = $self->dbh->do($t{sql});
]]>
插入新项目
<![CDATA[
$t{sql} = "INSERT INTO $t{ptable} (name,code,parts_Unit) ";
$t{sql} .= 'VALUES("' . $t{n1} . '","';
$t{sql} .= $t{c1} . '","';
$t{sql} .= $t{u1} . '")';
$t{DO} = $self->dbh->do("$t{sql}");
]]>
从表格中取一个项目并分解成几个单项(用"=="分开)
<![CDATA[
$t{GR} = $self->dbh->selectrow_array("SELECT $t{item} FROM $t{table} WHERE id = $t{id}");
@{ $t{GRs} } = split(/==/,$t{GR});
@loop = ();
for $n ( 1 .. $#{ $t{GRs} } ) {
my %row = (
id => $t{id},
NO => $n,
name => $t{GRs}[$n]
);
push(@loop, \%row);
}
$t{template}->param(LOOP => \@loop);
HTML::Template
<TMPL_LOOP NAME="LOOP">
id==><TMPL_VAR NAME="id"><br>
NO==><TMPL_VAR NAME="NO"><br>
name==><TMPL_VAR NAME="name"><br>
</TMPL_LOOP>
]]>
从表格中取复数个项目(1)
<![CDATA[
@{ $t{items} } = $self->dbh->selectrow_array("SELECT * FROM $t{table1} where id = $t{id}");
]]>
从表格中取复数个项目(2)
<![CDATA[
$t{sth} = $self->dbh->prepare("SELECT id, time, ourref, owner, ownerno FROM enq1 ORDER BY id DESC");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
my %row = (
id => $rec[0],
time => $rec[1],
ourref => $rec[2]
);
push(@loop, \%row);
}
$t{sth}->finish;
$t{template}->param(LOOP => \@loop);
# 方法2,用reference语句
while (@rec = $t{sth}->fetchrow_array) {
$row_ref = (); # 这个初始化非常重要!
$$row_ref{id} = $rec[0];
$$row_ref{ourref} = $rec[1];
push(@loop, $row_ref);
}
]]>
从表格中取复数个项目(3)
<![CDATA[
@moneyloop = ();
$t{sth} = $self->dbh->prepare("SELECT * FROM money");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{line1} = '<OPTION value="' . $rec[0] . '">' . $rec[1];
$t{line1} .= '==>' . $rec[2] . '</OPTION>';
my %row = (
line1 => $t{line1}
);
push(@moneyloop, \%row);
}
$t{sth}->finish;
货币单位
<SELECT NAME="money1">
<TMPL_LOOP NAME="MONEYLOOP">
<TMPL_VAR NAME="line1">
</TMPL_LOOP>
</SELECT>
]]>
CGI数据传送
<![CDATA[
# Get CGI query object
$t{q} = $self->query();
$t{html} = $t{q}->param("tmpl");
]]>
CGI程序启动设定例
<![CDATA[
<form action="" method="post">
<input type="submit" value="Parts管理へ戻る"><p>
<input type="hidden" name="tmpl" value="mscparts_start">
<input type="hidden" name="id" value="<TMPL_VAR NAME="id">">
<input type="hidden" name="rm" value="modeparts_start">
</form>
]]>
修改数据输入窗口例
<![CDATA[
<table>
<tr>
<td>name</td>
<td><input type=text size=100 name=name value="<TMPL_VAR NAME="name">"></td></tr>
<input type="hidden" name="name" value="">
</table>
]]>
从数个数据中选择一个的画面设置例
<![CDATA[
<form action="" method="post">
<table>
<tr bgcolor=white>
<th>NO</th>
<th><TMPL_VAR NAME="item">名</th>
<th>选择</th>
</tr>
<TMPL_LOOP NAME="LOOP">
<tr bgcolor=cyan>
<td><TMPL_VAR NAME="grid"></td>
<td><TMPL_VAR NAME="name"></td>
<td>
<input type="radio" name = "<TMPL_VAR NAME="item">" value="<TMPL_VAR NAME="grid">">
</td>
</tr>
</TMPL_LOOP>
</table>
<input type="hidden" name=id value="<TMPL_VAR NAME="id">">
<input type="hidden" name=pid value="<TMPL_VAR NAME="pid">">
<input type="hidden" name="table" value="<TMPL_VAR NAME="table">">
<input type="hidden" name="tmpl" value="mscparts_gd1">
<input type="hidden" name="rm" value="modeparts_gd1">
<input type="submit" value="<TMPL_VAR NAME="item">选择">
</form>
]]>
从数个数据中选择一个的画面设置例的CGI程序
<![CDATA[
$t{html} = $t{q}->param("tmpl");
$t{html} = $t{html} . '.htm';
$t{template} = $self->load_tmpl("$t{html}") || die "error loading tmpl";
$t{id} = $t{q}->param("id");
$t{pid} = $t{q}->param("pid");
$t{table} = $t{q}->param("table");
$t{item} = $t{q}->param("item");
# GR/DWG
$t{GR} = $self->dbh->selectrow_array("SELECT $t{item} FROM main_type1 WHERE id = $t{id}");
@{ $t{GRs} } = split(/==/,$t{GR});
@loop = ();
for $n ( 1 .. $#{ $t{GRs} } ) {
my %row = (
grid => $n,
item => $t{item},
name => $t{GRs}[$n]
);
push(@loop, \%row);
}
$t{template}->param(LOOP => \@loop);
$t{template}->param(id => $t{id});
$t{template}->param(pid => $t{pid});
$t{template}->param(table => $t{table});
$t{template}->param(item => $t{item});
]]>
戻る