PHP と SQL (MySQL) の連携　その 3

平成２３年度前期情報科学III （理系コア科目） PHP と SQL (MySQL) の連携　その3 担当松永　裕介月曜日２限 http://www.c.csce.kyushu-u.ac.jp/~matsunaga/lecture/php/ 本資料の一部は、堀良彰准教授、天野浩文准教授等による以前の講義資料をもとにしています。

演習問題 • テーブルのカラムとして • 商品名 • 価格 • 色 • 重さ　　を、用意し、「商品名」で検索し、「色」「重さ」はオプションで表示できるようなプログラムを作成せよ。 • テーブルのカラムとして • テーブル１ • 商品名 • 価格 • テーブル２ • 商品名 • 色 • テーブル３ • 商品名 • 重さを用意して、「商品名」で検索して、「価格」「色」「重さ」が表示されるプログラムを作成せよ。

テーブルの作成 mysql> create table table090706p (name char(32), price char(32)); mysql> create table table090706c (name char(32), color char(32)); mysql> create table table090706w (name char(32), weight char(32));

データ投入 mysql> insert into table090706p set name="book", price="1000"; mysql> insert into table090706p set name="pen", price="100"; mysql> insert into table090706p set name="cd", price="3000"; mysql> insert into table090706p set name="dvd", price="4000"; mysql> insert into table090706c set name="book", color="black"; mysql> insert into table090706c set name="pen", color="red"; mysql> insert into table090706c set name="pen", color="green"; mysql> insert into table090706c set name="pen", color=“blue"; mysql> insert into table090706c set name="cd", color="silver"; mysql> insert into table090706c set name="dvd", color="silver"; mysql> insert into table090706w set name="book", weight="100"; mysql> insert into table090706w set name="pen", weight="10"; mysql> insert into table090706w set name="cd", weight="5"; mysql> insert into table090706w set name="dvd", weight="5";

検索例 mysql> select * from table090706p join table090706c, table090706w; ………. | dvd | 4000 | cd | silver | pen | 10 | | book | 1000 | dvd | silver | pen | 10 | | pen | 100 | dvd | silver | pen | 10 | | cd | 3000 | dvd | silver | pen | 10 | | dvd | 4000 | dvd | silver | pen | 10 | | book | 1000 | book | black | cd | 5 | | pen | 100 | book | black | cd | 5 | | cd | 3000 | book | black | cd | 5 | | dvd | 4000 | book | black | cd | 5 | | book | 1000 | pen | red | cd | 5 | | pen | 100 | pen | red | cd | 5 | | cd | 3000 | pen | red | cd | 5 | | dvd | 4000 | pen | red | cd | 5 | | book | 1000 | pen | green | cd | 5 | | pen | 100 | pen | green | cd | 5 | | cd | 3000 | pen | green | cd | 5 | | dvd | 4000 | pen | green | cd | 5 | | book | 1000 | pen | blue | cd | 5 | | pen | 100 | pen | blue | cd | 5 | | cd | 3000 | pen | blue | cd | 5 | | dvd | 4000 | pen | blue | cd | 5 | | book | 1000 | cd | silver | cd | 5 | | pen | 100 | cd | silver | cd | 5 | | cd | 3000 | cd | silver | cd | 5 | | dvd | 4000 | cd | silver | cd | 5 | | book | 1000 | dvd | silver | cd | 5 | | pen | 100 | dvd | silver | cd | 5 | | cd | 3000 | dvd | silver | cd | 5 | | dvd | 4000 | dvd | silver | cd | 5 | +------+-------+------+--------+------+--------+ 96 rows in set (0.00 sec)

検索例 mysql> select * from table090706p join table090706c, table090706w on table090706p.name=table090706c.name and table090706p.name=table090706w.name; +------+-------+------+--------+------+--------+ | name | price | name | color | name | weight | +------+-------+------+--------+------+--------+ | dvd | 4000 | dvd | silver | dvd | 5 | | book | 1000 | book | black | book | 100 | | pen | 100 | pen | red | pen | 10 | | pen | 100 | pen | green | pen | 10 | | pen | 100 | pen | blue | pen | 10 | | cd | 3000 | cd | silver | cd | 5 | +------+-------+------+--------+------+--------+

