php date problem

[ajmcello]

I'm trying to get the previous days' date from a record I'm pulling from postgresql. So, $date may equal "2006-12-01" several times in my $date array, but I need to know what the previous date is, from a record in postgres, if it exists, such as "2006-11-30".

Does that make any sense? :) Here's the code I'm working with below. Of course the way I wrote it, it is only possibly accurate once.

[code=php:0]
$x=1;
$y=0;

    while ($row = pg_fetch_array($myresult))
    {
        $ctid = $row["ctid"];
        $time = $row["time"];
        $inout = $row["inout"];
        $date[$y] = $row["date"];
        $id = $row["id"];
        $empid = $row["fullname"];
        $timestamp = $row["timestamp"];

        $name = get_name($empid);
        print "
        <tr><td><table width=100%>
        $x)
        Employee: <font color=brown>$name</font>
        Date: <font color=blue>$date[$y]</font>
        Time: <font color=red>$time</font>
        Status: <font color=green>$inout</font>
        </select>
        </tr></td></table>
        ";
        print "Current: $date[$y]<br>";
        $y--;
        print "Last: $date[$y]<br>";
        $y++;
        $x++;
        $y++;
    }
[/code]
The code produces the following output:

1) Employee: Name1  Date: 2006-12-08  Time: 06:00:00  Status: in
Current: 2006-12-08
Last:
2) Employee: Name1 Date: 2006-12-08 Time: 12:00:00 Status: out
Current: 2006-12-08
Last: 2006-12-08
3) Employee: Name1 Date: 2006-12-08 Time: 12:45:00 Status: in
Current: 2006-12-08
Last: 2006-12-08
4) Employee: Name1 Date: 2006-12-08 Time: 19:00:00 Status: out
Current: 2006-12-08
Last: 2006-12-08
5) Employee: Name1 Date: 2006-12-09 Time: 05:30:00 Status: in
Current: 2006-12-09
Last: 2006-12-08
6) Employee: Name1 Date: 2006-12-09 Time: 12:05:05 Status: out
Current: 2006-12-09
Last: 2006-12-09
7) Employee: Name1 Date: 2006-12-09 Time: 13:15:01 Status: in
Current: 2006-12-09
Last: 2006-12-09
8) Employee: Name1 Date: 2006-12-09 Time: 19:30:12 Status: out
Current: 2006-12-09
Last: 2006-12-09
9) Employee: Name2 Date: 2006-12-11 Time: 11:33:45 Status: in
Current: 2006-12-11
Last: 2006-12-09
10) Employee: Name2 Date: 2006-12-11 Time: 11:33:49 Status: out
Current: 2006-12-11
Last: 2006-12-11

[craygo]

so you want to create a date before the one that is outputted?

[Psycho]

Your question is not very clear. Are you wanting to show the previous day for each date? Are you wanting to show the date from the previous record? Or are you wanting to show the date from the previous record - that did not have the same date.

Could you give an example of the data retrieved from the database and how you want it to be displayed?

[ajmcello]

What I ultimately want to do is determine when a new day occurs. So maybe I'm going about it all wrong. For example, records 1-4 for Employee Name1 occur in the same day, while 5-8 are in a new day.

So wouldn't the way to go about is to check to see when $date changes to the next day?

Sorry to be all so confusing.

[craygo]

So what you actually want is to group your results by dates?? Everytime a date changes, display the date and the employees that fall on that date.

Am I correct on this??

Ray

[Psycho]

In those situations I use a tracking variable to see when a particular data item changes. This is useful when you want to show sub heading or sub totals. Below is some code for you to test out - I also made some other corrections as your HTML tags were out of place, in the wrong order, or otherwise incorrect.

[code]<?php
    $x=0;
    $currentDate = "";

    print "<table width=100%>";

    while ($row = pg_fetch_array($myresult))
    {
        $x++;
        $ctid = $row["ctid"];
        $time = $row["time"];
        $inout = $row["inout"];
        $date = $row["date"];
        $id = $row["id"];
        $empid = $row["fullname"];
        $timestamp = $row["timestamp"];
        $name = get_name($empid);

        if ($currentDate != $date) {
            Print "<tr><td><b>Date: " . $date . "</b></td></tr>";
            $currentDate = $date;
        }

        print "<tr><td style=\"padding-left:10px\"> $x)
            Employee: <font color=brown>$name</font><br>
            Date: <font color=blue>$date[$y]</font><br>
            Time: <font color=red>$time</font><br>
            Status: <font color=green>$inout</font>
            </td></tr>";
    }

print "</table>";
?>[/code]

The code should print out something like this:


[b]Date: 2006-12-08[/b]
1) Employee: Name1
Time: 06:00:00
Status: in

2) Employee: Name1
Time: 12:00:00
Status: out

3) Employee: Name1
Time: 12:45:00
Status: in

4) Employee: Name1
Time: 19:00:00
Status: out

