MySQL(さくら)のデータベースをブラウザで表示～今日の課題～

★今日の課題★
　さくらインターネットで契約中のレンタルサーバにあるMySQLデータベースを活用してブラウザからデータを見られるようにする。

データベース

　データベースとは、データが格納されている書庫や倉庫のようなものです。単にデータが存在するだけなので、そこからデータを引出さなければ格納されている情報の価値は潜在したままです。

　Excelの表も遠からずデータベースの一種です。
　取り扱えるデータは縦と横の二次元的なマトリクス、そのマトリクスはSheetで別々の物を作る事ができ、Book単位でまとめられます。

　同じMicrosoft製品ではAccessというソフトがデータベース管理ソフトになっています。
　紙媒体で昔からある伝票を積み重ねて帳簿とするようなデータ管理ができます。
　1枚1枚の伝票には取引先や商品などの情報が記録され、それが1カ月単位や客先単位でまとめられ帳簿となります。1枚の伝票が出荷台帳や売上台帳などさまざな帳簿とリレーションする事になりますが、これを昔は手書きで行っていたと思うと、ものすごい労力です。Accessを使えば、1枚の伝票に記録するだけで、さまざまな帳簿にもデータが反映されます。

データのリレーション

　データベース（database）では、データ（data）を関連付け（relation）してを活用していきます。

　relation（リレーション）とは関連や結びつきなどの意味があります。

　データベースでは商品や取引先などをコード化し、伝票上で結び付けるとともに、一意的なデータ管理をします。

　缶コーヒーを仕入れれば『缶コーヒー』だけではわからないので付加情報が必要になります。『ジョージアエメラルドマウンテンブレンド』と入力しても容量がわかりません。
　こうしたときに役立つのがバーコード（JANコード）です。伝票にはコードを入力しておくと商品を取り違えにくくなります。
　これが一意的な入力方法の１つです。

　商品データベースにJANコードと商品名を登録しておくと、伝票にJANコードが入力されたときに商品名を呼び出すことができます。

　この商品が入荷されたときには在庫台帳に記録しますが、JANコードと数量を記録しておけば、あとで『何の商品だっけ？』と思っても、商品データベースに照合することで商品名がわかります。
　これがリレーションの方法の１つです。

　商品が売れれば『在庫が減った』『売上金を受け取った』という記録が行われますが、レジでいちいち在庫台帳と売上台帳に記帳している時間は無いので、レジ専用の伝票を使います。
　実際のオペレーションではバーコードを走査して、お金を受け取って商品を渡すという行為になりますが、裏では何時何分に何円で何を何個売ったという伝票が起票されています。
　細かい事を言えば商品のJANコード以外にもレジの機械番号やレジ係の個人番号、金種なども記録されます。

情報の見せ方

　データベースは、どのようにしてデータを集めて管理するかという事が先行しますが、データは二次利用されなければ価値が生まれません。

　入出庫や売上については業務で活用されるので、その時点で一度は利用されますが、実際には『何時ごろに売れている』『まとめ買いが多い』などの解析にも用いられます。

　今回、いくつかのデータをウェブサイトで見やすくしたいと思ったのですが、どうやったら情報の羅列であるデータベースを見やすくできるのか、そもそもデータベースをウェブサイトで公開できるのか、といった壁に当たりました。

MySQL

　契約中の『さくらのレンタルサーバ』では『MySQL』というデータベースが追加料金なしに設置できます。

　このMySQLがどういうものかわかりませんでしたが『SQL』という名前を冠しているので、おそらくこちらから命令したとおりにリレーショナルデータベースを操作できるのであろうと思いました。

　サーバ上に置くことができるデータベースがMySQLしか無かったので、この一択で話を進めることになりました。

サーバコントロールパネル

　『さくらのレンタルサーバ』でデータベースを管理するためには『サーバコントロールパネル』にログインする必要があります。

さくらのレンタルサーバ：サーバコントロールパネル

　ログインすると下図のような画面になります。

　メニューにある『データベース』を選ぶと、データベース管理画面に遷移します。遷移先でデータベースを作成します。

　以後のデータベース管理は『phpMyAdmin』というウェブツールを使います。

テーブル作成

