names of days

ramiwahdan · March 25, 2020

Hi,

I have db table that records the days (Sunday, Monday...) when the employee login to the system. Say that the employee logged in on Monday then Logged in on Wednesday so this means he was absent on Tuesday. I calculated the number of days to get the answer 2 (between Wednesday and Monday before logging in on Wednesday) how to get the name of the missing day "Tuesday"?

code:

$curdate = date("Y-m-d");
$currday = date('l');
$lastdate = $row['indate'];
$lastdayin = $row['lastdayin'];
					
if ($lastdate !=Null)
{
						
	$misseddates = strtotime($curdate) - strtotime($lastdate);
	$misseddates = $misseddates / 86400;
	echo $misseddates;
						
	$misseddays = strtotime($lastdayin) - strtotime($currday);
	$misseddays = $misseddays / 86400;
	echo $misseddays;
}

I tried to get the name of day in the last step but it only calculates 5 in numbers how to get the name. I want the answer to be "Tuesday" as of the example.

Thanks.

ramiwahdan · March 25, 2020

40 minutes ago, ramiwahdan said:
Hi,

I have db table that records the days (Sunday, Monday...) when the employee login to the system. Say that the employee logged in on Monday then Logged in on Wednesday so this means he was absent on Tuesday. I calculated the number of days to get the answer 2 (between Wednesday and Monday before logging in on Wednesday) how to get the name of the missing day "Tuesday"?

code:
$curdate = date("Y-m-d");
$currday = date('l');
$lastdate = $row['indate'];
$lastdayin = $row['lastdayin'];
					
if ($lastdate !=Null)
{
						
	$misseddates = strtotime($curdate) - strtotime($lastdate);
	$misseddates = $misseddates / 86400;
	echo $misseddates;
						
	$misseddays = strtotime($lastdayin) - strtotime($currday);
	$misseddays = $misseddays / 86400;
	echo $misseddays;
}
I tried to get the name of day in the last step but it only calculates 5 in numbers how to get the name. I want the answer to be "Tuesday" as of the example.

Thanks.

OK here is what i need as output if possible:

last login: Monday 23-3-2020
today's date: Wednesday 25-3-2020

add record to db that Tuesday 24-3-2020 is absent

the first 2 i am getting from records in db, how i can get the information like last line "Tuesday 24-3-2020"? then i can add to db.

Barand · March 25, 2020

1 ) Why do you need it as "Tuesday 24-3-2020" to add it to the DB. The only date format you should use in a database id yyyy-mm-dd.

2 ) Why do you need to store it at all? If you are storing the dates when they logged in you easily find the dates when they didn't.

Barand · March 25, 2020

For the record, here's how

SAMPLE INPUT

attendance_record                                                     staff
+----------+---------------------+---------------------+              +----------+--------+-------------+
| oracleid | clockingindate      | clockingoutdate     |              | oracleid | name   | designation |
+----------+---------------------+---------------------+              +----------+--------+-------------+
|   533349 | 2020-03-02 09:00:00 | 2020-03-02 14:00:00 |              |   533349 | Rami   | DT Teacher  |
|   533349 | 2020-03-03 09:00:00 | 2020-03-03 11:00:00 |              |   533355 | Fatima | CS Teacher  |
|   533349 | 2020-03-04 09:00:00 | 2020-03-04 10:00:00 |              +----------+--------+-------------+
|   533349 | 2020-03-09 09:00:00 | 2020-03-09 13:00:00 |
|   533349 | 2020-03-10 09:00:00 | 2020-03-10 11:00:00 |
|   533349 | 2020-03-11 09:00:00 | 2020-03-11 15:00:00 |
|   533349 | 2020-03-12 09:00:00 | 2020-03-12 12:00:00 |
|   533349 | 2020-03-13 09:00:00 | 2020-03-13 15:00:00 |
|   533349 | 2020-03-16 11:52:31 | 2020-03-16 17:52:52 |
|   533349 | 2020-03-18 07:59:58 | 2020-03-18 15:00:10 |
|   533349 | 2020-03-23 09:00:00 | 2020-03-23 14:00:00 |
|   533349 | 2020-03-24 09:00:00 | 2020-03-24 13:00:00 |
|   533355 | 2020-03-02 09:00:00 | 2020-03-02 16:00:00 |
|   533355 | 2020-03-03 09:00:00 | 2020-03-03 16:00:00 |
|   533355 | 2020-03-04 09:00:00 | 2020-03-04 11:00:00 |
|   533355 | 2020-03-05 09:00:00 | 2020-03-05 14:00:00 |
|   533355 | 2020-03-06 09:00:00 | 2020-03-06 14:00:00 |
|   533355 | 2020-03-10 09:00:00 | 2020-03-10 15:00:00 |
|   533355 | 2020-03-11 09:00:00 | 2020-03-11 15:00:00 |
|   533355 | 2020-03-12 09:00:00 | 2020-03-12 13:00:00 |
|   533355 | 2020-03-13 09:00:00 | 2020-03-13 12:00:00 |
|   533355 | 2020-03-16 00:26:08 | 2020-03-16 00:26:17 |
|   533355 | 2020-03-16 02:50:27 | 2020-03-16 11:50:49 |
|   533355 | 2020-03-23 09:00:00 | 2020-03-23 15:00:00 |
|   533355 | 2020-03-24 09:00:00 | 2020-03-24 16:00:00 |
+----------+---------------------+---------------------+

