PHPでSQLite(sqlite3)を使ってみる
レンタルサーバー上で動くPHP言語から、SQLite(sqlite3)というデータベースの仕組を使ってみます。表現が不適切かもしれませんが、この様なデータベースはエクセルの表の様なデータ構造で、SQLという言語を使って、高速にデータ検索・並び替え・集計・演算などといった処理を行えるものです。
今までのESPR DEVELOPERの確認でも何度かPHPプログラムを使用し、テキストファイルへの書き込み/読み出しを行っていますが、近いうちにESPR DEVELOPERを使って連続的データ測定を行ってみたいと思っていますので、その時のデータ書き込みにSQLiteを使うことを考えています。
私が使っているレンタルサーバーのロリポップでは、MySQLとSQLiteが使える様ですが、ライトプランのMySQLは“1”となっていて、WordPressで使っているので、とりあえずSQLiteを試すことにしました。(同じMySQLにテーブル等は作成出来るのかもしれませんが、今回はリスク回避することにします。)
テストプログラム概要
今回はテストプログラムとして、商品と価格を登録する簡単な仕組を作ってみました。リンクを開くと商品と価格を登録する画面に移動します。下記の図はリンクを開いた状態の様子です。
商品名と価格を入力し、“追加登録”ボタンをクリックするとデータが登録され、下のリストに入力内容が追加表示されます。最上位置の“全データ消去”ボタンをクリックするとリストの全データが消去されます。“テーブル抹消”ボタンはテーブルごと抹消しますので、真ん中の“テーブル作成”ボタンを押して、再度テーブルを作成しないとデータの追加登録は出来ません。
簡単な処理ですが、一応このテストプログラム内に、テーブル作成/抹消,データ追加/消去,データ検索などSQLiteを扱う際の基本となる処理は含んでいます。
プログラムコード
プログラムコードを示します。下記プログラムは、html表記の中にPHPプログラムを含めています。” <?php ” ~ ” ?> “で囲まれる範囲(行番38~125)が、PHP言語で書かれた部分です。
htmlの中の行番11~36が、商品名や金額を設定するテキスト入力フォームやボタンを形成する部分です。〈input〉タグの “type”が“submit”になっている箇所は、送信ボタンになっており、クリックすると“value” で設定された値(テキスト入力の場合は、入力値)を〈form〉タグの“action”でURL指定されたプログラムに送信します。ここでは、“action”指定はないので、自身のプログラムにPOST送信しています。(送信方法は、“methode”で指定)
自プログラムにPOST送信されたデータは、PHP側で受け取ります。行番40~44では、受信データである $_POST[‘フォーム部品の名前(“name”で設定)’] をそれぞれ変数に格納しています。
受け取った値によって、行番46~103で追加,データ抹消,テーブル抹消,テーブル作成などのそれぞれの処理を行います。これらの処理の後、行番108~124で登録されている全てのデータをブラウザー上に出力しています。
<!DOCTYPE html> <html lang="ja"> <head> <meta charset="utf-8"> <title>SQLiteテスト</title> </head> <body> <TABLE><TR> <!-- テーブル抹消 --> <TD><form method="POST" action=""> <input type="submit" name="dtbl" value="テーブル抹消"> </form></TD> <!-- テーブル追加 --> <TD><form method="POST" action=""> <input type="submit" name="ctbl" value="テーブル作成"> </form></TD> <!-- 全データ消去 --> <TD><form method="POST" action=""> <input type="submit" name="delf" value="全データ消去"> </form></TD> </TR></TABLE> <br> <!-- 商品追加登録 --> <form method="POST" action=""> <label>商品 : </label> <input type="text" name="name"><br> <label>価格 : </label> <input type="text" name="pric"><br> <input type="submit" value="追加登録"> </form> <?php $nam = "" ; $kkk = "" ; $kil = "" ; $nam = htmlspecialchars($_POST["name"]) ; $kkk = htmlspecialchars($_POST["pric"]) ; $kil = htmlspecialchars($_POST["delf"]) ; $tbl = htmlspecialchars($_POST["ctbl"]) ; $drp = htmlspecialchars($_POST["dtbl"]) ; if($nam != "" && $kkk != ""){ // 商品追加登録 $db = null ; $sql = null ; $res = null; $db = new SQLite3("./sq/list_price.sqlite3"); $sql = 'INSERT INTO lst_prc( g_nam , g_prc ) VALUES ("'.$nam.'","'.$kkk.'")'; $res = $db->exec($sql); echo "<br>"; echo "商品を追加登録しました。"; }elseif($kil != ""){ // 全データ抹消する $db = null ; $sql = null ; $res = null; $db = new SQLite3("./sq/list_price.sqlite3"); $sql = 'DELETE FROM lst_prc' ; $res = $db->query($sql); echo "<br>"; echo "全データ抹消しました。"; }elseif($drp != ""){ // テーブル抹消する $db = null ; $sql = null ; $res = null; $db = new SQLite3("./sq/list_price.sqlite3"); $sql = 'DROP TABLE lst_prc' ; $res = $db->query($sql); echo "<br>"; echo "商品リストテーブルを抹消しました。"; }elseif($tbl != ""){ // 商品の価格リスト用のテーブルを作成 $db = null ; $sql = null ; $res = null; $db = new SQLite3("./sq/list_price.sqlite3"); // テーブル有無確認 $sql = 'SELECT count(*) FROM sqlite_master WHERE type="table" AND name="lst_prc"'; // テーブルがない場合作成 if(!$db->querySingle($sql)){ $sql = "CREATE TABLE lst_prc( id INTEGER PRIMARY KEY, g_nam TEXT NOT NULL, g_prc INTEGER NOT NULL, created_datetime TIMESTAMP DEFAULT(datetime(CURRENT_TIMESTAMP,'localtime')))"; $res = $db->exec($sql); echo "<br>"; echo "商品リストテーブルを作成しました。"; }else{ echo "<br>"; echo "商品リストテーブルは既にあります。"; } }else{ echo "<br>"; echo "未入力項目があります。"; } echo "<br>"; echo "<br>"; // 登録データを全て表示する $db = null;$sql = null;$res = null; $db = new SQLite3("./sq/list_price.sqlite3"); $sql = 'SELECT * FROM lst_prc'; $res = $db->query($sql); while($row = $res->fetchArray(SQLITE3_NUM)) { for($i = 0 ; $i < count($row); $i++){ echo $row [$i]; if($i < count($row) - 1){ echo " , " ; } } echo "<br>"; } ?> </body> </html>
SQLについて
データ検索(プログラムの行番108~113)を例に、SQLite処理概要について簡単に流れを説明します。下記の通り、いずれの処理も大凡のフローはほぼ同じで、①変数初期化,②データベースファイル接続,③SQL設定,④SQL実行 の様になっています。
データ検索の場合には、検索結果を1件づつ表示するなどの処理を行います。
// 登録データを表示する ① $db = null;$sql = null;$res = null; ② $db = new SQLite3(“./sq/list_price.sqlite3”); ③ $sql = ‘SELECT * FROM lst_prc’; ④ $res = $db->query($sql); 〈検索の場合は、検索結果の処理〉 |
実際のデータベースに対する処理内容は、SQLで設定します。
下記は、全て接続したデータベースファイルの「lst_prc」というテーブルに対して①検索,②挿入(追加),③抹消 する処理になります。検索や抹消では、“where ”句を用いて検索範囲・抹消範囲を限定することなどもできます。
// SQL文の例 ① $sql = ‘SELECT * FROM lst_prc’; ② $sql = ‘INSERT INTO lst_prc( g_nam , g_prc ) VALUES (“‘.$nam.'”,”‘.$kkk.'”)’; ③ $sql = ‘DELETE FROM lst_prc’ ; |
今回のまとめ
今回はSQLITEを使ってみました。次回はESPR DEVELOPERで取得したデータをSQLITEに登録していく処理を行ってみたいと思っています。