The Mysql INNER JOIN fetch same data repeatly

Question

I try to design a web that keep track the match history of students.

tablegame has 4 column: player 1, player2 ,player 3 and player4. All of them are represented as ids in tablegame.(each game has a maximum of 4 player)

The column player1 player2 always store the IDs who win this game, and column player 3 player4 always store IDs who lose this game.

Tableplayer store basic information about the student.

However, as I fetch associated array from the $AimStudentHistoryWin. Data get repeatly fetched.

THE web actually shows me this:


================
GAMEID 15 


================
GAMEID 16 

================
GAMEID 15 


================
GAMEID 16 

================
GAMEID: 15

================
GAMEID 16

BUT I expect the web to only show me


 ================ GAMEID 15

================ GAMEID 16

How could I Fix that?

echo "<br><br> <center><h2>match history:</h2> <br>"; 
$Aimstudenthistorywin=$conn ->query( "SELECT tablegame.*,tableplayer.Name FROM tablegame INNER JOIN tableplayer ON tablegame.player1 ='$studentid' OR tablegame.player2 ='$studentid'");
$Aimstudenthistoryloss=$conn ->query("SELECT tablegame.*,tableplayer.Name FROM tablegame INNER JOIN tableplayer ON tablegame.player3 ='$studentid'OR tablegame.player4 ='$studentid'" );  
 $Num=0;
 While($row2= $Aimstudenthistorywin ->fetch_assoc()){
     
     
     echo "<br>================<br><i style ='color:blue;'><h3>WIN</h3></i>";
     echo "<br>Game".$Num;
     $Num++;
     echo "<br>".$row2['GameID'].":<br>".$row2['Name'];
     
 }while($row2 = $Aimstudenthistoryloss -> fetch_assoc()){
      echo "<br>================<br><i style ='color:red;'><h3>Lose</h3></i>";
      echo "<br>Game".$Num;
     $Num++;
     echo  "<h3>StudentName:".$TempStudentArray[$row2['player1']]."</h3><br>Game Date:".$row2['Date']."<br><br>"."----------------<br>Player1:".$TempStudentArray[$row2['player1']]."|||||||"."Player2:".$TempStudentArray[$row2['player2']]."<br><br>vs<br><br>Player3:".$TempStudentArray[$row2['player3']]."|||||||| Player4".$TempStudentArray[$row2['player4']]."<br>-------" ;
 }

echo"</center>"

I would suggest reading [How to ask](https://stackoverflow.com/help/how-to-ask) in general and also [tips for a good SQL question](https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question). — El_Vanja, Mar 17 '21 at 10:24
Your join is quite weird, since your `ON` clause only references `tablegame`, and not `tableplayer`. You are essentially doing a join of all players with the games that this specific student won or lost. By the look of your web output, it seems your student won or lost 2 games, and you have 3 rows in your `tableplayer` table, which causes the outputs to be printed 3 times — Pepper, Mar 17 '21 at 10:26
I know that this join is little wired. But even I change the $studentid into `tableplayer.StudentID` , the same problems still occur and it is even more chaotic. — Decarnation, Mar 17 '21 at 10:39
Please be warned that the given SQL query is open for SQL injection. Also, have you tried to run the query manually in any SQL tool (like MySQL workbench, phpMyAdmin), just to check whether the query itself is responsible for this, and not PHP (which I would assume is the case)? — Nico Haase, Mar 17 '21 at 10:58

score 0 · Answer 1 · answered Mar 17 '21 at 10:53

0

Ok it is solved. instead of using

"SELECT tablegame.*,tableplayer.Name FROM tablegame INNER JOIN tableplayer ON tablegame.player1 ='$studentid' OR tablegame.player2 ='$studentid'");

We can just add more conditions into this. Eventhough I don't know why it works but it works

"SELECT tablegame.*,tableplayer.Name FROM tablegame INNER JOIN tableplayer ON (tableplayer.StudentID = tablegame.player1 OR tableplayer.StudentID = tablegame.player2) AND (tablegame.player1 ='$studentid' OR tablegame.player2 ='$studentid')");

answered Mar 17 '21 at 10:53

Decarnation

1
1

Please share more details. What's the need for `(tablegame.player1 ='$studentid' OR tablegame.player2 ='$studentid')`? – Nico Haase Mar 17 '21 at 10:59
= $studentid specifies which matches involve the target student. Without it, the code will just fetch all the students where ids are the same – Decarnation Mar 17 '21 at 11:20

Pepper · Answer 2 · 2021-03-17T11:00:29.820

Your duplicates come from your join's ON clause, which basically acts as if it was a WHERE clause.

To fix it, you can properly JOIN both tables (using a column of each), and then selecting only those involving your specific student (in the WHERE clause):

SELECT tablegame.*,tableplayer.Name 
FROM tablegame 
INNER JOIN tableplayer 
    ON tablegame.player1 = tableplayer.StudentID 
    OR tablegame.player2 = tableplayer.StudentID
WHERE tableplayer.StudentID = [the id of your student]

This request will avoid duplicates (assuming a student can't be player 1 and 2 at the same time)

And while at it, I would suggest always using prepared statements, to avoid potential SQL injections.

The Mysql INNER JOIN fetch same data repeatly

2 Answers2