【WordPress】データベースから投稿データを取得する方法

プログラミング
※このページの内容に広告・PRが含まれます。

WordPressで投稿データの一覧を取得できるSQLが検索しても出てこなかったので、自分で作ってみました。

このコードを使用すれば、投稿のタイトルやslugはもちろん、ユーザー名やカテゴリー・タグの情報まで一緒に取得できます。

また、SQLに詳しくない人でも必要に応じて書き換えられるように、SQLコードの解説もつけました。

投稿データを取得するSQLコード

取得するカラムは、必要に応じて変更してください。

SELECT p.ID,
  u.display_name,
  p.post_modified,
  p.post_title,
  CONCAT('https://example.com/', u.user_nicename, '/', p.post_name, '/') AS 'URL',
  p.post_status,
  c.cat_name,
  IFNULL(t.tag_name, '') AS 'tag_name'
FROM wp_posts p
LEFT JOIN wp_users u
  ON p.post_author = u.ID
LEFT JOIN (
  SELECT cat_a.object_id, GROUP_CONCAT(cat_b.name) AS 'cat_name'
  FROM wp_term_relationships cat_a, wp_terms cat_b, wp_term_taxonomy cat_c
  WHERE cat_a.term_taxonomy_id = cat_b.term_id
    AND cat_a.term_taxonomy_id = cat_c.term_id
    AND cat_c.taxonomy = 'category'
  GROUP BY cat_a.object_id
) c
  ON p.ID = c.object_id
LEFT JOIN (
  SELECT tag_a.object_id, GROUP_CONCAT(tag_b.name) AS 'tag_name'
  FROM wp_term_relationships tag_a, wp_terms tag_b, wp_term_taxonomy tag_c
  WHERE tag_a.term_taxonomy_id = tag_b.term_id
    AND tag_a.term_taxonomy_id = tag_c.term_id
    AND tag_c.taxonomy = 'post_tag'
  GROUP BY tag_a.object_id
) t
  ON p.ID = t.object_id
WHERE p.post_type = 'post'
  AND p.post_status = 'publish'
ORDER BY p.ID ASC

上記のコードでは、以下のデータを取得できます。

  • 投稿ID
  • 投稿者名
  • 投稿の最終変更日時
  • 投稿のタイトル
  • 投稿のURL([ドメイン名] / [投稿者名] / [投稿のslug])
  • 投稿のステータス(「公開済み」や「下書き」、「非公開」など)
  • 投稿ごとのカテゴリー(複数登録の場合はコンマで結合したもの)
  • 投稿ごとのタグ(タグを登録してなければ空白)

 

他にも以下のデータも出力できるので、必要に応じて対応しているSQLをSELECTの後に追加してください。

取得できるデータSQL
投稿日時p.post_date
投稿の本文p.post_content
投稿者のユーザーIDp.post_author
投稿の種類
(投稿ページ、固定ページ、など)
p.post_type

なお、データの取得範囲は、「公開されている投稿ページ」のみとなっています。下書きや非公開の記事のデータも取得したい場合は、後ほどコードの変更方法を紹介します。

 

SQLコードの詳しい解説

URLのデータについて

コードの5行目でURLのデータを取得しています。

  CONCAT('https://example.com/', u.user_nicename, '/', p.post_name, '/') AS 'URL',

このサイトでは、筆者名と投稿slugをURLに使っているので、ドメインも含めてCONCAT()関数で結合することで投稿のURLにしています。

なお、ドメインは必要に応じて変更してください。

カテゴリー・タグの情報の取得

コードの13~27行目でカテゴリーとタグのデータを取得しています。

カテゴリーとタグはデータの取得方法がほぼ同じなので、ここではカテゴリーのデータを取得するコードを用いて解説します。

  SELECT cat_a.object_id, GROUP_CONCAT(cat_b.name) AS 'cat_name'
  FROM wp_term_relationships cat_a, wp_terms cat_b, wp_term_taxonomy cat_c
  WHERE cat_a.term_taxonomy_id = cat_b.term_id
    AND cat_a.term_taxonomy_id = cat_c.term_id
    AND cat_c.taxonomy = 'category'
  GROUP BY cat_a.object_id

カテゴリーやタグのデータは、「wp_term_relationships」「wp_terms」「wp_term_taxonomy」の3つのテーブルに分けて格納されているので、WHERE句で条件を指定してデータを取ってきています。

またGROUP BYした時に、すべての値をコンマで連結して取得できるGROUP_CONCAT()関数を使うことで、カテゴリーを複数登録している投稿でも問題なくデータを取得できるようにしました。

(参考記事)MySQLでGROUP BY時にすべての値を連結して取得 (GROUP_CONCAT)|レコチョクのエンジニアブログ

 

なお、タグを登録していない投稿もあると思いますが、その場合はNULLが出力されます。

本記事で紹介したコードでは、IFNULL()関数を使って、タグを登録してない場合は空白が出力されるようにしています。

データの取得範囲

コードの30~31行目でデータの取得範囲を指定しています。以下の部分ですね。

WHERE p.post_type = 'post'
  AND p.post_status = 'publish'

もし固定ページのデータも取得したい場合は、30行目を以下のように変更します。

WHERE (p.post_type = 'post' OR p.post_type = 'page')

下書きや非公開の記事のデータも取得したい場合は、上記と同様にORを使ってp.post_statusを複数指定してあげてください。

ステータス追加するp.post_status
公開済みpublish
下書きdraft
非公開private
ゴミ箱trash
予約済みfuture
承認待ちpending

 

phpMyAdminから投稿データを出力する方法

実際にphpMyAdminから投稿データを取得する方法を紹介します。

phpMyAdminのページを開く

多くのレンタルサーバーでは、管理画面からphpMyAdminを開くことができるかと思います。

例えばエックスサーバーの場合だと、以下のところにリンクがあります。

 

リンクをクリックすると、ユーザー名とパスワードを求められるので入力します(エックスサーバーの場合)。「ログイン」を押すとphpMyAdminのページを開けます。

SQLでデータを取得する

左のメニューから、データを取得したいサイトのデータベースを選択します。

 

上のメニューから「SQL」を選択します。

 

SQLコードを入れて「実行」を押します。

データをエクスポートする

データの取得結果ページの下の方にある、「クエリ結果操作」の中の「エクスポート」をクリックします。

 

以下のように選択します。なお、ここではCSV形式でダウンロードする方法を紹介します。

  • エクスポート方法:詳細
  • 行:(デフォルトのまま)
  • 出力:(デフォルトのまま)
  • フォーマット:CSV
  • フォーマット特有のオプション:「1 行目にカラム名を追加する」にチェックを入れる
  • エンコーディングへの変換:(デフォルトのまま)

 

最後に「実行」をクリックするとデータをダウンロードできます。

タイトルとURLをコピーしました