TODOリストをDB化して,SQLに触れてみよう

こちら第9回「プログラマー養成講座」になります！
目次はこちらからご参照下さい！
今回は全部、師匠のお話になります！

DBとは

DBとはデータベースのことで、データベースとは、データの集合体のことです。概念的な話をすると、Excelもデータベースの一種ですし、OfficeではMsAccessというデータベースのソフトウェアもあります。

用語

DB(デービー、データベース)

データベースという言葉にはいくつか意味があります。ソフトウェアやサービスのことをさしたり、テーブルの集合体の1単位だったり、ただのテーブルのことをデータベースということもあります。

TABLE(テーブル)

決められた項目を、複数格納することのできる場所のことを言います。前回のCSVファイルは１つのテーブルと言えます。

RECORD(レコード)

テーブル内の1個のデータのことをレコードといいます。

COLUMN(カラム)

テーブル内のデータ項目の1個1個のことをカラムといいます。前回のCSVファイルだと「項目名」「チェック」「登録日時」の３つのカラムで出来ています。

SQL言語(エス・キュー・エルげんご)

データベースからデータを抽出、挿入、更新、削除などをするプログラム言語をSQL言語といいます。

テーブル1個であれば、データ読込は単純なプログラミングで済みますが、
テーブルが2個、3個となってくると、データの読み出しをするのに長いプログラミングが必要になってきます。それらを簡単にできるようにするものがDBで、それを操作するプログラム言語をSQLと言います。

また、CSVファイルの場合、データの更新に長めのプログラミングが必要となりますが、SQL言語なら1行で書けます。

またＳＱＬはＰＨＰ以外のプログラム言語でも同じプログラムになります。例えば「Select * from TODOLIST」というＳＱＬ言語は、JavaでもPythonでも同じ書き方になります。

SQLite(えすきゅーらいと)について

今回使うデータベースサービスはSQLiteといいます。SQLiteは軽量なデータベースです。PHPで扱うデータベースは「mariaDB」「MySQL」「SQLite」などが主になります。

プログラム実装

まず最初に前回作った３つのPHPプログラムをコピーして、わかりやすいようにファイル名の最初に「sq_」をつけましょう。
sq_todo.php
sq_todo-entry.php
sq_todo-check.php

初期処理

最初にやることは、
・データベースファイルを探し、なければ作成
・データベース作成後に、テーブルを作成
を実装します。

sq_todo.phpの最初の方に書きます。テーブル作成は「create」を使用します。

//データベースがない場合の初期処理
if ( file_exists('todolist.db') == false )
{
	//DB接続
	$db = new SQLite3('todolist.db');

	//テーブル作成
	$db->exec("create table TODOLIST (autoid integer primary key autoincrement , todoname varchar(30) , checkflag int, entrydate varchar(20))");

	//DB切断
	$db->close();
}

データ挿入

sq_todo-entry.phpを編集します。やってることはCSV版とあまり変わりません。データ挿入には「insert」を使います。

<?PHP
//DB接続
$db = new SQLite3('todolist.db');

//SQLセット
$stmt = $db->prepare("insert into TODOLIST (todoname , checkflag , entrydate) values (:todoname , :checkflag , :entrydate);");

//値のセット
$stmt->bindValue( ':todoname'  , $_POST['todo-name']);
$stmt->bindValue( ':checkflag' , 2);
$stmt->bindValue( ':entrydate' , date('Y-m-d H:i:s') );

//実行
$res = $stmt->execute();

//DB切断
$db = null;
?>
登録しました<br />
<a href="sq_todo.php">戻る</a>

データ表示

sq_todo.phpのデータ表示個所をまるっと書き換えます。データ表示には「select」を利用します。

<form method="POST" action="./sq_todo-check.php">
<?php
//DB接続
$db = new SQLite3('todolist.db');

//SQL発行／データ読出
$result = $db->query('select * from TODOLIST;');

//データ表示
while ($row = $result->fetchArray())
{
	if ( $row['checkflag'] != 1 )
	{
		print '<div><input type="submit" name="chk['.$row['autoid'].']" value="no" />'.$row['todoname'].'　登録日時:'.$row['entrydate'].'</div>'."\n";
	}
	else
	{
		print '<div>yes'.$row['todoname'].'　登録日時:'.$row['entrydate'].'</div>'."\n";
	}
}
?>
</form>

データ更新

sq_todo-check.phpの編集をします。データ更新のプログラミングが一番変わりますかね。CSV版は全データを読みだした後に、該当するデータ以外も変数に格納して、CSVファイルを全部上書きするようなロジックです。SQL版は「update」を使い、「where」で更新対象を指定することで簡単にできます。

<?PHP
//更新する行数を変数に格納
$upd_num = '';
foreach($_POST['chk'] as $num => $value)
{
	if ( $value == 'no' )
	{
		$upd_num = $num;
	}
}

//更新する行数が記録されていたら更新処理を開始する
if ( $upd_num !== '' )
{
	//DB接続
	$db = new SQLite3('todolist.db');

	//SQLセット
	$stmt = $db->prepare("update TODOLIST set checkflag = :checkflag where autoid = :autoid;");

	//値のセット
	$stmt->bindValue( ':checkflag' , 1);
	$stmt->bindValue( ':autoid'    , $upd_num );

	//実行
	$res = $stmt->execute();
	
	//DB切断
	$db = null;
}
?>
更新しました<br />
<a href="sq_todo.php">戻る</a>

終わりに

どうだったでしょうか。これだけ見ると、言われた通りに書き換えただけなので、何が変わったかよくわからないかもしれません。ですがSQLについては勉強しておくと、いろんなデータベースで活用できます。
Oracle、DB2、MySQL、mariaDB、SQLServer、PosgreSQLなど、データベースサービスはたくさんありますが全部SQLで操作することが可能です。
100件程度のCSVファイルだけなら必要ありませんが、データベースは数百万件のビッグデータも短い時間で操作することができます。
また扱うテーブルも１０～２０と増えてくると、CSVファイルの操作では複雑なプログラムになってきます。プログラム自身もPHPだけではなく、他から操作することもあります。そういった環境においてSQLが使えることは有利になってきますので、勉強して慣れておくことをお勧めします。