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

问题描述：

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

我有一个表，我从中选择“项目经理”并提取有关它们的数据。 p>

他们每个人都有许多他们管理的“客户”。 p>

客户端按名称链接到他们的项目经理。 p>

例如：John Smith有3个客户。这些客户中的每一个都有一个名为“manager”的行。 p>

以下是该表的简单版本： p>

 名称| 类型| 经理
 -------------------------------------- 
John Smith | 经理| 
Client 1 | 客户|  John Smith 
Client 2 | 客户|  John Smith 
Client 3 | 客户|  John Smith 
John Carry | 经理| 
Client 4 | 客户|  John Carry 
Client 5 | 客户|  John Carry 
Client 6 | 客户|  John Carry 
  code>  pre> 
 
 我想返回以下数据： p> 
 
 
 John Smith  -  3个客户 p> 
 \  n 
 John Carry  -  3个客户端 p> 
 
 
我使用此查询返回数据： p> 
 
 
  select t.name，
  count（t1.name）TotalClients 
 from yourtable t 
inner通过t.name; 
  code>  pre> 
 
 在t.name = t1.manager 
group上加入yourtable t1 
   http://sqlfiddle.com/#!2/d72a87/2   p>  
 
 
哪些在phpMyAdmin和小提琴中正常工作，但当我使用PHP脚本（一个简单的查询并回显$ row ['name']）时，它选择了最后计算的客户端（客户端）  3）然而确实为John Smith返回了正确的Total Clients值。 p> 
 
 
如何让php脚本返回与sql脚本相同的结果？ p> 
 
  
 PHP脚本： p> 
 
 
  $ sql = mysql_query（“select t.name，
 count（t1.name）TotalClients 
 from users t 
 inner join users 在t.name = t1.manager 
 group上的t1 
  by t.name;“）; 
 
while（$ row = mysql_fetch_assoc（$ sql））{
 echo $ row ['name']; 
 echo $ row ['TotalClients']; 
} 
   code>  pre> 
 
 名称返回不正确的值;  TotalClients返回正确的值 p> 
  div>

答

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

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

相关推荐