本題です。MySQL+Perlで、検索機能のあるCGIを作成したいと思います。キーワードだけの検索は本を見ながらできました。項目を指定しての検索もできるようにしたいと思います。
http://sample.ethersky-online.net/hatenaman_sql/hatenaman.cgi
このページの詳細検索のようなことがしたいのですが、会員でないとダウンロードできないようです。><
http://cgidown.sannetwork.com/sample/forum/
こちらは、キーワードの検索のみになります。
SELECTのWHERE句の中を.=で繋げていろいろと加工することになると思うのですが、条件分岐が多くなりそうでうまく考えることができません。
条件分岐なども添えて全体の流れのご説明をお願いしたいです。
※スキルは、プログラミング、perl、MySQL初心者です。
質問文中にある例を見ましたが、それぞれの列ごとに別の文字列で検索する仕組みになっています。
1つ目の回答へのコメントにあるような
$IN .= qq{(name LIKE "%$q%" OR title LIKE "%$q%" OR message LIKE "%$q%") };
という検索条件では各列に対して同じ文字列で検索を行ってしまうため、予期しない検索結果になってしまうと思うのですが、いかがでしょうか。
まず、考え方を整理する必要があると思います。
ポイントは以下の2点です。
Perlは得意ではないので考え方だけ説明します。
まず、複数列の条件指定の方法です。
複数のテキストフィールドの値を連想配列に格納します。
cgi-lib.plを使うと良いでしょう。
続いて、連想配列の値を変数に格納します。
以下のようなイメージです。
$qno = $input{'no'}; $qtitle = $input{'title'}; $qname = $input{'name'}; $qzip = $input{'zip'}; $qken = $input{'ken'}; $qmessage = $input{'message'};
その後、その変数が空かどうかをチェックします。
空でないものに関してのみ、WHERE句で使うための検索条件を生成します。
例えば、2つ目の回答へのコメントにあるようにzipとkenにのみ検索条件が指定されていた場合、
$search_condition['zip'] = zip like '%$qzip%'
$search_condition['ken'] = ken like '%$qken%'
というようにそれぞれの列に対する検索条件を作り、連想配列に格納しておきます。
ANDをつけて整形し、以下のようなSQLにすることで複数列の条件を指定した検索が出来ます。
SELECT * FROM table WHERE zip like '%$qzip%' AND ken like '%$qken%';
ただし、これだけでは1つ列に対して複数のキーワードが指定されることが考慮できておりません。
複数のキーワードが指定されることを考慮する場合、半角スペースごとに分割し、配列に格納します。
このやり方はすでにご存知ですよね。
$qzipの内容を分割し、@qzipに格納したとします。
$i=1; $search_condition['zip'] = ""; while(@qzip[$i]){ $q = @qzip[$i]: $search_conditon['zip'] .= OR qzip LIKE '%$q%' } $search_condition['zip'] =~ s/OR//;
上記のようにして$search_condition['zip']の内容を qzip LIKE '%zip1%' OR qzip LIKE '%zip2%' OR ... といった感じにしておきます。
後は、連想配列%search_conditionの内容を列名ごとにチェックし、空でない場合、ANDをつけて整形すれば良いはずです。
細かい部分でのやり方は色々あると思いますが、複数列の条件を指定して検索する、ということと、1つの列を複数のキーワードで検索することを混同しなければ正しいSQLを生成できると思います。
動作確認などは行っていないので、シングルクォートの部分をダブルクォートにする、など細かい点で修正が必要かもしれません。
ご注意ください。
参考になれば幸いです。
URLはダミーです。
一つのテキストボックスにスペース区切りで検索項目を入力していって、複数のDBカラムに対してOR条件で検索をかけて行くみたいなイメージでしょうか?
たとえば、テキストボックスに[foo bar]のように入力があった場合、
select * from hoge where a = 'foo' or b = 'foo' or c = 'foo' or a = 'bar' or b = 'bar' or c = 'bar'
のようなSQL文が発行できればよいと思われます。
簡単なプログラムの流れを示すと以下の通り。
1,テキストボックスからの入力値をスペース区切りから配列などに一つずつの文字列として切り出す。
2,1で作った配列の数だけ3を繰り返す。ここで、繰り替え指数のカウンタをiとする。
3,"a = '配列[i]' or b = '配列[i]' or c = '配列[i]'"という文字列を組み立てていく。
4,最後に、"select * from hoge where "のあと3で作った文字列を"or"で連結していく。
こんな流れでいいのではないでしょうか?perlは余りよく知らないのでプログラムに落とす流れとしては適切かどうかは知りませんが、作りたいSQLさえ意識できれば自ずと適切な処理方法は見えてくるかと思います。Javaだと配列の代わりにArrayListを使うのが楽です。
$i =1
while(@word[$i]){
$q = @word[$i];
$IN .= qq{title LIKE "%q%" ,name LIKE "%q%",message LIKE "%q%" OR};
i++;
}
最後ORが残らないように整形
$q = $word[0];
$IN .= qq{(name LIKE "%$q%" OR title LIKE "%$q%" OR message LIKE "%$q%") };
#@wordは検索文字を格納
#INはWHEREに入れる文字です。
『SELECT * FROM table WHERE $IN』っていうふうに使います。キーワードだけで、and,orの分岐だけなら簡単ですが、上記の例では3つしかないですが、もっとデータが大きくなって項目を設定したときの検索するのはどうなるか難しいです。
http://sql.main.jp/cont/norm/2to3.html
第三正規形、という概念を分かってたずねているのでしょうか。
条件分岐も何も、検索には「流れ」は一つしかありませんけど。
その「流れ」とやらはデータベースが(というかデータベース設計時に)決めます。
もしかしてselect cola from aしてから、またselect ..ってやる
時に手順を知りたいとかいう話なのでしょうか?
えっとデータベースの中身が分からないので、何も言いようは
ないと思いますが、、
「項目を指定しての検索」例、も見てきましたが、別に「項目」という霞のようなデータを指定しているわけではなく、データベースのカラムを指定しているだけだと思います。これだと別に「流れ」
などはありません。検索しているだけですから。
いまいち、何が分からないかよく分かりません。
前回の質問キャンセルしないで残しておいたほうがよかったですね。ひとつ上のコメントで$INの整形しようと思ってるんですが、キーワードだけ、項目だけ、キーワードも項目も両方の場合に、こんな書き方をするという感じでお願いしたいです。仮にテーブル(no,title,name,zip,ken,message)で、zipとkenが選択して指定できると仮定してお返事いただけると助かります。質問のリンク先のように最終的にはもっと項目を多くしたいと思います。1個ぐらいならできるのですが、場合分けが必要になると書く経験が浅いため脳がついていけません。
第3正規形はわかりません。リンク先拝見させていただきたいと思います。よろしくお願いします。
[追記]同じ方の回答も歓迎です。
質問文中にある例を見ましたが、それぞれの列ごとに別の文字列で検索する仕組みになっています。
1つ目の回答へのコメントにあるような
$IN .= qq{(name LIKE "%$q%" OR title LIKE "%$q%" OR message LIKE "%$q%") };
という検索条件では各列に対して同じ文字列で検索を行ってしまうため、予期しない検索結果になってしまうと思うのですが、いかがでしょうか。
まず、考え方を整理する必要があると思います。
ポイントは以下の2点です。
Perlは得意ではないので考え方だけ説明します。
まず、複数列の条件指定の方法です。
複数のテキストフィールドの値を連想配列に格納します。
cgi-lib.plを使うと良いでしょう。
続いて、連想配列の値を変数に格納します。
以下のようなイメージです。
$qno = $input{'no'}; $qtitle = $input{'title'}; $qname = $input{'name'}; $qzip = $input{'zip'}; $qken = $input{'ken'}; $qmessage = $input{'message'};
その後、その変数が空かどうかをチェックします。
空でないものに関してのみ、WHERE句で使うための検索条件を生成します。
例えば、2つ目の回答へのコメントにあるようにzipとkenにのみ検索条件が指定されていた場合、
$search_condition['zip'] = zip like '%$qzip%'
$search_condition['ken'] = ken like '%$qken%'
というようにそれぞれの列に対する検索条件を作り、連想配列に格納しておきます。
ANDをつけて整形し、以下のようなSQLにすることで複数列の条件を指定した検索が出来ます。
SELECT * FROM table WHERE zip like '%$qzip%' AND ken like '%$qken%';
ただし、これだけでは1つ列に対して複数のキーワードが指定されることが考慮できておりません。
複数のキーワードが指定されることを考慮する場合、半角スペースごとに分割し、配列に格納します。
このやり方はすでにご存知ですよね。
$qzipの内容を分割し、@qzipに格納したとします。
$i=1; $search_condition['zip'] = ""; while(@qzip[$i]){ $q = @qzip[$i]: $search_conditon['zip'] .= OR qzip LIKE '%$q%' } $search_condition['zip'] =~ s/OR//;
上記のようにして$search_condition['zip']の内容を qzip LIKE '%zip1%' OR qzip LIKE '%zip2%' OR ... といった感じにしておきます。
後は、連想配列%search_conditionの内容を列名ごとにチェックし、空でない場合、ANDをつけて整形すれば良いはずです。
細かい部分でのやり方は色々あると思いますが、複数列の条件を指定して検索する、ということと、1つの列を複数のキーワードで検索することを混同しなければ正しいSQLを生成できると思います。
動作確認などは行っていないので、シングルクォートの部分をダブルクォートにする、など細かい点で修正が必要かもしれません。
ご注意ください。
参考になれば幸いです。
わかりやすくありがとうございます。
テキストフィールドではなく、ぴったり一致の選択式の場合は、$qzip LIKE '%$zip[1]%' から$qzip = '$zip[1]'にすれば大丈夫ですね。
ご回答いただいた列ごとの検索とフリーワードの全体の検索を組み合わせると
SELECT * FROM table WHERE '$IN' AND(zip like '%$qzip%' AND ken like '%$qken%');
#$INはご指摘いただいたとおりおかしかったです。
というふうにすれば、条件を指定したフリーワードの検索ができますでしょうか。
連想配列も勉強になりました。こういうときに使うんですね。手がかりがつかめたのでとりあえずいろいろ書いて動かしてみたいと思います。
書くのは慣れていないのでまた質問させていただくところもあるかもしれませんが、そのときはよろしくお願いします。
わかりやすくありがとうございます。
テキストフィールドではなく、ぴったり一致の選択式の場合は、$qzip LIKE '%$zip[1]%' から$qzip = '$zip[1]'にすれば大丈夫ですね。
ご回答いただいた列ごとの検索とフリーワードの全体の検索を組み合わせると
SELECT * FROM table WHERE '$IN' AND(zip like '%$qzip%' AND ken like '%$qken%');
#$INはご指摘いただいたとおりおかしかったです。
というふうにすれば、条件を指定したフリーワードの検索ができますでしょうか。
連想配列も勉強になりました。こういうときに使うんですね。手がかりがつかめたのでとりあえずいろいろ書いて動かしてみたいと思います。
書くのは慣れていないのでまた質問させていただくところもあるかもしれませんが、そのときはよろしくお願いします。