目次
- MYSQLデータベース・アクセス
- データベースへの接続
- mysqliのプリペアードステートメント
- SELECTその1:1行取得のパターン
- SELECTその2:複数項目・行の結果を処理する場合
- SELECT その3:結果の件数だけを取得するケース
- INSERTのパターン
- UPDATEのパターン
- DELETEのパターン
MYSQLデータベース・アクセス
フレームワークとかを使うと不要になる知識ですが、とりあえず整理しておきます。
ただ、「mysql」APIは、5.5から非推奨で、7.0系になって廃止になっています。
なので、選択肢としては mysqli と PDO_MySQLの2択です。
PDOのほうが一般的みたいですが、機能的には若干mysqliのほうが優れているということで、今回はmysqliをとりあげてます。
比較検討したサイトはこちらです。
データベースへの接続
データベースへの接続は、以下のようにします。
$mysqli = new mysqli(localhost, 'your_user', 'your_pass', 'dbname');
環境が変わると、ホスト名~DB名まで書き直さないといけないので、そこら中のファイルに書いたりすると面倒です。
たとえば、以下のように別ファイルにfunctionを書いて、使いまわすのが一般的じゃないですかね。
<?php
function get_db_connection(){
$mysqli = new mysqli('localhost', 'username', 'password', 'dbname');
if ($mysqli->connect_error) {
print("DB接続NG");
exit();
} else {
$mysqli->set_charset("utf8");
}
return $mysqli;
}
?>
上記を、common.php等の名前で保存します。
それで、さきほどのHTMLのファイルの上の方に、PHPスクリプトを追加して、保存したcommon.phpをインクルードして使うわけですね。
$mysqli = get_db_connection();
// データベースを使った処理
?>
mysqliのプリペアードステートメント
CRUDをは、C(Create=insert文)、R(Read=Select文)、U(Update=update文)、D(delete=delete文)のことです。
検索条件や更新する値をパラメーター化し、SQLを使い回す「プリペアードステートメント」に絞って整理します。
mysqliのプリペアードステートメントは、プレースホルダとして「?」を置くパターンのみです。
PHPで検索してると、:name みたいな名前付きのプレースホルダーを使う方法もでてきますが、あれは、PDO_MySQLの説明です。
SELECTその1:1行取得のパターン
取得する項目を明示的に指定して、一意に1行分のデータのみを取得するパターンです。
最初に、例を示してから整理していきます。
// A: SQL文を定義する
$sql = "select b.c_folder from hoge as a join folder as b on a.n_folder = b.n_folder where a.c_session=?";
// B: SQL文を$stmt オブジェクトに紐付ける
if ($stmt = $mysqli->prepare($sql)) {// C: プレースホルダと変数を結びつける
$stmt->bind_param("s", $sid);// D: 実行する
$stmt->execute();// E: 取得した値を変数に結びつける
$stmt->bind_result($folder_name);// F: 結びつけた変数に取得した値をセットする。
$stmt->fetch();// クローズする
$stmt->close();
}
このA~Eまでが1セットです。
順に補足説明していきます。
A: SQL文を定義する
実行するSQL文をそのまま書きます。
後で変数として値を与えたい部分(プレースホルダー)の位置に「?」を書きます。
「?」を複数書くと、後続の処理で変数をバインドする時とかは、左から見て「?」が出現した順番で処理されます。
B: SQL文を$stmt オブジェクトに紐付ける
$mysqli->prepare($sql) という構文で、$sqlにセットしたSQL文を、「mysqli_stmt」というプリペアードステートメントを表すクラスに変換して返します。
変換がうまくできなかった時(エラー時)には、FALSEを返すので、IF文でくくって、エラー時には処理されないようにしときます。
あとの操作は、すべて、「mysqli_stmt」である変数(今回の例の場合は$stmt)を使って行います。
C: プレースホルダと変数を結びつける
「$stmt->bind_param("s", $sid);」のように、SQL文内の「?」と、実際の変数を関連づけます。
複数の場合は、「$stmt->bind_param("ssid", $sid,$sid2,$num,$dbl);」みたいに、「?」マークの出現順に続けて書きます。
この最初の引数の"ssid"の部分は、変数の型を指定します。
文字1文字が、変数1個に対応しています。
使える型は以下のとおりです。(PHPマニュアルから引用)
あれ?日付や時刻がない!
と思った方正解です。
日付や時刻を表す型指定文字はなくて、文字列の「s」を指定するみたいです。
こうしておいて、あとで関連づけた変数の値を変更して、実行とすることで、一度生成したSQL文をループの中で使いまわせるという仕掛けです。
返り値は、成功TRUE か 失敗 FALSEが返ります。
例では、特にしてませんが、ここでIFで結果チェックしてログなどに出す方が良い場合も多いとは思います。
D: 実行する
文字とおり。
execute() = 実行する・・です。
補足はありません。
E: 取得した値を変数に結びつける
今度は、取得した値をセットする変数との紐付けです。
今回の例だと、「select b.c_folder from hoge」で、取得しているのが1項目なので、「$stmt->bind_result($folder_name);」で、b.c_folderと、$folder_nameを紐付けているわけです。
これで、取得する項目が 「select col1,col2,col3 from・・・」のように3つあったら、「$stmt->bind_result($col1,$col2,$col3);」のように同じ数だけ引数に追加します。
順番は、selectの中での左からの指定順序に従います。
返り値は、やっぱり成功TRUEと失敗FALSEです。
注意しないといけないのは、ここで変数に紐付けただけでは、変数の中身は空っぽだということです。
F: 結びつけた変数に取得した値をセットする
$stmt->fetch(); で、変数に値をセットします。
これが呼ばれて、初めて変数を使った処理ができます。
返り値は、成功・失敗だけでなく、成功したけど空だった(NULL)・・というのも取得できますので、空のデータが渡ると具合が悪い場合はチェックしたほうが良いです。
SELECTその2:複数項目・行の結果を処理する場合
今度は、「select * from・・・」で、事前に項目数を把握して、変数とマッピングできない場合を整理してみます。
あわせて、一覧表示する時みたいに、複数行のデータを取得する場合もやってみます。
mysqliの構文を説明する前に、繰り返し処理を使うので、それもかんたんに触れておきます。
まず「while」です。
while (式) {
/* 処理 */
}
こんな感じで、式の結果がTRUEである限り、処理を実行します。
次は、foreachです。
配列を順番に取り出し変数にセットして処理を繰り返します。
例
$array_a = array(1, 2, 3, 4);
foreach ($array_a as &$value) {
// $valueを使った何らかの処理}
unset($value);
例だと、配列 $array_a の内容を一つずつ取り出して、$valueにセットします。
最後のunset($value)は、お約束ごとです。
これをしないと、foreachのループが終わっても、$valueは配列を参照したままになるので、後でめんどくさいバグの原因になったりしますから、必ず、やっておくほうが安全です。
上記の2種類のループを使って、複数項目・複数行を取得した場合の例です。
if ($stmt = $mysqli->prepare("select * from xxxtable where item_id like ?")) {
$stmt->bind_param("s",$id);
$id = "00%";
$isSuccess = $stmt->execute();
if($isSuccess){// G:結果をとりだす
$result = $stmt->get_result();// H:結果を配列で一行ずつ取り出す
while ($row = $result->fetch_array(MYSQLI_NUM)){
print("<tr>\r\n");// I:1行分項目があるかぎり繰り返す。
foreach ($row as $r){
print "<td>".$r."</td>\r\n";
}unset($r);
print "</tr>\r\n";
}
}else{
printf("<h2>取得失敗</h2>");
}
$stmt->close();
}
「 G:結果を取り出す」までは、前に説明したのと同じです。
whereの条件にlikeを使う例にした位が変更点ですが、見れば分かる程度なので説明はしません。
G:結果を取り出す
「$result = $stmt->get_result();」で、結果セットを格納した「mysqli_result」オブジェクトを取得します。
この結果セットには、複数行の結果すべてがはいっています。
ここから後ろは、mysqli_resultオブジェクトの命令を使います。
H:結果を配列で一行ずつ取り出す
このループで、結果セット(複数行)から、1行ずつ配列でデータを取り出して、処理を行います。
while ($row = $result->fetch_array(MYSQLI_NUM)){
}
上記例の $rowは1行分のデータが、項目数分の配列で入ります。
ちなみに、fetch_array(MYSQLI_NUM) の「MYSQLI_NUM」を、「MYSQLI_ASSOC」に変更すると、配列ではなく、連想配列で1行分のデータを返すようになります。
すべての行を取り出し終わったら、NULLを返すので、ループから抜けるわけですね。
I:1行分項目があるかぎり繰り返す
1行に複数項目が配列ではいっている・・わけですから、この部分はわかりやすいと思います。
foreach ($row as $r){
print "<td>".$r."</td>\r\n";
}
$rowは1行のすべての項目を含んだ配列ですから、それを、1項目ずつとりだして「$r」におさめて処理を行うということです。
ちなみに、上記例のprint分で<tr>とか<td>等のHTMLのタグをつけて出力していますが、<table>~</table>の間で、上記を実行した結果は、例えばこんな感じになります。
SELECT その3:結果の件数だけを取得するケース
結果の値を取得するのでなく、件数だけを取得したいケースもありますよね。
その場合は、若干、違うやり方があります。
例です。
$sql = "select * from mocktable where id=?";
if ($stmt = $mysqli->prepare($sql)) {
$stmt->bind_param("s", $sid);
$stmt->execute();
$stmt->store_result(); // これ忘れるとnum_rowsは0
$cnt = $stmt->num_rows;
$stmt->close();
}
SQL文を実行するまでは同じですが、その後で、「$stmt->store_result();」を呼ぶところが違います。
これを実行すると、$stmt->num_rows;に取得件数がはいるので、これをチェックすることで、取得に成功したかどうかを判断をすることができます。
INSERTのパターン
insertも、プリペアードステートメントの構文の形としては、selectとほぼおなじです。
かつ、selectのように、結果を処理する部分がないので、かなりシンプルです。
例です。
$sql = "insert into mocktable values (?,?,?)";
$none = null;
if ($stmt = $mysqli->prepare($sql)) {
$stmt->bind_param("sis", $sid, $no, $none);
if($stmt->execute()){
print("DB更新成功");
}else{
printt("DBの更新失敗");
}
$stmt->close();
}
例をみると、SQL文が、insert文になっているだけで、流れは、selectのケースの実行までとほぼ同じだということがよくわかります。
insertの場合は、execute()が成功すれば、データベースが更新されます。
UPDATEのパターン
updateもSQL文が違うだけで、流れはinsertとほぼ同じです。
$sql = "update mocktable set no = ? where key_str = ?";
if ($stmt = $mysqli->prepare($sql)) {
$stmt->bind_param("is",$no,$key_str);
if($stmt->execute()){
print("DB更新成功");
}else{
print("DB更新失敗");
}
$stmt->close();
}
SQL文が違う以外、構文の流れ的にはほぼ一緒ですよね。
updateの場合も、execute()が成功すれば、データベースが更新されます。
DELETEのパターン
もう想像ついていると思いますが、delete文もパターンは同じです。
例
if ($stmt = $mysqli->prepare("delete from mocktable where no = ?")) {
$stmt->bind_param("i",$no);
$no = 2;
$isSuccess = $stmt->execute();
if($isSuccess){
print("DB削除成功");
}else{
print("DB削除失敗");
}
$stmt->close();
}
deleteの場合も、execute()が成功すれば、データベースが更新されます。
とりあえずの整理としては、こんな感じで。
ではでは。