PDOでデータベースを扱う

PDO(PHP Data Object)

MySQLを使っていきます

まずMySQLでデータベースを作成

localhost

mysql -u root

mysql

create database db;

grant all on db.* to dbuser@localhost identified by 'A';

use db;

create table users (
  id int not null auto_increment primary key,
  name varchar(255),
  score int
);

1.PDOの設定

PDOは基本的にtryとcatchで書く。

pdo.php

define('DB_DATABASE', 'db');
define('DB_USERNAME', 'dbuser');
define('DB_PASSWORD', 'A');
//Data Source Nameの略でデータベースシステムに応じて接続するための文字列(mysqlの場合に'mysql:host=localhost;dbname='とかく)
define('PDO_DSN', 'mysql:host=localhost;dbname=' . DB_DATABASE);

try{
  //PDOに接続するにはPDOクラスのインスタンスを作る
  $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
  //エラーが出たときにはExceptionを出す設定
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

}catch(PDOException $e){
  echo $e->getMessage();
  exit;
}

2. exec()について

pdo.php

define('DB_DATABASE', 'db');
define('DB_USERNAME', 'dbuser');
define('DB_PASSWORD', 'A');
define('PDO_DSN', 'mysql:host=localhost;dbname=' . DB_DATABASE);

try{
  //PDOに接続するにはPDOクラスのインスタンスを作る
  $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
  //エラーが出たときにはExceptionを出す設定
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // insert
  $db->exec("insert into users (name,score) values ('taguti',55)");//格納されるだけ
  echo "user added!";

  //データベースの接続を切断する
  $db = null;


  /*
  PDOでSQLを実行する際の手段について
  (1) exec(); 結果を返さない、安全なSQL
  (2) query(); 結果を返す、安全、何回も実行されないSQL
  (3) prepare(); 結果を返す、安全対策が必要、複数回実行されるSQL、悪意のあるコードの対策
  */  
}catch(PDOException $e){
  echo $e->getMessage();
  exit;
}

3. prepare()について

pdo.php

define('DB_DATABASE', 'db');
define('DB_USERNAME', 'dbuser');
define('DB_PASSWORD', 'A');
define('PDO_DSN', 'mysql:host=localhost;dbname=' . DB_DATABASE);

try{
  //PDOに接続するにはPDOクラスのインスタンスを作る
  $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
  //エラーが出たときにはExceptionを出す設定
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  /*
  PDOでSQLを実行する際の手段について
  (1) exec(); 結果を返さない、安全なSQLを実行するのに使う
  (2) query(); 結果を返す、安全で何回も実行されないSQL
  (3) prepare(); 結果を返す、安全対策が必要、複数回実行されるSQL、悪意のあるコードの対策
  */

  //値を渡すときはとりあえず？にしておく
  //statementオブジェクトが返ってくる
  $stmt = $db->prepare("insert into users (name,score) values (?,?)");
  //statementオブジェクトにはexcute()メソッドがあって、配列を渡してあげると値を安全な形で埋め込んで実行してくれる
  $stmt->execute(['taguchi', 44]);
  //挿入されたレコードのidを引っ張ってくる
  echo "inserted: " . $db->lastInsertId();

}catch(PDOException $e){
  echo $e->getMessage();
  exit;
}

4. prepare()の名前付きパラメータ

pdo.php

define('DB_DATABASE', 'db');
define('DB_USERNAME', 'dbuser');
define('DB_PASSWORD', 'A');
define('PDO_DSN', 'mysql:host=localhost;dbname=' . DB_DATABASE);

try{
  //PDOに接続するにはPDOクラスのインスタンスを作る
  $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
  //エラーが出たときにはExceptionを出す設定
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  //名前付きパラメータは扱うカラムが多くなってきたときに便利
  $stmt = $db->prepare("insert into users (name,score) values (:name, :score)");
  //executeメソッドにわたす配列をkeyとValueのペアにする
  $stmt->execute([':name'=>'fkoji',':score' => 44]);
  echo "insert: " . $db->lastInsertId();

}catch(PDOException $e){
  echo $e->getMessage();
  exit;
}

