PHP排行榜问题

debugcn 发表于 Dev

用户3473873

好的，这样客户就向经理发布了反馈，他们得到了总分。我希望该页面每月执行的操作是创建一个COUNT项经理所收到的提交数量及其总分，作为收到的反馈总数，

此刻的代码计算出现在数据库中的$ managers名称的数量，但其中仅计算一次经理名称！我对经理A有两个反馈，但是例如，计数仅显示一个。

创建的总分数是所有经理的总分数，而不是单个经理的总分数。

这是代码：

<table style="border-collapse: separate;    border-spacing: 10px;    cellspacing: 47px;    width: 100%;">
<tr>

<td>Manager</td><td>Submissions</td><td>Total Count</td>

</tr>
<?php

 //This page is maangers vs managers on feedback recieved and feedback score overall

//$m = date("m");

$m = '09';

include "dbconnect.php";

$sql = ("SELECT * FROM `clientsfeedback` WHERE MONTH(submitteddate) = '".$m."' ORDER BY `manager` ASC");

//SELECT * FROM `clientsfeedback` WHERE MONTH(submitteddate) = '09' ORDER BY `manager` ASC

            $result=mysql_query($sql); 

            while($row=mysql_fetch_array($result)){ 

            $manager = $row['manager'];

            $tc = $row['totalscore'];

            $manager = $row['manager'];

            $count = ("Select count(".$manager.") FROM clientsfeedback WHERE MONTH(submitteddate) = '".$m."'"); //Spot on

            $c = ("Select SUM(totalscore) FROM `clientsfeedback` WHERE `manager` = ".$manager." AND MONTH(submitteddate) = '".$m."' ORDER BY `manager` ASC");

            $q = mysql_query($c);
            $rt = mysql_fetch_array($q);


            $ct = count($count);

 echo "<tr><td>".$manager."</td><td>".$ct."</td><td>".$rt[0]."</td></tr>";
            }

?>
</table>

先感谢您

样本数据--表的表结构clientsfeedback-

CREATE TABLE IF NOT EXISTS `clientsfeedback` (
  `id` int(250) NOT NULL AUTO_INCREMENT,
  `manager` varchar(255) NOT NULL,
  `securityofficer` varchar(255) NOT NULL,
  `siteid` varchar(255) NOT NULL,
  `sitename` varchar(255) NOT NULL,
  `group` varchar(255) NOT NULL,
  `duedate` varchar(11) NOT NULL,
  `stuniform` int(1) NOT NULL,
  `stattitude` int(1) NOT NULL,
  `stcustomercare` int(1) NOT NULL,
  `docsai` int(1) NOT NULL,
  `docshealthnsafety` int(1) NOT NULL,
  `docsregisters` int(1) NOT NULL,
  `oppatrols` int(1) NOT NULL,
  `opsitetraining` int(1) NOT NULL,
  `oplicences` int(1) NOT NULL,
  `supmanagervisits` int(1) NOT NULL,
  `supcontract` int(1) NOT NULL,
  `supresponce` int(1) NOT NULL,
  `opmcomments` varchar(255) NOT NULL,
  `customerservicedeliverycmt` varchar(255) NOT NULL,
  `totalscore` int(2) NOT NULL,
  `submitteddate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completedhow` varchar(255) NOT NULL,
  `completedby` varchar(255) NOT NULL,
  `confirmed` int(1) NOT NULL,
  `superior` int(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`)
   ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `clientsfeedback`
--

INSERT INTO `clientsfeedback` (`id`, `manager`, `securityofficer`, `siteid`, `sitename`, `group`, `duedate`, `stuniform`, `stattitude`, `stcustomercare`, `docsai`, `docshealthnsafety`, `docsregisters`, `oppatrols`, `opsitetraining`, `oplicences`, `supmanagervisits`, `supcontract`, `supresponce`, `opmcomments`, `customerservicedeliverycmt`, `totalscore`, `submitteddate`, `completedhow`, `completedby`, `confirmed`, `superior`) VALUES
(1, 'Bennett', 'Brian', '', '', 'ABC', '22/09/2016', 2, 0, 2, 2, 0, 2, 2, 2, 2, 0, 2, 2, 'hello', 'hello', 18, '2016-09-29 16:02:42', 'telephone', 'Dylon', 1, 0),
(2, 'Manager2', 'Pip', '', '', '', '', 1, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 'None', 'None', 14, '2016-09-12 09:22:57', '', 'Manager2', 1, 0),
(3, 'Bennett', 'Manager2', '', '', '', '', 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 'None', 'None', 14, '2016-09-12 09:29:08', '', '', 1, 0);

phpchap

看来您的sql计数有点不正确。

如果要获取给定月份所有经理的所有记录的计数，则应为：

$count = ("Select count(manager) FROM clientsfeedback WHERE MONTH(submitteddate) = '".$m."'");

而如果要获取给定月份特定经理的所有记录的计数，则需要对其进行限制，例如：

$count = ("Select count(manager) FROM clientsfeedback WHERE MONTH(submitteddate) = '".$m."' AND manager = '".$manager."'");

要获得唯一管理者的列表，您需要GROUP BY在初始SELECT声明中添加，例如：

$sql = ("SELECT * FROM `clientsfeedback` WHERE MONTH(submitteddate) = '".$m."' GROUP BY manager ORDER BY `manager` ASC");

- - - 编辑 - - -

例如，您最好是使用单个sql查询而不是在循环中嵌套2个查询来获取这些值。

$m = '09';

include "dbconnect.php";

$sql = ("SELECT manager, COUNT(manager) as managercount, SUM(totalscore) AS totalscore FROM clientsfeedback WHERE MONTH(submitteddate) = '".$m."' GROUP BY manager ORDER BY manager ASC");

$result=mysql_query($sql); 
while($row=mysql_fetch_assoc($result)){ 

    echo "<tr><td>".$row['manager']."</td><td>".$row['managercount']."</td><td>".$row['totalscore']."</td></tr>";
}

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。