PHPでDBアクセス

PDO(PHP Data Objects)

データーベース抽象化レイヤ(DBの違いを気にせずに使える)


エラー通知

以下の記述をしておくと、SQLの構文エラーを通知してくれる

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

※$dbはPDOオブジェクト



DBを開く

new PDO(データベース接続文字列, ユーザー名, パスワード)



データベース接続文字列

mysql:host=ホスト名; dbname=データベース名; charset=文字コード



DBを閉じる

PDOオブジェクトを破棄する(NULLにする)
※こうしなくてもスクリプトが終了すれば自動でDB接続が切断される



例)ローカルDBのデータベース名php10を文字コードutf8で開く

//開く
$db = new PDO('mysql:host=localhost;dbname=php10;charset=utf8', 'phpusr', 'phppass');

//閉じる
$db = NULL;




例外処理

try {
  例外が発生する可能性のあるコード
} catch (例外の種類 変数名) {
  例外発生時の処理
}


例)
try {
  $db = new PDO('mysql:host=localhost;dbname=php10;charset=utf8', 'phpusr', 'phppass');
} catch (PDOException $e) {
  die('エラーメッセージ:' . $e->getMessage());
}



SQLの実行

以下の手順が必要

・SQLの準備
・SQLにパラメータをセット
・SQL実行



SQLの準備

PDOオブジェクト->prepare(SQL文);

※PDOStatementオブジェクトが返ってくる




SQLにパラメータをセット

SQL文の中にプレイスホルダ(:名前)を使った場合、そこに値をセットする

※プレイスホルダを使わないとセキュリティ上の問題(SQLインジェクション)がある

PDOStatementオブジェクト->bindValue(パラメータ名, 値);


※セットする値が数値(int)の場合は第三引数に「PDO::PARAM_INT」を付けた方がよい

例)
$stt->bindValue(':age',$arr['age'],PDO::PARAM_INT);




SQL実行

PDOStatementオブジェクト->execute();


例)INSERTを実行

//DB接続
$db = new PDO('mysql:host=localhost;dbname=php10;charset=utf8', 'phpusr', 'phppass');

//SQLの準備
$stt = $db->prepare('INSERT INTO schedule(title) VALUES(:title)');

//SQLにパラメータをセット
$stt->bindValue(':title', $_POST['title']);

//SQL実行
$stt->execute();

//DB切断
$db = NULL;




DBからレコードを取得


SELECT文をexecuteメソッドを実行するとその結果がPDOStatementに保存される




取得したレコードの表示

１行ずつフェッチ(読み込み)をする

PDOStatementオブジェクト->fetch();




フェッチの形式を変更

fetchメソッドは規定で配列または連想配列でアクセスできるように返してくれる

変更したい場合はfetchメソッドに以下の引数を渡す

PDO::FETCH_NUM…配列($row[1])
PDO::FETCH_ASSOC…連想配列($row['title'])
PDO::FETCH_BOTH…配列または連想配列[規定]($row[1] or $row['title'])
PDO::FETCH_OBJ…オブジェクト($row->title)



例)

$db = new PDO('mysql:host=localhost;dbname=php10;charset=utf8', 'phpusr', 'phppass');
$stt = $db->prepare('SELECT title FROM schedule');
$stt->execute();

//全てのレコードをフェッチ
while ($row = $stt->fetch()) {
  //タイトル列を表示
  print($row['title']);
}
$db = NULL;




文字列→日付変換

strtotime(日付時刻文字列);

日付の文字列がUnixタイムスタンプ(1970/1/1 00:00:00から経過した秒数)に変換される

例)
$ts = strtotime('2014/12/26');




日付をフォーマット

フォーマットした日付を文字列で取得

date(フォーマット文字列 [, Unixタイムスタンプ]);

※タイムスタンプは省略可能、その場合は現在日時・時刻となる

例)現在日時をYYYY/MM/DDの形で取得
$str = date('Y/m/d');




タイムスタンプ取得

現在日時・時刻のUnixタイムスタンプを取得

time()

例)
$ts = time;




任意のタイムスタンプ取得

任意の日付・時刻のUnixタイムスタンプを取得

mktime(時, 分, 秒, 月, 日, 年);


例) 2015/1/6 9:18:45秒のタイムスタンプを取得
$ts = mktime(9, 18, 45, 1, 6, 2015);





日付書式

Y	4桁の年。例：2014
y	2桁の年。例：14
L	閏年。1：閏年　0：閏年ではない

m	ゼロ詰めの月。01～12
n	ゼロなしの月。1～12
F	フルスペルの月。例：January
M	3文字形式の月。例：Jan
t	月の日数。28～31

d	ゼロ詰めの日。01～31
j	ゼロなしの日。1～31
z	年間の通算日。0～365

