【MYSQL】【PHP】2番目、3番目のデータを取得する方法について【初心者】

Question

現在、プログラムの勉強をしている初心者です。
勉強用に作成しているサイトにて解決できない問題が発生してしまいました。

---

【環境】

サーバ： Localhost via UNIX socket
サーバの種類： MySQL
サーバのバージョン： 5.6.38 - MySQL Community Server (GPL)
プロトコル バージョン: 10
サーバの文字セット: UTF-8 Unicode (utf8)
Apache
データベースクライアントのバージョン： libmysql - mysqlnd 5.0.11-dev
PHP 拡張: mysqliドキュメント curlドキュメント mbstringドキュメント
PHP のバージョン: 5.6.33
phpmyadmin:バージョン情報： 4.7.7 (最新版)

---

【実現したい事】

個人サイトによくあります
「逆アクセスランキング上位4サイトの記事をそれぞれ1つずつ紹介」というものを実現したいのですが
MYSQLのデータベースから上手く抽出する事が出来ませんでした。

例：

説明：
画像Aにアクセスランキング1位のサイトで5日間集計で1番クリック数が多い記事を紹介
画像Bにアクセスランキング2位のサイトの5日間集計で1番クリック数が多い記事を紹介
画像Cにアクセスランキング3位のサイトの5日間集計で1番クリック数が多い記事を紹介
画像Dにアクセスランキング4位のサイトの5日間集計で1番クリック数が多い記事を紹介

---

【やってみた事】

matome(テーブル名)

id
title
url
imgurl
date
name(ブログ名が格納されています)
pvcount:int型(クリック数が格納されています)

site(テーブル名)

name(ブログ名が格納されています)
henkan:int型(他ブログへの返還率が入っています。この値が小さい程アクセスランキングが上位になります。)

---

【MYSQL:データベースに入っている内容】

matome
id:1
title:記事タイトルA
url: https://testA.com
date:2018-02-07
name:サイト名Ａ
pvcount:10

id:2
title:記事タイトルB
url: https://testB.com
date:2018-02-05
name:サイト名B
pvcount:20

id:3
title:記事タイトルC
url: https://testC.com
date:2018-02-01
name:サイト名C
pvcount:30

id:4
title:記事タイトルD
url: https://testD.com
date:2018-02-02
name:サイト名D
pvcount:10


site
name:記事タイトルA
henkan:10

name:記事タイトルB
henkan:9

name:記事タイトルC
henkan:8

name:記事タイトルD
henkan:7

【条件：site.henkanが小さい順番に4つのサイトから
それぞれ1つずつ5日間以内の記事でmatome.pvcountの値が大きい順番に抽出】

$stmt = $db->prepare("select matome.id,matome.title,matome.url,
matome.imgurl,matome.date,matome.name,
matome.pvcount,
site.henkan
from matome left join site on matome.name=site.name
where date > CURRENT_TIMESTAMP + INTERVAL - 5 DAY
order by site.henkan, matome.pvcount desc limit 4");
$stmt->execute();
$mysql = $stmt->fetchAll(\PDO::FETCH_OBJ);

【実行結果】
site.henkanが1番目に小さいサイトから5日間以内の記事で
matome.pvcountの値が大きい順番に4つの記事が抽出されていました。

理想としては
site.henkanが1番目に小さいサイトの抽出は成功する事が出来たので
site.henkanが2番目,3番目,4番目に小さいサイトの抽出が出来れば
「html」にて
site.henkanが1番目に小さいサイトから5日間以内の記事でmatome.pvcountが1番多い1つの記事を抽出
site.henkanが2番目に小さいサイトから5日間以内の記事でmatome.pvcountが1番多い1つの記事を抽出
site.henkanが3番目に小さいサイトから5日間以内の記事でmatome.pvcountが1番多い1つの記事を抽出
site.henkanが4番目に小さいサイトから5日間以内の記事でmatome.pvcountが1番多い1つの記事を抽出

という風に並べると
「逆アクセスランキングの順番に4つのサイトからそれぞれ1つの記事を紹介」というのが実現できると
考えているのですが

site.henkanが2番目に小さいサイトから5日間以内の記事でmatome.pvcountが1番多い1つの記事を抽出
site.henkanが3番目に小さいサイトから5日間以内の記事でmatome.pvcountが1番多い1つの記事を抽出
site.henkanが4番目に小さいサイトから5日間以内の記事でmatome.pvcountが1番多い1つの記事を抽出

というのが私自身の力不足の為に実現する事が出来ませんでした。

解決方法をご存知の方や
何か気になる点がある方がいらっしゃいましたら
お力をお貸し頂けると嬉しいです。

どうかよろしくお願いいたします

Answer 1

DBのSQL

CREATE TABLE IF NOT EXISTS `matome` (
  `id` int(11) DEFAULT NULL,
  `title` varchar(50) DEFAULT NULL,
  `url` varchar(50) DEFAULT NULL,
  `imgurl` varchar(50) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `pvcount` int(11) DEFAULT NULL
);

INSERT INTO `matome` (`id`, `title`, `url`, `imgurl`, `date`, `name`, `pvcount`) VALUES
	(1, 'A', 'a.com', NULL, '2018-02-07', 'A', 10),
	(2, 'B', 'b.com', NULL, '2018-02-05', 'B', 20),
	(3, 'C', 'c.com', NULL, '2018-02-01', 'C', 30),
	(4, 'D', 'd.com', NULL, '2018-02-02', 'D', 10);

CREATE TABLE IF NOT EXISTS `site` (
  `name` varchar(50) DEFAULT NULL,
  `henkan` int(11) DEFAULT NULL
);

INSERT INTO `site` (`name`, `henkan`) VALUES
	('A', 10),
	('B', 9),
	('C', 8),
	('D', 7);

サンプルプログラム

<?php
$db = new PDO('mysql:host=127.0.0.1;dbname=my_db;charset=utf8', 'user', 'password');

$stmt = $db->prepare("select name from site order by henkan");
$stmt->execute();
$henkans = $stmt->fetchAll(\PDO::FETCH_OBJ);

$results = [];

foreach($henkans as $henkan) {
    
    $stmt = $db->prepare("select * from matome where name = ? order by pvcount desc limit 1");
    $stmt->bindValue(1, $henkan->name, PDO::PARAM_STR);
    $stmt->execute();
    $result = $stmt->fetchAll(\PDO::FETCH_OBJ);

    array_push($results, $result[0]);
}
?>
<table border=1>
    <?php foreach($results as $x): ?>
    <tr>
        <td><?=$x->title?></td>
        <td><?=$x->url?></td>
        <td><?=$x->date?></td>
        <td><?=$x->name?></td>
        <td><?=$x->pvcount?></td>
    </tr>
    <?php endforeach; ?>
</table>

実行結果

Comments

[shimane]

回答有難うございます！

このように考えて
仕組みを作っていくんですね。

プログラムの勉強は難しいことばかりで
自分一人では解決する事が出来ないことも多くて悩んだり
立ち止まっている時間が長くなってしまうので

今回の教えて頂いた事が本当に勉強になりました。
大感謝です！