Date matching loop

Question

I'm coding a time registry for employes. I have two columns. Stamp_in and Stamp_Out. When an employe register in, a DateTime value is stored in db. Looking like: $datetime_now = date('Y-m-d H:i:s'); and equal when register out. But when registering in, the value in stamp_out column will be: 0000-00-00 00:00:00. And the same for registering out, in stamp_in column.

Now, when i loop this out, i like to match dates on same day to print on the same row, but i don't want the "empty" dates to print.

How should this code look like?

This is what i got so far:

PHP

//Loop out the result
while ($row = $stmt->fetch()) {
  echo "<tr>";
    echo "<td>" .$row['usr_stamp_in']."</td>";
    echo "<td>" .$row['usr_stamp_in']."</td>";
  echo "</tr>";
}

I tried

if($row['usr_stamp_in'] != "0000-00-00 00:00:00"{
  while($row ...

instead of dealing with php when your employee registers in, why don't you just act it in your db with a timestamp like CURRENT_TIMESTAMP or NOW() on login/logout ? You're almost on it with your query, but you need to do it this way : while($row = $stmt->fetch()){ if($row['usr_stamp_in']!="0000-00-00 00:00:00"){//do whatever you want}}. BTW to match same day, you need to fix it with a WHERE and then ORDER BY when you get data from your db AND you should remove empty rows from db in your query too with a WHERE clause ;) — Julo0sS
– Julo0sS, Commented Feb 3, 2015 at 8:13
@Jens. Yes, you're logging events. Only one date is needed, and a field indicating the type of event. — KIKO Software
– KIKO Software, Commented Feb 3, 2015 at 8:14
I'm on a webhost company, wich server time isn't the same as mine. So, timestamp will give the wrong time. Otherwise very useful tips. Thank you! — d00rman
– d00rman, Commented Feb 3, 2015 at 12:58

Community · Accepted Answer · 2017-05-23 11:49:30Z

1

You should be doing the filter part in your SQL query using the not equal operator <>. Things will look like SELECT * FROM employee_table WHERE usr_stamp_in <> '0000-00-00 00:00:00'

To be more precise there is a topic on SO about that MySQL select where column is not empty

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Feb 3, 2015 at 8:14

Jaay

2,1411 gold badge17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter Over a year ago

I'd go for != to check whether usr_stamp_in is set. But good suggestion!

Peter · Accepted Answer · 2015-02-03 08:27:22Z

0

You should do the if statement inside your while loop:

//Loop out the result
while ($row = $stmt->fetch()) {
    if ($row['usr_stamp_in'] != "0000-00-00 00:00:00") {
        echo "<tr>";
        echo "<td>" .$row['usr_stamp_in']."</td>";
        echo "<td>" .$row['usr_stamp_in']."</td>";
        echo "</tr>";
    }
}

You could also do it in mysql as follows:

SELECT *
FROM users
WHERE usr_tstamp_in != '0000-00-00 00:00:00'
ORDER BY usr_tstamp_in DESC;

This will select all records from the 'users' table that do not have a usr_tstamp_in with value '0000-00-00 00:00:00' and orders those records by date newest to oldest.

I still suggest you use the PHP part as well. Better safe than sorry.

edited Feb 3, 2015 at 8:27

answered Feb 3, 2015 at 8:12

Peter

9,1868 gold badges69 silver badges100 bronze badges

Collectives™ on Stack Overflow

Date matching loop

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related