[b]Date: 2006-12-09 [/b]
5) Employee: Name1
Time: 05:30:00
Status: in

6) Employee: Name1
Time: 12:05:05
Status: out

7) Employee: Name1
Time: 13:15:01
Status: in

8) Employee: Name1
Time: 19:30:12
Status: out

[b]Date: 2006-12-11[/b]
9) Employee: Name2
Time: 11:33:45
Status: in

10) Employee: Name2
Time: 11:33:49
Status: out

[ajmcello]

I have it sorted properly using ORDER BY and WHERE for each employee from the pgsql query.

What I want to do next is determine when the date changes from one day to the next, per employee. This is actually a time system I'm working on. For each day if the employee works 6.0 hours or more, I want to add 60 minutes of paid lunch. This is where the date change comes in.

Here's the entire thing.

[code]
<html>
<body>
<?php

include 'defs.php';
include 'functions.php';

$connection = pg_connect("dbname=$dbname user=$dbuser password=$dbpasswd host=$dbhost");
if (!$connection) {
print("Connection Failed.");
exit;
}

        $dates = array();

        $fromdate = $_GET['fromdate'];
        $todate = $_GET['todate'];
        list($fromyear, $frommonth, $fromday) = split("-", $fromdate, 3);
        list($toyear, $tomonth, $today) = split("-", $todate, 3);

        $start = strtotime($fromdate);
        $end = strtotime($todate);
        while ($start <= $end)
        {
                $dates[] = date('Y-m-d',$start);
                $start += 86400;
        }
        $x=0;        $count = count($dates);
        while ($count > $x)
        {
                $query = "SELECT * from holidays";
                $myresult = pg_exec($connection,$query);
                $num_rows = pg_numrows($myresult);
                if ($num_rows == 0) {
                        print("No records exist from the <b>holiday</b> table.<br>");
                        exit(1);
                }
                while ($row = pg_fetch_array($myresult))
                {
                        $hdays = $row["hdays"];
                        if($hdays == $dates[$x])
                                $hhours = $hhours + $hdaypaidhours;
                }
                // echo "$dates[$x]<br>";
                $x++;
        }

                echo "<br><br><br>";

        $query = "SELECT ctid,* FROM timeclock WHERE date BETWEEN '$frommonth.$fromday.$fromyear'
AND '$tomonth.$today.$toyear' ORDER BY fullname";
        // echo "<br>Query: $query<br>";

$myresult = pg_exec($connection,$query);
$num_rows = pg_numrows($myresult);

if ($num_rows == 0) {
        print("No records exist or employee number doesn't exist");
        exit(1);
}
else {
print("Reports From: <b>$fromdate</b> To: <b>$todate</b><br><br>");

$x=1;
$y=0;

while ($row = pg_fetch_array($myresult))
{
                $ctid = $row["ctid"];
                $time = $row["time"];
                $inout = $row["inout"];
                $date[$y] = $row["date"];
                $id = $row["id"];
                $empid[$y] = $row["fullname"];
                $timestamp = $row["timestamp"];

                $name = get_name($empid[$y]);
                print "
                <tr><td><table width=100%>
                $x)
                Employee: <font color=brown>$name</font>
                Date: <font color=blue>$date[$y]</font>
                Time: <font color=red>$time</font>
                Status: <font color=green>$inout</font>
                </select>
                </tr></td></table>
                ";

                if($inout == "in")
                        $inepoch=$timestamp;

                if($inout == "out")
                {
                        $outepoch=$timestamp;
                        $difftime = $difftime + ($outepoch - $inepoch) / $secsperhour;
                        $workedtime = ($outepoch - $inepoch) / $secsperhour;
                        if($difftime >= $lunchhours)
                                print "$name has $lunchbreak minutes of paid lunch time<br>";
                        printf("Accumulated hours: %.01f, Worked hours: %.01f<br><br>", $difftime
, $workedtime);
//
// need fix to actually check per day
//
                        if($workedtime > $maxhours)
                                print "<font color=red>*** POSSIBLE ERROR! Worked hours exceeds 1
6!</font><br>";
                        $octid = getlast($empid[$y], $fromdate, $todate);

                }

                if($ctid == $octid)
                {
                        if($inout == "in")
                                print "<font color=red>*** ERROR! No out record. Please correct!!
!</font><br>";
                        printf("<b>Total hours: %.01f</b><br>", $difftime+$hhours);
                        if($hhours > 0)
                                printf("<b>Holiday hours: %.01f</b><br>", $hhours);
                        print "<br><br><br>";
                        $difftime = 0;
                }

                $x++;
                $y++;
        }
}
?>
</body>
</html>
[/code]

[Psycho]

[quote]What I want to do next is determine when the date changes from one day to the next, per employee.[/quote]

The example I gave you shows how to determine when the date changes - just use the same process to determine when the employee changes and use a running variable to detemrine total hours.

BTW: Your HTML is still all messed up.