PHP脚本和MYSQL查询返回不同（和不需要的）数据

Question

I have a table that I select "project managers" from and pull data about them.

Each of them has a number of "clients" that they manage.

Clients are linked to their project manager by name.

For example: John Smith has 3 Clients. Each of those Clients have his name in a row called "manager".

Here's what a simple version of the table looks like:

name       | type    | manager
--------------------------------------
John Smith | manager |
Client 1   | client  | John Smith
Client 2   | client  | John Smith
Client 3   | client  | John Smith
John Carry | manager |
Client 4   | client  | John Carry
Client 5   | client  | John Carry
Client 6   | client  | John Carry

I want to return the following data:

John Smith - 3 Clients

John Carry - 3 Clients

I used this query to return the data:

select t.name,
  count(t1.name) TotalClients
from yourtable t
inner join yourtable t1
  on t.name = t1.manager
group by t.name;

http://sqlfiddle.com/#!2/d72a87/2

Which worked correctly in phpMyAdmin and on the fiddle, but when I used the the PHP script (a simple query and echoing of $row['name']) it selected the last client that was counted (Client 3) however did return the correct Total Clients value for John Smith.

How can I make the php script return the same results as the sql one does?

PHP script:

$sql = mysql_query("select t.name,
      count(t1.name) TotalClients
    from users t
    inner join users t1
      on t.name = t1.manager
    group by t.name;");

while($row = mysql_fetch_assoc($sql)){
    echo $row['name'];
    echo $row['TotalClients'];
}

Name returns incorrect value; TotalClients returns correct value

Answer 1

You are not showing the problem. Your fiddle exemple works, and it should work just fine in PHP too. But the Fiddle exemple is not your reality. Where is the table Users? Perhaps theres your mistake.

Anyway, your query seems excessively redundant. You can achieve the same result using something simplier:

SELECT
    `manager`,
    COUNT(*)
FROM
    `yourtable`
WHERE
    `type` = 'client'
GROUP BY
    `manager`;

Unless of course you want to display manager with 0 clients too, in this case you will need something more elaborated with LEFT JOIN:

SELECT
    `manager`.`name`,
    COUNT(`client`.`name`) `TotalClients`
FROM
    `yourtable` `manager`
LEFT JOIN
    `yourtable` `client`
ON
    `manager`.`name` = `client`.`manager`
WHERE
    `manager`.`type` = 'manager'
GROUP BY
    `manager`.`name`;