第9回 PHP環境上SQLによるDBの操作でデータ表を作ろう
SQLの基本操作
データ表の作成
データ表の見本
データ表を視覚的に理解しやすいように、googleスプレッドシートを作成しました。
内容は、ある学年のテスト結果です。
データ表見本
データ表作成SQL
続いて、見本データ表と同じデータ構成のDBを作成しましょう。
以下のSQLを利用して、DB「db_test_002」を作成してください。
CREATE DATABASE IF NOT EXISTS `db_test_002` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE `db_test_002`; CREATE TABLE `user` ( `user_id` int(11) NOT NULL, `user_name` varchar(255) NOT NULL, `class` varchar(255) NOT NULL, `score_1` int(11) DEFAULT '0', `score_2` int(11) DEFAULT '0', `score_3` int(11) DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `user` (`user_id`, `user_name`, `class`, `score_1`, `score_2`, `score_3`) VALUES (1, '鈴木', 'A', 80, 95, 77), (2, '佐藤', 'A', 53, 45, 70), (3, '足立', 'B', 100, 99, 89), (4, '三島', 'B', 44, 35, 30), (5, '荒木', 'B', 79, 78, 75), (6, '川田', 'C', 74, 72, 70); ALTER TABLE `user` ADD PRIMARY KEY (`user_id`); ALTER TABLE `user` MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
SELECT文によるデータの取得とMySQL関数
そして、その操作にとって肝となるのがMySQL関数です。
MySQLでは便利な関数がいくつか用意されており、SELECT文の中で使用することで取得したデータの数をカウントしたり平均を計算したりといったことが可能です。
この段での操作はphpMyAdminを利用して行っていきます。
結果を表示したい場合は、SQL入力ページを利用しましょう。
生徒のテスト結果を一覧で取得する
では、生徒の名前である「user_name」と「score_1」の一覧を表示してみましょう。
SQLは以下のようになります。
SELECT：レコードの取得
SELECT `user_name`,`score_1` FROM `user`;
表示したい項目が複数あるため、カンマで区切って指定しています。
次に、A「class」の「user_name」と「score_2」と「score_3」の一覧を表示してみましょう。
WHERE：条件による絞り込み
SELECT `user_name`,`score_2`,`score_3` FROM `user` WHERE `class` = 'A';
※数字以外の文字列はシングルコーテーションで囲みましょう
WHEREの利用はかなり重要で比較演算子や論理演算子と呼ばれるものを多用します。
どのような使われ方をするか、いくつか例を挙げます。
/* score_1が80点以上の生徒 */ SELECT * FROM `user` WHERE `score_1` >= 80; /* score_2が50点未満のクラスBの生徒 */ SELECT * FROM `user` WHERE `score_2` < 50 AND `class` = 'B'; /* score_1かscore_3が70点以上の生徒 */ SELECT * FROM `user` WHERE `score_1` >= 70 OR `score_3` >= 70;
以上がよく使われる演算子のSQL例ですが、細かい条件を指定するには、さらに便利なものがあります。
以下の記事がとても参考になります。
テスト結果で並び替える
「score_1」の得点が高い順に表示してみましょう。
ORDER BY：レコードの並び替え
SELECT * FROM `user` ORDER BY `score_1` DESC;
並び順の指定はASC(昇順)、DESC(降順)で行います。
よって、得点が低い順を表示したい場合は
SELECT * FROM `user` ORDER BY `score_1` ASC;
となります。
合計点、平均点、件数の取得
「score_1」の全体の合計点を表示してみましょう。
SUM：指定カラムのレコード合計
AS：カラム名の指定
SELECT SUM(`score_1`) AS `score_1_sum` FROM `user`;
丸括弧で合計を出したいカラムを指定します。
※合計点のカラムを「score_1_sum」と指定します
次に、各「class」の「score_2」の平均点を表示してみましょう。
AVG：指定カラムのレコード平均
GROUP BY：データをグループ化
SELECT `class`,AVG(`score_1`) AS `score_2_avg` FROM `user` GROUP BY `class`;
SUMと同じく、丸括弧で平均を出したいカラムを指定します。
グループ化は、GROUP BYの後にカラムを指定。
となります。
最後に、各「class」の「score_3」の得点が75点以上の人数（件数）を表示してみましょう。
COUNT：指定カラムのレコード件数
SELECT `class`,COUNT(*) AS `score_3_count` FROM `user` WHERE `score_3` >= 75 GROUP BY `class`;
今までの覚えた知識を詰め込むんだので混乱しますが、各命令や関数ごとに分けて考えるとわかりやすいでしょう。
PHPによるDB接続・操作
DBの接続用ファイル
C:\xampp\htdocs内にdb_connect.phpファイルを作成します。
ファイルを作成したらテキストエディタを開き、以下の内容をコピペします。
<?php
define('DB_USERNAME', 'USER_NAME');//DB作成時に設定したユーザー名
define('DB_PASSWORD', 'PASSWORD');//ユーザーに設定したパスワード
function db_connect($db_name){
$pdo = new PDO(
"mysql:dbname={$db_name};host=localhost;charset=utf8mb4",
DB_USERNAME,
DB_PASSWORD
);
return $pdo;
}
?>
設問ファイル
続いて、C:\xampp\htdocs内にdb_test_002.phpファイルを作成し、以下をコピペします。
<?php
require_once('db_connect.php');
try {
// データベースに接続
$db_name = 'db_test_002';
$pdo = db_connect($db_name);
//例外処理を投げるようにする（throw）
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->query('SELECT * FROM user');
$list = $stmt->fetchAll();
//データベース接続切断
$pdo = null;
} catch (PDOException $e) {
// エラーが発生した場合は「500 Internal Server Error」でテキストとして表示して終了する
header('Content-Type: text/plain; charset=UTF-8', true, 500);
exit($e->getMessage());
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>db_test_002</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/css/bootstrap.min.css" integrity="sha384-GJzZqFGwb1QTTN6wy59ffF1BuGJpLSa9DkKMp0DgiMDm4iYMj70gZWKYbI706tWS" crossorigin="anonymous">
</head>
<body>
<div class="container">
<p class="lead my-4">SQLを駆使して以下の空欄を埋めなさい</p>
<table class="table table-bordered">
<thead class="thead-light">
<tr>
<th>user_id</th>
<th>user_name</th>
<th>class</th>
<th>score_1</th>
<th>score_2</th>
<th>score_3</th>
<th>all_score</th>
</tr>
</thead>
<tbody>
<?php foreach($list as $key => $val):?>
<tr>
<td><?php echo $val['user_id']; ?></td>
<td><?php echo $val['user_name']; ?></td>
<td><?php echo $val['class']; ?></td>
<td><?php echo $val['score_1']; ?></td>
<td><?php echo $val['score_2']; ?></td>
<td><?php echo $val['score_3']; ?></td>
<td></td>
</tr>
<?php endforeach; ?>
<tr>
<th>合計</th>
<td class="text-center">------</td>
<td class="text-center">------</td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</tbody>
</table>
</div>
</body>
</html>
上記内容の表示に成功すると、以下のリンク先のようになります。
設問内容
『SQLを駆使して以下の空欄を埋めなさい』という設問にしました。
まずは、正しくファイルの設置とコードの貼り付けを終え、表示されるようにしましょう。
その後、講義内で説明をしながら一緒に問題を解いていきます。
解答、解説は次回講義内で行います。
まとめ
- DBからの情報の呼び出しはSELECT文を使う
- MySQLでは便利なMySQL関数がいくつか用意されている
- MySQL関数には、数値を扱って合計や平均を取得するものがある
- WHEREで条件による絞り込みを行う際には、比較演算子や論理演算子が必要である
今回の講義では、DBデータの主だった取得方法を紹介しました。
これをさらに深く学習していくと、食べログのような評価ランキングサイトや価格ドットコムのような無数のカテゴリからなる価格比較サイトが作れるようになります。
逆を言えば、SQLの知識がないと、そういった情報を並べることも、絞り込んで表示することもできませんので、まともなWEBサイトが作れないという事になります。
DB管理を専門とするプログラマがいるように、とても奥の深い世界です。
頭に馴染ませられるよう、次回の講義ではDBとの連携したプログラミングを行っていきます。
次回講義までの自主学習
学習サイトをピックアップしたので、以下のリンクから自分に合ったものを使って自主学習しましょう。
NEW!!
SQL | プログラミングの入門なら基礎から学べるProgate[プロゲート]
MySQL入門 (全36回) – プログラミングならドットインストール
基礎知識
HTML & CSS | プログラミングの入門なら基礎から学べるProgate[プロゲート]
HTML/CSS入門編のレッスン一覧 | プログラミング学習サービス【paizaラーニング】
HTML入門 (全15回) – プログラミングならドットインストール
CSS入門 (全17回) – プログラミングならドットインストール
実践！ウェブサイトを作ろう (全16回) – プログラミングならドットインストール
STEP UP!!
はじめてのJavaScript (全11回) – プログラミングならドットインストール
JavaScript入門編のレッスン一覧 | プログラミング学習サービス【paizaラーニング】
JavaScript | プログラミングの入門なら基礎から学べるProgate[プロゲート]
Bootstrap 4入門 (全22回) – プログラミングならドットインストール
さらにSTEP UP!!
PHP入門編のレッスン一覧 | プログラミング学習サービス【paizaラーニング】
PHP | プログラミングの入門なら基礎から学べるProgate[プロゲート]
PHP入門 (全30回) – プログラミングならドットインストール
理想はすべてのレッスンをやることです。
今は目で見て手で触れる時間を増やすときなので、時間が許す限りやってみましょう。
