YouTube APIのデータをPHPでMYSQLのDBに保存

今回はPHPから取得してきた動画データをデータベースに保存する方法を解説して行きます。データベースに情報を保存する事で、定期的にデータを更新したり数値の変動をグラフ化したりと、時間経過による変化を把握する事ができるようになります。動画まとめサイトの作り方だけでなく、あらゆるツールを作る際に間違いなく役に立つ知識ですのでぜひ覚えて行って下さい。

MYSQLのデータベースを扱う時、僕の頭の中では常にエクセルの表計算のイメージがあります。行と列が並んでそこに指定した値を詰め込んでいく感じです。僕の中でのプログラミングの原点がエクセルだからでしょうかね、PHPを覚えるよりも簡単に扱えるようになりました。エクセルで例えるなら、データを保存する仕組みがMYSQLで関数で計算する部分はPHPが担っていると考えれば理解が進むかもしれませんね。

前回の記事で取得したデータを使うので、まだ読んでいない方は一読しておいて下さい。

ワードプレスの各種DBに接続
ワードプレスから記事が生成されているか確認
まとめ&次回予告
youtubeの動画を自動で投稿するwpプラグイン完成！
個人開発プログラマーを応援するメンバーシップを始めました('ω')ノ

ワードプレスの各種DBに接続

これまでの解説で「リクエストURLの生成→CURLでリクエスト実行」と進み、YouTube APIのデータをPHPプログラムで取得する事ができました。次はこの準備したデータをワードプレスをインストールした際に生成されたデータベースに保存していきます。ワードプレスはMYSQLと呼ばれるデータベース管理システムを使用しており、PHPから簡単にDBの操作が可能です。

ワードプレスをインストールした段階でデータベースとその中に12個のテーブルが生成されます。それぞれのテーブルには事なる情報を保存しており、コメント・カテゴリ・記事データ・ユーザー情報などが別管理されています。すべて解説するのは書くのも読むのも大変だと思うので、動画まとめサイトの作成に必要なテーブルだけに焦点を絞ってお話します。

wordpressの詳しい仕組みを知りたい方は下記の書籍でオススメです。

ワードプレスの記事の仕組み

プログラミングからはちょっと横道にそれますが、改造に必要なのでワードプレスの記事データを管理する仕組みについてお話しようと思います。

普段ワードプレスで普通に記事を書く時って、ワードプレスにログインして「投稿 > 新規追加
」からエディタを開いてぽちぽちと文章を書きますよね？んで、記事公開ボタンを押して保存し執筆完了となるワケですが、この時書いた記事はデータベース内の「wp**_posts」というテーブルに保存されています（上の画像を参照）。「**」の部分はワードプレスのインストール時にランダムで英数字が割り振られるためそれぞれ異なります。

実はワードプレスで記事を書くという行為は、言い換えるならワードプレスのwp**_postsというテーブルに文字を保存する事と言えます。つまり、外部からwp**_postsテーブルに文字を挿入できるならワードプレスのエディタで手書きする必要は無いんです。

ではwp**_postsテーブルにデータを入れると本当にワードプレスの記事ができるのか試してみましょう。レンサバのphpMyAdminからMYSQLを開いて「データベース：【ユーザー名】_wp***」 > 「テーブル：wp**_posts」を探して下さい。

そしてphpMyAdminのヘッダーメニューの挿入から画像のようにデータを挿入してみます。入力する項目はいくつかありますが、下の表のように入力してみましょう。記載していないカラムは触らなくて大丈夫です。

カラム名	値
post_author	1
post_date	【今日の日付】
post_date_gmt	【今日の日付】
post_content	post_content：記事データ作成テスト
post_title	post_title：記事データ作成テスト
post_status	draft
post_modified	【今日の日付】
post_modified_gmt	【今日の日付】

値の入力が終わったら一番下までスクロールし実行ボタンを押しデータを挿入して下さい。そしてワードプレスにログインし投稿一覧を確認してみましょう。下の画像のようになっているハズです。

はい、ワードプレスの記事データをエディタを使わずにテーブルに直接挿入する事に成功しました。後は今手動で行ったwp**_postsテーブルへのデータ挿入を、PHPのプログラムを用いて行えば内部処理は完成です。

サイトの完成までに時間は掛かりますが、こうやって仕組みを理解していけばそんなに難しくないですよね。

PDOを使ってMYSQLへ接続する

