09 06/23 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>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
insert
into
into
into
into
into
into
into
into
into
into
into
into
into
into
table090706p
table090706p
table090706p
table090706p
table090706c
table090706c
table090706c
table090706c
table090706c
table090706c
table090706w
table090706w
table090706w
table090706w
set
set
set
set
set
set
set
set
set
set
set
set
set
set
name="book", price="1000";
name="pen", price="100";
name="cd", price="3000";
name="dvd", price="4000";
name="book", color="black";
name="pen", color="red";
name="pen", color="green";
name="pen", color=“blue";
name="cd", color="silver";
name="dvd", color="silver";
name="book", weight="100";
name="pen", weight="10";
name="cd", weight="5";
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.weigh
t 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>
mysql>
mysql>
mysql>
insert
insert
insert
insert
into
into
into
into
table090706pp
table090706pp
table090706pp
table090706pp
set
set
set
set
name="book", price=1000;
name="pen", price=100;
name="cd", price=3000;
name="dvd", price=4000;
mysql>
mysql>
mysql>
mysql>
insert
insert
insert
insert
into
into
into
into
table090706ww
table090706ww
table090706ww
table090706ww
set
set
set
set
name="book", weight=100;
name="pen", weight=10;
name="cd", weight=5;
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 で動作を確認すること

Download Report