PHP実習：MySQL環境作成②、PHPからクエリ実行

今夜もMySQLについての勉強。

まずは前回までの復習でコマンドまとめ。

Vagrant Upしたあと、PuTTYで仮想環境にログインし、MySQLを使うディレクトリまで移動する。

1.MySQLへのアクセス

　mysql -u root

2.データベース作成

　create database (テーブル名) ;

3.データベースにアクセスできるユーザーの設定

　grant all on (DB名).* to (ユーザー)@localhost identified by '(パスワード)';

4.データベースへ入る

　use (DB名);

5.クエリでテーブル作成

　create table (テーブル名) (row int,name varchar(255),...);

6.MySQL終了

　\q

7.3で作成したユーザーとしてログイン

　mysql -u (ユーザー) -p (DB名)

　※パスワードを聞かれる

8.テーブル一覧確認

　show tables;

9.テーブルデザイン確認

　desc (テーブル名);

昨日はこれらのコマンドで、データベース作成、ユーザー設定、テーブル作成を行いました。

今日はドットインストールの続きから。

MySQLを立ち上げたディレクトリまで移動し、index.phpを作成。

データベース関連のいろいろな情報を定数にしておくのがいいとのことなので、以下のコードをindex.phpに書いていく。

<?php
　define('DB_DATABASE','example');
　define('DB_USERNAME','dbuser');
　define('DB_PASSWORD','abc123');
　define('PDO_DSN','mysql:dbhost=localhost;dbname='.DB_DATABASE);

上から順にデータベース名、ユーザー名、パスワードを設定している。

最後でPDO_DSNの設定を行う。DSNはデータソースネームの略で、データベースに接続するための文字列。システムによって違うが、MySQLの場合は上のように書けばよいとのこと。最後はドットの後に先ほど設定したデータベース名を渡している。

基本的にはtryとcatchを使って操作を行う。以下のように付け足し。

try{

　//connect

　$db=new PDO(PDO_DSN,DB_USERNAME,DB_PASSWORD);

　$db->setAttribute(PDO::ATTR_ERRMODE),PDO::ERRMODE_EXCEPTION);

　//処理

} catch (PDOException $e){

　echo $e->getMessage();

　exit;

}

PDOの新しいインスタンスを作成し、setAttributeでエラーの際の処理をどう行うか設定する。ここらへんは決まり文句っぽい。エラーがおきたらcatchで対応。

//処理と書いたところで処理を行うのだが続きは次回…つまり有料区間。無料版のドットインストールではここまでが限界…。

とりあえず詰まるところまで本に戻って進む。

第7章-01

phpMyAdminを使ったデータベースの作成・設定方法についての章。

教材に合わせ、PuTTYでも同じデータベースを作っていく。

MySQL起動

mysql -u root

データベース作成

create database user;

ユーザー設定

grant all on user.* to vagrant@localhost identified by 'vagrant';

データベースへ入る

use user;

テーブル作成

create table user(

id int not null auto_increment primary key,

name varchar(50),

age int,

email varchar(100)

);

f:id:R_de_aru:20180625203123p:plain

f:id:R_de_aru:20180625203138p:plain

テーブルを作成する際、「id int not null～」と書くべきところを「id not null～」としてしまっていた。注意。

教材側で気になったのが照合順序について。これは文字列の検索に関する設定で、一般的には「utf8_general_ci」を使用するとのこと。よく使われる照合順序は以下の通り。

utf8_general_ci

→アルファベットの大文字/小文字は区別しない

utf8_unicode_ci

→大文字小文字/全角半角を区別しない

utf8_bin

→完全一致

第7章-02

これから作成したテーブルにデータを入れていく。以下のInsert文を作成。

INSERT INTO `user`.`user` (`id`,`name`,`age`,`email`)

VALUES(NULL,'鈴木太郎','26','sample@sample.com');

