WordPressのDBから記事データを抽出する

WordPressのDBから記事データを抽出する

2020-04-3025 min read

目次

  1. 概要
  2. wordpressのdb関連図
  3. 公開記事一覧の取得
  4. タグカテゴリの取得
  5. サムネイルの取得
  6. おまけ-phpスクリプト化しました
  7. 参考にしたところ

概要

WordPressにため込んだデータMarkdown化しGatsbyで作ったブログに引越しするために記事データを抽出した時のSQLの話。

かなり泥臭かったです。

WordPressのDB関連図

WordPressのDBの関連図は次のようになっています。画像は基本的なテーブルです。

実際に実行したのはこちらのテーブル。

プラグイン依存のテーブルがありますが、これは無視していきます。

+-----------------------------+
| Tables_in_bitnami_wordpress |
+-----------------------------+
| wp_aiowps_events            |
| wp_aiowps_failed_logins     |
| wp_aiowps_global_meta       |
| wp_aiowps_login_activity    |
| wp_aiowps_login_lockdown    |
| wp_aiowps_permanent_block   |
| wp_as3cf_items              |
| wp_cocoon_accesses          |
| wp_cocoon_affiliate_tags    |
| wp_cocoon_function_texts    |
| wp_cocoon_item_rankings     |
| wp_cocoon_speech_balloons   |
| wp_commentmeta              |
| wp_comments                 |
| wp_links                    |
| wp_options                  |
| wp_postmeta                 |
| wp_posts                    |
| wp_term_relationships       |
| wp_term_taxonomy            |
| wp_termmeta                 |
| wp_terms                    |
| wp_urls                     |
| wp_usermeta                 |
| wp_users                    |
+-----------------------------+

このデータベースからタイトル・記事データ・サムネイル・カテゴリ・タグを抽出していきます。

公開記事一覧の取得

WordPressのタイトル・記事内容の情報はwp_postsに格納されていますが、画像データなど様々な物が含まれているので、フィルターをかけて抽出します。

実際のクエリはこちら

SELECT 
  * 
FROM
  wp_posts
WHERE 
  post_status = 'publish'
  AND 
  post_title <> ''

取得できたデータはこちら。IDが主キーとなります。

*************************** 1. row ***************************
                   ID: 5
          post_author: 3
            post_date: 2018-01-22 12:15:57
        post_date_gmt: 2018-01-22 12:15:57
         post_content: '略: 本文本文本文本文本文本文'
           post_title: '略: タイトルタイトルタイトルタイトル'
         post_excerpt: 
          post_status: publish
       comment_status: closed
          ping_status: open
        post_password: 
            post_name: hello-world
              to_ping: 
               pinged: 
        post_modified: 2019-01-14 17:52:46
    post_modified_gmt: 2019-01-14 08:52:46
post_content_filtered: 
          post_parent: 0
                 guid: http://0.0.0.0/?p=5
           menu_order: 0
            post_type: post
       post_mime_type: 
        comment_count: 0

タグ・カテゴリの取得

タグやカテゴリは、これらを管理するwp_term_taxonomyと記事IDに紐つく連関エンティティ wp_term_relationships を利用して取得します。

wp_term_relationships

+------------------+---------------------+------+-----+---------+-------+
| Field            | Type                | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------+-------+
| object_id        | bigint(20) unsigned | NO   | PRI | 0       |       |
| term_taxonomy_id | bigint(20) unsigned | NO   | PRI | 0       |       |
| term_order       | int(11)             | NO   |     | 0       |       |
+------------------+---------------------+------+-----+---------+-------+

wp_term_taxonomy

+------------------+---------------------+------+-----+---------+----------------+
| Field            | Type                | Null | Key | Default | Extra          |
+------------------+---------------------+------+-----+---------+----------------+
| term_taxonomy_id | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| term_id          | bigint(20) unsigned | NO   | MUL | 0       |                |
| taxonomy         | varchar(32)         | NO   | MUL |         |                |
| description      | longtext            | NO   |     | NULL    |                |
| parent           | bigint(20) unsigned | NO   |     | 0       |                |
| count            | bigint(20)          | NO   |     | 0       |                |
+------------------+---------------------+------+-----+---------+----------------+