　データベースの構築の第一歩目は名前を付けるところからですが、実質的な作業は『テーブル』（table）を作る事から始まります。

　テーブルとは、Excelでいうsheetのような意味を持ちます。データのまとまりです。

　例えば『商品』『社員』『在庫』などを作ります。名称は日本語で『tbl_商品』でも良いのですが『tbl_staff』『tbl_zaiko』など半角英数にしておいた方が不具合が出づらいと思います。

カラムの追加

　カラム（column）はExcelでいうセルの列に類似します。

　テーブルは複数のカラムで構成されます。

　識別・重複防止用にユニークなカラムが１つ設定されるのが一般的です。

　社員テーブルであれば社員IDで識別し、氏名や職種、連絡先などのカラムが合わさって１つのテーブルを構成します。

　MySQLでは『phpMyAdmin』という管理ツールを使いますが、その中でカラムを追加する事が出来ます。
　『構造』タブの中に『個のカラムを追加する』という枠がありますので、任意の数を指定します。下図では17個を『class_code』の後へ追加する指示を出しています。

インポート

　作られたテーブル（データベース）にデータを入力するのは手打ちでも構いませんが、外からインポートすることもできます。

　今回はExcelで作ったマスタをCSVファイル（カンマ区切り）に変換して流し込みました。
　Excelの操作は『名前を付けて保存』からファイル種別をCSVにするだけです。
　デフォルトでは１行目も取り込まれるのでご注意ください。

　phpMyAdminではインポートタブからファイルを選択し、フォーマットを『CSV』にして『実行』を押します。

公開用ファイル

　今回はインターネットブラウザで見られるように、ホームページ上に公開することにしました。

　データビュー専用ページを設ける方法が早そうでしたので、そちらにチャレンジしました。

　『test.php』というファイルをパソコン上に作り『メモ帳』ソフトでタグを入力する方法でページファイルを制作します。
　それを『さくらのレンタルサーバ』の『サーバコントロールパネル』から『ファイルマネージャ』を使って原始的にウェブサーバへアップロードする方法としました。

　冒頭の記述はデータベースへの接続になります。
　『$server』にはphpMyAdminに接続する際に表示されているものを入力します。おそらく４桁の数字があると思います。
　『$userName』『$password』にはMySQLのユーザー名、パスワードが入ります。

　『$dbName』はデータベース名です。データベース作成時に任意で付けた名前です。

$server = “mysql●●●●.db.sakura.ne.jp”;
$userName = “●●●●●●”;
$password = “●●●●●●”;
$dbName = “●●●●●●”;

<?php
server = "mysql●●●●●.db.sakura.ne.jp";
$userName = "●●●●●";
$password = "●●●●●";
$dbName = "●●●●●";

$mysqli = new mysqli($server, $userName, $password, $dbName);

if ($mysqli->connect_error){
    echo $mysqli->connect_error;
    exit();
}else{
    $mysqli->set_charset("utf-8");
}

$sql = "SELECT * FROM ●●●●● ORDER BY ●●●●● Asc";

$result = $mysqli -> query($sql);

//クエリー失敗
if(!$result) {
    echo $mysqli->error;
    exit();
}

//レコード件数
$row_count = $result->num_rows;

//連想配列で取得
while($row = $result->fetch_array(MYSQLI_ASSOC)){
    $rows[] = $row;
}

//結果セットを解放
$result->free();

// データベース切断
$mysqli->close();

?>

　後半では表示するデータ（カラム）と表示名を指定します。
　下記コードで表示名を順番に指定していきます。黒丸の所は任意の文字列です。日本語でも大丈夫です。

　下記コードではページのどのカラムを表示するか指定します。
　黒丸のところには、MySQLのカラム名を入力します。任意で『td』で囲まれた行を増やしていきます。

<?php
foreach($rows as $row){
?>
<tr>
<td><?php echo $row[‘●●●●”];,?></td>
</tr>
<?php
]
?>

　以後のデータベース管理は『phpMyAdmin』というウェブツールを使います。


<!DOCTYPE html>
<html>
<head>
<title>●●●●●</title>
<meta charset="utf-8">
</head>
<body>

<div style="text-align: center;">
<a href="/db"><img src="●●●●●.png" alt="●●●●●" title="●●●●●"></a>
</div>

<hr>
<br>

