このブログですが、長期間放置していた時期もあり、古い内容の記事もあるため、ちょっと整理でもしてみようっかなって思っています。
で、記事の一覧を作成して、出来れば、その記事の一覧にGoogle Analysisから取得した過去のPV数等を絡めて表示したら、便利かなってな感じです。
でも、Google Analysisから取得した情報をメインにすると、PV数が0の記事が取得出来なかったり、削除済みの記事が残っていたり、何かと不便です。
なので、いま公開している記事をベースに基本となる記事一覧を、まずは作ってみよっかなって思った次第です。
MySQLデータベースから記事一覧を取得する方法
抽出用SQL文の作成
まずは、必要な項目の洗い出しと、それらの項目が属しているテーブルの確認です。
必要な項目は、
- 記事のタイトル
- 記事のURL
- 公開日
- 更新日
です。他にもカテゴリなどもあれば便利な気もしますが、今回はシンプルに上の項目に絞って見ました。
で、これらの項目が収まっているテーブルは「wp_posts」となります。ただし「wp_posts」には、公開済みの記事以外も含まれていますので、今回は公開記事のみ絞り込んで表示したいと思います。
さて、上記の条件で作成したSQL文は、こんな感じです。
SELECT
WP.ID
, WP.post_title
, CONCAT('/', WP.post_name, '/') as post_name
, WP.post_date
, WP.post_modified
FROM
wp_posts WP
WHERE
WP.post_status = 'publish'
AND WP.post_type = 'post'
ORDER BY
WP.post_modified DESC
ちなみに、今回のSQL文ですが、一応プライマリーキーのIDを先頭入れています。
次に記事名と記事のURLを取得します。記事のURLですが、そもままの値でも大丈夫ですが、今回はGoogle Analysisで取得するURLに合うように「/」で囲んで表示するようにしています。
後は、公開日と更新日を出力しています。
また、今回は公開済みの記事のみ抽出するようにWHERE句を書いています。
最後に、記事一覧を更新日の降順で出力出来るようにORDER BY句を書いています。
と、まあ色々と書いていますが、SQL文に関しては、コピペで全く問題ありません。
phpMyAdminでのデータの抽出
作成したSQL文をphpMyAdminから流しましょう。
まずは、phpMyAdminにログインして、左側のツリー状に表示されている部分から対象のデーターベースを選択します。
次に、「SQL」タブをクリックして、作成したSQL文をペーストし、右下の「実行」ボタンをクリックします。
その結果、下のような一覧が表示されれば、OKです。
抽出データをExcelファイルに出力
仕上げに抽出されたデータをExcelファイルに出力します。
先ほどの一覧画面を一番下までスクロールして、「クエリ結果操作」の「エクスポート」をクリックします。
すると、エクスポート画面に遷移するので、フォーマットで「CSV」を選択して、「実行」ボタンをクリックします。
ちなみに、ここでフォーマットで「CSV for MS Excel」と、エンコーディングへの変換で「SJIS」を選びたくなりますが、「CSV for MS Excel」は訳が分からないし、エンコードの変換は私の環境では効かないので、このままで問題ありません。phpMyAdminのコードを書き換えて修正することも出来ますが、今回はレンタルサーバーってこともあって、このまま進めます。
「実行」ボタンをクリックすると、「wp_posts.csv」と言うファイルがダウンロード出来ますので、エディタなどでファイルを「Shirt-JIS」に変換します。
ちなみに、私は秀丸を愛用しているので、こんな感じで、秀丸で開いて、保存って感じで変換しています。
変換が完了したら、「wp_posts.csv」をダブルクリックするとExcelが立ち上がって記事一覧が表示されます。
ここまで来れば、後はExcelの世界なので、煮るなり焼くなりって感じで、いかがでしょうか？
最後に
今回は、全て手動で、記事一覧を作成するまでってことで、まとめてみました。
この後は、Google Analysisで取得したPV数の情報を付加したデータを作成して、日々の参照したいところです。
そうなってくると、この一連の流れをプログラミング化したくなっちゃいますね。
もし、Google Analysisで取得したPV数の情報を付加する方法や、プログラミング化する段階になったら、別の記事で紹介したいと思います。
ってことで、今回はこんな感じで。
