#StayAtHome ×

names of days

Recommended Posts

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.

Share on other sites
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.

Share on other sites

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.

Share on other sites
Posted (edited)

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 by Barand

Share on other sites
Posted (edited)
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 by ramiwahdan
only works if i remove word temporary

Share on other sites

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

Share on other sites
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?

Share on other sites

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>
<title>Example</title>
<style type="text/css">
table {
border-collapse: collapse;
width: 600px;
}
th, td {
}
th {
background-color: black;
color: white;
}
</style>
<body>
<table border='1'>
<tr><th>ID</th><th>Name</th><th>Absent</th></tr>
<?=\$tdata?>
</table>
</body>
</html>```

Share on other sites
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>
<title>Example</title>
<style type="text/css">
table {
border-collapse: collapse;
width: 600px;
}
th, td {
}
th {
background-color: black;
color: white;
}
</style>
<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?

Share on other sites
Posted (edited)

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:

1. Load every day (as I am) but then use a DELETE query to remove Fridays and Saturdays, or
2. 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 by Barand

Share on other sites
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>
<title>Example</title>
<style type="text/css">
table {
border-collapse: collapse;
width: 600px;
}
th, td {
}
th {
background-color: black;
color: white;
}
</style>
<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?

Share on other sites

I haven't a clue.

Which foreach() is it complaining about?

Share on other sites
Posted (edited)
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>
<title>Example</title>
<style type="text/css">
table {
border-collapse: collapse;
width: 600px;
}
th, td {
}
th {
background-color: black;
color: white;
}
</style>
<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";

// 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>
<title>Example</title>
<style type="text/css">
table {
border-collapse: collapse;
width: 600px;
}
th, td {
}
th {
background-color: black;
color: white;
}
</style>
<body>
<table border='1'>
<tr><th>ID</th><th>Name</th><th>Absent</th></tr>
<?=\$tdata?>
</table>
</body>
</html>```

`WHERE a.oracleid IS NULL`

Edited by ramiwahdan
something wrong

Share on other sites
Posted (edited)

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 by Barand

Share on other sites
18 minutes ago, Barand said:

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

```
\$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

Share on other sites
Posted (edited)
32 minutes ago, ramiwahdan said:

```
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.

Edited by Barand

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

×

• Activity

• Chat
×
• Create New...