【PHP×MySQL】ランキングナビを自作しました

ブログ

市場が開いている日は日本株の投資成績を毎日投稿するようにしています。見に来てくださる方はいるのですが、そのページだけを見て終わる(直帰)パターンが多いです。
関連記事も表示していますが投資成績の記事をランダムに見せているだけなので、あまり役立っていません。
「暴落した日、暴騰した日がランキング形式で見れたらおもしろいんじゃね?」ってことで自作してみました。

汎用性が高いとは言えないですが、ログがてら記事にします。
もし、わからない、わかりにくい、うまくいかない等ありましたらコメントください。

実現したこと

  • 前営業日比の増減金額トップ3とワースト3へのリンクを含んだナビゲーション
  • 7日以内の記事がランクインしていたら「NEW!」マークを表示
  • 見ている記事がランクインしている場合はリンクのタイトルを変更
ランキングナビ(PC表示)
ランキングナビ(PC表示)
ランキングナビ(スマホ表示)
ランキングナビ(スマホ表示)
ランキングナビ(NEWアイコン、閲覧中記事)
ランキングナビ(NEWアイコン、閲覧中記事)
  • PHPのバージョン:PHP7.2.17
  • MySQLのバージョン:MySQL5.7

制作の流れ

ランキングナビ部分の制作は以下のような流れで行いました。

MySQLでランキングナビ用のテーブルを作成
 ↓
HTML、CSSでデザインを作成
 ↓
上記HTMLを生成するPHPを作成

実装の方法

今回の作業は若干難易度が高いです。危機管理なしに「ガンガンいこうぜ」で突き進まないようにお願いします。

以下の作業を行っていますので、万が一のミスがあると取り返しがつかなくなることがあります。有事の際に元に戻せるように作業前にバックアップを取ってください。
・テーマの基幹となるfunctions.phpに関わる変更
・ワードプレスの投稿内容を格納しているMySQLのテーブルからのデータ取得

MySQLでランキングナビ用のテーブルを作成

MySQL
まずはランキングナビ表示のためのデータをMySQLで用意します。
以下の項目を持ったテーブルを作成しています。

  • post_name
    URLのスラッグ部分。日本株の投資成績は「stock-jp-XXXXXX」と日付を使っているのでプライマリーキーとしても利用。
  • date
    投稿の日付。
  • title
    投稿のタイトル。タイトルの最後につけている「[日本株成績XXXXXX]」は入れない。
  • post_content
    投稿の中身。処理が終わったらNULLにしている。
  • day_change
    前営業日比の増減金額。
  • continuous_days
    連騰、続落の連続日数。今回のランキングナビには利用していない。
  • weather
    投資結果の天気(晴=1、晴ときどき曇=2、曇=3、雨=4、雷雨=5)。今回のランキングナビには利用していない。
  • is_new
    投稿が1週間以内かのフラグ。NEWマークの表示に利用。

MySQLの操作はエックスサーバーのサーバーパネルから利用できるphpMyAdminを利用しました。

phpMyAdminでのMySQLコマンド実行方法
phpMyAdminでのMySQLコマンド実行方法

SQLタブに以下のコマンドを入力し、実行するとday_change、continuous_days、weatherを除く項目が入ったテーブルが作成できます。
今回はwp_stock_jpという名前のテーブルを作成しています。

<MySQLコマンド>

CREATE TABLE wp_stock_jp (post_name varchar(200) NOT NULL PRIMARY KEY, date varchar(8), title text,
  post_content longtext, day_change int(7) DEFAULT 0, continuous_days int(2), weather int(1), is_new int(1) DEFAULT 0);	#①
INSERT IGNORE INTO wp_stock_jp (post_name, title, post_content) select post_name, post_title,
  post_content FROM wp_posts WHERE post_status = 'publish' AND post_name like 'stock-jp-%';	#②
