Jump to content
#FlattenTheCurve ×
ramiwahdan

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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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>
<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>

 

  • Like 1

Share this post


Link to post
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>
<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?

Share this post


Link to post
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
added code

Share this post


Link to post
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>
<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?

 

Share this post


Link to post
Share on other sites

I haven't a clue.

Which foreach() is it complaining about?

Share this post


Link to post
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>
<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 by ramiwahdan
something wrong

Share this post


Link to post
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
  • Great Answer 1

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
Posted (edited)
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 by Barand

Share this post


Link to post
Share on other sites

Join the conversation

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

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

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


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.