Php/MySQL count () for a ranking

Question

Ich have a MySQL Table games:


  Gid (auto)
  Tid (number from other table tournament)
  Gaway (Player1)
  Ghome (Player2)
  Gwon (Player1)

The content of table games is


Gid (1,2,3,4...)
Tid (1,1,1,1...)
Gaway (Player1, Player2, Player3, Player4...) 
Ghome (Player3, Player4, Player4, Player1...)
Gwon (Player1, Player4, Player4, Player4...)

I read the number of gwon with the following:


        prepare(„SELECT tid, ghome, gaway, gwon, COUNT(gwon) AS anzahl FROM games WHERE tid = ‚“.$_GET[‚tid‘].“‘ AND gwon NOT LIKE ‚draw‘ GROUP BY gwon ORDER BY anzahl DESC“);
        $stat->execute();
        $i = 1;
        While($row = $stat->fetch())
        {
          Echo $i;
               $row['gwon'];
               $row['anzahl'];
            $i++;
          } 
         ?>

Now he sums up the winning games and groups them together to gwon. With it I create a ranking.

Space | name | points

1 | Player4 | 3

2 | Player1 | 1

The problem is, if someone has not yet won (in the example Player2 and Player3) , then he will not appear in the ranking. Does anyone have an idea how to still spend the players in the ranking? For example

Space | name | points

1 | Player4 | 3

2 | Player1 | 1

3 | Player2 | 0

3 | Player3 | 0

As you are using `GROUP BY` most likely in a invalid way.. See [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Raymond Nijland, Oct 10 '19 at 08:47
Also as your code seams to be prone for SQL injections you should also read, [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Raymond Nijland, Oct 10 '19 at 08:49
*"No one but me can contact the system "* @Strawberry *"Famous last words."* haha great comment..Does that sounds like the topicstarter is trusting *"Security through obscurity"* -> *"Security through obscurity can be said to be bad because it often implies that the obscurity is being used as the principal means of security. Obscurity is fine until it is discovered, but once someone has worked out your particular obscurity, then your system is vulnerable again."* — Raymond Nijland, Oct 10 '19 at 09:16
Security is important, but not for my code, because the system is not on the www and is in my basement. Does anyone have a solution to my problem? — Lalo, Oct 10 '19 at 09:25
*"Does anyone have a solution to my problem? "* See mine first comment.. — Raymond Nijland, Oct 10 '19 at 09:26

score 0 · Answer 1 · answered Oct 10 '19 at 12:30

This is not the Exact solution but You can use in this way

SELECT player_name,tid, ghome, gaway, gwon, COUNT(gwon) AS anzahl FROM(
SELECT distinct Gaway player_name FROM table_name
UNION 
SELECT distinct Ghome player_name FROM table_name
UNION
SELECT distinct Gwon player_name FROM table_name
) t1 
LEFT JOIN table_name t2 on Gwon.t2=t1.player_name
WHERE tid = ‚“.$_GET[‚tid‘].“‘ AND gwon NOT LIKE ‚draw‘  ORDER BY anzahl DESC
GROUP BY player_name

Hi Mukesh, I exchanged the code for mine but it does not work. Currently have no idea what I have to change — Lalo, Oct 10 '19 at 15:21

Php/MySQL count () for a ranking

1 Answers1