John Davidson

Message:

I am trying to count how many days a user has clocked in within a certain date period, as the user can be clocked in for several days at a time i need to find the difference between the two dates and then count the total days for each column within the date period and only where the accountid matches.

Does anyone have any suggestions on where i am going wrong.

---

Database Table

id, datein, dateout, accountid

1, 2022-08-01, 2022-08-03, 1

2, 2022-08-04, 2022-08-05, 2

3, 2022-08-05, 2022-08-05, 1

4, 2022-08-05, 2022-08-08, 1

5, 2022-08-06, 2022-08-07, 2

6, 2022-08-06, 2022-08-08, 3

7, 2022-08-07, 2022-08-10, 1

8, 2022-08-08, 2022-08-08, 2

---

Code

<?php

$accountid =$user->data()->id;

$monthdate=date("Y-m-d", strtotime("-1 month")); 

$crrntdte=date("Y-m-d");

$query3=mysqli_query($conn,"SELECT DATEDIFF('datein', 'dateout') as monthlysessions from addsession where ((datein) between '$monthdate' and '$crrntdte') && (accountid='$accountid');");

$result3=mysqli_fetch_array($query3);

$sum_monthly_sessions=$result3['monthlysessions'];

?>



<?php echo $sum_monthly_sessions;?>

---

My Answer:
It looks like there are a few issues in your code that may be causing it not to work as expected. Here are some suggestions to correct them:

1. Remove the single quotes around the column names 'datein' and 'dateout' in the DATEDIFF function. It should be DATEDIFF(datein, dateout) instead of DATEDIFF('datein', 'dateout').

2. Use the SUM function to count the total days instead of DATEDIFF. You can sum the result of DATEDIFF for each row to get the total days.

3. You need to loop through the result set to calculate the total days for each row and then sum them up to get the total days for the user within the date period.

Here is an updated version of your code:

php
$accountid = $user->data()->id;
$monthdate = date("Y-m-d", strtotime("-1 month"));
$crrntdte = date("Y-m-d");

$query3 = mysqli_query($conn, "SELECT SUM(DATEDIFF(dateout, datein)) as monthlysessions FROM addsession WHERE datein BETWEEN '$monthdate' AND '$crrntdte' AND accountid = '$accountid'");
$sum_monthly_sessions = 0;

while ($result3 = mysqli_fetch_array($query3)) {
$sum_monthly_sessions += $result3['monthlysessions'];
}

echo $sum_monthly_sessions;
?>


This code should calculate the total days a user has clocked in within the specified date range. Make sure to adjust the column names and table name in the query if they are different in your database.