2021.05.29

【SQL】WordPress Popular Postsのアクセスデータを取り出して自作の人気ランキングに取り込む

phpMyAdminのSQLクリエ(Query)を利用して必要なカラム(列)のデータだけを探し出して表示し、それをCSV形式でエクスポートしてみました。

 

前置き

ランキングを自作したのでphpMyAdminでワードプレスで人気記事のランキングを作成するプラグイン「WordPress Popular Posts(以後、「wpp」と略)」の特定のカラム(列)のデータだけ抜き出したかったんですが、「SQLクエリ(Query)」を使わないとできないみたいなので諦めて一旦、テーブルごとエクスポートしてエクセル等の表計算ソフトで修正してやろうと思いました。

ただ、エクスポートしたCSVファイルの容量が約60MBあって表計算ソフトがフリーズして全く開けない状態に陥ったのでやはり「SQLクエリ(Query)」でやることにしました。

 

 

wppの「pageviews」と「post_id」データをCSV形式でエクスポートする

wppはページがアクセスされるたびにそのデータを「wp_populardata」というテーブル(表)の「pageviews」という項目(列)に保存されているのでそちらのデータを抜き出します。

phpMyAdminにログインします。

エックスサーバーの場合はアカウントにログインして「サーバー管理」→「サーバーパネル」→「データベース」という項目にあり、そこからログインします。尚、phpMyAdminにログインするにもパスワードが必要なので詳しくはマニュアルで確認してください。

phpMyAdmin

取り出したいデータの入っているデータベースのテーブル(エクセルの表みたいなもの)でwp_popularpostdataを選択し、SQLタブを開きます。

たぶん、SELECT * FROM ‘wp_popularpostdata’ WHERE 1と命令文が記載されていると思いますが意味は以下のような感じです。

• 「SELECT」は検索して表示(抽出)するという意味
• 「FROM」はどのテーブルかを指す
• 「アスタリスク(*)」は「すべて」の意味
• 「WHELE」は「どこのレコード(行)から取得するか」という意味で「1」は「真実(true)」なので「WHERE 1」はすべての場所を意味し、実はWHEREを記載せずに実行するSELECT文と同じ結果になる

このまま実行ボタンを押すと’wp_popularpostdata’に入っているデータがすべて表示されます。

postidとpageviewsしか必要ないので命名文を消し、下記の3パターンのいずれかの方法で書いて実行ボタンを押します。※ 表示されている命名文は「クリア」でも消せます。

SELECT `postid`, `pageviews` FROM `wp_popularpostsdata`

SELECT postid, pageviews FROM wp_popularpostsdata

SELECT 
     postid,
     pageviews
FROM
     wp_popularpostsdata

意味は「wp_popularpostsdata」というテーブル(表)から「postid」と「pageviews」という列のデータを探して表示するという感じです。

入力した命令文に間違いがなければデータベースの指定したテーブル内のカラム(列)データが表示される筈です。

下部にスクロールするとクエリ結果操という項目にエクスポートという項目があるのでそちらをクリック。

エクスポート方法で「詳細 – 可能なオプションをすべて表示」、フォーマットを「CSV」、フォーマット特有のオプションを「1行目にカラム名を追加」にチェックして「実行」ボタンをクリックします。

表計算ソフトで開くとちゃんとデータを必要なデータだけを取り出せていました。

 

 

取り出したデータを別のテーブルに差し込む

ワードプレスのカスタムフィールドのデータは「wp_postmeta」というテーブルに保存されているので取り出したデータを「wp_postmeta」に差し込むんですが、テーブルの構造が異なるとエラーで差し込めないみたいなのでまずテーブルの構造を確認します。

 

インポートするテーブルの構造にする

テーブルを選択して「表示」や「構造」メニューからそのテーブルの構造を確認できます。下図は「表示」メニューです。

「wp_postmeta」はmeta_id, post_id, meta_key, meta_valueという4つの項目の列(カラム)で出来ているのが分かります。

また「wp_popularpostsdata」は「postid」と「pageviews」で出来ているのでCSVで取り出したデータを表計算ソフトで開いてテーブル構造が同じになるように修正します。

meta_id    → 新規作成
postid       → post_idに改名
meta_key  → 新規作成
pageviews → meta_valueに改名

meta_idは空のままでいいんですがmeta_keyの項目にはpost_views_countと入力してカンマ区切りCSVで保存します。

この時にUTF-8(BOMなし)にしていないと「CSV 入力のカラム数が不正です (行: 1)」などのエラーが出ることがあるようです。詳しくないですがUTF-8(BOMあり)には見えないデータが記載されているみたいなのです。

UTF-8(BOMあり)の表記方法はテキストエディタにより違いがあるのでそこは注意が必要です。

エディタによる表記の違い

メモ帳・・・UTF-8
TeraPad・・UTF-8N

表計算ソフトで保存時に文字コードを選択できない場合は、メモ帳で一旦開いてステータスバーに「UTF-8」となっていればOKです。尚、文字コードが変更できるのはWindows10 19H1以降に搭載されているメモ帳です。

もしなってなければファイルメニューから文字コードを「UTF-8」にして上書き保存します。

例では「wpp_pv_data.csv」としておきます。

 

CSV(データベース)のインポート

「wp_posts」を選択した状態で「インポート」メニューを選択し、アップロードファイルに先ほど保存した「wpp_pv_data.csv」を指定すると自動的にフォーマットが「CSV」となります。

ほかにいろいろと項目がありますが下図のようになっていればそのほかの項目はデフォルトのままでOKだと思います。

特にフォーマット特有のオプションにある「Update data when duplicate keys found on import (add ON DUPLICATE KEY UPDATE)」は重複するデータがあった場合には上書きする項目みたいなのでチェックを入れずに行いました。

データを差し込むとpost_idは重複する可能性があり、重複した場合にはmeta_idに自動的に番号が振り分けられるようです。

「インポートは正常に終了しました。」となればOKです。

ちゃんと書き込まれているか確認したところ、重複するpost_idには上書きされずに自動的にmeta_idが割り当てられて問題なくインポートできているみたいです。

データの移行後にちゃんと人気ランキングが表示されていました。