練習問題

練習問題を通して本レッスンで学んだことを確認してみましょう!

問題1

小倉百人一首をデータベースに入れて管理したいと思っています。

以下のサイトから、小倉百人一首のデータをCSV形式でダウンロードすることができます。
CSVファイルをダウンロードしたら、次のカラムを持つテーブル「songs」を作成し、百人一首のデータを一括挿入してください。

小倉百人一首の一覧をCSVで配布しているサイト:
http://kujirahand.com/web-tools/Hyakunin.php
※ページの一番下にダウンロードリンクがあります。

作成したいテーブル「songs」:

カラム名


説明

song_id

INTEGER

主キー。歌番号

kami

TEXT

歌の上の句

simo

TEXT

歌の下の句

author

TEXT

作者

ヒント

CSVファイルをデータベースに挿入するバッチを作る必要があるでしょう。本文でもコンソールからPHPを実行する方法を紹介していますので参考にしてください。

答え

上記サイトからダウンロードした「hyakunin.csv」と以下のプログラムを同じディレクトリに配置して、コンソールからプログラムを実行してみてください。「hyakunin.db」という名前のSQLiteデータベースが作成されます。

file:1-db-insert.php

<?php
$savepath = "hyakunin.db";  // データベースの保存先
$csvpath  = "hyakunin.csv"; // CSVファイルのパス
// 日本語でUTF-8を使うことを明示
mb_language("Japanese");
mb_internal_encoding("utf-8");
// SQLのひな型
$create_query = <<< __SQL__
    CREATE TABLE songs (
        song_id    INTEGER PRIMARY KEY,
        kami       TEXT, /* 上の句 */
        simo       TEXT, /* 下の句 */
        author     TEXT  /* 作者 */
    );
__SQL__;
$insert_query = <<< __SQL__
    INSERT INTO songs (song_id,kami,simo, author)
               VALUES (?, ?, ?, ?)
__SQL__;
// データベースと接続
$db = new PDO("sqlite:$savepath");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// テーブルを定義
$db->exec($create_query);
// CSVファイルを一行ずつ処理
$db->beginTransaction();
$stmt = $db->prepare($insert_query);
$fp = fopen($csvpath, "r");
fgets($fp); // 先頭の一行はヘッダなので捨てる
while (($line = fgets($fp)) != false) {
    $line = mb_convert_encoding($line, "utf-8", "sjis");
    $line = trim($line);
    list($song_id,$kami,$simo,$n1,$n2,$author) = explode(",", $line);
    $song_id = intval($song_id);
    $stmt->execute(array($song_id, $kami, $simo, $author));
    echo $song_id."\n"; // 経過報告
}
$db->commit();
echo "ok!";

問題2

問題1で作成した百人一首のデータベースの内容を一覧を表示するプログラムを作ってください。

ヒント

ここでは、<table>タグで上の句と下の句に分けて表示してみましょう。

答え

以下がデータベースに挿入されている百人一首の歌を一覧表示するプログラムです。

file: 2-db-show.php

<h1 style="text-align:center;">百人一首</h1>
<style> td { border-bottom: solid 1px gray; }</style>
<table>
<tr><th>NO</th><th>上の句</th><th>下の句</th><th>作者</th></tr>
<?php
// データベースに接続
$db = new PDO("sqlite:hyakunin.db");
// 一覧を出力
$stmt = $db->query("SELECT * FROM songs");
foreach ($stmt as $row) {
    $id   = $row["song_id"];
    $kami = $row["kami"];
    $simo = $row["simo"];
    $auth = $row["author"];
    echo "<tr><td align='right'>$id</td>".
         "<td>$kami</td><td>$simo</td>".
         "<td>$auth</td></tr>\n";
}
?></table>

実行すると、以下のように表示されます。

画面
画面