PHP script and MYSQL query return different (and undesirable) data -

i have table select "project managers" , pull data them.

each of them has number of "clients" manage.

clients linked project manager name.

for example: john smith has 3 clients. each of clients have name in row called "manager".

here's simple version of 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 return following data:

john smith - 3 clients

john carry - 3 clients

i used query return data:

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

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

which worked correctly in phpmyadmin , on fiddle, when used the php script (a simple query , echoing of $row['name']) selected last client counted (client 3) did return correct total clients value john smith.

how can make php script return same results sql 1 does?

php script:

$sql = mysql_query("select t.name,       count(t1.name) totalclients     users t     inner join users t1       on t.name = t1.manager     group t.name;");  while($row = mysql_fetch_assoc($sql)){     echo $row['name'];     echo $row['totalclients']; } 

name returns incorrect value; totalclients returns correct value

you not showing problem. fiddle exemple works, , should work fine in php too. fiddle exemple not reality. table users? perhaps theres mistake.

anyway, query seems excessively redundant. can achieve same result using simplier:

select     `manager`,     count(*)     `yourtable`     `type` = 'client' group     `manager`; 

unless of course want display manager 0 clients too, in case need more elaborated left join:

select     `manager`.`name`,     count(`client`.`name`) `totalclients`     `yourtable` `manager` left join     `yourtable` `client` on     `manager`.`name` = `client`.`manager`     `manager`.`type` = 'manager' group     `manager`.`name`;