5. bindvalue()について

pdo.php

define('DB_DATABASE', 'db');
define('DB_USERNAME', 'dbuser');
define('DB_PASSWORD', 'A');
define('PDO_DSN', 'mysql:host=localhost;dbname=' . DB_DATABASE);

try{
  //connect
  $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // insert
  //bindValue
  $stmt = $db->prepare("insert into users (name,score) values (?,?)");

  //bindValueはカラムごとに値をセットしていくことができる
  //$stmt->bindValue(?のところ, 値, データ型指定);

  $name ='taguchi';
  $stmt->bindValue(1, $name, PDO::PARAM_STR);
  $score = 23;
  $stmt->bindValue(2, $score, PDO::PARAM_INT);
  $stmt->execute();  //execute()の引数は何もなかったらbindした値を返す
  //scoreだけ変える
  $score = 44;
  $stmt->bindValue(2, $score, PDO::PARAM_INT);
  $stmt->execute();

  //名前付きパラメータのとき
  //$stmt->bindValue(':name', $name, PDO::PARAM_STR);

  //第三引数
  //PDP::PARAM_NULL NULLの場合
  //PDP::PARAM_BOOL 論理値の場合

}catch(PDOException $e){
  echo $e->getMessage();
  exit;
}

6. bindParam()について

pdo.php

define('DB_DATABASE', 'db');
define('DB_USERNAME', 'dbuser');
define('DB_PASSWORD', 'A');
define('PDO_DSN', 'mysql:host=localhost;dbname=' . DB_DATABASE);

try{
  //connect
  $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  //bindValue: ？と値をbindする
  //bindParam: ？と変数への参照（変数が指し示しているもの）をbindする
  $stmt = $db->prepare("insert into users (name,score) values (?,?)");

  $name ='taguchi';
  $stmt->bindValue(1, $name, PDO::PARAM_STR);

  //二番目の？にはその時々で指し示しているものにbindする
  $stmt->bindParam(2, $score, PDO::PARAM_INT);
  $score = 52;
  $stmt->execute();
  $score = 44;
  $stmt->execute();
  $score = 6;
  $stmt->execute();


  //PDP::PARAM_NULL NULLの場合
  //PDP::PARAM_BOOL 論理値の場合

}catch(PDOException $e){
  echo $e->getMessage();
  exit;
}

7. query()の全件抽出

pdo.php

define('DB_DATABASE', 'db');
define('DB_USERNAME', 'dbuser');
define('DB_PASSWORD', 'A');
define('PDO_DSN', 'mysql:host=localhost;dbname=' . DB_DATABASE);

try{
  //connect
  $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // //あらかじめ3つのデータを作っといた
  // insert
  //$stmt = $db->prepare("insert into users (name,score) values (?,?)");
  // $name ='taguchi';
  // $stmt->bindValue(1, $name, PDO::PARAM_STR);
  // $score = 55;
  // $stmt->bindValue(2, $score, PDO::PARAM_INT);
  // $stmt->execute();
  // $name ='fkoji';
  // $stmt->bindValue(1, $name, PDO::PARAM_STR);
  // $score = 82;
  // $stmt->bindValue(2, $score, PDO::PARAM_INT);
  // $stmt->execute();
  // $name ='doti';
  // $stmt->bindValue(1, $name, PDO::PARAM_STR);
  // $score = 66;
  // $stmt->bindValue(2, $score, PDO::PARAM_INT);
  // $stmt->execute();


  //抽出の場合
  //全件抽出select all　ステイトメントオブジェクトが返ってくる
  $stmt = $db->query("select * from users");
  //引っ張ってくるときにどういうデータ形式にするか指定
  $users = $stmt->fetchAll(PDO::FETCH_ASSOC); //PDO::FETCH_ASSOCはキーと値のペアの配列で返ってくるオプション
  foreach ($users as $user) {
    var_dump($user);
  }
  echo $stmt->rowCount() . " records found."; //rowCount()は引っ張ってきたレコードの数を引っ張ってくる


}catch(PDOException $e){
  echo $e->getMessage();
  exit;
}

