MySQL操作程序八
返回
搜索数据库(search1.pl)
<![CDATA[
use strict;
use DBI;
my(%t,$n,@fld,@rec,$pref,$aref,%seen);
# 连接数据库
$$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 search word=";
chop($t{word1}=<STDIN>);
# 从enq1中 search ourref and ENQ的编号
$aref = $$pref{dbh}->selectcol_arrayref("SELECT id FROM enq1 WHERE ourref LIKE \'\%$t{word1}\%\'
OR ownerno LIKE \'\%$t{word1}\%\'
ORDER BY id DESC");
@{ $t{list1} } = @$aref;
# 从enq1.hullnoid=>hull_no.name search 船名
$aref = $$pref{dbh}->selectcol_arrayref("SELECT enq1.id FROM enq1,hull_no WHERE hull_no.name LIKE \'\%$t{word1}\%\'
AND hull_no.id = enq1.hullnoid
ORDER BY enq1.id DESC");
@{ $t{list2} } = @$aref;
# 两个搜索结果合并(可能有相同id的情况)
@{ $t{list3} } = (@{ $t{list1} },@{ $t{list2} });
@{ $t{list4} } = grep { ! $seen{$_} ++ } @{ $t{list3} };
# 排序
@{ $t{list} } = reverse sort {$a<=>$b} @{ $t{list4} };
if ( $t{list}[0] ) {
print "SEARCH OK\n";
print "@{ $t{list} }\n";
} else {
print "SEARCH NG\n";
}
# 关闭数据库
$$pref{dbh}->disconnect;
]]>
旧搜索程序保存
<![CDATA[
$t{enq1_word} = '';
# main_type1のnameとDWGのsearch
$t{sth} = $self->dbh->prepare("SELECT id, name, DWG FROM main_type1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
@{ $t{DWGs} } = split(/=/,$rec[2]);
$t{DWG} = join(' ',@{ $t{DWGs} });
$t{name1} = $rec[1] . ' ' . $t{DWG};
$t{name1} = lc($t{name1});
if ( $t{name1} =~ /$t{word1}/ ) {
$t{main_type1list}{$rec[0]}++;
$t{SEARCH_OK} = 1;
}
}
$t{sth}->finish;
# enq1 id の獲得
if ( $t{SEARCH_OK} == 1 ) {
@{ $t{main_type1s} } = keys %{ $t{main_type1list} };
for $n ( 0 .. $#{ $t{main_type1s} } ) {
$t{main_type1id} = $t{main_type1s}[$n];
$t{sth} = $self->dbh->prepare("SELECT id,type1id FROM enq1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
@{ $t{name1s} } = split(/==/,$rec[1]);
for $n1 ( 0 .. $#{ $t{name1s} } ) {
if ( $t{main_type1id} == $t{name1s}[$n1] ) {
$t{enq1list1}{$rec[0]}++;
$t{SEARCH_OK} = 2;
}
}
}
$t{sth}->finish;
}
}
if ( $t{SEARCH_OK} == 2 ) {
$t{enq1_word} = 'TYPE_DWG_';
}
# 各partsテーブル search
$t{type1_leng} = $self->dbh->selectrow_array("SELECT count(*) FROM e1");
for $n ( 1 .. $t{type1_leng} ) {
$t{ptable1} = sprintf("%06d",$n);
$t{ptable1} = 'a' . $t{ptable1};
$t{count1} = $self->dbh->selectrow_array("SELECT count(*) FROM e1} WHERE code LIKE \'\%$t{word1}\%\'");
if ( $t{count1} > 0 ) {
$t{main_type1list2}{$n}++;
$t{SEARCH_OK} = 3;
}
}
# enq1 id の獲得
if ( $t{SEARCH_OK} == 3 ) {
@{ $t{main_type1s} } = keys %{ $t{main_type1list2} };
for $n ( 0 .. $#{ $t{main_type1s} } ) {
$t{main_type1id} = $t{main_type1s}[$n];
$t{sth} = $self->dbh->prepare("SELECT id,type1id FROM enq1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
@{ $t{name1s} } = split(/==/,$rec[1]);
for $n1 ( 0 .. $#{ $t{name1s} } ) {
if ( $t{main_type1id} == $t{name1s}[$n1] ) {
$t{enq1list2}{$rec[0]}++;
$t{SEARCH_OK} = 4;
}
}
}
$t{sth}->finish;
}
}
if ( $t{SEARCH_OK} == 4 ) {
$t{enq1_word} .= '_PARTS_';
}
# enq1のshipnameなどのサーチとenq1 idの獲得
$t{sth} = $self->dbh->prepare("SELECT * FROM enq1 ORDER BY id DESC");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{shipname} = $self->dbh->selectrow_array("SELECT name FROM WHERE id = $rec[5]");
$t{name1} = ' ' . $t{shipname};
$t{orderno} = $self->dbh->selectrow_array("SELECT orderno FROM HERE id = $rec[0] and orderno is not NULL");
$t{name1} .= ' ' . $rec[0] . ' ' . $rec[2] . ' ' . $rec[4];
if ( $t{orderno} ) {
$t{name1} .= ' ' . $t{orderno};
}
$t{name1} = lc($t{name1});
if ( $t{name1} =~ /$t{word1}/ ) {
$t{enq1list3}{$rec[0]}++;
$t{SEARCH_OK} = 5;
}
}
$t{sth}->finish;
if ( $t{SEARCH_OK} == 5 ) {
$t{enq1_word} .= '_OURREF_';
}
# enq1 のまとめと出力
if ( $t{SEARCH_OK} == 2 or $t{SEARCH_OK} == 4 or $t{SEARCH_OK} == 5 ) {
@{ $t{enq1list1keys} } = keys %{ $t{enq1list1} };
@{ $t{enq1list2keys} } = keys %{ $t{enq1list2} };
@{ $t{enq1list3keys} } = keys %{ $t{enq1list3} };
@{ $t{enq1list} } = (@{ $t{enq1list1keys} },@{ $t{enq1list2keys} enq1list3keys} });
# 合并重复号
%seen = ();
@{ $t{enq1lists} } = ();
foreach $item (@{ $t{enq1list} }) {
unless ( $seen{$item} ) {
$seen{$item} = 1;
push(@{ $t{enq1lists} },$item);
}
}
@{ $t{enq1s} } = reverse sort {$a<=>$b} @{ $t{enq1lists} };
]]>
返回