こんにちは。たきもとです。
先日はMySQLコマンド一覧をまとめました。
その時の記事はこちら。
MySQL コマンド一覧 | たきもとけんご.com
今回はPHPでMySQLを操作する方法について紹介します。
動作環境
windows8.1 64bit
vagrant 1.8.1
centOS 6.7
Apache 2.2.15
MySQL 14.14
PHP 5.6.20
PuTTY 0.67
Chrome
テーブル設定
MySQLを立ち上げたら次の設定を行います。
これはお決まりの作業です。
詳しくは前回の記事を参考にして下さい。
前回の記事はこちら。
MySQL コマンド一覧 | たきもとけんご.com
- mkdir :作業ディレクトリの作成
- mysql -u root :MySQLにrootでログイン
- create database ○○ :データベース作成
- grant all on ○○(←DB名).* to □□(←ユーザ名)@localhost identified by ‘◇◇(←パスワード)’ :ユーザ、パスワードの設定
- use ○○(←DB名) : データベース選択
- create table ○○(←テーブル名) (); :テーブル設定
PDO設定
PDOとは
PHP Data Object MySQLをはじめ、様々なデータベースを扱えるようになる仕組みのこと。
準備
index.php
define('DB_DATABASE', 'tst_db'); define('DB_USERNAME', 'tst_user'); define('DB_PASSWORD', 'xxxxxxxx'); define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE); try { // connect $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e ){ $db->rollback(); echo $e->getMessage()."\n"; exit; }
データベース関連情報は定数としてまとめる。
DSN :Data Source Name データベースシステムに接続するための文字列。
今回はMySQLに接続。
データベースの操作は try, chatchで行う。
オプション(setAttribute)は次のページを参照。
ここではデフォルトのエラーハンドルを指定。
Exception の形で例外を投げる。
PDO::setAttribute | PHPマニュアル
テーブルの設定
PDOでSQLを実行するコマンドは3種類ある。
- excec(): 結果を返さない安全なSQL
- query(): 結果を返す安全なSQL. 何度も実行されないSQL.
- prepare(): 結果を返すSQL. 安全対策が必要.複数回実行されるSQL.
その時々のシチュエーションに合わせてどのコマンドを使うかを選択する。
prepare()はSQLをキャッシュしてくれるのでよく使われる(らしい)。
ここではexcecを利用して基本的なデータベースの接続とテーブルへのデータ挿入を実現する。
<?php // DB設定 define('DB_DATABASE', 'DB_1605'); define('DB_USERNAME', 'takimoto'); define('DB_PASSWORD', 'xxxxxxxx'); define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE); try { // connect $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらchatch(例外処理)に移る // Insert $db->exec("insert into tstTable (name, tel) values ('takimoto','09011112222'), ('aaaa','09033334444'), ('bbbb','09055556666') "); // disconnect $db = null; //明示的に接続を切る } catch (PDOException $e ){ echo $e->getMessage()."\n"; // err時はメッセージを表示 exit; } ?>
prepare()
prepare()の基本的な使い方
prepare()を使うと,statementオブジェクトが返ってくる。
この返り値を変数に入れて更にコードを記述してゆくのが一般的。
先程と同様に execute() でMySQLに接続する。
ここではprepareと予約語を用いてデータを挿入する。
予約語は ? を使うが、実際には :変数名 という形を取れば何でも良い。
これについては後述する。
<?php define('DB_DATABASE', 'DB_1605'); define('DB_USERNAME', 'takimoto'); define('DB_PASSWORD', 'xxxxxxxx'); define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE); try { // connect $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る // Insert // $db->exec("insert into tstTable (name) values $stmt = $db->prepare("insert into tstTable (name, tel) values (?, ?)"); $stmt->execute(['taki', '08055554444']); echo 'inserted: '.$db->lastInsertId();// 最終IDが表示される // disconnect $db = null; //明示的に接続を切る } catch (PDOException $e ){ echo $e->getMessage()."\n"; exit; } ?>
prepare()で名前付きパラメータを使う
先程は ? を予約語にしたが、別のモノを試してみる。
先述した通り :フィールド名(カラム名) という形を用いる場合、excecute時には
- キー
- 値
という具合に扱う。
予約語 ? は挿入する値が何でもOKというメリットが有る。
一方で、 :フィールド名 の記述は、フィールド名が多い場合に便利。
<?php define('DB_DATABASE', 'DB_1605'); define('DB_USERNAME', 'takimoto'); define('DB_PASSWORD', 'xxxxxxxx'); define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE); try { // connect $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る // Insert $stmt = $db->prepare("insert into tstTable (name, tel) values (:name, :tel)"); $stmt->execute([':name' => '2ndPerson', ':tel' => '08055554444']); echo 'inserted: '.$db->lastInsertId();// 最終IDが表示される // disconnect $db = null; //明示的に接続を切る } catch (PDOException $e ){ // $db->rollback(); echo $e->getMessage()."\n"; exit; } ?>
bindValue()
テーブルの一部を変更したい、またはフィールド(カラム)毎に値を設定したい時に便利な方法。
bindValueについてはこちらのページに詳細がまとめられています。
PDOStatement::bindValue | php.net
<?php define('DB_DATABASE', 'DB_1605'); define('DB_USERNAME', 'takimoto'); define('DB_PASSWORD', 'xxxxxxxx'); define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE); try { // connect $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る // Insert $stmt = $db->prepare("insert into tstTable (name, score) values (?, ?)"); /** * bindValue(引数1, 引数2, 引数3); * 引数1 | prepareした予約語の?の位置を指定 * 引数2 | 挿入する変数(値) * 引数3 | 挿入した値のデータ型を指定 */ $name = 'taki'; $stmt->bindValue(1, $name, PDO::PARAM_STR); $score = 23; $stmt->bindValue(2, $score, PDO::PARAM_INT); $stmt->execute(); $score = 44; $stmt->bindValue(2, $score, PDO::PARAM_INT); $stmt->execute(); // disconnect $db = null; //明示的に接続を切る } catch (PDOException $e ){ // $db->rollback(); echo $e->getMessage()."\n"; exit; } ?>
bindValueの第3引数にはデータ型を指定する。
ここで指定するデータ型についてはこちら。
定義済み定数 | php.net
execute() の引数を指定しない場合は、bindValueで指定した値がそのまま実行される。
bindValueをループと併用すると、多くのデータの一部を変更するような処理が実現可能。
名前付きパラメータを用いる場合
名前付きパラメータ使用時は、bindValue第1引数に次のように指定する。
<?php define('DB_DATABASE', 'DB_1605'); define('DB_USERNAME', 'takimoto'); define('DB_PASSWORD', 'xxxxxxxx'); define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE); try { // connect $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る // Insert // $stmt = $db->prepare("insert into tstTable (name, score) values (?, ?)"); $stmt = $db->prepare("insert into tstTable (name, score) values (:name, :score)"); /** * bindValue(引数1, 引数2, 引数3); * 引数1 | prepareした予約語の?の位置を指定 * 引数2 | 挿入する変数(値) * 引数3 | 挿入した値のデータ型を指定 */ $name = 'taki'; // $stmt->bindValue(1, $name, PDO::PARAM_STR); $stmt->bindValue(':name', $name, PDO::PARAM_STR); $score = 11; $stmt->bindValue(':score', $score, PDO::PARAM_INT); $stmt->execute(); $score = 22; $stmt->bindValue(':score', $score, PDO::PARAM_INT); $stmt->execute(); // disconnect $db = null; //明示的に接続を切る } catch (PDOException $e ){ // $db->rollback(); echo $e->getMessage()."\n"; exit; } ?>
bindParam()
- bindValue(): ?(予約語)と値をバインド
- bindParam(): 変数への参照をバインド
bindParam()では予め変数を指定しておき、後から変数の中身を変更することが可能。 コードをすっきりまとめたいときに。
<?php define('DB_DATABASE', 'DB_1605'); define('DB_USERNAME', 'takimoto'); define('DB_PASSWORD', 'xxxxxxxx'); define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE); try { // connect $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る // Insert $stmt = $db->prepare("insert into tstTable (name, score) values (?, ?)"); // $stmt = $db->prepare("insert into tstTable (name, score) values (:name, :score)"); $name = 'taki'; $stmt->bindValue(1, $name, PDO::PARAM_STR); $stmt->bindParam(2, $score, PDO::PARAM_INT); $score = 11; $stmt->execute(); $score = 22; $stmt->execute(); $score = 33; $stmt->execute(); // disconnect $db = null; //明示的に接続を切る } catch (PDOException $e ){ // $db->rollback(); echo $e->getMessage()."\n"; exit; } ?>
query()による抽出
本ページ上部でquery()について次のように記述しました。
改めて書くと次のようになります。
PDOでSQLを実行するコマンドは3種類ある
excec(): 結果を返さない安全なSQL
query(): 結果を返す安全なSQL. 何度も実行されないSQL.
prepare(): 結果を返すSQL. 安全対策が必要.複数回実行されるSQL.
ここではquery()とPDO::Fetchを用いた抽出方法を紹介する。
PDO::Fetchについてはこちらを参照。
PDOStatement::fetch | php.net
指定したデータの全てを表示する場合は次のメソッドを利用する。
PDOStatement::fetchAll | php.net
<?php define('DB_DATABASE', 'DB_1605'); define('DB_USERNAME', 'takimoto'); define('DB_PASSWORD', 'xxxxxxxx'); define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE); try { // connect $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る /** * select * * fetchAll | 全ての結果行を含む配列を返す * PDO::FETCH_ASSOC | キー, 値 のペアで結果が返ってくるオプション * rowCount() | 対象の行数を返すメソッド */ // select $stmt = $db->query("select * from tstTable"); $users = $stmt->fetchALL(PDO::FETCH_ASSOC);//レコードの表示 foreach ($users as $val) { echo "<pre>"; // ブラウザでの表示をキレイにするために var_dump($val); echo "</pre>"; } echo $stmt->rowCount()." records found !"; // disconnect $db = null; //明示的に接続を切る } catch (PDOException $e ){ // $db->rollback(); echo $e->getMessage()."\n"; exit; } ?>
prepare()を使った条件付き抽出
<?php define('DB_DATABASE', 'DB_1605'); define('DB_USERNAME', 'takimoto'); define('DB_PASSWORD', 'xxxxxxxx'); define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE); try { // connect $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る // select $stmt = $db->prepare("select score from tstTable where score > ?"); $stmt->execute([20]);// ? の値を指定 $users = $stmt->fetchALL(PDO::FETCH_ASSOC); foreach ($users as $val) { echo "<pre>"; var_dump($val); echo "</pre>"; } echo $stmt->rowCount()." records found !"; // disconnect $db = null; //明示的に接続を切る } catch (PDOException $e ){ // $db->rollback(); echo $e->getMessage()."\n"; exit; } ?>
like を使った抽出
likeを使ったあいまい検索についてはこちらのページで紹介しました。
MySQL コマンド一覧 | たきもとけんご.com
ここではその応用として、idに 5 を含むデータを抽出する方法を紹介します。
<?php define('DB_DATABASE', 'DB_1605'); define('DB_USERNAME', 'takimoto'); define('DB_PASSWORD', 'xxxxxxxx'); define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE); try { // connect $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る /** * select * * fetchAll | 全ての結果行を含む配列を返す * PDO::FETCH_ASSOC | キー, 値 のペアで結果が返ってくるオプション * rowCount() | 対象の行数を返すメソッド */ // select $stmt = $db->prepare("select id from tstTable where id like ?"); $stmt->execute(['%5%']);// 数字の5を含むidを抽出 $users = $stmt->fetchALL(PDO::FETCH_ASSOC); foreach ($users as $val) { echo "<pre>"; // ブラウザでの表示をキレイにするために var_dump($val); echo "</pre>"; } echo $stmt->rowCount()." records found !"; // disconnect $db = null; //明示的に接続を切る } catch (PDOException $e ){ // $db->rollback(); echo $e->getMessage()."\n"; exit; } ?>
order by ~~ desc を用いてデータを降順(逆順)に並べる方法
データの並べ替えには order を利用します。
このあたりのコマンドはこちらにまとめました。
MySQL コマンド一覧 | たきもとけんご.com
execute()の引数は基本的には文字列で渡される。
limitを指定した場合は数字を扱うので、bindValueを利用する。
数字を扱うので、データ型まで指定する。
<?php define('DB_DATABASE', 'DB_1605'); define('DB_USERNAME', 'takimoto'); define('DB_PASSWORD', 'xxxxxxxx'); define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE); try { // connect $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る // select $stmt = $db->prepare("select score from tstTable order by score desc limit ?"); $stmt->bindValue(1, 1, PDO::PARAM_INT); $stmt->execute(); $scores = $stmt->fetchALL(PDO::FETCH_ASSOC); foreach ($scores as $val) { echo "<pre>"; var_dump($val); echo "</pre>"; } echo $stmt->rowCount()." records found !"; // disconnect $db = null; //明示的に接続を切る } catch (PDOException $e ){ // $db->rollback(); echo $e->getMessage()."\n"; exit; } ?>
PDO::FETCH_CLASS
抽出したデータを直接クラスで使いたいときがある。
そんな時に PDO::FETCH_CLASSを利用すると便利。
FETCH_CLASSでは自動的にテーブルのカラムをクラスのプロパティとして設定してくれる。
<?php define('DB_DATABASE', 'DB_1605'); define('DB_USERNAME', 'takimoto'); define('DB_PASSWORD', 'xxxxxxxx'); define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE); class ClassUser { // property // public $id; // public $name; // public $tel; // public $score; // method public function show(){ echo $this->name."(".$this->id.")"; } } try { // connect $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る // select $stmt = $db->query("select * from tstTable"); $users = $stmt->fetchALL(PDO::FETCH_CLASS, 'ClassUser'); foreach ($users as $val) {// ここがClassUserになる echo "<pre>"; $val->show();// クラスで指定したmethodがここで使える echo "</pre>"; } echo $stmt->rowCount()." records found !"; // disconnect $db = null; //明示的に接続を切る } catch (PDOException $e ){ // $db->rollback(); echo $e->getMessage()."\n"; exit; } ?>
レコードの更新・削除
PHPからMySQLを操作してレコードの更新・削除を行うことができる。
- update: テーブルの更新
- delete: テーブルの削除
<?php define('DB_DATABASE', 'DB_1605'); define('DB_USERNAME', 'takimoto'); define('DB_PASSWORD', 'xxxxxxxx'); define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE); class ClassUser { // property // public $id; // public $name; // public $tel; // public $score; // method public function show(){ echo $this->name."(".$this->id.")"; } } try { // connect $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る // update $stmt = $db->prepare("update tstTable set score = :score where id = :id"); $stmt->execute([ ':score' => 100, ':id' => 16, ]); echo 'row has updated as follow:'. $stmt->rowCount(); // delete $stmt = $db->prepare("delete from tstTable where id = :id"); $stmt->execute([ ':id' => 17, ]); echo 'row has deleted as follow:'. $stmt->rowCount(); // disconnect $db = null; //明示的に接続を切る } catch (PDOException $e ){ // $db->rollback(); echo $e->getMessage()."\n"; exit; } ?>
transaction
ある処理が確実に終わっていることを知りたい時がある。
そんなときに便利なのがtransactionだ。
transactionはある一連の処理が終わることを保障してくれる仕組み。
たとえば、次のようなテーブルがあるとする。
+----+------+------+-------+ | id | name | tel | score | +----+------+------+-------+ | 15 | taki | NULL | 11 | | 16 | taki | NULL | 100 | +----+------+------+-------+
id=16 から id=15 へ scoreを40点移動させることを考える。
このとき、
id:16 → 100 – 40 = 60
id:15 → 11 + 40 = 51
となるはず。
このscoreの移動の一方が何らかの理由で行われない時、作業を中断して元の状態に戻すまでの処理を考える。
<?php define('DB_DATABASE', 'DB_1605'); define('DB_USERNAME', 'takimoto'); define('DB_PASSWORD', 'xxxxxxx'); define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE); try { // connect $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る // transaction $db->beginTransaction();// transaction開始の合図 $db->exec("update tstTable set score = score - 40 where id = 16"); $db->exec("update tstTable set score = score + 40 where id = 15"); $db->commit();// 結果を反映させる合図 // disconnect $db = null; //明示的に接続を切る } catch (PDOException $e ){ $db->rollback();// transactionのどちらかが失敗したら元に戻す echo $e->getMessage()."\n"; exit; } ?>