The query below is ment for searching old games from specific player and ratingchanges from both players. Ratingchanges are stored in own table called "ratingverloop", because they need to be saved for trending of ratingchanges, while too old games will be deleted after a certain time. A lot of times, data of ratingchanges are not correct.
Displaying the resultset to screen, showed that not always playerIDs of whitePlayer and blackPlayer were used. Sometimes same was used twice.
Checking data in MySQL itself, revealed my problem: Fore the same gameID, ratingchanges are stored at least twice. For both players, theire own row. (used for showing graphic of ratingchanges in time). And sometimes more in case of a corrected/reset of a game.
I have joined table ratingverloop twice, each for one player. (rv1,rv2).
But how can I be sure to get the right row? I just cannot find any simular examples om how use a left join for this. (should I use it here anyways, or is there another solution?)
$sql = "SELECT games.gameID
,whitePlayer
,blackPlayer
,gameMessage
,messageFrom
,date_format(dateCreated , '%d-%m-%Y') AS startdatum
,lastMove
,date_format(lastMove , '%d-%m-%Y') AS lastMovex
,whiteNick
,blackNick
,rv1.userID AS userID1
,rv1.ratingvan AS userID1_ratingvan
,rv1.rating AS userID1_rating
,rv2.userID AS userID2
,rv2.ratingvan AS userID2_ratingvan
,rv2.rating AS userID2_rating
FROM games
LEFT JOIN ratingverloop rv1 ON games.gameID = rv1.gameID
LEFT JOIN ratingverloop rv2 ON games.gameID = rv2.gameID
WHERE
(
gameMessage <> ''
AND gameMessage <> 'playerInvited'
AND gameMessage <> 'inviteDeclined'
)
AND
(
whitePlayer = :playerID
OR blackPlayer = :playerID
)
ORDER BY lastMove DESC";
[EDIT]As asked:
CREATE TABLE `ratingverloop` (
`index` int(11) NOT NULL,
`userID` int(10) UNSIGNED NOT NULL,
`datumtijd` datetime NOT NULL,
`gameID` int(11) NOT NULL,
`ratingvan` smallint(5) UNSIGNED NOT NULL,
`rating` smallint(5) UNSIGNED NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Gegevens worden geëxporteerd voor tabel `ratingverloop`
--
INSERT INTO `ratingverloop` (`index`, `userID`, `datumtijd`, `gameID`, `ratingvan`, `rating`) VALUES
(3071747, 7144, '2023-03-03 00:46:40', 2786547, 1193, 1198),
(3071748, 2187, '2023-03-03 00:46:40', 2786547, 938, 933);
--
-- Indexen voor geëxporteerde tabellen
--
--
-- Indexen voor tabel `ratingverloop`
--
ALTER TABLE `ratingverloop`
ADD PRIMARY KEY (`index`),
ADD KEY `userID` (`userID`),
ADD KEY `gameID` (`gameID`);
Result:
(
[gameID] => 2786547
[whitePlayer] => 2187
[blackPlayer] => 7144
[gameMessage] => checkMate
[messageFrom] => black
[startdatum] => 23-01-2023
[lastMove] => 2023-03-03 00:46:40
[lastMovex] => 03-03-2023
[whiteNick] => @Gijs
[blackNick] => E-Street
[userID1] => 7144
[userID1_ratingvan] => 1193
[userID1_rating] => 1198
[userID2] => 7144
[userID2_ratingvan] => 1193
[userID2_rating] => 1198
)
Result userID1 and userID2 are from the same row.
It should be: 2187 and 7144
--
-- Tabelstructuur voor tabel `games`
--
CREATE TABLE `games` (
`gameID` int(20) UNSIGNED NOT NULL,
`whitePlayer` mediumint(9) DEFAULT NULL,
`blackPlayer` mediumint(9) DEFAULT NULL,
`gameMessage` enum('','playerInvited','inviteDeclined','draw','playerResigned','checkMate','VERLOPEN','ONGELDIG','tijdoverschrijding','ingetrokken','stalemate','zettijdoverschrijding') NOT NULL,
`messageFrom` enum('','black','white','Admin') NOT NULL,
`wit_rem_aanvraag` datetime NOT NULL,
`zwart_rem_aanvraag` datetime NOT NULL,
`rem_antw_zwart` datetime NOT NULL,
`rem_antw_wit` datetime NOT NULL,
`dateCreated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`lastMove` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`ratingset` enum('notset','set') NOT NULL DEFAULT 'notset',
`rating_white_start` int(11) DEFAULT NULL,
`rating_white_end` int(11) NOT NULL DEFAULT '0',
`rating_black_start` int(11) DEFAULT NULL,
`rating_black_end` int(11) NOT NULL DEFAULT '0',
`notitie_wit` text NOT NULL,
`notitie_zwart` text NOT NULL,
`vak_wit` date NOT NULL DEFAULT '0000-00-00',
`vak_zwart` date NOT NULL DEFAULT '0000-00-00',
`her_invite` tinyint(4) NOT NULL DEFAULT '0',
`pionpromo` tinyint(4) NOT NULL DEFAULT '0',
`beurtaan` tinytext NOT NULL,
`whiteNick` tinytext,
`blackNick` tinytext,
`kibW` enum('ja','nee') NOT NULL DEFAULT 'ja',
`kibZ` enum('ja','nee') NOT NULL DEFAULT 'ja',
`comment` tinytext NOT NULL,
`witOnline` datetime NOT NULL,
`zwartOnline` datetime NOT NULL,
`chataanvraagdoor` int(10) UNSIGNED NOT NULL,
`chataanvraagtijd` datetime NOT NULL DEFAULT '1900-00-00 00:00:00',
`ftoernooi` enum('0','1') NOT NULL DEFAULT '0',
`tafel` enum('0','1') NOT NULL DEFAULT '0',
`ratingrange` smallint(5) UNSIGNED NOT NULL DEFAULT '500',
`speeltijd` mediumint(3) UNSIGNED DEFAULT NULL,
`speeltijdwit` mediumint(8) UNSIGNED NOT NULL,
`speeltijdzwart` mediumint(8) UNSIGNED NOT NULL,
`maxzettijd` time NOT NULL DEFAULT '168:00:00',
`startklok` datetime NOT NULL,
`opener` mediumint(8) UNSIGNED DEFAULT NULL,
`ts_ladder` tinyint(3) UNSIGNED DEFAULT NULL COMMENT 'teamschaken laddertoernooi',
`t_partij` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
`tt` tinyint(3) UNSIGNED NOT NULL COMMENT 'teamtoernooi',
`rated` tinyint(3) UNSIGNED NOT NULL DEFAULT '1',
`klok` tinyint(4) NOT NULL DEFAULT '0',
`toernooisoort` tinyint(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0 = geen toernooi, 1 = tsreg',
`vakmodus` tinyint(4) NOT NULL DEFAULT '0',
`50zetten` tinyint(3) UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='herinvite: 1=uitnodiging, 2 voor lopende partij';
--
-- Gegevens worden geëxporteerd voor tabel `games`
--
INSERT INTO `games` (`gameID`, `whitePlayer`, `blackPlayer`, `gameMessage`, `messageFrom`, `wit_rem_aanvraag`, `zwart_rem_aanvraag`, `rem_antw_zwart`, `rem_antw_wit`, `dateCreated`, `lastMove`, `ratingset`, `rating_white_start`, `rating_white_end`, `rating_black_start`, `rating_black_end`, `notitie_wit`, `notitie_zwart`, `vak_wit`, `vak_zwart`, `her_invite`, `pionpromo`, `beurtaan`, `whiteNick`, `blackNick`, `kibW`, `kibZ`, `comment`, `witOnline`, `zwartOnline`, `chataanvraagdoor`, `chataanvraagtijd`, `ftoernooi`, `tafel`, `ratingrange`, `speeltijd`, `speeltijdwit`, `speeltijdzwart`, `maxzettijd`, `startklok`, `opener`, `ts_ladder`, `t_partij`, `tt`, `rated`, `klok`, `toernooisoort`, `vakmodus`, `50zetten`) VALUES
(2786547, 2187, 7144, 'checkMate', 'black', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '2023-01-23 22:11:26', '2023-03-03 00:46:40', 'set', NULL, 0, NULL, 0, '', '', '0000-00-00', '0000-00-00', 0, 0, '2187', '@Gijs', 'E-Street', 'ja', 'ja', '', '2023-03-02 21:58:22', '2023-03-03 00:46:56', 0, '1900-00-00 00:00:00', '0', '0', 500, NULL, 0, 0, '168:00:00', '2023-01-24 09:06:01', NULL, NULL, 0, 0, 1, 0, 0, 0, 6);
--
-- Indexen voor geëxporteerde tabellen
--
--
-- Indexen voor tabel `games`
--
ALTER TABLE `games`
ADD UNIQUE KEY `gameID_2` (`gameID`),
ADD KEY `gameID` (`gameID`),
ADD KEY `idx_partijlijst_datum` (`gameMessage`,`lastMove`),
ADD KEY `inx_tafel` (`tafel`),
ADD KEY `idx2_whitePlayer` (`whitePlayer`),
ADD KEY `idx2_blackPlayer` (`blackPlayer`),
ADD KEY `toernooisoort` (`toernooisoort`),
ADD KEY `vakmodus` (`vakmodus`),
ADD KEY `expired_games` (`lastMove`,`ratingset`,`vak_wit`,`vak_zwart`,`ts_ladder`,`klok`);
--
-- AUTO_INCREMENT voor geëxporteerde tabellen
--
--
-- AUTO_INCREMENT voor een tabel `games`
--
ALTER TABLE `games`
MODIFY `gameID` int(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2795936;
>Solution :
You need to include the white and black user IDs in the join conditions:
LEFT JOIN ratingverloop rv1
ON games.gameID = rv1.gameID AND games.whitePlayer = rv1.userID
LEFT JOIN ratingverloop rv2
ON games.gameID = rv2.gameID AND games.blackPlayer = rv2.userID