PHPとPDOを使ったMySQLコード・クエリ役立つまとめ

DB操作でよく使うPDOやクエリは、殆ど決まりきったものが殆どなので、よく使うコードをまとめておこうと思います。

ある程度、PDOが理解できるとマニュアルだけで十分かもしれないので、マニュアルもブクマしておきましょう！PHP Data Objects(PDOのマニュアル)

PHP/PDOでMysqlに接続する

/*接続用ステータス*/
$dsn = "mysql:dbname=●DB名●;host=●ホスト名●;charset=utf8mb4";
$User_Name = "●●●●●●";
$Password = "●●●●●●";
$Driver_Options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];
/*接続・実行*/
try{$pdo = new PDO($dsn, $User_Name, $Password, $Driver_Options);}
/*エラー出力*/
catch(PDOException $PDO_ERROR){
header('Content-Type: text/plain; charset=UTF-8', true, 500);
exit('DataBase ERROR/'.$PDO_ERROR->getMessage());
}

●●●には、それぞれ適宜、値を入力。

これでMySQLデータベースに接続、もしエラーがあればエラーを出力することができます。クエリなど実行コードはtryの中に記述します。

MySQLでテーブルを新規で作る

$DB_table_name = "new_table";
$create_query = '
CREATE TABLE IF NOT EXISTS '.$DB_table_name.'(
id INT( 8 ) NOT NULL AUTO_INCREMENT primary key, 
name VARCHAR(10) NOT NULL, 
age int(3) NOT NULL
)'; 
$create = $pdo -> prepare($create_query);
$create -> execute();

設定したテーブル名が存在しなければ新規でテーブルを作成します。ソースコードはサンプルで、id,name,ageのテーブルを組んでいます。クエリ文を組んで、実行しているだけの基本的なものです。

MySQLにデータをINSERT(挿入)する

$name = "name";
$age = "20";
$query = $pdo->prepare('INSERT INTO '.$DB_table_name.' (name,age) VALUES (:name,:age)');
$query->bindValue(':name', $name, PDO::PARAM_STR);
$query->bindValue(':age', $age, PDO::PARAM_INT);
$query->execute();

INSERTする値を変数で指定する場合は、bindValueして関連付けます。クエリ文の変数は＄が「：」になります。数値の場合は、int指定します。あとは実行するだけ。

シングルクォート(single quote)とバッククォート(backquote)

