WordPressの記事一覧をMySQLデータベースから取得しExcelファイルに出力する方法。

2019年10月16日2021年5月23日

URLをコピーしました！

このブログですが、長期間放置していた時期もあり、古い内容の記事もあるため、ちょっと整理でもしてみようっかなって思っています。

で、記事の一覧を作成して、出来れば、その記事の一覧に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」に変換します。

ちなみに、私は秀丸を愛用しているので、こんな感じで、秀丸で開いて、保存って感じで変換しています。