Jump to content

names of days

ramiwahdan

By ramiwahdan
in PHP Coding Help

 Share

Recommended Posts

ramiwahdan Advanced Member

ramiwahdan

Posted
Posted

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.

Link to comment
https://forums.phpfreaks.com/topic/310369-names-of-days/
Share on other sites
ramiwahdan Advanced Member

ramiwahdan

Posted
  • Author
Posted
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. 

Link to comment
https://forums.phpfreaks.com/topic/310369-names-of-days/#findComment-1575766
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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.

Link to comment
https://forums.phpfreaks.com/topic/310369-names-of-days/#findComment-1575767
Share on other sites
Barand Prolific Member

Barand

Posted
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
Link to comment
https://forums.phpfreaks.com/topic/310369-names-of-days/#findComment-1575768
Share on other sites
ramiwahdan Advanced Member

ramiwahdan

Posted
  • Author
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
Link to comment
https://forums.phpfreaks.com/topic/310369-names-of-days/#findComment-1575771
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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
Link to comment
https://forums.phpfreaks.com/topic/310369-names-of-days/#findComment-1575775
Share on other sites
ramiwahdan Advanced Member

ramiwahdan

Posted
  • Author
Posted
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?

Link to comment
https://forums.phpfreaks.com/topic/310369-names-of-days/#findComment-1575776
Share on other sites
Barand Prolific Member

Barand

Posted
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
Link to comment
https://forums.phpfreaks.com/topic/310369-names-of-days/#findComment-1575777
Share on other sites
ramiwahdan Advanced Member

ramiwahdan

Posted
  • Author
Posted
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?

 

Link to comment
https://forums.phpfreaks.com/topic/310369-names-of-days/#findComment-1575778
Share on other sites
ramiwahdan Advanced Member

ramiwahdan

Posted
  • Author
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
Link to comment
https://forums.phpfreaks.com/topic/310369-names-of-days/#findComment-1575780
Share on other sites
Barand Prolific Member

Barand

Posted
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
Link to comment
https://forums.phpfreaks.com/topic/310369-names-of-days/#findComment-1575781
Share on other sites
ramiwahdan Advanced Member

ramiwahdan

Posted
  • Author
Posted
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

Link to comment
https://forums.phpfreaks.com/topic/310369-names-of-days/#findComment-1575782
Share on other sites
Barand Prolific Member

Barand

Posted
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
Link to comment
https://forums.phpfreaks.com/topic/310369-names-of-days/#findComment-1575783
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

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.

×
 Share
Go to topic listing
×
×
  • 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.