CODE

$month = 'March';
$start_date = new DateTime("first monday of $month");
$incr = DateInterval::createFromDateString('next weekday');
$period = new DatePeriod($start_date, $incr, new DateTime());

// create temporary date table
$conn->exec("CREATE TEMPORARY TABLE date (date date not null primary key)");
// populate it
foreach ($period as $d) {
    $dates[] = "('{$d->format('Y-m-d')}')" ;
}
$conn->exec("INSERT INTO date VALUES " . join(',', $dates));

// now get the days absent
$res = $conn->query("SELECT s.oracleid
                          , s.name
                          , date_format(date, '%W %d/%m/%Y') as absent
                     FROM staff s
                          CROSS JOIN
                          date d
                          LEFT JOIN
                          attendance_record a ON s.oracleid = a.oracleid
                                              AND d.date = DATE(a.clockingindate)
                     WHERE a.oracleid IS NULL
                     ORDER BY s.oracleid, d.date    
                    ");

QUERY RESULTS

    +----------+--------+----------------------+
    | oracleid | name   | absent               |
    +----------+--------+----------------------+
    |   533349 | Rami   | Thursday 05/03/2020  |
    |   533349 | Rami   | Friday 06/03/2020    |
    |   533349 | Rami   | Tuesday 17/03/2020   |
    |   533349 | Rami   | Thursday 19/03/2020  |
    |   533349 | Rami   | Friday 20/03/2020    |
    |   533349 | Rami   | Wednesday 25/03/2020 |
    |   533355 | Fatima | Monday 09/03/2020    |
    |   533355 | Fatima | Tuesday 17/03/2020   |
    |   533355 | Fatima | Wednesday 18/03/2020 |
    |   533355 | Fatima | Thursday 19/03/2020  |
    |   533355 | Fatima | Friday 20/03/2020    |
    |   533355 | Fatima | Wednesday 25/03/2020 |
    +----------+--------+----------------------+

Edited March 25, 2020 by Barand

ramiwahdan · March 25, 2020

2 hours ago, Barand said:

// create temporary date table $conn->exec("CREATE TEMPORARY TABLE date (date date not null primary key)");

this is not created in the db? i am missing anything?

Edited March 25, 2020 by ramiwahdan
only works if i remove word temporary

Barand · March 25, 2020

As it says on the tin - it's a temporary table. It lasts until the connection closes, which is whe when the script terminates,

ramiwahdan · March 25, 2020

Just now, Barand said:

As it says on the tin - it's a temporary table. It lasts until the connection closes, which is whe when the script terminates,

i did understand this but why i am getting empty page, i did what you did. how to show the data?

Barand · March 25, 2020

I thought you might be able manage that bit. Here's a fuller version

<?php

$month = 'March';
$start_date = new DateTime("first monday of $month");
$incr = DateInterval::createFromDateString('next weekday');
$period = new DatePeriod($start_date, $incr, new DateTime());

// create temporary date table
$conn->exec("CREATE TEMPORARY TABLE date (date date not null primary key)");
// populate it
foreach ($period as $d) {
    $dates[] = "('{$d->format('Y-m-d')}')" ;
}
$conn->exec("INSERT INTO date VALUES " . join(',', $dates));

// now get the days absent
$res = $conn->query("SELECT s.oracleid
                          , s.name
                          , date_format(date, '%W %d/%m/%Y') as absent
                     FROM attendance_staff s
                          CROSS JOIN
                          date d
                          LEFT JOIN
                          attendance_record a ON s.oracleid = a.oracleid
                                              AND d.date = DATE(a.clockingindate)
                     WHERE a.oracleid IS NULL
                     ORDER BY s.oracleid, d.date    
                    ");
$tdata = '';
foreach ($res as $r) {
    $tdata .= "<tr><td>" . join('</td><td>', $r) . "</td></tr>\n";
}
?>
<html>
<head>
<title>Example</title>
<style type="text/css">
    table {
        border-collapse: collapse;
        width: 600px;
    }
    th, td {
        padding: 8px;
    }
    th {
        background-color: black;
        color: white;
    }
</style>
</head>
<body>
    <table border='1'>
        <tr><th>ID</th><th>Name</th><th>Absent</th></tr>
        <?=$tdata?>
    </table>
</body>
</html>

ramiwahdan · March 25, 2020

6 minutes ago, Barand said:

I thought you might be able manage that bit. Here's a fuller version


<?php

$month = 'March';
$start_date = new DateTime("first monday of $month");
$incr = DateInterval::createFromDateString('next weekday');
$period = new DatePeriod($start_date, $incr, new DateTime());

// create temporary date table
$conn->exec("CREATE TEMPORARY TABLE date (date date not null primary key)");
// populate it
foreach ($period as $d) {
    $dates[] = "('{$d->format('Y-m-d')}')" ;
}
$conn->exec("INSERT INTO date VALUES " . join(',', $dates));

// now get the days absent
$res = $conn->query("SELECT s.oracleid
                          , s.name
                          , date_format(date, '%W %d/%m/%Y') as absent
                     FROM attendance_staff s
                          CROSS JOIN
                          date d
                          LEFT JOIN
                          attendance_record a ON s.oracleid = a.oracleid
                                              AND d.date = DATE(a.clockingindate)
                     WHERE a.oracleid IS NULL
                     ORDER BY s.oracleid, d.date    
                    ");
$tdata = '';
foreach ($res as $r) {
    $tdata .= "<tr><td>" . join('</td><td>', $r) . "</td></tr>\n";
}
?>
<html>
<head>
<title>Example</title>
<style type="text/css">
    table {
        border-collapse: collapse;
        width: 600px;
    }
    th, td {
        padding: 8px;
    }
    th {
        background-color: black;
        color: white;
    }
</style>
</head>
<body>
    <table border='1'>
        <tr><th>ID</th><th>Name</th><th>Absent</th></tr>
        <?=$tdata?>
    </table>
</body>
</html>

Thanks,

one more thing, instead of first Monday how to make it from first day, also in my country the holiday is Friday and Saturday not Saturday and Sunday, how to change that?

Barand · March 25, 2020

You can set the start date with

$start_date = new DateTime('2020-03-01');

Set the $incr value with

$incr = new DateInterval('P1D');

You will now get every day in the DatePeriod so you now have two choices:

Load every day (as I am) but then use a DELETE query to remove Fridays and Saturdays, or
Don't add Fri and Sat when iterating through the date period values.

EDIT: On further thought, my code writes Mon-Fri dates so if you always subtract 1 day when writing to the date table, you should get Sun-Thu dates.

foreach ($period as $d) {
    $d->sub(new DateInterval('P1D'));            // subtract one day before adding to table
    $dates[] = "('{$d->format('Y-m-d')}')" ;
}

Edited March 25, 2020 by Barand
added code

ramiwahdan · March 25, 2020

42 minutes ago, Barand said:

I thought you might be able manage that bit. Here's a fuller version


<?php

$month = 'March';
$start_date = new DateTime("first monday of $month");
$incr = DateInterval::createFromDateString('next weekday');
$period = new DatePeriod($start_date, $incr, new DateTime());

// create temporary date table
$conn->exec("CREATE TEMPORARY TABLE date (date date not null primary key)");
// populate it
foreach ($period as $d) {
    $dates[] = "('{$d->format('Y-m-d')}')" ;
}
$conn->exec("INSERT INTO date VALUES " . join(',', $dates));

// now get the days absent
$res = $conn->query("SELECT s.oracleid
                          , s.name
                          , date_format(date, '%W %d/%m/%Y') as absent
                     FROM attendance_staff s
                          CROSS JOIN
                          date d
                          LEFT JOIN
                          attendance_record a ON s.oracleid = a.oracleid
                                              AND d.date = DATE(a.clockingindate)
                     WHERE a.oracleid IS NULL
                     ORDER BY s.oracleid, d.date    
                    ");
$tdata = '';
foreach ($res as $r) {
    $tdata .= "<tr><td>" . join('</td><td>', $r) . "</td></tr>\n";
}
?>
<html>
<head>
<title>Example</title>
<style type="text/css">
    table {
        border-collapse: collapse;
        width: 600px;
    }
    th, td {
        padding: 8px;
    }
    th {
        background-color: black;
        color: white;
    }
</style>
</head>
<body>
    <table border='1'>
        <tr><th>ID</th><th>Name</th><th>Absent</th></tr>
        <?=$tdata?>
    </table>
</body>
</html>

i just ran this code, i am getting error


Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\AttendanceSystem\try.php

why?

Barand · March 25, 2020

I haven't a clue.

Which foreach() is it complaining about?

ramiwahdan · March 25, 2020

10 minutes ago, Barand said:

I haven't a clue.

Which foreach() is it complaining about?

$tdata = '';
foreach ($res as $r) {
    $tdata .= "<tr><td>" . join('</td><td>', $r) . "</td></tr>\n";
}
?>
<html>
<head>
<title>Example</title>
<style type="text/css">
    table {
        border-collapse: collapse;
        width: 600px;
    }
    th, td {
        padding: 8px;
    }
    th {
        background-color: black;
        color: white;
    }
</style>
</head>
<body>
    <table border='1'>
        <tr><th>ID</th><th>Name</th><th>Absent</th></tr>
        <?=$tdata?>
    </table>
</body>
</html>

if i add this i get the error, i noticed if i create normal table i am getting results but it also includes the non absent days. can you check where is the mistake?

full code

<?php

$month = 'March';
$start_date = new DateTime("first monday of $month");
$incr = DateInterval::createFromDateString('next weekday');
$period = new DatePeriod($start_date, $incr, new DateTime());

$servername = "localhost";
$username = "???";
$password = "???";
		
$conn = new PDO("mysql:host=$servername;dbname=timeclock", $username, $password);

// create temporary date table
$conn->exec("CREATE TEMPORARY TABLE date (date date not null primary key)");
// populate it
foreach ($period as $d) {
    $dates[] = "('{$d->format('Y-m-d')}')" ;
}
$conn->exec("INSERT INTO date VALUES " . join(',', $dates));

// now get the days absent
$res = $conn->query("SELECT s.oracleid
                          , s.name
                          , date_format(date, '%W %d/%m/%Y') as absent
                     FROM staff s
                          CROSS JOIN
                          date d
                          LEFT JOIN
                          attendance_records a ON s.oracleid = a.oracleid
                                              AND d.date = DATE(a.clockingindate)
                     WHERE a.oracleid IS NULL
                     ORDER BY s.oracleid, d.date    
                    ");

$tdata = '';
foreach ($res as $r) {
    $tdata .= "<tr><td>" . join('</td><td>', $r) . "</td></tr>\n";
}
?>
<html>
<head>
<title>Example</title>
<style type="text/css">
    table {
        border-collapse: collapse;
        width: 600px;
    }
    th, td {
        padding: 8px;
    }
    th {
        background-color: black;
        color: white;
    }
</style>
</head>
<body>
    <table border='1'>
        <tr><th>ID</th><th>Name</th><th>Absent</th></tr>
        <?=$tdata?>
    </table>
</body>
</html>

what about this, why is it null?

WHERE a.oracleid IS NULL

Edited March 25, 2020 by ramiwahdan
something wrong

Barand · March 25, 2020

You need to set a few attributes when you create your PDO connection.

    $conn = new PDO("mysql:host=$servername;dbname=timeclock", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Then you will be notified of any sql problems.

Did you read my edit to earlier post about subtracting 1 day from the dates (to get Sun - Thu working week)?

Edited March 25, 2020 by Barand

ramiwahdan · March 25, 2020

18 minutes ago, Barand said:
You need to set a few attributes when you create your PDO connection.
    $conn = new PDO("mysql:host=$servername;dbname=timeclock", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
Then you will be notified of any SQL problems.

Did you read my edit to earlier post about subtracting 1 day from the dates (to get Sun - Thu working week)?

Thank you very much sir, really great help and promise i will do something about it. Great

Barand · March 25, 2020

32 minutes ago, ramiwahdan said:
what about this, why is it null?
WHERE a.oracleid IS NULL

Because we are looking for missing dates. It's null because it isn't there. If it were there, they would not be absent.

Read about LEFT JOINS.

Edited March 25, 2020 by Barand

Sign In

names of days

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information