ワードプレスの記事テーブルにデータを挿入するためには該当のデータベースへ接続しなければいけません。PHPにはデータベースへ接続するためにPDOと呼ばれる便利な仕組みが用意されています。見た目的には関数を記述するのと大差ありませんので心配無用です。PHPからMYSQLに接続する方法はいくつかありますが、現在はPDOを使った接続が便利で一般的なので他の方法は割愛します。ではこのPDOを使ってMYSQLに接続する方法を見ていきましょう。

PDOでは「データベースへの接続」「データ操作の内容」「実行トリガー」をそれぞれ分けて記述する事ができます。例えば、接続して操作内容を記述しても実行トリガーを忘れると動いてくれません。しかしこれらの要素の間に別のプログラムを書く事で実行の順番を操作したり、より複雑な命令を与える事もできます。

サンプルとして「00_youtube_api_movie_info.php」から「wp**_posts」テーブルへ接続し、記事データを取得するプログラムを書いてみました。

<?php

try {
    $db = new PDO('mysql:host=localhost;dbname=DB名;charset=utf8', 'DBのユーザー名', 'DBのパスワード');
} catch(PDOException $e) {
    die('エラーメッセージ：'.$e->getMessage());
}

$stt = $db->prepare("SELECT * FROM テーブル名 LIMIT 10");
$stt->execute();

while ($row = $stt->fetch()) {
    print_r($row);
}

?>

ざっくり説明すると、3-7行目がDBへの接続・9行目がデータ操作の内容・10行目が実行トリガー・12-16行目で取得したデータに対する処理を行ってます。

まず困るのは4行目のDB接続に必要な情報かなと思います。DB名には接続したいDB（上記のサンプルなら*****_wp179）を、ユーザー名とパスワードはワードプレスインストール時に生成されたデータベースのユーザー名とパスワードを使用して下さい。しかし、使用するレンサバによっては自分でユーザー名とパスワードを設定するパターンとmixhostのように自動で生成されるパターンがあるようです。

いずれにしろ、データベースのログイン名とパスワードはワードプレスのディレクトリ内に生成されている「wp-config.php」内に記述されているので、下の画像を参考に探してみて下さい。

後は9行目のテーブル名を書き換え（wp**_posts）て完成です、このプログラムを実行して下のように記事データが取得できればPDOのDB接続成功です。

はい、どうですか順調ですか？難しい事はないと思うので、一つずつ手順を確認しながら作業してみて下さい。PHPとMYSQLの接続やコツを知りたい方にはこんな書籍もあります。

取得した動画データをDBに追加する

次はデータベースに対して何をさせるかデータ操作の内容を指定していきます。これはプリペアドステートメントとかSQL文とか呼ばれたりしますが、僕は勝手に「データベースへの命令」として理解してしまってます。趣味レベルなのでこの理解で困った事はありませんでした。

こいつがデータ操作を行う上で非常に重要で、PHPから様々な操作をデータベースに対して行う事ができるようになります。良く使う「取得」「挿入」「更新」「削除」4つのSQL文を紹介します。この4つさえ覚えておけば、思いついたほとんどのアイデアは形に出来ると思います。

SQL文	内容
SELECT	DBからデータを取得し変数に格納する。そのまま表示したり計算に使ったりできる。
INSERT	DBに新しくレコードを挿入する。エクセルでデータを追加する感じ。
UPDATE	DB内のデータを更新する。Cronの自動実行と組み合わせると数値の監視ができる。価格の変動とかパチンコの当たり回数の推移とか。
DELETE	DBの指定したレコードを削除する。指定条件を誤るとデータがごっそり消滅する（実体験）危険もあるので注意。

先ほどのPDOでデータベースへ接続するサンプルプログラムには「SELECT」を使用しましたが、YouTube APIから取得したデータをDBへ新規追加するには「INSERT」を使います。insert文は挿入するテーブル名と列（カラムと呼びます）の種類と値を指定する事で完成します。

ではこのINSERT文を「00_youtube_api_movie_info.php」に追加してYouTube APIのデータを記事テーブルに挿入してみましょう。僕はこんな感じで書いてみました。今までのサンプルプログラムよりも工夫している部分が多いので、なるべく丁寧に解説してみます。

<?php

include(dirname(__FILE__) . '/functions.php');

try {
  $db = new PDO('mysql:host=localhost;dbname=DB名;charset=utf8', 'DBのユーザー名', 'DBのパスワード');
} catch(PDOException $e) {
  die('エラーメッセージ：'.$e->getMessage());
}

