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

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

show the actual PHP code. you're probably fetching/displaying the problem, and it won't be a problem with the query. — Marc B
– Marc B, Commented Sep 3, 2013 at 18:11
from users t inner join yourtable t1 slightly differs from from yourtable t inner join yourtable t1 isn't it? — u_mulder
– u_mulder, Commented Sep 3, 2013 at 18:16

Havenard · Accepted Answer · 2013-09-03 18:33:14Z

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`;

edited Sep 3, 2013 at 18:33

answered Sep 3, 2013 at 18:22

Havenard

28.1k5 gold badges38 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Max Hudson Over a year ago

first query didn't work at all. second did the charm. thanks!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related