クエリ

select 
    term_taxonomy.taxonomy,
    terms.name
from wp_posts posts
    inner join wp_term_relationships term_relationships 
    on posts.id = term_relationships.object_id 
    inner join wp_term_taxonomy term_taxonomy 
    on term_relationships.term_taxonomy_id = term_taxonomy.term_taxonomy_id 
    inner join wp_terms terms 
    on term_taxonomy.term_id = terms.term_id 
where 
    posts.ID = :id 
+----------+-----------------------+
| taxonomy | name                  |
+----------+-----------------------+
| category | Web                   |
| post_tag | HTML                  |
| post_tag | CSS                   |
| post_tag | JavaScript            |
| post_tag | canvas                |
+----------+-----------------------+

カテゴリとタグを取得できました。

サムネイルの取得

サムネイルのデータは wp_posts に格納されています。

select 
    thumbnail.guid as url
from wp_posts posts
    inner join wp_postmeta postmeta 
    on posts.ID = postmeta.post_id 
    inner join wp_posts thumbnail 
    on postmeta.meta_value = thumbnail.ID 
where 
    posts.ID = :id
    AND
    postmeta.meta_key = '_thumbnail_id'

ただし、画像の管理に関して様々なプラグインを利用しており、 結局目的の情報は取得できなかったので自分のブログに対しスクレイピングで画像を1件ずつ取得する方法にしました。

おまけ: PHPスクリプト化しました

記事データをmarkdownに吐くphpスクリプトを書きました。

<?php
define('DB_HOST', 'localhost:3306');
define('DB_NAME', '');
define('DB_USER', '');
define('DB_PASSWORD', '');
define('DB_CHARSET', 'utf8');

try {
    $dbh = new PDO(
        sprintf(
            "mysql:dbname=%s;host=%s;charset=%s;",
            DB_NAME,
            DB_HOST,
            DB_CHARSET
        ),
        DB_USER,
        DB_PASSWORD
    );
} catch (PDOException $e) {
    echo "connect fail: " . $e->getMessage() . "\n";
    exit(1);
}

$items = getItems();

$ignoreTags = ['web &amp; programming'];
$counter = 0;
$pathList = [];
foreach ($items as $row) {
    $id = $row['ID'];
    $taxonomy = getTagsById($id);
    $tags = [];
    foreach ($taxonomy as $tag) {
        $tag = mb_strtolower($tag['name']);
        if (in_array($tag, $ignoreTags)) {
            continue;
        }
        $tags[$tag] = 1;
    }
    $tags = array_keys($tags);
    $thumbnail = '';
    // note: DBから画像が取得できる場合
    // $thumbnail = getThumbnailById($id);
    // if (count($thumbnail) > 0) {
    //     $thumbnail = $thumbnail[0]['url'];
    // } else {
    //     $thumbnail = '';
    // }

    $oldPath = sprintf(
        "/%s/%s/%s/",
        substr($row['post_date'], 0, 4),
        substr($row['post_date'], 5, 2),
        $id
    );
    try {
        $content = file_get_contents('https://tech-blog.s-yoshiki.com'. $oldPath);
        if (!$content) {
            continue;
        }
        $thumbnail = saveThumbnail($content);
    } catch (Exception $e) {
        // echo($e);
        continue;
    }
    $counter++;
    $newPath = sprintf("/entry/%s", $counter);
    _mkdir("./entry/" . $counter);
    // MD file
    $result = flushFile([
        'title' => $row['post_title'],
        'date' => $row['post_date'],
        'content' => $row['post_content'],
        'thumbnail' => $thumbnail,
        'path' => $newPath,
        'tags' => json_encode($tags, JSON_UNESCAPED_UNICODE),
    ]);
    file_put_contents('./entry/' . $counter . '/index.md', $result);
    $pathList[] = [
        'fromPath' => $oldPath,
        'toPath' => $newPath,
    ];
}
file_put_contents('./entry/redirect-config.json', json_encode($pathList, JSON_UNESCAPED_UNICODE));
exit(0);


