データベースを活用したサイト運営(一覧表と個別の表でデータを一元管理)

記事上部注釈
弊サイトでは実際に利用したサービスなどをアフィリエイトリンク付きで紹介することがあります

サイトを運営・管理するうえで、全体の表と個別の表で使用するデータを統一したいこともありましょう。ここでは、私の実体験をベースに合理的なやりかたを提案します。

写真1. 撮り鉄スポットの管理にも生かした

シチュエーションと概要

  • 想定シチュエーション:特定の地域のラーメンサイトの主要メニューの価格
  • 方法概要:MySQLなどのデータベースシステムを活用し、それぞれの形式に出力

詳細なやりかたは以下で述べます。

※PHPの基本は以下の書籍を活用するとわかりやすいと思います。

マイナビ出版 たにぐち まこと
松浦 健一郎、司 ゆき(共著) SBクリエイティブ

やりたいことの概要

では、何をやりたいと想定されている記事なのか、ということを明確にするために、ここであえて具体的な例を示しましょう。

まず、○○駅近くのラーメン店を紹介するサイトを作るとしましょう。

図1. トップページの様子

トップページのイメージを示しました(図1)。ここでは、ラーメンを4つのパターンに分類し、それぞれのラーメン店に該当する価格をまとめました。「こんな乱暴な表はどうかしている」「価格以外にも書くことがあるだろう」というおしかりがあるかもしれませんが、例ということでご容赦ください。

図2. 個別ページの様子

個別のラーメン店のレビューページもでっち上げてみました(図2)。図1に示した特定のラーメン店について詳細に書いている設定です。

図3. 数字が連動していると、管理しやすい!

ここで2つの表を出しましたが、トップページに記した価格と個別ページに出した価格の数字が連動していると、サイト運営的には楽です(図1)。今回の記事はこの方法論を考えるというものです。具体的には、税金の値上げがあり、多くの店舗で値上げするという場面の更新作業を考えるとわかりやすいでしょう。

データを連動させる方法

補足

図4. 行と列の覚えかた

以下、行と列という概念が登場します。縦が列、横が行と呼ばれます。これが覚えにくい場合は、行と列と漢字を書き、行は漢字のなかに横の線が2本あり、列という漢字には縦の線が2本あることを思い出しながら書くと覚えやすいです(図4)。

Excelの感覚では、表を連結させれば良さそうです。しかし、Webサイトではそのような機能はありません。では、あきらめるのかというと、そのようなことはありません。

具体的な方法は後で詳細に記しますが、やりかたのあらましは、データベースという中身表という枠組み別に管理することです。

手順1:データベースを構築

まず、データという中身を入れるデータベースを作成します。

図5. データベースの例

データベースの例を示しました(図5)。ここで最初の例とデータの中身が変わっていますが、弊サイトの見本を見せたということでご容赦ください。

私はMySQLを使用していますが、別のデータベースシステムでも構いません。その際は、適宜読み替えてください。データベース(Excelでいう1つの表)を作成する際、データベースのそれぞれの形式を決めることです。

図6. MySQLの構造

そう難しい内容ではありません(図6)。Excelでいうどの列にどのようなデータを入れるかを考え、それに従って自分でデータ形式を定義するだけです。数字しか入れない箇所はINT、文字列(地名、人名など)はTEXTにする程度の区別で大丈夫(と思います)。例えば、ラーメンの価格を入れる場合はINTで良いでしょうし、最混雑区間を入れる場合はTEXTが良いでしょう。

ここで重要なことは自動採番のidを決めておき、データベースを管理しやすくすることです。自動採番とは、データを追加する際にデータの番号を自動で付与する機能です。

データベースの大元はMySQLではなく、Excelで作成したほうがやりやすいと思います。では、ExcelからMySQLにデータを移行するにはどうしたら良いでしょうか。

図7. Excelの状態

Excelの状態を示しました(図7)。それぞれの列に必要な情報が入っています。山手線(外回り)であれば、路線名はB列、最混雑区間はD列、混雑率はE列に入っているという具合です。

  1. ="INSERT INTO データベース名(路線,混雑率) VALUES('"&C4&"',"&F4&");"


コード1. Excelに貼り付ける式

