PHP store total count in a variable for each specific MySQL column value

Question

Im trying to create a Day Closure for our factory (employees).

What I have created so far:

It show's a total of all records in the database with startdate and status as given into the SQL SELECT function. So $totalWorkedTime = 16.68. But this is not what I want. I want to show for each department the total value.

$datestring = '23-10-2017';
$qClosure = 'SELECT * FROM timeRegistration WHERE startdate="'. $datestring .'" && status="3" ';
$rClosure = mysqli_query($conn, $qClosure);

$totalWorkedTime = 0;

while($row = mysqli_fetch_assoc($rClosure)) {

$totalWorkedTime += $row['worktime'];

}
echo $totalWorkedTime;

My goal is as following:

A function which stores for the different departments (1, 2, 3, 4, 5, 6) a total count of worked hours. In my MySQL Database I have a column called: worktime. In this column I have different values like: 3.42, 3.42, 4.92, 4.92 (these are hours). So I have 4 records right. Now these records have different departments which stored in the column: department.

I want that my function stores those values for each department. How can I manage this? An example is:

$totalhoursDepartment5 = 6.84; $totalhoursDepartment4 = 9.84;

The function should create more $totalhoursDepartment if is there any other records with department: 3 or 2 (example).

@GyandeepSharma I hope the extra information is now clear. Please let me know. — nhatimme
– nhatimme, Commented Oct 23, 2017 at 12:17

Nic3500 · Accepted Answer · 2017-10-23 12:32:57Z

1

SQL sum() will do this for you:

 SELECT SUM(worktime),department
 FROM Depts
 GROUP BY department;

Department should be a foreing key to a department table.

answered Oct 23, 2017 at 12:32

Nic3500

8,68410 gold badges33 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sco · Accepted Answer · 2017-10-23 12:24:37Z

0

    $datestring = '23-10-2017';
    $qClosure = 'SELECT SUM(worktime) AS totalworktime
FROM timeRegistration
Where departement in (select departement
from timeRegistration
WHERE startdate="'. $datestring .'"
and status="3")';
    $rClosure = mysqli_query($conn, $qClosure);

?

Use 'sum' and 'in'

http://sql.sh/cours/where/in and

http://sql.sh/fonctions/agregation/sum

edited Oct 23, 2017 at 12:24

answered Oct 23, 2017 at 12:17

Sco

7591 gold badge7 silver badges13 bronze badges

Collectives™ on Stack Overflow

PHP store total count in a variable for each specific MySQL column value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related