$stt = $db->prepare("INSERT IGNORE INTO wp**_posts ( post_author, post_date, post_date_gmt, post_content, post_title, movie_tags, post_status, post_modified, post_modified_gmt, movie_id ) VALUES ( :post_author, :post_date, :post_date_gmt, :post_content, :post_title, :movie_tags, :post_status, :post_modified, :post_modified_gmt, :movie_id )");

$dog_category = array('アメリカンコッカースパニエル', 'コーギー', 'ゴールデンレトリバー');

foreach($dog_category as $value){

    $option = array(
    
        //apiキー
        'key' => 'APIキー',
    
        //必須パラメータ：id or snippet
        'part' => 'snippet',
    
        //フィルタ：最大1個まで
        'forContentOwner' => null,
        'forMine' => null,
        'relatedToVideoId' => null,

        //省略可能なパラメータ
        'channelId' => null,
        'channelType' => null,
        'eventType' => null,
        'maxResults' => '1',
        'onBehalfOfContentOwner' => null,
        'order' => 'relevance',
        'pageToken' => null,
        'publishedAfter' => null,
        'publishedBefore' => null,
        'q' => $value,
        'regionCode' => 'JP',
        'safeSearch' => null,
        'topicId' => null,
        'type' => 'video',
        'videoCaption' => null,
        'videoCategoryId' => '15',
        'videoDefinition' => null,
        'videoDimension' => null,
        'videoDuration' => null,
        'videoEmbeddable' => null,
        'videoLicense' => null,
        'videoSyndicated' => null,
        'videoType' => null
    
    );
    
    $search_url = "https://www.googleapis.com/youtube/v3/search?".http_build_query($option, '&');
    $decode_search_url = array(htmlspecialchars_decode( $search_url ));
    $api_content = youtube_api_get($decode_search_url);
    print_r($api_content);
    
    $video_count = $api_content[0]["pageInfo"]["resultsPerPage"];
   
    for ($j = 0; $j < $video_count; $j++) {

        $video_option = array(
            
            //apiキー
            'key' => 'APIキー',
        
            //フィルタ
            'chart' => null,
            'id' => $api_content[0]["items"][$j]["id"]["videoId"],
            'myRating' => null,
        
            //任意のフィルタ
            'maxResults' => null,
            'onBehalfOfContentOwner' => null,
            'nextPageToken' => null,
            'regionCode' => null,
            'videoCategoryId' => null,
        
            //指定可能のレスポンス：
            'part' => 'snippet,contentDetails,statistics,player,topicDetails,recordingDetails'
        );
    
        $video_url = "https://www.googleapis.com/youtube/v3/videos?".http_build_query($video_option, 'a', '&');
        $decode_video_url = array(htmlspecialchars_decode( $video_url ));
        $video_api_content = youtube_api_get($decode_video_url);
        print_r($video_api_content);

        $stt->bindValue(':post_author', 1);
        $stt->bindValue(':post_date', date("Y/m/d"));
        $stt->bindValue(':post_date_gmt', date("Y/m/d"));
        $stt->bindValue(':post_content', $video_api_content[0]["items"][0]["snippet"]["description"]);
        $stt->bindValue(':post_title', $api_content[0]["items"][$j]["snippet"]["title"]);
        
        if(isset($video_api_content[0]["items"][0]["snippet"]["tags"])){
            $tag_count = count($video_api_content[0]["items"][0]["snippet"]["tags"]);
            $tags = null;
            for ($tc = 0; $tc < $tag_count; $tc++) {
                $tags .= $video_api_content[0]["items"][0]["snippet"]["tags"][$tc].",";
            }
        } else {
            $tags = null;
        }
        
        $stt->bindValue(':movie_tags', $tags);
        $stt->bindValue(':post_status', 'draft');
        $stt->bindValue(':post_modified', date("Y/m/d"));
        $stt->bindValue(':post_modified_gmt', date("Y/m/d"));
        $stt->bindValue(':movie_id', $api_content[0]["items"][$j]["id"]["videoId"]); //重複チェック用
        $stt->execute();
    
    } // for $j

}

?>

サンプルで作ってきたコードを下記のように追記しました。

11行目：INSERT文でテーブル名とカラム名を指定
62/64/115行目：取得した件数分処理を繰り返す
66-90行目：videoメソッドで不足データの収集
92-112行目：テーブルに挿入する値をセット
113行目：INSERT文のトリガーを実行

大きな修正はINSERT文を追記してテーブルにデータを挿入できるようにした部分です。後はパラメータに応じて出力されたAPIデータの結果件数分、挿入する値のセットとトリガーの実行を繰り返し処理しています。ここまでの処理の過程でいくつか新しく出てきた要素があるので細かく見ていきましょう。

