user3473873 user3473873 - 1 month ago 15
MySQL Question

Php Leaderboard issue

Ok so a client posts feedback on a manager and they get a total score. What I want this page to do per month is to create a COUNT of the number of submissions that manager has received with their total score for the total amount of feedback received,

The code at the moment counts the number of $managers name appeared in the database however where its only counting the manager name once! I have two feedbacks for Manager A however count only shows one for example.

The total score created is the total score for all managers and not for the individual manager.

Here is the code:

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


Thank you in advance

SAMPLE DATA
--
-- Table structure for 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);

Answer

It looks like your count sql is slightly incorrect.

If you want to get the count for all records for all managers for the given month then it should be:

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

Whereas if you want to get the count for all records for a specific manager for the given month then you need to limit it, for example:

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

To get a list of unique managers, you'll need to add a GROUP BY in your initial SELECT statement, for example:

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

----- EDIT -----

You would probably be best to get these values with a single sql query instead of nesting 2 queries in loops, for example

$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>";
}
Comments