g	12時間単位の時（ゼロなし）。1～12
G	24時間単位の時（ゼロなし）。0～23
h	12時間単位の時。01～12
H	24時間単位の時。00～23

i	分。00～59

s	秒。00～59

a	午前：am　午後：pm
A	午前：AM　午後：PM

D	3文字形式の曜日。例：Mon
l	フルスペルの曜日。例：Monday
N	ISO-8601形式の曜日。1（月）～7（日）
w	曜日。0（日）～6（土）

isset,empty,is_null

値	if($var)	isset	empty	is_null
$var=1	TRUE	TRUE	FALSE	FALSE
$var="";	FALSE	TRUE	TRUE	FALSE
$var="0";	FALSE	TRUE	TRUE	FALSE
$var=0;	FALSE	TRUE	TRUE	FALSE
$var=NULL;	FALSE	FALSE	TRUE	TRUE
$var	FALSE	FALSE	TRUE	TRUE
$var=array()	FALSE	TRUE	TRUE	FALSE
$var=array(1)	TRUE	TRUE	FALSE	FALSE

リダイレクト

以下のように書けば強制的に別のページに移動する

header('Location: リダイレクト先のURL');

※headerの前に出力(echoなど)をしてはいけない

例) yahooへリダイレクトさせる

header('Location: http://yahoo.co.jp/');




ファイルを開く

fopen(ファイルのパス, オープンモード)

※戻り値としてファイルハンドルを返す


オープンモード

rb…読み取り専用
wb…書き込み専用
w+b…読み書き
ab…追記書き込み
a+b…読み書き(追記)




ファイルを閉じる

fclose(ファイルハンドル);



ファイルに書き込む

fputs(ファイルハンドル, 書き込む文字列);

※fwriteでもOK



ファイルのロック

flock(ファイルハンドル, ロックの種類)

LOCK_SH…共有ロック(書き込み禁止)
LOCK_EX…排他ロック(読み書き禁止)
LOCK_UN…ロックの解除


例)
//ファイルをオープン
$file = fopen('guest.dat', 'ab');

//ファイルをロック
flock($file, LOCK_EX);

//ファイルに書き込み
fputs($file, 'abc');

//ロックを解除
flock($file, LOCK_UN);

//ファイルを閉じる
fclose($file);



@演算子

処理の実行に失敗した場合でも警告を発生させない

※その場合、エラーがあったかどうか分からないので「or die…」を付ける



CSVファイルの読み込み

fgetcsv(ファイルハンドル, 読み込む最大文字列, 区切り文字)

例)タブ区切りファイルの読み込み
$file = @fopen('guest.dat', 'rb') or die('ファイルが開けませんでした');
while ($row = fgetcsv($file, 1024, "\t")) {
  echo $row[0]));
}
fclose($file);


ファイルの読み込み

fgets(ファイルハンドル)



文字を分割

explode(区切り文字, 文字列)




トランザクション処理

DBへの複数回に渡る追加、更新をまとめて処理すること

※大量の件数の処理を行う場合はトランザクションを使ったほうが早い

※InnoDBではないと場合はトランザクションは使えない




トランザクション開始

beginTransaction



トランザクション完了

commit


トランザクションの巻き戻し

rollback


例)CSVファイルの中身を全件DBへ登録
try {
  $db = new PDO('mysql:host=localhost;dbname=php10;charset=utf8', 'phpusr', 'phppass');

  //トランザクション開始
  $db->beginTransaction();

  $file = @fopen('ejdic-hand-utf8.txt', 'rb') or die(' failure!');

  //CSVファイルの中身を全件登録
  while ($row = fgetcsv($file, 5000, ",")) {
    $stmt = $db->prepare('INSERT INTO ejdic (word, mean) VALUES(:word, :mean) ');
    $stmt->bindValue(':word', $row[0]);
    $stmt->bindValue(':mean', $row[1]);
    $stmt->execute();
  }

  //トランザクションを完了
  $db->commit();
} catch (PDOException $e) {

  //例外が発生した場合処理を巻き戻す
  $db->rollBack();
  echo $e->getMessage();
}

データベース(SQL)

MySQL

オープンソースのリレーショナルデータベース



リレーショナルデータベース(RDB)

データを表形式で表すデータベース



phpMyAdmin

MySQLの管理ツール



DBMS

DataBase Management Systemの略でデータベース管理システム



照合順序

DBでの文字の並び替え順、比較などを決めるものでDBの文字コードもこれで決まる



ユーザー

DBにアクセスする人を区別し、権限を与えて検索や更新を許可する



テーブル

データを保存する二次元の表のようなもの



カラム

列をカラム、またはフィールドという



レコード

行単位のデータをレコードという



データ型

数値

INT 整数
FLOAT,DOUBLE 小数