見慣れたSQL文だが、`(バッククォート)がついている。実際にこれがなくてもSQL文は問題ないので、さらに書き慣れた以下の形にして実行。

INSERT INTO user.user (id,name,age,email)

VALUES(NULL,'鈴木太郎','26','sample@sample.com');

さらにもう一行追加。

INSERT INTO user.user (id,name,age,email)

VALUES(NULL,'中村花子','45','sample2@sample.com');

ちょっと確認。

select * from user.user;

f:id:R_de_aru:20180625204143p:plain

問題なくデータが登録されている。SQL文は仕事でさんざんやっているので復習はしないが、Webサービスを作るためのCRUD機能とSQLの関係だけまとめておく。

Create(作る)　Insert

Read(読む)　Select

Update(更新する)　Update

Delete(削除する)　Delete

これだけで多くのWebサービスが実現可能。

第7章-02　練習問題

作成したuserデータベースのuserテーブルについて、

idが2以上の行で、id,email,ageのカラム情報を引き出す
idが2の行のageカラムのデータを「46」に更新

1_解答

select id,email,age from user.user where id>=2;

2_解答

update user.user set age=46 where id=2;

f:id:R_de_aru:20180625204847p:plain

第7章-04

いよいよPHPを絡めてMySQLを使っていく。なんとPDOを使っているので、ここからは教材とそこまでズレはなさそう!!

教材でもPDO(PHP Data Objects)というPHPがデフォルトで持っているデータベース操作の仕組みを使っていく。これは最近の接続方法の主流で、接続方法はほぼ書き方が定型文化している。先ほどMySQLを立ち上げたディレクトリにindex.phpを作成し、以下のコードを書いてMySQLに接続していく。

<?php
　$dsn='mysql:dbname=user;host=localhost;charset=utf8';
　$user='root';
　$password='';

　try {
　　$dbh=new PDO($dsn,$user,$password);
　　$dbh -> setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
　　$sql="";
　　$stmt=$dsn->prepare($sql);
　　$stmt->execute();
　　echo "接続に成功しました。";
　} catch (PDOException $e) {
　　print($e->getMessage());
　　die();
　}

ドットインストールでやった接続文とほぼ同じ内容である。今回はユーザー名をrootにしたが、これはデータベースの総合的な管理者を表している。権限が強すぎるので本番環境ではまず使用しない。また、rootにパスワードは指定しないので空の文字列を設定している。本番環境ではユーザー名とパスワードを設定するので、それを記入すること(今回ならユーザー名とパスワードはvagrantになる)。

このコードを使い、MySQL側にデータを追加する。以下のように書き換え。

<?php
　$dsn='mysql:dbname=user;host=localhost;charset=utf8';
　$user='root';
　$password='';

　try {
　　$dbh=new PDO($dsn,$user,$password);
　　$dbh -> setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
　　$sql="INSERT INTO user (id,name,age,email) VALUES(null,'田中三郎','28','sample5@sample.com')";
　　$stmt=$dbh->prepare($sql);
　　$stmt->execute();
　　echo "接続に成功しました。";
　} catch (PDOException $e) {
　　print($e->getMessage());
　　die();
　}

$sqlについてはダブルクォーテーションでくくるとSQL文のシングルクォーテーションと衝突しないで済むので、SQL文を書く際のくくりはダブルクォーテーションを推奨。

これでブラウザからアクセスすると、画面には「接続に成功しました」という文字が出る。この状態でMySQLを確認すると…

f:id:R_de_aru:20180625215924p:plain

これでデータが追加されたことが分かる。$dbhと書くべきところを$dsnと書いてしまっていたところがいくつもあったので注意。

実際のWebサービスではSQL文にクライアントから送信されたデータを当てはまることが多くなる。先ほどのコードを以下のように書き換え。

<?php
　$dsn='mysql:dbname=user;host=localhost;charset=utf8';
　$user='root';
　$password='';

　// 1.挿入したいデータを格納
　$name='長澤まさみ';
　$age=32;

　try {
　　$dbh=new PDO($dsn,$user,$password);
　　// 2.エラーをどこまで報告するか指定
　　$dbh -> setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
　　// 3.置き換える部分を「コロン+名前」で指定
　　$sql="INSERT INTO user (name,age) VALUES(:name,:age)";
　　// 4.プリペアドステートメント
　　$stmt=$dbh->prepare($sql);
　　// 5.SQLに変数の値を当てはめる
　　$stmt->bindValue(':name',$name,PDO::PARAM_STR);
　　$stmt->bindValue(':age',$age,PDO::PARAM_STR);

　　// 実行
　　$stmt->execute();
　　echo "処理が終了しました。";
　} catch (PDOException $e) {
　　print($e->getMessage());
　　die();
　}

ブラウザの画面には「処理が終了しました。」と表示され、userテーブルにはレコードが追加された。

f:id:R_de_aru:20180625221130p:plain

上のコードを読み解いていこう。

1.挿入したいデータを格納

これは実際にPOSTで値を受け取ることを想定している。$dbhにはデータベースに接続するためのオブジェクトが入っている(切符のようなイメージ)。

2.エラーをどこまで報告するか指定

$dbh->setAttributeという書き方でエラーレポートの仕方を指定することができる。PDO::ERRMODE_EXCEPTIONと指定することで、データベース操作中に問題が発生したときにその内容を受け取れるようになる。PDO::ERRMODE_SILENTというオプションもあるが、これは本番サイトで指定する。エラーの報告はしない設定。

3.置き換える部分を「コロン+名前」で指定

VALUE(:name, :age)というように、「コロン＋名前」とすることによって後で変数の値を置き換えることができるようにしている。コロンの後はどんな名前でもよい(変数名と揃えておく方が分かりやすい)。

4.プリペアドステートメント

SQLはすぐに実行せず、$dbh->prepare($sql)によって変数の当てはめを待機する状態にしている。この記述をプリペアドステートメントという。これによって受け取った悪意のある入力値を無害なものに変換するという処理が行われるので、必ず実行すること。

5.SQLに変数の値を当てはめる

$stmt->bindValueによってはじめて変数の値がSQLに当てはめられる。「:name」と書かれた箇所を「$name」で置き換え。値が文字列の場合はPDO::PARAM_STR、数字の場合はPDO::PARAM_INTを指定すること。なお、emailのカラムはNull(なくてもよい)の設定がしてあるので今回はValueになくてもよい。

最後に、エラーの対処法についてまとめる。

<?php
　$dsn='mysql:dbname=user;host=localhost;charset=utf8';
　$user='root';
　$password='';
　$name='長澤まさみ';
　$age=32;

　// 1.try以降でエラーがあった場合、例外処理をする

　try {
　　$dbh=new PDO($dsn,$user,$password);
　　// 省略

　} catch (PDOException $e) { // 2.例外を検知する

　　// 3.例外を表示する
　　print($e->getMessage());

　　// 4.処理を停止する
　　die();
　}

通常データベースへの接続や処理にエラーが発生した場合は、そこで処理が終了してしまう。発生する可能性があるエラーのことを例外という。

1.try以降でエラーがあった場合、例外処理をする

tryを使用することでエラー発生時にプログラム全体が停止しないようにすることができる。

2.例外を検知する

tryにはcatchブロックを用意し、その後の処理を指定する。

3.例外を表示する

PDOException $eと書くことで4eにエラー内容を格納する。これを文字列にするには$e->getMessage()と書く。$eはオブジェクトであり、「->(アロー演算子)」でその中のメソッドを指定していく。

4.処理を停止する

最後にdie()で処理を停止する。die()の代わりにexit()も使える。

第7章-04　練習問題

先ほど作成したindex.phpを参考に、プリペアドステートメントを使用してデータの更新を行う。

idが3の人のemailを「prepare@statement.com」に変更する。ただし、メールアドレスの値は一度$emailに格納してbindValue()を使って後からSQLに当てはめること。

<?php
　$dsn='mysql:dbname=user;host=localhost;charset=utf8';
　$user='root';
　$password='';
　$email='prepare@statement.com';

　try {
　　$dbh=new PDO($dsn,$user,$password);
　　$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
　　$sql="UPDATE user SET email=:email WHERE id='3'";
　　$stmt=$dbh->prepare($sql);
　　$stmt->bindValue(':email',$email,PDO::PARAM_STR);
　　$stmt->execute();
　　echo "データを更新しました。";
　} catch (PDOException $e) {
　　print($e->getMessage());
　　die();
　}