Question 42322 in PHP Question posted in
Official manual can be found here.

finding the number of different users missed tasks with join queries – PHP

I have two tables, one being the employees information and one with their task progress information. They look like this.

employee table                                  Task table
empid first_name last_name                      task_id    stats  pf  empid

1      lana        law                          1           0      1   6  
5      Carla        davis                       2           1      0   6   
6      lana         doe                         3           1      0   6
                                                4           0      1   5
                                                5           0      0   5
                                                6           1      0   5
                                                7           0      0   5
                                                8           0      1   6
                                                9           0      1   6

I want to produce a table that will show the number of missed tasks. Meaning the number of entries where pf = 1 and stats = 0. Using the above data it should look like this.

number     fname    lname
  0       lana      law
  1       Carla    davis
  3       lana      Doe

This is the code i have tried but it doesnt print correctly

$query1 = $conn-> query("SELECT empfname, emplname from employee");
      while ($row1 = $query1 -> fetch_array()){

      $query = $conn -> query("SELECT count(1) from tasks INNER JOIN employee where tasks.eemail = employee.eid and tasks.pf = '1' and tasks.stats = '0' ");  
      while($row = $query -> fetch_array())  
      {  

     $number = $row[0];     
     $output .= '<tr>  
                            
                          <td>'.$number.'</td>  
                          <td>'.$row1["empfname"].'</td>
                          <td>'.$row1["emplname"].'</td>
                 </tr>

This is the table i get when I run the above code which shows incorrect info

number     fname    lname
      4       lana      law
      4        Carla    davis
      4       lana      Doe

Could someone please help me figure out what ive done wrong with my query. Thanks in advance.

Ive tried this code as well but it returns the following

number   fname    lname
lana      lana    lawrence
carla     carla   davis
lana      lana    doe

I mightve messed the code up when calling the number results but im not sure how, i have tried other variations but got the similar result as the above.

$output = '';  
      mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
      include ('dbcon.php'); 

      

      $query = $conn -> query("SELECT employee.empfname, employee.emplname,
      COALESCE( (
        SELECT COUNT( tasks.tid )
        FROM tasks
        WHERE tasks.eemail = employee.eid
        AND tasks.stats = 0
        AND tasks.pf = 1
      ), 0 ) AS uncompleted_tasks
      FROM employee
      WHERE 1");  

      while($row = $query -> fetch_array())  
      {  

     $uncompleted_tasks = $row[0];     
     $output .= '<tr>  
                            
                          <td>'.$uncompleted_tasks.'</td>  
                          <td>'.$row["empfname"].'</td>
                          <td>'.$row["emplname"].'</td>
                 </tr>  
                          ';  
      } 
     
      return $output;  
     }
Tags:

2

Answers


  1. 0

    You can fetch both by doing a JOIN. First we group by empid to count how many failed tasks. Then join THAT with the employee names.

    SELECT `number`, fname, lname FROM 
`employee` AS a 
LEFT JOIN (SELECT empid, COUNT(1) AS `number`
FROM `Task`
WHERE `stats` = 0 AND `pf` = 1
GROUP BY empid) AS b
ON a.empid = b.empid

    Now for a version that produces the correct results:

    SELECT
    (
        SELECT
            COUNT(1)
        FROM
            `Task` AS t
        WHERE
            `stats` = 0
            AND `pf` = 1
            AND t.empid = a.empid
    ) AS `number`,
    fname,
    lname
FROM
    `employee` AS a
    Login or Signup to reply.
  2. 0

    This uses a sub query to count uncompleted tasks for each employee. Coalescing to zero if no matches found.

    SELECT employee.fname, employee.lname,
COALESCE( (
  SELECT COUNT( Task.task_id )
  FROM Task
  WHERE Task.empid = employee.empid
  AND Task.stats = 0
  AND Task.pf = 1
), 0 ) AS uncompleted_tasks
FROM employee
WHERE 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search