8. 条件付き抽出

pdo.php

define('DB_DATABASE', 'db');
define('DB_USERNAME', 'dbuser');
define('DB_PASSWORD', 'A');
define('PDO_DSN', 'mysql:host=localhost;dbname=' . DB_DATABASE);

try{
  //connect
  $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);



  //条件付き抽出



  //55より大きいscoreを抽出する（データは挿入されている状態）
  $stmt = $db->prepare("select score from users where score > ?");
  $stmt->execute([55]);

  //likeを使った抽出
  $stmt = $db->prepare("select name from users where name like ?");
  //％はこっちでつかう
  $stmt->execute(['%t%']);

  //逆順に並べて上位何人かを引っ張る
  $stmt = $db->prepare("select score from users order by score desc limit ?");
  $stmt->bindValue(1, 1, PDO::PARAM_INT);//大体文字列を渡さなければならない。だからbindValueで型の指定をする
  $stmt->execute();


}catch(PDOException $e){
  echo $e->getMessage();
  exit;
}

9. PDO::FETCH_CLASSを使う

pdo.php

define('DB_DATABASE', 'db');
define('DB_USERNAME', 'dbuser');
define('DB_PASSWORD', 'A');
define('PDO_DSN', 'mysql:host=localhost;dbname=' . DB_DATABASE);


//fetch()で使えるPDO::FETCH_CLASSというモードについて
//これを使うと抽出したデータを自分が指定したクラスに直接セットできるので、使い方を見ていきます。

try{
  //connect
  $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $stmt = $db->query("select * from users");
  //PDO::FETCH_CLASS モードにするにはここを PDO::FETCH_CLASS として、第 2 引数にクラスと名前を書いてあげれば OK です。
  $users = $stmt->fetchAll(PDO::FETCH_CLASS, 'User');
  //$userがuserクラスになる
  foreach ($users as $user) {
    $user->show();
  }
}catch(PDOException $e){
  echo $e->getMessage();
  exit;
}

10. レコードの更新・削除

pdo.php

define('DB_DATABASE', 'db');
define('DB_USERNAME', 'dbuser');
define('DB_PASSWORD', 'A');
define('PDO_DSN', 'mysql:host=localhost;dbname=' . DB_DATABASE);

try{
  //connect
  $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  //update
  //tagutiのスコアを変更する
  $stmt = $db->prepare("update users set score = :score where name = :name");
  $stmt->execute([
    ':score' => 100,
    ':name' => 'taguti',
  ]);
  echo 'row updated: ' . $stmt->rowCount(); //rowCount()で個数出力

  //delete
  //名前のinstallを削除
  $stmt = $db->prepare("delete from users where name = :name");
  $stmt->execute([
    ':name' => 'install',
  ]);
  echo 'row deleated: ' . $stmt->rowCount(); //rowCount()で個数出力
}catch(PDOException $e){
  echo $e->getMessage();
  exit;
}

11. トランスザクション

pdo.php

define('DB_DATABASE', 'db');
define('DB_USERNAME', 'dbuser');
define('DB_PASSWORD', 'A');
define('PDO_DSN', 'mysql:host=localhost;dbname=' . DB_DATABASE);

try{
  //connect
  $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  //トランザクション
  //これはある一連の処理が必ず行われることを保証してくれる仕組み
  //transaction
  $db->beginTransaction();
  $db->exec("update users set score = score - 10 where name = 'taguti'");
  $db->exec("update users set score = score + 10 where name = 'ishii'");
  $db->commit();//両方とも上手くいったときはじめて結果に反映


//もしどちらかの処理が失敗した場合はどちらもデータを元に戻してあげたいので、その場合は Exception が出るはずなので、こちらの方で $db->rollback(); としてあげてください。
}catch(PDOException $e){
  $db->rollback();
  echo $e->getMessage();
  exit;
}