Excelにはこのような式を貼り付けます(コード1)。ここでは「データベース名」という名前のデータベースにデータを入れるという想定です。データを入れたいExcelの列の名前(MySQLでいうカラムの名前)と、Excelの列のデータを対応させます。今回の場合、路線名には山手線(外回り)が該当し、混雑率には94が該当します。レコードは複数ありますが、Excelでいう1列ぶんをコピーすれば、レコード(Excelでいう列)が50あろうが100あろうが怖いものはありません。

ここで注意することは、数字(となる箇所)は"94"のように囲み、文字(となる箇所)は'"山手線(外回り)"'のように文字に該当する箇所のほうが、コーテーションマークが増えることです。

とにかく、何らかの方法でデータベースを完成させます。例えば、東京都の鉄道路線の2021年度の混雑データであれば、tokyo2021という名前のデータベースとします。

データベースは形式が統一されているとやりやすいですから、同様のデータベースは同じ形式で作成すると良いです。MySQLの操作画面で操作できます。

図8. 構造のコピー

新しく作成したいデータベース名を指定し、「構造のみ」にチェックして実行します(図8)。場合によってはデータの中身をコピーすることも良いでしょう。ここで、id番号は前のデータベースと連動することが重要です。

データベースが1つであれ、2つ以上であれ、とりあえず必要なデータベースを作りきります。

手順2:個別ページの表を作成する

次は、HTMLの知識が必要な段階です。山手線、京浜東北線の混雑率をまとめた表を考えましょう。以下の表を作る感覚です。

表1. 山手線と京浜東北線の混雑率データ(中身は架空です)

路線 混雑率
山手線 100%
京浜東北線 120%

表の作成方法をご承知の人は、次の章を飛ばしてもかまいません。

復習:表の作成方法

まず、表の作成方法を復習します。

表1(再掲). 山手線と京浜東北線の混雑率データ(中身は架空です)

路線 混雑率
山手線 100%
京浜東北線 120%

適当な表を作ってみました(表1)。このソースコードを示します(コード2)。

  1. <table><tr><th>路線</th><th>混雑率</th></tr><tr><td>山手線</td><td>100%</td></tr><tr><td>京浜東北線</td><td>120%</td></tr></table>


コード2. 表の基本的なコード

まず、以下のタグで表のスタートと終わりを示します。

  1. <table></table>


表は行ごとに作成するイメージです。1行作り、また1行作り…。その繰り返しです。

  1. <tr></tr>


trタグで囲まれた箇所で行の開始と終了を指示します。

  1. <tr><th></th><th></th></tr>


この中にthタグで囲うと1行の中で1列、また1列と増えます。今回の例は2列ですので、thタグで囲うものは2つです。thタグとtrタグがありますが、タイトルのようなものはthタグ、具体的な中身はtdタグで囲みます。1列のなかでthタグとtdタグを混用してもかまいません。

PHPでテーブルを作成する

表を作成するのはHTMLで可能ですが、今回はあえてPHPを使います。PHPの特徴は臨機応変に出力する中身を変えられることです。

まず、データベースの特定のidの情報を呼び出しましょう。id=1の情報を取りだすイメージです(コード3)。

  1. <?php
  2. //データベース接続のため省略
  3. echo '<table>';
  4. echo'<tr><th>最混雑区間</th><td>';
  5. foreach ($dbh->query('select * from データベース名 where id=1') as $row){
  6. echo $row['区間'];
  7.     }
  8. echo'</td></tr>';
  9. echo'<tr><th>混雑率</th><td>';
  10. foreach ($dbh->query('select * from データベース名 where id=1') as $row){
  11. echo $row['混雑率'];
  12.     }
  13. echo'%</td></td></tr>';
  14. echo'</table>';
  15. ?>


コード3. id=1の表を作成

このような命令を記述すると、データベース名のid=1の対応するデータが自動で出力されます。これで個別ページの表は完成です。

表を共通のフォーマットにする

これで一応完成なのですが、これが面倒なのは、各個別ページの表を別個に管理することです。例えば、毎年61の表について、データベースを更新するときに全部の表を更新せねばなりません。これでは不便です。

