Q&A
テーブル同士のリレーションがわからないと、誰も答えられないのでは？ テーブルのカラムのデータ型もわからないし、主キーや外部キーもわからない。
##解決したいこと
データベースから任意のデータを抽出したい
##背景
以下の選手のデータ詳細を表示させるために、取り組み中です。
「点数(何点目か)」「試合日時」「対戦相手」「ゴールタイム」
以下のように与えられたデータベースからどのようにデータを抽出すればよろしいでしょうか？
leftjoinとかinnerjoinとか勉強してみましたが、複雑にデータが絡み合ってよくわからなくなりました。
データベース
countriesテーブル
goalsテーブル
goals_tmpテーブル
pairingsテーブル
pairings_tmpテーブル
playersテーブル
##完成予想図
ここに選手の得点履歴として、「点数(何点目か)」「試合日時」「対戦相手」「ゴールタイム」を追加したい。
このsql文で上記画像が実装できている
Player.php
public function findById($id = 0):Array { $sql = 'SELECT c.name AS c_name, p.id, p.uniform_num, p.position, p.name, p.club, p.birth, p.height, p.weight FROM players p INNER JOIN countries c ON c.id = p.country_id WHERE p.id = :id'; $sql_score = 'SELECT ( SELECT COUNT(1) + 1 FROM goals g INNER JOIN pairings p ON g.pairing_id = p.id WHERE goals.player_id = g.player_id AND ( ( goals.goal_time > g.goal_time AND pairings.kickoff = p.kickoff ) OR ( pairings.kickoff > p.kickoff ) ) ) AS 点数 , pairings.kickoff AS 試合日時 , countries.name AS 対戦相手 , goals.goal_time AS ゴールタイム FROM players INNER JOIN goals ON players.id = goals.player_id INNER JOIN pairings ON goals.pairing_id = pairings.id INNER JOIN countries ON pairings.enemy_country_id = countries.id WHERE players.id = :id ORDER BY pairings.kickoff , goals.goal_time'; $sth = $this->dbh->prepare($sql); $sth_score = $this->dbh->prepare($sql_score); $sth->bindParam(':id', $id, PDO::PARAM_INT); $sth_score->bindParam(':id', $id, PDO::PARAM_INT); $sth->execute(); $sth_score->execute(); $result = $sth->fetch(PDO::FETCH_ASSOC); $result_score = $sth_score->fetch(PDO::FETCH_ASSOC); return array($result,$result_score); }
PlayerControler.php
<?php require_once(ROOT_PATH .'/Models/Player.php'); class PlayerController { private $request; private $player; public function __construct() { $this->request['get'] = $_GET; $this->request['post'] = $_POST; $this->Player = new Player(); } public function index() { $page = 0; if(isset($this->request['get']['page'])) { $page = $this->request['get']['page']; } $players = $this->Player->findAll($page); $players_count = $this->Player->countAll(); $params = [ 'players' => $players, 'pages' => $players_count / 20 ]; return $params; } public function view() { if(empty($this->request['get']['id'])) { echo '指定のパラメーターが不正です。このページを表示できません。'; exit; } $player = $this->Player->findById($this->request['get']['id']); $params = [ 'player' => $player ]; return $params; } }
show.php
<?php ini_set('display_errors', "On"); require_once(ROOT_PATH .'Controllers/PlayerController.php'); require_once(ROOT_PATH .'/Models/Player.php'); $player = new PlayerController(); $params = $player->view(); var_dump($params); ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <link rel="stylesheet" href="/css/base.css"> <title>worldcup</title> </head> <body> <h2>選手詳細</h2> <table class="show-table"> <?php foreach($params['player'] as $player): ?> <tr> <th>No</th> <td><?=$player['id'] ?></td> </tr> <tr> <th>背番号</th> <td><?=$player['uniform_num'] ?></td> </tr> <tr> <th>ポジション</th> <td><?=$player['position'] ?></td> </tr> <tr> <th>名前</th> <td><?=$player['name'] ?></td> </tr> <tr> <th>所属</th> <td><?=$player['club'] ?></td> </tr> <tr> <th>誕生日</th> <td><?=$player['birth'] ?></td> </tr> <tr> <th>身長</th> <td><?=$player['height'] ?>cm</td> </tr> <tr> <th>体重</th> <td><?=$player['weight'] ?>kg</td> </tr> <tr> <th>所属国</th> <td><?=$player['c_name'] ?></td> </tr> <tr> <th>得点</th> <td><?=$player['点数'] ?></td> </tr> <?php endforeach; ?> </table> <div class="ed-btn"> <div class="edit"><a href="show.php?id=<?php echo $player['id'] ?>">編集</a></div> <div class="delete"><a href="show.php?id=<?php echo $player['id'] ?>">削除</a></div> </div> <br> <div class="top"> <a href="index.php">トップへ戻る</a> </div> </body> </html>
得点表示
選手詳細はうまく表示できるのですが、得点が表示できずに困っています。
var_dumpでも得点部分がbool(false)返ってきます。
まず、思ったように組んでみてください。
サンプルデータと、完成予想図もあったほうがいいです。
いつもご回答いただきましてありがとうございます。
回答1件
0
ベストアンサー
sql
1SELECT 2 tmp.score AS 点数 3 , tmp.kickoff AS 試合日時 4 , tmp.enemy_name AS 対戦相手 5 , tmp.goal_time AS ゴールタイム 6FROM 7 ( 8 SELECT 9 SUM(1) OVER (PARTITION BY pairings.id ORDER BY goals.goal_time) AS score 10 , pairings.kickoff 11 , enemy_country.name AS enemy_name 12 , goals.goal_time 13 , goals.player_id 14 FROM 15 players 16 INNER JOIN countries my_country 17 ON players.country_id = my_country.id 18 INNER JOIN pairings 19 ON my_country.id = pairings.my_country_id 20 INNER JOIN goals 21 ON pairings.id = goals.pairing_id 22 INNER JOIN countries enemy_country 23 ON pairings.enemy_country_id = enemy_country.id 24 WHERE 25 players.id = 対象プレーヤーのID 26 ) tmp 27WHERE 28 tmp.player_id = 対象プレーヤーのID 29ORDER BY 30 tmp.kickoff 31 , tmp.goal_time
デーブルの関係など詳細が分からないのでもろもろエスパーですが、こんな感じでしょうか。
Window関数を使用しているので、MySQL8.0からしか動きません。
追記
上記の「点数」は「1試合内の何点目か」を出していました。
「該当選手の生涯で何点目か」であれば、下記のような感じです。
sql
1SELECT 2 ( 3 SELECT 4 COUNT(1) + 1 5 FROM 6 goals g 7 INNER JOIN pairings p 8 ON g.pairing_id = p.id 9 WHERE 10 goals.player_id = g.player_id 11 AND ( 12 ( 13 goals.goal_time > g.goal_time 14 AND pairings.kickoff = p.kickoff 15 ) OR ( 16 pairings.kickoff > p.kickoff 17 ) 18 ) 19 ) AS 点数 20 , pairings.kickoff AS 試合日時 21 , countries.name AS 対戦相手 22 , goals.goal_time AS ゴールタイム 23FROM 24 players 25 INNER JOIN goals 26 ON players.id = goals.player_id 27 INNER JOIN pairings 28 ON goals.pairing_id = pairings.id 29 INNER JOIN countries 30 ON pairings.enemy_country_id = countries.id 31WHERE 32 players.id = 対象プレーヤーのID 33ORDER BY 34 pairings.kickoff 35 , goals.goal_time
投稿2021/03/04 11:53
編集2021/03/04 15:00
総合スコア144
コードをわざわざご教示いただきましてありがとうございます。上記の下のコードで実装する際に、すでに選手名や体重などに加えて得点履歴を表示させるためには、どのように記述すればいいのでしょうか？
'SELECT c.name AS c_name, p.id, p.uniform_num, p.position, p.name, p.club, p.birth, p.height, p.weight
FROM players p
INNER JOIN countries c
ON c.id = p.country_id
(SELECT COUNT(1) + 1 FROM goals g
WHERE goals.player_id = g.player_id
AND goals.goal_time > g.goal_time) AS 点数, pairings.kickoff AS 試合日時, countries.name AS 対戦相手, goals.goal_time AS ゴールタイム
FROM players
INNER JOIN goals
ON players.id = goals.player_id
INNER JOIN pairings
ON goals.pairing_id = pairings.id
INNER JOIN countries
ON pairings.enemy_country_id = countries.id
WHERE
players.id = :id
ORDER BY pairings.kickoff, goals.goal_time';
選手のデータだけの場合と、ゴールの履歴データとは持ってきたいデータの粒度が違うので、私ならSQLを2回発行してそれぞれ別に取得します。
どうしても1つのSQLで持ってきたいのであれば、私の回答のSELECT句にplayersテーブルの必要な列を追加してください。
INNER JOIN countries my_country
ON pairings.my_country_id = my_country.id
とFROM句を追加すれば自国の名前のデータも持ってこれます。
sqlを2回発行して取得！！？それもわからないぐらい無知なのですが、例えば方法として、
$sql = 選手のデータ;
$sql2 = 得点履歴のデータ;
$sth = $this->dbh->prepare($sql);
$sth2 = $this->dbh->prepare($sql2);
$sth->bindParam(':id', $id, PDO::PARAM_INT);
$sth2->bindParam(':id', $id, PDO::PARAM_INT);
$sth->execute();
$sth2->execute();
ってことですか？？
はい、そういうことです。
無理に全てのデータを1回で取得する必要は無いです。
コメント書いててSQLにまた不具合を見つけたので修正しました。
申し訳ないです。
ご回答いただきましてありがとうございます。sqlを2回発行する方式でコードを質問文に追記したように書いたのですが、うまくできずに困っています。
bool(false)がどうしても返ってきてしまうのですが、どのように対応すればいいのでしょうか？？
あなたの回答
tips
プレビュー