phpMyAdminを使ってクエリをイジっていると、実行されたSQL文にシングルクォート「’」に似た記号「`」が付加されます。これは「バッククォート(backquote)」と呼ばれる記号です。

$query = "select * from `テーブル名` where `カラム名` = '値'";

• テーブル名やフィールド名はバッククォーテーション
• 値や文字列はシングルクォーテーション

かんたんに説明すると「予約語をエスケープ」するためです。

$query = "select * from `テーブル名` where `update` = '値'";

例えば更新日を記録するカラム名を「update」とた場合、updateは予約語なのでエラーになります。エラーにしないためにはバッククォートで囲むとカラム名としてに認識して正常に稼働します。
予約語の一覧はマニュアルで確認できます！

最新のレコードを3つだけ取得する

$query = "
select * from `テーブル名` 
order by `基準となるカラム名` desc limit 3;
";

基準となるカラムをorder byしてあげるだけでOK！
もし、取得したいカラム要素が日付データで、その日付でも昇順(古い順)にしたかったらas句を使って2回order byします。

select * from (
    select * from `テーブル名` order by `日付カラム名` desc limit 3
) as BETSUMEI
order by `日付カラム名`;

一旦、日付カラムを取得して、取得したデータを再度並び替えるという流れです。
as句を使わないと、テーブル全体から見た日付カラムの古い順で取得されるため日付を考慮しません。

昇順(ASC)と降順(DESC)新しい順はどっち？

• 昇順(ASC/アスク)：古い順
• 降順(DESC/デスク)：新しい順

ASCは昇順で昇るから、小さい値から大きい値へ(1,2,3,4,5…)
DESCは降順で降りるから、大きい値から小さい値へ(5,4,3,2,1)

ASC、DESCを覚えられないなら、ちょっと工夫。それぞれの文字の頭と尻尾に注目して、それをアルファベットの順番(ABCDEFG)で当てはめて考える。

ASCはA → C、(A→B→C)小さい値から大きい値(昇順)
DESCはD → C、(D→C→B→A)大きい値から小さい値(降順)

DBに配列(array)のまま挿入する

//配列をシリアライズする
$array_hairetsu = array("りんご","アップル","あっぽう");
$array_serialize = serialize($array_hairetsu);

//出力するときはアンシリアライズする
$array_unserialize = unserialize($array_serialize);

arrayをそのままDBに挿入すると「Array」となり意味をなさなくなるため、シリアライズしてから挿入すれば良い。ただし、出力するときもアンシリアライズしなければならない。

これめっちゃ便利！

※これ注意
格納するときBLOB型のカラムに入れること。取得するときstringだけど実はバイナリ文字列なので、text型に格納すると配列が壊れる可能性があるよ。serializeのphpマニアルにも書いてあるよ！

prepareとqueryの違い

クエリを実行するときに、prepareとqueryが存在します。prepareとqueryを単純に表現すると、

• prepare()：ユーザーからの入力がある(INSERTやUPDATEなど)
• query()：DBの情報を取得するだけ(show columnsとかWHERE)

prepare()の場合は、クエリでアップデートする情報・変数をバインドするステップが必要になります。

バインドするとは？バインド(bind)ってなんやねん！

PDOで最初にポカーンしたのは、「バインドする」でした。翻訳ツールでbindを調べると「翻訳結果：バインド」と出てきてムキーしたことは記憶に新しいです。

結論から言えば、バインド(bind)は結びつけるという意味です。

PDOでは、基本的に変数を直接、扱えません。そこで、変数を「：」で始まるものに結びつけ(関連付け)ているのです。

$query->bindValue(':name', $name, PDO::PARAM_STR);

後ろについてるやつは、文字列なら「PDO::PARAM_STR」数字なら「PDO::PARAM_INT」を指定しているやつです。

PDOマニュアルは理解が深まると役に立ちますのでブックマークしておきましょう。

PHP Data Objects(PDOのマニュアル)

「bindValue」と「bindParam」の違い

$name = "名前1";
$query->bindValue(':name', $name, PDO::PARAM_STR);
$query->bindParam(':name', $name, PDO::PARAM_STR);
$name = "名前2";

• bindValue：値をバイントする
• bindParam：変数をバイントする

バインドにも２つの記述があります。大きな違いは、変数を評価(確認)するタイミングです。bindParamはexecute()(実行)した時点で評価するのので、結果は「名前2」になります。
bindValueは、即時評価されるため、結果は「名前1」となります。

また、bindValueは値を直接入力してもエラーは出ませんが、bindParamは変数で設定しておかないとエラーを出します。

データベースの中身を表示&配列で取得する

$query = $pdo->query('SELECT * FROM '.$DB_table_name.' ORDER BY id desc');
/*出力*/
while($row = $query -> fetch(PDO::FETCH_ASSOC)) {
$query_array[] = $row;
echo $row['id']."\t / ".$row['name']."\t / ".$row['age']."<br />";
}

中身を出力するために、queryで取得して、whileで回して出力します。queryの場合は「execute」は必要ありません。
ついでに配列化もしたいなら「$query_array[] = $row;」で全てのデータを分かりやすく配列で取得できます。

whileを使わなくても取得する方法はこちら

重複が無いならINSERT、有れば上書き(UPDATE)する

$INSERT_query = $pdo->prepare('INSERT INTO '.$DB_table.'(
Name,
PageName,
) 
VALUES (
:Name,
:PageName
) ON DUPLICATE KEY UPDATE Name = VALUES (Name), PageName = VALUES (PageName)'
);
$INSERT_query->bindValue(':Name', $Name, PDO::PARAM_STR);
$INSERT_query->bindValue(':PageName', $PageName, PDO::PARAM_STR);
$INSERT_query->execute();

このINSERT文はめちゃめちゃ効率がいいです！
重複がなければINSERTして、重複があれば指定のカラムをUPDATEする。これはほんとに便利！

でも注意点が１つ。

テーブル側に「PRIMARY KEY」「UNIQUE KEY」の設定が必要です。例えば、idにPRIMARY KEYを設定、PageNameにUNIQUE KEYを設定します。

この設定が無いと重複していても新規で行が増えていきます。

Nullをゼロとして認識させるMySQLテーブル設定

Pv INT(7) default 0

これだけ。NOT NULLでテーブルを設定すると、INSERTされた際、何も値がなければNullが入ります。0ではなく、Nullは何も無いという意味なので、計算するときなどに困ります。

この場合、デフォルト値を「0」にしてあげれば、何も値がない場合は0が挿入されるというわけ。もちろん、デフォルトを「1」にしても問題ありません！

テーブルにインデックスを付けておく

$query = '
CREATE TABLE IF NOT EXISTS '.$DB_table.'(
id INT( 8 ) NOT NULL AUTO_INCREMENT primary key, 
Name VARCHAR(10) NOT NULL, 
Age INT(2)  NOT NULL, 
Sex VARCHAR(5)  NOT NULL, 
INDEX indexnonamae(Age, Sex)
)'; 
$table = $pdo -> prepare($query);
$table -> execute();

インデックスは後からでもつけられますが、テーブルを作るときに先につけておくこともできます。Create TableしたとにINDEXと名前を指定してインデックスするカラムを指定すればOK！

カラム１つ１つにインデックスを付けるのもありですが、１つのクエリを実行するとき、１つのテーブルにつき１つのインデックスしか使用できない特徴があるので、まとめて付けておくと便利です。

ユニークを設定しておけばINSERTの重複処理が簡単

UNIQUE (カラム1,カラム2,カラム3,カラム4)

テーブル設定で重複したら困るカラムにユニークを設定しておく。

NSERT IGNORE INTO テーブル名 (カラム1,カラム2,カラム3,カラム4)
VALUES (:kara1,:kara2,:kara3,:kara4)

INSERTの際にIGNOREと入れておくと、ユニークで指定したカラムが重複していなければ通常通りINSERTされ、重複しているとスルー(INSERTしない)されます。IGNOREを入れていないとエラーが出ます。

テーブルにあるカラムを一覧表示したい

クエリ文は単純。カラムを見せろと指示を送るだけ。

show columns from `テーブル名`;

取得したカラムは配列に直して使用します。

$result_array = $result -> fetchAll();
//確認用 echo "<pre>";print_r($result_array);echo "</pre>";
for($i=0; $i<count($result_array); $i++){
echo $result_array[$i][Field]."<br />";
}

キー名は配列を表示して確認して下さい。

バイナリ(BLOB型)をlike検索したい

BLOB型だとlike検索できないので、LOCATEを使います。検索したいキーワードをutf8にcovertして出現するカラム位置を特定する流れです。

$query = "SELECT * FROM `テーブル名` 
where
LOCATE(CONVERT('検索したいキーワード' using utf8), `検索対象カラム名`)
";

複数のキーワードで絞り込みたい場合は下記。

$query = "SELECT * FROM `テーブル名` 
where
LOCATE(CONVERT('検索したいキーワード' using utf8), `検索対象カラム名`)
OR //(または AND) 
LOCATE(CONVERT('検索したいキーワード' using utf8), `検索対象カラム名`)
";

単純にORかANDで繋いでやれば絞り込みも出来ます。

異なる構造のテーブルを結合してデータを検索する

テーブルの構造が異なるとUNIONしたときエラーになる「SQLSTATE[23000]: Integrity constraint violation: 1052 Column」

それを回避するために以下のように記述する

SELECT 
a,b,c,d,e
FROM `テーブル名` where a = "田中"
UNION ALL
SELECT 
a,b,
"" as c,
"" as d,
e
FROM `テーブル名` where a = "田中"

カラム数が異なるとエラーになるため、片方にないカラムは 「”” as カラム名」と表記して空とし列数を揃える。

レコードの行数だけ取得したい

$Recordcount = $pdo->query('SELECT COUNT(*) AS Record FROM DB_table_name001');
//結果例　[Record] => 49

クエリ文です。テーブル内のレコード数を返してくれます。AS ほにゃららとすると取得したデータに名前を付けられます。

複数のテーブルのレコード数(行数)をカウントして取得したい場合は、少しいじります。

SELECT (SELECT COUNT(*) FROM DB_table_name001) AS Record_001,(SELECT COUNT(*) FROM DB_table_name002) AS Record_002

SELECTの中に複数のSELECTを入れてやると、一度に複数の情報を取得することができます。

取得したデータを表示(出力)したい

$result = $pdo->query('SELECT * FROM table_name');
$result_array = $result -> fetch(PDO::FETCH_ASSOC);

$result_arrayに配列として取得したデータが入れられます(最初の1つのみ)。もし複数のデータがある場合は、fetchの部分を変えます。

複数のデータを配列で取得

$result_array = $result -> fetchAll();

上記同様に$result_arrayに配列として取得したデータが入れられます。

取得した配列を表示する場合は、配列内容を一旦確認してカラム名を調べて下さい。

echo "<pre>";print_r($result_array);echo "</pre>";
foreach($result_array as $value) {
echo $value[Field]."<br />";
}

全ての取得したデータを配列で返すので、データが膨大にある場合は処理の負担が増大します。DB側のクエリ文で検索条件を絞ってから使うようにしたいです。

直接、表示したい場合はwhileを使います。

直接、表示させたい

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    print $row[column_name]."<br />";
}

取得したレコードが順番に表示されます。カラム名の指定が必要です。$rowだけで表示させるとArrayと表示されるだけになります。

指定カラムにある同じ値の数を数える

SELECT shitei_column, count(shitei_column) FROM table_name GROUP BY shitei_column

指定したカラムに保存されている同じ値の数をカウントすること出来ます。

例えば、性別カラムを指定すれば、男性・女性それぞれの数がカウントされることになります。fetchALL()で配列取得、またはwhileで表示してデータを取り出します。