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

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指定します。あとは実行するだけ。

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

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

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

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

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

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

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

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

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

$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 cunt_Pv = cunt_Pv + 1'
);
$INSERT_query->bindValue(':Name', $Name, PDO::PARAM_STR);
$INSERT_query->bindValue(':PageName', $PageName, PDO::PARAM_STR);
$INSERT_query->execute();

このINSERT文はめちゃめちゃ効率がいいです!同じ行があればスルーして、重複があれば指定のカラムだけUPDATEができる。これはほんとに便利!

でも注意点が1つ。

テーブル側に「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!

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

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

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

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

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

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

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

$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に配列として取得したデータが入れられます。

全ての取得したデータを配列で返すので、データが膨大にある場合は処理の負担が増大します。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で表示してデータを取り出します。

投稿日: 2017/10/15
最終更新日: 2017/11/01
 
筆者のご紹介
角政典@moreiic
真性のお家大好きフリーランスです。プログラムよりご飯の方が断然好き!博多出身のデブデザイナー。インドアだけど遊んでくれる人募集中!
よく検索されてる記事