UPDATE wp_stock_jp SET title=LEFT(title, CHAR_LENGTH(title)-13) WHERE post_content IS NOT null;	#③
UPDATE wp_stock_jp SET date=CONCAT("20",RIGHT(post_name, 6)) WHERE post_content IS NOT null;	#④
UPDATE wp_stock_jp SET post_content = NULL;	#⑤
UPDATE wp_stock_jp SET	#⑥
  is_new =
    CASE
        WHEN date > CURRENT_TIMESTAMP() - INTERVAL 7 DAY THEN 1
        ELSE 0
    END;

それぞれのコマンドで以下の処理を行っています。

  1. 先ほど挙げた項目を持ったテーブルを作成
  2. 作成したテーブルにワードプレスの投稿情報が入ったテーブルwp_postsから日本株投資成績の投稿を抽出して挿入
    (post_statusがpublishでスラッグがstock-jp-xxxxxxとなっているレコードを抽出)
  3. 投稿タイトルから「[日本株成績XXXXXX]」の部分を削除
    (全体の文字数-13を左側から抽出)
  4. スラッグstock-jp-XXXXXXから最後の6桁を抽出し、年を2019とするために20を頭に追加
  5. 処理が終わったのでpost_contentをNULLに変更
  6. dataと現在の日付を比較し、7日以内の場合は1、それ以外は0を入力

そして機械的には取得できないday_change、continuous_days、weatherは手入力を行いました。(各投稿を見ながら60弱のレコードを30分かけて入力しました。。)

ランキングナビ用テーブル完成イメージ
ランキングナビ用テーブル完成イメージ

HTML、CSSでデザインを作成

HTML CSS
ランキングナビをどのようなデザインにするかは結構時間がかかりました。2カラムにするコードはCocoonで用意されたものを使っています。

<HTMLコード>

<div class="ranking">
  <div class="rank-title">暴騰&amp;暴落日ランキング</div><div class="rank-date"><small><small>(期間:2019/6/24~)</small></small></div>

  <div class="ranking-contents">
    <div class="wp-block-cocoon-blocks-column-2 column-wrap column-2 column-2-2-1-1 layout-box">
      <div class="wp-block-cocoon-blocks-column-left column-left">
        <ul class="rank-top">
          <li><a href="/XXXXXX/" id="rank-top1"><div class="rank-num">第1位 +XXXXXX</div><div class="rank-date">2019/X/X</div>XXXXX</a></li>
          <li><a href="/XXXXXX/" id="rank-top2"><div class="rank-num">第2位 +XXXXXX</div><div class="rank-date">2019/X/X</div>XXXXX</a></li>
          <li><a href="/XXXXXX/" id="rank-top3"><div class="rank-num">第3位 +XXXXXX</div><div class="rank-date">2019/X/X</div>XXXXX</a></li>
        </ul>
      </div>
      <div class="wp-block-cocoon-blocks-column-right column-right">
        <ul class="rank-bottom">
          <li><a href="/XXXXX/" id="rank-bottom1"><div class="rank-num">第XX位 -XXXXX</div><div class="rank-date">2019/X/X</div>XXXXX</a></li>
          <li><a href="/XXXXX/" id="rank-bottom2"><div class="rank-num">第XX位 -XXXXX</div><div class="rank-date">2019/X/X</div>XXXXX</a></li>
          <li><a href="/XXXXX/" id="rank-bottom3"><div class="rank-num">第XX位 -XXXXX</div><div class="rank-date">2019/X/X</div>XXXXX</a></li>
        </ul>
      </div>
    </div>
  </div>
</div>

<CSSコード>

/*---------------------------------
ランキングナビ
--------------------------------*/
.ranking{
  padding: 2px 8px;
  background: #F5DEB3;
}

.ranking-contents{
  font-size: small;
  font-weight: bold;
}

.ranking ul {
  -webkit-flex-direction: column;
  flex-direction: column;
  list-style-type: none;
  margin: 0;
  padding: 0;
}

