Can't get my innerjoin to work with select sum groupby

[alphamoment]

Hello. I'm trying to get this piece of code finished but it's not going my way, if anyone could help me out that would be great.
Here whats I'm trying to do: 

Table1                                   Table2

----------------            ---------------------------------      
      ID       |            | PlayerID        |  PlayerName |
----------------             --------------------------------
1393           |            | 1393              | Player1   |
2097           |            | 2097              | Player2   |
3888           |            | 3888              | Player3   |
3888           |            | 4017              | Player4   |
3888           |            ---------------------------------
4017           |                
4017           |                
4017           |                
----------------

I Want to Count the entries in Table1 (3888 has 3 entries so it will display like "3888: 3")
Then I want to join Table1 and Table2 using the ID so I can get the players name (3888=Player3 so it would display like Player3 : 3)

Here's the code I'm using:

<?php
//connect to db

$query = "SELECT * FROM Table1 INNER JOIN Table2 WHERE Table1.PlayerID = Table2.ID";

$query2 = "SELECT ID, SUM(ID=0) AS n0, SUM(ID=1) AS n1, COUNT(*) AS total FROM Table1 GROUP BY ID";

$result = mysql_query($query) or die($query."<br/><br/>".mysql_error());
$result2 = mysql_query($query2) or die($query2."<br/><br/>".mysql_error());

echo "<table border='1'>
<tr>
<th>PlayerName</th>
<th>Entries</th>
</tr>";
while($row2 = mysql_fetch_array($result2))
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['ID'] . "</td>";
echo "<td>" . $row['PlayerName'] . "</td>";
echo "<td>" . $row2['Total'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysql_close($link);
?>

If I use $query on its own it joins perfectly, If I use $query2 on its own, it displays the results exactly how I want them listed but with the ID instead of the PlayerName, 
I tried putting them both together as shown above but I can't get them to work together.

What I want my end Result to be

Player1            1

Player2            1

Player3            3

Player4            3

How it keeps coming out;

1393             1  

2097             1
 
3888             3

4017             3

 Can anyone see where I'm going wrong? Thank you!!

[Barand]

try

SELECT
    t2.PlayerName
  , COUNT(t1.ID)
FROM Table2 t2
    LEFT JOIN Table1 ON t2.PlayerID = t1.ID 

[alphamoment]

Hello Barand, Thanks for the quick reply. 

I've tried your Query and the results displayed only 1 row

ID        COUNT
4017       85

Not quite sure how it got 85 when there is only 3 listings for ID 4017. Hmmmm

 

[Barand]

Oops. I forgot the GROUP BY

SELECT
    t2.PlayerName
  , COUNT(t1.ID)
FROM Table2 t2
    LEFT JOIN Table1 ON t2.PlayerID = t1.ID 
GROUP BY PlayerName

[alphamoment]

Haha, thanks! Works perfect, Thank you so much Barand. One last question; for the echo I've used

$row['COUNT(t1.ID)']

How can I get it to ORDER BY  the highest count descending 

ORDER BY t1.COUNT(t1.ID) DESC

This doesn't seem to do the trick!

Edit: I got it working using;

ORDER BY COUNT(*) DESC

Thank you!

Edited May 28, 2015 by alphamoment

[Barand]

If you give the count an alias, say 'total', then you can reference that eg

echo $row['total'];

and also order by the total too

SELECT
    t2.PlayerName
  , COUNT(t1.ID) as total
FROM Table2 t2
    LEFT JOIN Table1 ON t2.PlayerID = t1.ID 
GROUP BY PlayerName
ORDER BY total DESC

[alphamoment]

That's a lot cleaner thanks! If I wanted to limit the results to 50 rows being displayed how can I come about that? (Sorry for all the questions)

[Barand]

Add "LIMIT 50" to the end of the query

[alphamoment]

Got it! Barand MVP. I really appreciate your help, Thank you once again.