function getItems()
{
    global $dbh;
    $sql = "
    SELECT * 
    FROM
    wp_posts
    WHERE 
    post_status = 'publish'
    AND  post_title <> '' ";
    $prepare = $dbh->prepare($sql);
    $prepare->execute();
    $result = $prepare->fetchAll(PDO::FETCH_ASSOC);
    return $result;
}

function getTagsById($id)
{
    global $dbh;
    $sql = "
    select 
    term_taxonomy.taxonomy,
    terms.name
    from wp_posts posts
    inner join wp_term_relationships term_relationships on posts.id = term_relationships.object_id 
    inner join wp_term_taxonomy term_taxonomy on term_relationships.term_taxonomy_id = term_taxonomy.term_taxonomy_id 
    inner join wp_terms terms on term_taxonomy.term_id = terms.term_id 
    where 
    posts.ID = :id ";
    $prepare = $dbh->prepare($sql);
    $prepare->bindValue(':id', $id, PDO::PARAM_INT);
    $prepare->execute();
    $result = $prepare->fetchAll(PDO::FETCH_ASSOC);
    return $result;
}

function getThumbnailById($id)
{
    global $dbh;
    $sql = "
    select 
    thumbnail.guid as url
    from wp_posts posts
    inner join wp_postmeta postmeta on posts.ID = postmeta.post_id 
    inner join wp_posts thumbnail on postmeta.meta_value = thumbnail.ID 
    where 
    posts.ID = :id
    AND postmeta.meta_key = '_thumbnail_id'";
    $prepare = $dbh->prepare($sql);
    $prepare->bindValue(':id', $id, PDO::PARAM_INT);
    $prepare->execute();
    $result = $prepare->fetchAll(PDO::FETCH_ASSOC);
    return $result;
}

function get_og_image_url($content)
{
    if (preg_match_all( "<meta property=\"og:([^\"]+)\" content=\"([^\"]+)\">", $content, $ogp_list )){
        if (is_array($ogp_list) && isset($ogp_list[1])) {
            for ($i = 0; $i < count($ogp_list[1]); $i++) {
                $ogp_result[$ogp_list[1][$i]] = $ogp_list[2][$i];
            }
        }
        if (isset($ogp_result['image'])) {
           return $ogp_result['image'];
        }
    }
    return false;
}

function _mkdir($dir)
{
    if (!is_dir($dir)) {
        `mkdir -p $dir`;
    }
}

function saveThumbnail($content)
{
    $dstPath = '';
    $thumbnail = '../../../images/no-image.png';
    $ogimage = get_og_image_url($content);
    if (!$ogimage) {
        return $thumbnail;
    }
    $parsedUrl = parse_url($ogimage);
    $parsedPath = pathinfo($parsedUrl['path']);
    if ($parsedPath['extension'] === 'gif') {
        return $thumbnail;
    }
    $dstPath = './images/thumbnail/' . $parsedPath['basename'];
    $thumbnail = '../../../images/thumbnail/' . $parsedPath['basename'];
    if (file_exists($dstPath)) {
        return $thumbnail;
    }
    _mkdir('./images/thumbnail/' . $parsedPath['dirname']);
    $rawImg = file_get_contents($ogimage);
    file_put_contents($dstPath, $rawImg);
    return $thumbnail;
}

function flushFile($arg)
{
    $title = $arg['title'];
    $date = $arg['date'];
    $content = $arg['content'];
    $tags = $arg['tags'];
    $path = $arg['path'];
    $thumbnail = $arg['thumbnail'];
    return <<<EOT
---
title: "$title"
path: "$path"
date: "$date"
coverImage: "$thumbnail"
author: "XXXXX"
tags: $tags
---
$content
EOT;
}