.rank-top li a{
  display: block;
  margin: 5px 0px 12px;
  padding: 0.8em;
  background: #FFF;
  border-left: solid 10px #009933;
  color: #000;
  box-shadow: 0px 3px 3px rgba(0, 0, 0, 0.3);
  background-image: linear-gradient(to right, rgba(0,0,0,0) 50%, #009933 50%);
  background-size: 200% auto;
  background-position: 0 0;
  transition: all .3s ease 0s;
  text-decoration: none;
}

.rank-top li a:hover {
  cursor: pointer;
  background-position: -100% 0;
  color: #fff;
}

.rank-bottom li a{
  display: block;
  margin: 5px 0px 12px;
  padding: 0.8em;
  background: #FFF;
  border-left: solid 10px #ff333a;
  color: #000;
  box-shadow: 0px 3px 3px rgba(0, 0, 0, 0.3);
  background-image: linear-gradient(to right, rgba(0,0,0,0) 50%, #ff333a 50%);
  background-size: 200% auto;
  background-position: 0 0;
  transition: all .3s ease 0s;
  text-decoration: none;
}

.rank-bottom li a:hover {
  cursor: pointer;
  background-position: -100% 0;
  color: #fff;
}

.rank-title{
  font-weight: bold;
  float: left;
}

.rank-num{
  float: left;
  /*maker blue*/
  background: linear-gradient(transparent 60%, #a8dafb 60%);
}

.rank-date{
  text-align: right;
}

.rank-up-color {
  color: #009933;
}

.rank-down-color {
  color: #ff333a;
}
  • ボタンの縦の配置はフレックスボックスで行っています。
  • .rank-title、.rank-numにfloat: leftを付与し、.rank-dateにtext-align: rightを付与することで1行で左寄せと右寄せを混在させています。

HTMLを生成するPHPを作成

PHP
上記HTMLを作成するスクリプトをPHPで作成します。ランキングの部分はMySQLのテーブルから情報を引っ張ってきます。
MySQLとの接続はPDOで実現する気満々でしたが、ワードプレスでデータベースへのアクセスのために用意されたクラス $wpdbがあることを知ったのでそちらをありがたく使わせてもらいました。

下準備として今回作成したwp_stock_jpをwp-db.phpに追記する作業が必要になります。以下の記事を参考に追記しました。

PHPファイルは以下を作成しました。

<ranking.php>

<?php

//トップ用code作成
$code_top = '<ul class="rank-top">';

  $num = 1;

  //day_changeの上位3レコードを取得(結果は配列に格納される)
  $results = $wpdb->get_results("SELECT * FROM $wpdb->stock_jp ORDER BY day_change DESC LIMIT 3");

  foreach ( $results as $value ) {
    //ランキングに7日以内の記事が入っているか    
    if ($value->is_new) {
      $new_mark = '<span class="rank-up-color">NEW!</span> ';
    }else {
      $new_mark = '';
    }

    //ランキングに閲覧中の記事が入っているか
    if ($value->post_name === substr($_SERVER['REQUEST_URI'], 14, 15)) {
      $title = '<span class="rank-up-color">本記事です</span>';
    }else {
      $title = $value->title;
    }

    $code_top = $code_top . '<li><a href="' . '/result/stock/' . $value->post_name . '/" id="rank-top' . $num . '"><div class="rank-num">第' . $num . '位 +' . number_format($value->day_change) . '</div><div class="rank-date">' . $new_mark . date('Y/n/j', strtotime($value->date)) . '</div>';
    $code_top = $code_top . $title . '</a></li>';
    $num++;
  }

$code_top = $code_top . '</ul>';

//ワースト用code作成
$code_bottom = '<ul class="rank-bottom">';

  $num = 1;
  //順位用にレコード数を取得
  $num_max = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->stock_jp");

  $results = $wpdb->get_results("SELECT * FROM $wpdb->stock_jp ORDER BY day_change ASC LIMIT 3");

  foreach ( $results as $value ) {
    if ($value->is_new) {
      $new_mark = '<span class="rank-down-color">NEW!</span> ';
    }else {
      $new_mark = '';
    }

    if ($value->post_name === substr($_SERVER['REQUEST_URI'], 14, 15)) {
      $title = '<span class="rank-down-color">本記事です</span>';
    }else {
      $title = $value->title;
    }

    //idは最下位からrank-bottom1~3を付与
    $code_bottom = $code_bottom . '<li><a href="' . '/result/stock/' . $value->post_name . '/" id="rank-bottom' . $num . '"><div class="rank-num">第' . $num_max . '位 ' . number_format($value->day_change) . '</div><div class="rank-date">'  . $new_mark . date('Y/n/j', strtotime($value->date)) . '</div>';
    $code_bottom = $code_bottom . $title . '</a></li>';
    $num++;
    $num_max--;
  }

$code_bottom = $code_bottom . '</ul>';

//htmlコードを生成
echo<<<eof
<div class="ranking"><div class="rank-title">暴騰&amp;暴落日ランキング</div><div class="rank-date"><small><small>(期間:2019/6/24~)</small></small></div>
<div class="ranking-contents">
<!-- cocoonレイアウト2カラムを使用 -->
<div class="wp-block-cocoon-blocks-column-2 column-wrap column-2 column-2-2-1-1 layout-box"><!-- wp:cocoon-blocks/column-left -->
<div class="wp-block-cocoon-blocks-column-left column-left">

$code_top

<!-- /wp:cocoon-blocks/column-left --></div>
<!-- wp:cocoon-blocks/column-right -->
<div class="wp-block-cocoon-blocks-column-right column-right">

$code_bottom

<!-- /wp:cocoon-blocks/column-right --></div>
</div>
</div></div>

eof;
?>

今回は関係ありませんが、MySQLからデータを取ってくる際に第三者に入力された値を使う場合はprepare()を使ったエスケープ処理が必要になります。

7日以内の投稿の場合は「NEW!」アイコンを表示、閲覧中の記事のタイトルは「本記事です」を表示するようにしています。

ショートコードとして登録し、設置

ワードプレス
htmlを生成するphpファイルranking.phpが用意できたので、ranking.phpを実行するショートコードを登録します。そのショートコードをお好みの位置に設置すれば実装完了です。
ショートコードの登録については、以下の記事で説明しています。設置場所も同じく「投稿本文下」のウィジェットとしました。

function.phpを編集しますので万が一のためにバックアップを忘れずに取ってください。

テーブルのメンテナンス

実装についての説明は終わりですが、今回作成するランキングナビは投稿するたびにテーブルへのレコードの追加が必要になります。

メンテナンスを楽にするために変更したこと

実装の部分で書きましたが、テーブルの項目は「MySQLコマンドで抽出したもの」と「手入力で対応したもの」がありました。具体的に言うと、以下の3つの項目は手入力での対応としていました。

  • day_change
    前営業日比の増減金額。
  • continuous_days
    連騰、続落の連続日数。今回のランキングナビには利用していない。
  • weather
    投資結果の天気(晴=1、晴ときどき曇=2、曇=3、雨=4、雷雨=5)。今回のランキングナビには利用していない。

なるべく手入力での対応をなくそうを知恵を絞り、day_changeとweatherについては手入力をなくすことに成功しました。

day_change

前日比の増減金額の部分にday-change-valueというidを追加しました。

weather

天気入力用のショートコードを作成し、引数として天気の数字を使用するように変更しました。(例:[weather 1])
ショートコードの記載は投稿内容にそのまま記載されているので、引数の数字を抽出してテーブルに格納します。

function weather($num){
  ob_start();

  if ($num[0] == 1) {
    $text = '晴';
  } elseif ($num[0] == 2) {
    $text = '晴ときどき曇';
  } elseif ($num[0] == 3) {
    $text = '曇';
  } elseif ($num[0] == 4) {
    $text = '雨';
  } elseif ($num[0] == 5) {
    $text = '雷雨';
  }
  
  echo '<span id="weather">' . $text . '</span>';

  return ob_get_clean();
}
add_shortcode('weather', 'weather');

別ファイルではなく、function.php内に実行コードを記載しました。

メンテナンス用MySQLコマンド

レコードの追加およびcontinuous_daysを除く項目の入力までを実行するコマンドが以下になります。日本株投資成績の記事を投稿後、コマンドを実行します。

<MySQLコマンド>

INSERT IGNORE INTO wp_stock_jp (post_name, title, post_content) select post_name, post_title, post_content
  FROM wp_posts WHERE post_status = 'publish' AND post_name like 'stock-jp-%';	#①
UPDATE wp_stock_jp SET title=LEFT(title, CHAR_LENGTH(title)-13) WHERE post_content IS NOT null;	#②
UPDATE wp_stock_jp SET date=CONCAT("20",RIGHT(post_name, 6)) WHERE post_content IS NOT null;	#③
UPDATE wp_stock_jp SET weather = SUBSTRING(post_content, LOCATE("weather ", post_content) + CHAR_LENGTH("weather "), 1)
  WHERE post_content IS NOT null;	#④ ※実際のコードではweatherの左側に[がつく(ショートコードが実行されるため除去)
UPDATE wp_stock_jp SET post_content = REPLACE(post_content, ",", "") WHERE post_content IS NOT null;	#⑤
UPDATE wp_stock_jp SET day_change =
  SUBSTRING(post_content, LOCATE("day-change-value\">", post_content) + CHAR_LENGTH("day-change-value\">"),
    LOCATE("<", post_content , LOCATE("day-change-value\">", post_content))-LOCATE("day-change-value\">", post_content) + CHAR_LENGTH("day-change-value\">"))
  WHERE post_content IS NOT null;	#⑥
UPDATE wp_stock_jp SET post_content = NULL;	#⑦
UPDATE wp_stock_jp SET	#⑧
  is_new =
    CASE
        WHEN date > CURRENT_TIMESTAMP() - INTERVAL 7 DAY THEN 1
        ELSE 0
    END;

それぞれのコマンドで以下の処理を行っています。

  1. wp_postsテーブルにpost_nameが重複しないレコードがあれば挿入
  2. 挿入したレコードの投稿タイトルから「[日本株成績XXXXXX]」の部分を削除
    ※WHERE post_content IS NOT nullを入れることで既存のレコードには実行されない
  3. スラッグstock-jp-XXXXXXから最後の6桁を抽出し、年を2019とするために20を頭に追加
  4. weatherのショートコードの記載から数字を抽出
    (「[weather 」の次の文字を抽出)
  5. 前日比の増減金額から「,」を取るために投稿全体から「,」を削除
    ※「,」付きでは抽出がうまくいかなかった
  6. 前日比の増減金額を抽出
    (「day-change-value”>」とその次に出てくる「<」の間を抽出)
  7. 処理が終わったのでpost_contentをNULLに変更
  8. dataと現在の日付を比較し、7日以内の場合は1、それ以外は0を入力

参考サイト

■CASE式で条件分岐をSQL文に任せる
  https://qiita.com/sfp_waterwalker/items/acc7f95f6ab5aa5412f3
■【CSS】今更ながら、フレックスボックスの使い方をまとめてみた。
  https://www.plusdesign.co.jp/blog/?p=8747
■ホバー時に背景が左からスライド
 https://haniwaman.com/parts/parts-4117/

あとがき

初めてのMySQLを使ったプラグラムでしたが何とか完成させることができました。ドはまりポイントはこれと言ってありませんでしたが、私にしては大物でしたので時間がかかりました。

MySQLについては以下のサイトで勉強しました。MySQL以外にもいろいろな言語が学べるので英語の読みが多少できる人にはお勧めです。

W3Schools Online Web Tutorials

以下は実現したかったが技術的に実現できなかった部分なので今後の課題です。

  • ランキングナビ用のDBをワードプレス用のDBと分けて作り、wpdbを利用してアクセスする
  • MySQLのユーザーを追加し、ワードプレス用のDBにはread権限のみ、ランキングナビ用のDBにはread/write権限をつける
  • 連騰日数、続落日数をMySQLのコマンドで入力する
    9/28追記 実現しました!
          【MySQL】前のレコードからデータ取得(OFFSET句)
  • メンテナンス用のMySQLのコマンドをワードプレスの画面から実行できるようにする

皆さん、せっかく作ったランキングナビを活用してもらえると嬉しいです。(暴騰、暴落の規模が小さくてすみません。。)

コメント

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