文字列

VARCHAR(len) 可変長文字列
text 文字列(文字数が多い場合)


日付

DATETIME 日付時刻
DATE 日付
TIME 時刻


バイナリ

BLOB


設定情報

デフォルト値、NULLを許可、A/I(オートインクリメント)、インデックス(索引)



主キー(プライマリーキー)

レコードを一意に識別する列、全てのテーブルに設定すべき



SQL


基本

・大文字、小文字を区別しない
・キーワードの区切りは空白
・文の末尾はセミコロン(;)
・識別子(列名など)をバッククォートで囲んでもよい


SELECT
  `sid`
FROM
  `schedule`




利用できるデータベースの表示

SHOW DATABASES;



INSERT

テーブルに新規データを挿入する


INSERT INTO テーブル名 (
  フィールド名1,
  フィールド名2,
  ・・・
)
VALUES (
  値1,
  値2,
  ・・・
);


例)タイトル「打ち合わせ」日付「2014/12/25」のレコードを挿入

INSERT INTO schedule (
  title,
  sdate
)
VALUES (
  '打ち合わせ',
  '2014-12-25'
);


複数の行を一度に挿入

VALUESの後の値をカンマ区切りで複数指定する

INSERT INTO schedule
(title, sdate)
VALUES
('打ち合わせ', '2014-12-25'),
('会議', '2014-12-27');



自動でセットされる値

INSERTで値をセットしなかった値には以下の優先順で値がセットされる

1.自動連番(オートインクリメント)
2.デフォルト値
3.NULL



文字列、日付

文字列、日付は値をシングルクォート(')で囲まなければならない

INSERT INTO schedule (sid, title, sdate)
VALUES (7, 'WINGS忘年会', '2012-11-23');



UPDATE

データを更新する


UPDATE テーブル名
SET
  フィールド名1 = 値1,
  フィールド名2 = 値2,
  ・・・
WHERE
  条件式


例)sidが「2」のレコードのタイトル「面接」を時刻を「18:00」に更新

UPDATE schedule
SET
  title = '面接',
  stime = '18:00:00'
WHERE
  sid = 2;


※条件式は省略可だが、その場合は全てのレコードが更新される



DELETE

データを削除する

DELETE
FROM
  テーブル名
WHERE
  条件式

※条件式は省略可だが、その場合は全てのレコードが削除される


例)sidが「3」のレコードを削除

DELETE
FROM
  schedule
WHERE
  sid = 3;



SELECT

データを取得する


SELECT
  フィールド名1,
  フィールド名2,
FROM
  テーブル名
WHERE
  条件式
ORDER BY
  ソート式

例)sidが「2～8」のレコードを取得(日付の降順で「タイトル」「日付」のみ)

SELECT
  title,
  sdate
FROM
  schedule
WHERE
  sid BETWEEN 2 AND 8
ORDER BY
  sdate DESC


アスタリスク

フィールド名を「*」だけにすると全ての列が表示される


WHERE句

取り出すデータを絞り込む


比較演算子

BETWEEN X AND Y …X～Yの範囲
IN (X, Y, Z) …X,Y,Zのいずれか
IS NULL …NULLであるか
LIKE 文字列  …含まれるか(%をワイルドカードとして使う)
  例)LIKE '%山%' …山が含まれているかどうか



ORDER BY句

列名と並び順を指定して並び替える


並び順

ASC…昇順
DESC…降順
※並び順を省略した場合はASC(昇順)



GROUP BY句

特定の列でレコードをまとめて集計などをするためにグループ化し、集計関数と共に使う


集計関数

AVG…平均
COUNT…件数
MAX…最大値
MIN…最小値
SUM…合計


例)同じ日付の件数を集計する

SELECT
  sdate,
  COUNT(*)
FROM
  schedule
GROUP BY
  sdate



句の並び順

WHERE → GROUP BY → ORDER BY



列別名

SELECT句の列名に別の名前を付けることができる

SELECT
  列名 as 列別名
FROM
  ・・・


例)memoをdescriptionという別名で表示する

SELECT
  memo as description
FROM
  schedule



LIMIT句

取得したレコードの件数を制御する

※LIMIT句が無いDBもあるので注意(Oracleは無い)


LIMIT [開始位置,] 取得する行数

※開始位置は省略可


例)データを5件取得
SELECT  *
  FROM  schedule
 LIMIT  5

例)データを3件目から10件取得
SELECT  *
  FROM  schedule
 LIMIT  3, 10



HAVING句

グループ化したデータを絞り込む

※WHERE句はグループ化したデータには使えない為


例)平均年齢が30以上のデータを取得
SELECT  blood, AVG(age)
FROM  persons
GROUP BY  blood
HAVING  AVG(age) >= 30