参考にしたところ

データベース構造 - WordPress Codex 日本語版

Recommends
WordPressのDBから記事データを抽出する
2020-04-30
wordpress
mysql
blog
WordPressやめます Gatsbyに移行しました
2020-05-11
wordpress
blog
gatsby
Ubuntu18.04にApache MariaDB PHP7.2 をセットアップ
2019-07-29
amazon%20aws
php
linux
【WordPress】MySQLのバックアップ、不要コメント+投稿のリビジョンデータを削除...
2018-09-24
wordpress
%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9
mysql
AWS Lightsail + bitnami に自作アプリケーションを作って共存させる ...
2018-08-18
amazon%20lightsail
php
apache
【WordPress】ソースコードから編集してhttps対応URLに変更
2018-02-10
amazon%20aws
amazon%20lightsail
wordpress
Fisher-Yates shuffleで配列シャッフル [js/ts/php]
2022-06-19
javascript
node.js
typescript
GatsbyからNext.jsへのサイト移行
2022-04-04
next.js
gatsby
amazon%20aws
AWS CDK v2 でVPC上にAPI Gateway + Lambda + RDS +...
2022-02-28
amazon%20aws
aws%20cdk
node.js
PostfixでメールリレーしてMailHogで受信する開発用Dockerコンテナの構築
2021-05-19
docker
postfix
centos
php-fpmのステータスページを表示 Apache & htaccess
2021-03-24
php
php%20fpm
apache
DBクライアントツールはDBeaverをおすすめしたい
2021-03-08
oracle
mysql
sqlite
Homebrew で php7.4 + Xdebug をインストール
2021-02-01
php
xdebug
mac
MySQL8.0 で利用できるパラメータを表示する方法
2021-01-27
mysql
mariadb
centos
CentOS に MySQL8.0をインストールする
2021-01-26
mysql
mariadb
centos
New Posts
Amazon S3 でライフサイクルポリシーを設定する
2022-06-19
amazon%20aws
amazon%20s3
AWS Certified Developer Associate に合格した
2022-01-01
amazon%20aws
%E8%B3%87%E6%A0%BC%E8%A9%A6%E9%A8%93
Fisher-Yates shuffleで配列シャッフル [js/ts/php]
2022-06-19
javascript
node.js
typescript
JavaScriptでUTF-16コードを文字列に変換
2022-06-18
javascript
node.js
[JS]乱数でランダムな整数を生成する
2022-06-18
javascript
node.js
[JS]BigIntでMathが使えない件
2022-06-12
javascript
node.js
atcoder
AWS SAPに合格しました
2022-06-11
amazon%20aws
%E8%B3%87%E6%A0%BC%E8%A9%A6%E9%A8%93
[AWS]DataSync/Storage Gateway/Transfer Family...
2022-05-29
amazon%20aws
[AWS CDK]ECS FargateでNestJSで作成したRESTfull APIデ...
2022-05-24
nestjs
amazon%20aws
aws%20cdk
[AWS CDK]S3 CloudFront OAI Route53 構成 で NextJ...
2022-05-23
amazon%20aws
aws%20cdk
typescript
[CDK]SNS+SQS+DynamoDBでBounceとComplaint情報を収集する...
2022-04-11
amazon%20aws
node.js
typescript
[AmazonSES] node.js と ejs を利用してEメールを送信する
2022-04-09
javascript
node.js
amazon%20aws
GatsbyからNext.jsへのサイト移行
2022-04-04
next.js
gatsby
amazon%20aws
[AWS CDK] Lambda で S3 オブジェクトを読み書きするStackの構築
2022-03-18
aws%20cdk
amazon%20aws
typescript
[AWS CDK] S3 + CloudFrontの構築とOriginAccessIden...
2022-03-09
amazon%20aws
aws%20cdk
typescript
Hot posts!
Proxy環境下でcurlを実行する
2019-12-07
linux
curl
OpenCVのMatのタイプ一覧表
2018-11-25
%E7%94%BB%E5%83%8F%E5%87%A6%E7%90%86
opencv
Macでも利用できるDBクライアント MySQL PostgreSQL Oracle など
2019-12-21
linux
%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9
mysql
TablePlusを使ってみる。シンプルでモダンなSQLクライアントツール
2018-09-30
%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9
DBクライアントツールはDBeaverをおすすめしたい
2021-03-08
oracle
mysql
sqlite
AWS S3のアクセスキーIDとシークレットアクセスキーの取得 作業用ユーザを作成
2019-06-12
amazon%20aws
linux
amazon%20s3
AtCoderで初めて色がつくまでの話(茶色) レートが中々上がらなかった原因
2018-11-25
%E3%82%A2%E3%83%AB%E3%82%B4%E3%83%AA%E3%82%BA%E3%83%A0
%E7%AB%B6%E6%8A%80%E3%83%97%E3%83%AD%E3%82%B0%E3%83%A9%E3%83%9F%E3%83%B3%E3%82%B0
%E9%9B%91%E8%AB%87
CentOS8でEPELとPowerToolsリポジトリの有効化
2020-11-30
centos
red%20hat
EPEL
Macでターミナルからポートスキャンを行う方法。
2018-12-09
linux
mac
apple
Python + OpenCVのfillConvexPolyで複雑なポリゴンを描画する
2018-11-27
python
%E7%94%BB%E5%83%8F%E5%87%A6%E7%90%86
opencv
Date
▶︎
2022 年 (31)
▶︎
2021 年 (40)
▶︎
2020 年 (30)
▶︎
2019 年 (90)
▶︎
2018 年 (89)
▶︎
2017 年 (1)
Tags
javascript(96)
linux(47)
amazon%20aws(45)
%E3%82%A2%E3%83%AB%E3%82%B4%E3%83%AA%E3%82%BA%E3%83%A0(36)
node.js(34)
%E7%94%BB%E5%83%8F%E5%87%A6%E7%90%86(30)
html5(29)
php(24)
centos(24)
typescript(23)
python(22)
%E7%AB%B6%E6%8A%80%E3%83%97%E3%83%AD%E3%82%B0%E3%83%A9%E3%83%9F%E3%83%B3%E3%82%B0(21)
canvas(18)
mac(18)
opencv(17)
mysql(17)
%E9%9B%91%E8%AB%87(15)
wordpress(15)
atcoder(14)
docker(14)
apache(12)
%E6%A9%9F%E6%A2%B0%E5%AD%A6%E7%BF%92(12)
%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9(12)
amazon%20s3(12)
red%20hat(12)
ubuntu(11)
github(10)
git(10)
vue.js(10)
%E7%94%BB%E5%83%8F%E5%87%A6%E7%90%86100%E6%9C%AC%E3%83%8E%E3%83%83%E3%82%AF(10)
css3(8)
%E5%8F%AF%E8%A6%96%E5%8C%96(8)
%E5%B0%8F%E3%83%8D%E3%82%BF(8)
mariadb(8)
aws%20cdk(8)
amazon%20lightsail(7)
react(7)
%E3%83%96%E3%83%AD%E3%82%B0(6)
cms(6)
oracle(6)
perl(6)
gitlab(6)
next.js(6)
iam(5)
amazon%20ec2(5)
%E8%B3%87%E6%A0%BC%E8%A9%A6%E9%A8%93(5)
aws%20amplify(5)
curl(4)
webassembly(4)
ssh(4)
Author
s-yoshiki
s-yoshiki
githubzenntwitterqiita
ただの備忘録です。
JavaScript/TypeScript/node.js/React/AWS/OpenCV
※このブログの内容は個人の見解であり、所属する組織等の見解ではありません。