-2

This is my MySQL table:

_______________________________________________
Id | Name| Employee id | Date       | Attendance
_______________________________________________
1  | xyz |     196     | 2013-04-01 | present  
2  | xyz |     196     | 2013-04-02 | present  
3  | xyz |     196     | 2013-04-03 | present  
4  | xyz |     196     | 2013-04-04 | absent  
5  | xyz |     196     | 2013-04-05 | present  
6  | abc |     197     | 2013-04-01 | present

7  | abc |     197     | 2013-04-02 | present  
8  | abc |     197     | 2013-04-03 | present  
9  | abc |     197     | 2013-04-04 | present  
10 | abc |     197     | 2013-04-05 | present

_______________________________________________

I want to count a days who employee are mostly present, and I want like this result in PHP:

___________________________________________________________
 Name| Employee id| Attendance     | Best OR NOT
___________________________________________________________
 xyz |  196       | 4 Present days |

 abc |  197       | 5 Present days | This is best employees of the year

__________________________________________________________________________

How can i do this?

halfer
  • 19,824
  • 17
  • 99
  • 186
user2260431
  • 75
  • 1
  • 3
  • 14
  • 2
    So you're the best employee because you were lucky enough to not get sick/worst period pains? (I did not downvote) – Sebas Apr 20 '13 at 14:27
  • 3
    There are tons of php/mysql tutorials out there. You could try and write some code yourself. What you are asking is a complete solution, not "fixing an issue". – ZZ-bb Apr 20 '13 at 14:32
  • Presumably an employee who was present for 4 days and absent for 0 is "better" than an employee who was present for 6 days and absent for 3? If so, the current solutions that only look at present days are not quite what you want. – halfer Apr 20 '13 at 14:36

5 Answers5

1

SQL

SELECT name, employeeID count(userID) as datesAttend FROM Attendance GROUP BY name

PHP (mysql class method, just an example)

/* mysql_query example ( use beter db class ) */

$best = 0;
$bestEmployee = '';

$query = mysql_query("SELECT Name, EmployeeID, count(EmployeeID) as datesAttend FROM Attendance GROUP BY EmployeeID");

while($row = mysql_fetch_array($query)) {
  echo $row['Name']. " " .$row['EmployeeID']. " " .$row['datesAttend'];
  if($row['datesAttend'] > $best) {
   $best = $row['datesAttend'];
   $bestEmployee = $row['EmployeeID'];
  }
}

echo $bestEmployee ." is employee of the year!!!";
throw PartyForEmployee();

Example: http://www.sqlfiddle.com/#!2/9d282/2/0

S.Visser
  • 4,645
  • 1
  • 22
  • 43
0

A query like this:

select employeeId, count(employeid) as num from table_name group by attendence limit 1 

will give you the best worker's id. And you can edit the table by inserting it to the proper location. Also could you explain your question a little more?

//DB_HOST etc. are db connection constants
$dbc=mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$data = mysql_query('your query') or die(mysql_error());
// A COUNT query will always return 1 row
// Use fetch_assoc it's easier to use
$row = mysql_fetch_assoc($data);
$bestId = $row['employeeId'];
$bestDayNumber = $row['num'];
Barış Akkurt
  • 2,255
  • 3
  • 22
  • 37
0

This query will give you the best employee:

select Name, "Employee id", count(*) 
from table_name 
where Attendance = "present" 
group by Name 
order by count(*) desc 
limit 1;

The "group by" clause means you want a separate result for each employee. The "where" clause makes sure you don't count days when they were absent. The "order by" clause puts the employees with the most days present first. The "limit" clause just gets the first one, since you only want the best.

Whit Kemmey
  • 2,230
  • 22
  • 18
0

Try this query..

SELECT *, COUNT(employee_id) AS cnt, CASE WHEN employee_id = (SELECT employee_id FROM employees
WHERE 1 GROUP BY  employee_id ORDER BY COUNT(employee_id) DESC LIMIT 0 , 1)   THEN 'Best' 
ELSE 'Not' END AS avl
FROM  `employees`
WHERE 1 
GROUP BY employee_id

Change table name and field name according to your need

Pankaj Dadure
  • 679
  • 5
  • 11
-1

Try this.

   $query = mysql_query("select name ,`Employee id` as emplyee_id, CONCAT(count(`Employee id`),' Present days') as Attendance
   from Table1 
   where `Attendance` = 'present' group by `Employee id` ");



   while($row = mysql_fetch_array($query)) {
  echo $row['name']. " - " .$row['emplyee_id']. " - " .$row['Attendance']."</ br>";
 }

DEMO HERE

echo_Me
  • 37,078
  • 5
  • 58
  • 78