検索例 mysql> select table090706p.name,table090706p.price,table090706c.color,table090706w.weight from table090706p join table090706c, table090706w on table090706p.name=table090706c.name and table090706p.name=table090706w.name; +------+-------+--------+--------+ | name | price | color | weight | +------+-------+--------+--------+ | dvd | 4000 | silver | 5 | | book | 1000 | black | 100 | | pen | 100 | red | 10 | | pen | 100 | green | 10 | | pen | 100 | blue | 10 | | cd | 3000 | silver | 5 | +------+-------+--------+--------+

検索例 mysql> select table090706p.name, table090706p.price,table090706c.color, table090706w.weight from table090706p join table090706c, table090706w on table090706p.name=table090706c.name and table090706p.name=table090706w.name where table090706p.name = "pen"; +------+-------+-------+--------+ | name | price | color | weight | +------+-------+-------+--------+ | pen | 100 | red | 10 | | pen | 100 | green | 10 | | pen | 100 | blue | 10 | +------+-------+-------+--------+ 3 rows in set (0.00 sec)

データベースで数字を扱って見る。 mysql> create table table090706pp (name char(32), price int); mysql> create table table090706ww (name char(32), weight int); mysql> insert into table090706pp set name="book", price=1000; mysql> insert into table090706pp set name="pen", price=100; mysql> insert into table090706pp set name="cd", price=3000; mysql> insert into table090706pp set name="dvd", price=4000; mysql> insert into table090706ww set name="book", weight=100; mysql> insert into table090706ww set name="pen", weight=10; mysql> insert into table090706ww set name="cd", weight=5; mysql> insert into table090706ww set name="dvd", weight=5;

検索例 mysql>select table090706pp.name, table090706pp.price, table090706c.color, table090706ww.weight from table090706pp join table090706c, table090706ww on table090706pp.name=table090706c.name and table090706pp.name=table090706ww.name; +------+-------+--------+--------+ | name | price | color | weight | +------+-------+--------+--------+ | book | 1000 | black | 100 | | pen | 100 | red | 10 | | pen | 100 | green | 10 | | pen | 100 | blue | 10 | | cd | 3000 | silver | 5 | | dvd | 4000 | silver | 5 | +------+-------+--------+--------+ 6 rows in set (0.00 sec)

検索例: 価格が2000以下の商品を検索 mysql> select table090706pp.name, table090706pp.price, table090706c.color, table090706ww.weight from table090706pp join table090706c, table090706ww on table090706pp.name=table090706c.name and table090706pp.name=table090706ww.name where table090706pp.price <= 2000; +------+-------+-------+--------+ | name | price | color | weight | +------+-------+-------+--------+ | book | 1000 | black | 100 | | pen | 100 | red | 10 | | pen | 100 | green | 10 | | pen | 100 | blue | 10 | +------+-------+-------+--------+ 4 rows in set (0.00 sec)

検索例： 価格が2000以下で、重さが50以上のものを検索 mysql> select table090706pp.name, table090706pp.price, table090706c.color, table090706ww.weight from table090706pp join table090706c, table090706ww on table090706pp.name=table090706c.name and table090706pp.name=table090706ww.name where table090706pp.price <= 2000 and table090706ww.weight >= 50; +------+-------+-------+--------+ | name | price | color | weight | +------+-------+-------+--------+ | book | 1000 | black | 100 | +------+-------+-------+--------+ 1 row in set (0.00 sec)

練習問題 • 商品、価格、販売店、販売店距離のデータがあるとき • 価格で商品を選択できるプログラムを作成せよ • 例：100円以下とか • 販売店の距離で選択できるプログラムを作成せよ • 例：500m以内とか • 価格と販売店の距離両方の条件を選択できるプログラムを作成せよ。 • 例：200円以下で距離が1000m以内 • 注意 • 普通、全部の販売店で同じ商品があるわけではないと思いますので、そのあたり注意して設計してください。 • まず、SQL で動作を確認すること

PHP と SQL (MySQL) の連携　その 3