JSON形式のデータにアクセスする書き方

62/73/95/96/98/99/102/112行目ではYouTube APIで取得したデータをプログラム内で直接使用しています。ようやく動画データを計算に反映させる場面が出てきましたね。このAPIの個別データを取得する方法が初めて出てきたので説明します。

YouTube APIで得られるデータはJSONという形式になっており、様々なデータがカテゴリ毎に分けられ階層を成しています。これらの値を利用するには特定の記述方法が必要で、例えば上記の画像のように検索結果の件数である「resultsPerPage」の値である「１」が欲しい場合は「[0][“pageInfo”][“resultsPerPage”]」と階層ごとに左から順番に指定します。そして、このデータ群はユーザー定義関数のyoutube_api_get();によって、$api_contentに格納されているので、$api_content[0][“pageInfo”][“resultsPerPage”];と指定する事で、1という値を取得する事ができます。

不足データを補うためのvideoメソッド

記事化する動画データはYouTube APIのsearchメソッドでキーワード検索して収集していますが、searchメソッドだけでは動画の説明文が一部しか取得できなかったり、動画に付けられたタグ情報は取得する事ができません。これらの情報は記事にタグを付与する時に使用するので、動画の詳細情報を取得できるvideoメソッドを使用して収集します。

動画の説明文は記事データ内の「post_content」に入れるので問題ないのですが、動画のタグ情報を入れるカラムがありません。なので、記事テーブルに新しく「movie_tags」カラムを追加します。

データの保存場所を確保できたら、videoメソッドを実行し動画の説明文とタグ情報を取得します。上記のサンプルコードの66-90行目で記述している部分が該当のプログラムになります。searchメソッドで取得した動画IDをvideoメソッドのフィルタに設定し、詳細な動画情報を取得しています。

タグ情報は配列の状態で取得できますが、後で使いやすくするためにカンマ区切りのテキスト形式でカラムの中に保存しています。また、動画によってはタグを付けられていない場合もあるので、タグが存在する場合のみ処この理を行います。該当するコードはサンプルの98-106行目の部分です。ここまでで記事テーブルの中身はこんな感じになっているハズです。

カラムにユニークキーを設定して重複登録を避けるテクニック

プログラムをこのまま実行すると、同じ動画データを重複して登録してしまうという欠点があります。サイトの種類によっては同一データを繰り返し登録しても問題ない場合もあると思いますが、今回は動画まとめサイトなので同じ動画を何度も投稿したくありません。

なので、すでに記事テーブルに挿入済みの動画データなのかを判定し、挿入済みの動画ならテーブルに追加しない、挿入してない動画ならテーブルに追加する、という条件分岐が必要になります。この条件分岐は複雑なプログラムを書く必要は無く、カラムにユニークキーを設定＋INSERT文にIGNOREを付与のコンボで実現する事ができます。

カラムにユニークキーを設定

レコードのダブりを避けるためにはユニークキーと呼ばれる絶対に重複しないIDが必要となります。YouTube APIで取得できるデータ内にもユニークキーはいくつもありますが、今回は動画単位での判定なので動画IDを使います。チャンネルを重複させたくない場合はチャンネルIDを、カテゴリを重複させたくない場合はカテゴリIDと必要に応じて使用するユニークキーは違うのを使って下さい。

この動画IDをユニークキーとして使用するためのカラムを設定します。記事テーブルの中には取得した動画IDを入れるカラムが無いので、まずはメニューの構造から「movie_id」カラムを新しく追加します。

「movie_id」カラムができたら次はこのカラムをユニークキーとして設定します。

INSERT文にIGNOREを付与

次はレコードを挿入する際にユニークキーを参照して重複を避ける命令を書いて行きますが、実はINSERT文にちょっと追記するだけで勝手に重複チェックを行ってくれます。

上記「00_youtube_api_movie_info.php」の11行目を見ると「INSERT IGNORE INTO テーブル名 ~」となっており、INSERT文のデフォルト構文である「INSERT INTO テーブル名 ~」にIGNOREが追加されている事が分かります。IGNOREを追加すると挿入対象のテーブル内からユニークキーに設定されたカラムを探し、今から挿入する値がそのカラム内に存在していないか一気にチェックしてくれます。

ユニークキーの設定とINSERT IGNOREのコンボで行うレコードの重複チェックは、外部データを扱うサイト構築に使えるので覚えておいて損はないと思います。