<h1>●●●●●</h1>

<div style="text-align: center;">
<a href="https://www.●●●●●" target="_blank" style="text-decoration:none;">●●●●●</a>
</div>

レコード件数：<?php echo $row_count; ?>

<table border='1'>
<tr>
<th>●●●●●</th>
<th>●●●●●</th>
<th>●●●●●</th>
</tr>

<?php
foreach($rows as $row){
?>
<tr>
<td><?php echo $row['●●●●●']; ?></td>
<td align="center"><?php echo $row['●●●●●']; ?></td>
<td align="center"><a href="●●●●●.php?id=<?php echo $row['●●●●●']; ?>" target="_blank"><?php echo $row['●●●●●']; ?></a>
</td>
</tr>
<?php
}
?>

</table>

</body>
</html>

test.PHPファイルのサンプル（zipファイル）

公開

　『さくらのレンタルサーバ』の『サーバコントロールパネル』を開きます。その中にある『ファイルマネージャ』を開きます。

　ウェブサイト用に用意してあるフォルダ（www）に、先ほどの『test.php』をアップロードします。

　ブラウザを開いて、先ほどのファイルのアドレスを入力します。
　例えば『http://www.●●●.sakura.ne.jp/test.php』のようなアドレスです。黒丸には契約時に決めたIDが入ると思います。

DBデータメインテナンス

　データベースの構造は一度制作すれば終わりですが、中身のデータは更新していく必要があります。

　当方ではデータ管理をMicrosoft Accessで行っているため、このデータをMySQLに書き込むことになります。

　MySQLでインポートに対応しているファイルはCSVやSQLなど限られたファイル形式です。AccessのMDBファイルやExcelのXLSXファイルなどは対象外です。

　一番手軽なのがCSVファイルです。Excelなどでも標準的にエクスポートできます。

Access ⇒ CSVエクスポート

　Accessでは『外部データ』のタブ内に『エクスポート』という項目が用意されています。

　今回はCSVファイルなので『テキストファイル』を選びますが、『Excel』を出力してからCSVに変換する方法でも同じ結果は得られます。

　エクスポートのテキストファイルを選ぶと下図の『データのエクスポート先の選択』画面が現れます。

　ファイルの拡張子が『.txt』となっているところを、『.csv』に書き換えて『OK』を押します。

　ウィザードが現れて細かな決定ができますが、特に変更することなく次々と先へ進んでファイルを出力（エクスポート）します。

　私の場合はここでもうひと手間加えています。
　出力（エクスポート）したCSVファイルをExcelで開いて、中身は何も触らずに『名前を付けて保存』を選び、ファイル形式を『CSV UTF-8 (コンマ区切り)』に指定して保存し直しています。
　phpMyAdminのインポートでは『ファイルの文字セット』がデフォルトで『UTF-8』になっているようですので、そちらに合わせています。
　他の作業でもJISでは上手くいかずにUTF-8にすると上手くいくことが多いので、このひと手間を加えています。

　だいたいの場合、Excelから操作で問題なかったのですが、ときどき上手くいかない事がありますので、お勧めするのは『メモ帳』を使う方法です。

　データベースの主キーとなるID等をゼロ詰めのコード、例えば１番なら『0001』としている場合、Excelで操作すると数字の前のゼロを消してしまいます。

　消されるとコードが崩れてしまい、データベースとして使えない場合があるので、勝手に変換する機能を持たない『メモ帳』を使います。

　メモ帳で『名前を付けて保存』を選び、ダイアログボックスの下方にある『文字コード』を『UTF-8』にして保存します。

　ファイル形式はドロップダウンメニューから選べないので、手入力でファイル名の末尾を『.csv』にします。

　もし、CSVにせずTXTで保存してしまった場合には、エクスプローラーで編集します。
　普段は表示されていないかもしれませんが、エクスプローラーの『表示』タブを開くと『ファイル名の拡張子』というチェックボックスがあるので、そこにチェックを入れると、エクスプローラー上のファイル名に拡張子が表示されます。
　先ほどのファイルは『.txt』という拡張子で終わっていると思いますので、そこを『.csv』に変えれば完了です。

　まぁまぁ面倒な作業なので、メモ帳で完結しておいた方が良いと思います。