ここで先ほどの表に小細工を加えます(コード4)。

  1. <?php
  2. echo '<table>';
  3. echo'<tr><th>最混雑区間</th><td>';
  4. foreach ($dbh->query('select * from データベース名 where id='.$a.'') as $row){
  5. echo $row['区間'];
  6.     }
  7. echo'</td></tr>';
  8. echo'<tr><th>混雑率</th><td>';
  9. foreach ($dbh->query('select * from データベース名 where id='.$a.'') as $row){
  10. echo $row['混雑率'];
  11.     }
  12. echo'%</td></td></tr>';
  13. echo'</table>';
  14. ?>


コード4. 個別の表を個別のidから変数のidに変更した

先ほどのid=1の箇所に変数$aを指定しました。変数の記号は必ずしもaである必要はありませんが、ここではaとしました。統一さえされていれば、何でも構いません。ここで''で囲まれている変数を入れるので、.$a.とドットで囲むことを忘れてはいけません。

変数の何が便利かというと、使いまわしがきくことです。データベースへの接続を省略しました。今後、このフォーマット単独で使うことがないからです。

このファイルをbase.phpとでも保存しましょう。

idごとに表を作成する

山手線のidは1、京浜東北線のidは2、…と自分のなかで決め(そしてそのidは先ほど作成したデータベースに反映されているはずです)、それらを呼び出すPHPを作成します(コード5)。

  1. <?php
  2. //データベースへの接続のため省略
  3. $a=1;
  4. require('base.php');
  5. ?>


コード5. id=1の表作成用のファイル

これを1つのファイルとして保存します。同様に$a=2、3、…と別々のファイルを作成します。$aの値をここで定義しますから、共通のbase.phpはただの変数で良かったのです。このファイルは基本的にメンテナンスの対象ではありません。

これの何がラクかというと、データベースの更新作業が反映されるファイルがbase.phpの1つだけ(後で述べる全体の表もそうですが)で済むのです。今回私は路線別の混雑率の表を61個作成しましたが、61個の更新か1つの更新(※)かというのは労力が桁違いです。この発想に至った私は自分を天才と評価しました!

※今回、私は路線別の実態に合わせて4つのフォーマットを作成しています。そのため、実際には1個の更新ではなく、4個の更新です。

全体の表を作成する

個別の表を作成したら、今度は全体の表を作成します。さきの個別ページの表作成でも活用しましたが、foreach文を活用します。この文は配列の全ての要素を取り出すまで繰り返しの処理をするというものです。

ソースコードを例示してみましょう(コード6)。

  1. <?php
  2. echo '<table>';
  3. echo'<tr><th>路線名</th><th>最混雑区間</th><th>混雑率</th>';
  4. foreach ($dbh->query('select * from データベース名') as $row){
  5. echo '<tr><td>';//行の開始を<tr>タグで示す
  6. echo $row['路線'];
  7. echo '<td/><td>';
  8. echo $row['区間'];
  9. echo '<td/><td>';
  10. echo'$row['混雑率']'
  11. echo'</td></tr>';//行の終了を</tr>で示す
  12. }//ここで繰り返し処理は終了なので}で閉じる
  13. echo'</table>';//テーブルの終了は繰り返しの必要はないのでforeach文の外
  14. ?>


コード6. 全体の表のコード

tableタグの開始と終了は繰り返し処理が生じません。また、表の一番上の行も繰り返し処理が生じません。そのため、繰り返し処理の中に入れません。

表形式にしたときにあるidで1行使いますから、繰り返し処理の箇所にtdタグの開始と終了を入れます。

全体の表作成の高度なテクニック

ここで2つの疑問が思い浮かびます。1つは、表の左端に大きな欄を書きたい場合、idの順番以外で並べたい場合です。それぞれの場合の解決策を簡単に紹介しましょう。

要望1.左に大きな欄を書きたい場合

左に結合したセルを加えたい場合、どうすれば良いのでしょうか。例えば、先の表(表1)で鉄道会社を追加(表2)したい場合です。

表1(再掲). もともとの表

路線 混雑率
山手線 100%
京浜東北線 120%

表2. 鉄道会社を加えた表

会社 路線 混雑率
JR 山手線 100%
京浜東北線 120%

この場合、結合する一番上の行で結合を書き、2つ目の行からは1行抜いた状態でテーブルを作成します(コード7)。

  1. <table><tr><th>会社</th><th>路線</th><th>混雑率</th></tr>
  2. <tr><td rowspan="2">JR</td><td>山手線</td><td>100%</td></tr>
  3. <tr><td>京浜東北線</td><td>120%</td></tr></table>


コード7. 結合した行がある場合のHTMLコード

では、この場合、PHP処理はどうするのでしょうか。id=1には結合したセルを入れ、idが2以降の場合は結合したセルを入れないという命令はできるのでしょうか。

この場合の解決法は多いと思いますが、私は強引な方法で解決しました。私が作成した表の場合はid=1にJRと入れ、idが2~20は一番左のセルが結合された状態(trタグに一番左の列の情報を入れない)です。

この場合、id=1とidが2~20の場合で処理を分けました(コード8)。

  1. <?php
  2. //データベースへの接続のため省略
  3. echo '<table><tr><th>会社</th><th>路線名</th><th>最混雑区間</th><th>混雑率</th></tr>';
  4. //まずはJRの1行目(各社の1行目は一番左の会社名の列が加わる)
  5. foreach ($dbh->query('select * from データベース名 where id=1') as $row){
  6. echo'<tr><td rowspan="20">JR</td><td>';
  7. echo $row['路線'],'</td><td>';
  8. echo $row['区間'];
  9. echo '</td><td>';
  10. echo $row['混雑率'],'%';
  11. echo '</td></tr>';
  12. }
  13. //次にJR東日本の2行目以降(各社の2行目は一番左の列は命令文に入れない)
  14. foreach ($dbh->query('select * from データベース名 where id BETWEEN 2 AND 20') as $row){
  15. echo'<tr><td>';
  16. echo $row['路線'],'</td><td>';
  17. echo $row['区間'];
  18. echo '</td><td>';
  19. echo $row['混雑率'],'%';
  20. echo '</td></tr>';
  21. }
  22. ?>
  23. </table>


コード8. idによって処理を分けた

7行目にはJRの記述がありますが、16行目以降にJRの記述がないことがわかると思います。これは7行目の記述で、「20行を結合する」と命令しており、idが2~20に該当する行については一番左の行が不要なためです。ここではJRが20行だったので、ソースコードの7行目にrowspan(異なる行の結合)="20"としました。もしも5行の結合であれば、この数字は20ではなく5です。

21行目以降に会社名に東京メトロに加える場合、同様の処理を加えます。

id順から少し変えたい

今回のプログラムはid順に実行するものでした。しかし、表示順をid順から変えたい場合があると思います。例えば、今回JR線をid番号1~20に割り振りました。そして、21以降は別の鉄道会社です。では、後日にid番号62~70のJR線を加えた場合はどうするのでしょうか。

この場合、id順に処理するとJR線が上下に分けて見にくいです。id番号62~70を追加したのは私の都合だからです。

その場合、先の章で示した分割という考えかたを使います。id番号1を処理 → id番号2~20を処理 → id番号を62~70を処理 → id番号21~を処理と、処理するタイミングをこちらで指定するのです。

これで将来的にシステムが拡張されても、全体の表を作成することができます。

データベースを活用したシステムを作ってみて

今回、定期的メンテナンスを前提としたデータベースシステムを組み込んだ方法を提案させていただきました。人によってはPythonなどの別の言語で実装することでしょう。しかし、初歩的なPHPの知識があるだけでサイト運営の労力が減るのです。

今回、私の乏しい知識を総動員し、1つのモデルケースを示しました。初心者がゆえに、他の技術系ブログよりも親切で詳細な内容になっている自負があります。よろしければご自身のサイト運営にご活用ください。

ここまでやれば大物ブロガーと差別化できます。でも、大物ブロガーがこの記事を真似したら?その心配は無用です。弊サイトの記事はそこまでメジャーではありません。そう、この記事を読んだあなたは他の人と差を付けるチャンスなのです!

マイナビ出版 たにぐち まこと
松浦 健一郎、司 ゆき(共著) SBクリエイティブ
スポンサーリンク

シェアする

  • このエントリーをはてなブックマークに追加

フォローする