The TimeClock conundrum continues...now with more MATH

[josh.sheffield]

Hey Everyone,
A few days ago I posted a question about getting a timeclock database/web front end to behave properly and got great responses...I'm hoping to duplicate that with this issue:
Lunch Overages

I need to run a query that will pull when they left and when they got back (easy enough.) However, here's where the water gets over my ears; I also need to then subtract the leave time from the arrive time for each user for each day THEN only return the results that are longer than 30 minutes.
Here's the query that I'm running to pull the 411:

$lunch = "SELECT data.uid, data.date, data.time, data.trutime, data.reason, users.FirstName, users.LastName, users.uid 
FROM data, users 
WHERE 
	data.uid = users.uid 
AND 
	data.date LIKE '%-$month-%' 
AND 
(	
	data.reason = 'lunch_leave'
OR
	data.reason = 'lunch_return'
)

ORDER BY data.date, data.trutime";

the month is posted in from a form on the previous page. Not sure if a foreach php run or straight mysql loop will work to do what I need it to do, but any help will be greatly appreciated!
Thanks
Josh

[K. Wolfe]

Hey Everyone,
A few days ago I posted a question about getting a timeclock database/web front end to behave properly and got great responses...I'm hoping to duplicate that with this issue:
Lunch Overages

I need to run a query that will pull when they left and when they got back (easy enough.) However, here's where the water gets over my ears; I also need to then subtract the leave time from the arrive time for each user for each day THEN only return the results that are longer than 30 minutes.
Here's the query that I'm running to pull the 411:

$lunch = "SELECT data.uid, data.date, data.time, data.trutime, data.reason, users.FirstName, users.LastName, users.uid 
FROM data, users 
WHERE 
    data.uid = users.uid 
AND 
    data.date LIKE '%-$month-%' 
AND 
(    
    data.reason = 'lunch_leave'
OR
    data.reason = 'lunch_return'
)

ORDER BY data.date, data.trutime";

the month is posted in from a form on the previous page. Not sure if a foreach php run or straight mysql loop will work to do what I need it to do, but any help will be greatly appreciated!
Thanks
Josh

This is relatively simple, just need to look at it a new way Can you do a 'show create table' for me so I can see how your dates / times are stored please?

[josh.sheffield]

Thanks for the quick reply! Here ya go

For the user table:

Table Create Table
users CREATE TABLE `users` (
`uid` int(11) NOT NULL auto_increment,
`username` varchar(60) NOT NULL,
`FirstName` varchar(50) NOT NULL,
`LastName` varchar(50) NOT NULL,
`password` varchar(32) NOT NULL,
PRIMARY KEY (`uid`),
KEY `LastName` (`LastName`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8

now for the data table:
CREATE TABLE `data` (
`id` int(11) NOT NULL auto_increment,
`UID` int(11) NOT NULL,
`date` varchar(11) NOT NULL,
`time` varchar(20) NOT NULL,
`trutime` time NOT NULL,
`reason` varchar(25) NOT NULL,
PRIMARY KEY (`id`),
KEY `reason_2` (`reason`),
KEY `time_2` (`time`),
FULLTEXT KEY `reason` (`reason`),
FULLTEXT KEY `time` (`time`)
) ENGINE=MyISAM AUTO_INCREMENT=405 DEFAULT CHARSET=utf8

[K. Wolfe]

Thanks for the quick reply! Here ya go

For the user table:

Table Create Table
users CREATE TABLE `users` (
`uid` int(11) NOT NULL auto_increment,
`username` varchar(60) NOT NULL,
`FirstName` varchar(50) NOT NULL,
`LastName` varchar(50) NOT NULL,
`password` varchar(32) NOT NULL,
PRIMARY KEY (`uid`),
KEY `LastName` (`LastName`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8

now for the data table:
CREATE TABLE `data` (
`id` int(11) NOT NULL auto_increment,
`UID` int(11) NOT NULL,
`date` varchar(11) NOT NULL,
`time` varchar(20) NOT NULL,
`trutime` time NOT NULL,
`reason` varchar(25) NOT NULL,
PRIMARY KEY (`id`),
KEY `reason_2` (`reason`),
KEY `time_2` (`time`),
FULLTEXT KEY `reason` (`reason`),
FULLTEXT KEY `time` (`time`)
) ENGINE=MyISAM AUTO_INCREMENT=405 DEFAULT CHARSET=utf8

What are the roles of 'date', 'time' and 'truetime'? What format are the strings in 'date' and 'time' in?

I'm sure you can guess where I'm going to go with this next post

[josh.sheffield]

I'm thinking I'm about to expose my inexperience when I tell you this, but here it goes:
The date field is populated by php date() when the form is submitted as is time. trutime is the only true TIME field (go ahead, be amazed at my inventive naming structure.)
Date and time are then repopulated into a report. The trutime field is where I was planning to do the math with as it's a true integer and the others are VARCHAR and Indexed for searching.

[K. Wolfe]

I'm thinking I'm about to expose my inexperience when I tell you this, but here it goes:
The date field is populated by php date() when the form is submitted as is time. trutime is the only true TIME field (go ahead, be amazed at my inventive naming structure.)
Date and time are then repopulated into a report. The trutime field is where I was planning to do the math with as it's a true integer and the others are VARCHAR and Indexed for searching.

It's ok, I've already derived that from the format of your field, but there's many ways you can format a date in PHP, so i need to see an example of it populated.

Ideally you'd just run a single datetime field for anything, you can extract just the time or the date from the field if needed.

[josh.sheffield]

So cold. ;-)

Here's the code:

<?php
include "includes/topnav.php";
?>    
    <table id=report>
        <tr>
        <td colspan=4><div id=title> Overages Report for month  of <?php echo $monthName;?></div></td>
        </tr>
        <tr>
            <th>Name</th>
            <th>Date</th>
            <th>Time</th>
            <th>Reason</th>
        </tr>
        
<?php
    

        //find overages
        include "includes/lunch_query.php";
     
        $query = mysql_query($lunch);
        $check = mysql_num_rows($query);
         if ($check == 0) {
             die($query."<br/><br/>".mysql_error());
                 }
        else {
        while ($result = mysql_fetch_array( $query ))   {
        
        $first=$result['FirstName'];
        $last=$result['LastName'];
        $name= $first . " " . $last;
        
        echo "<tr><td>";
        echo $name;
        echo "</td><td>";
        
        echo $result['date'];
        echo "</td><td>";
        echo $result['time'];
        echo "</td><td>";
        echo $result['reason'];
        echo "</td></tr>";
        
?>
<?php
}
} 
 

?>
</table>
</html>

Here is output:
Name Date Time Reason
Anne Bowen 2013-01-15 11:22:55 am lunch_leave
Delilah Brantley 2013-01-15 11:49:00 am lunch_leave
Anne Bowen 2013-01-15 11:51:36 am lunch_return
Josh Sheffield 2013-01-15 12:03:50 pm lunch_leave
Lychelle Allen 2013-01-15 12:10:20 pm lunch_leave
Delilah Brantley 2013-01-15 12:19:04 pm lunch_return
Josh Sheffield 2013-01-15 12:27:06 pm lunch_return
Heath Lee 2013-01-15 12:42:31 pm lunch_leave
Lychelle Allen 2013-01-15 12:49:21 pm lunch_return
Heath Lee 2013-01-15 01:03:33 pm lunch_return
Delilah Brantley 2013-01-16 11:44:11 am lunch_leave
Josh Sheffield 2013-01-16 12:10:06 pm lunch_leave
Delilah Brantley 2013-01-16 12:16:58 pm lunch_return
Josh Sheffield 2013-01-16 12:23:16 pm lunch_return
Heath Lee 2013-01-16 12:28:10 pm lunch_leave
Heath Lee 2013-01-16 12:59:21 pm lunch_return
Lychelle Allen 2013-01-16 01:14:17 pm lunch_leave
Lychelle Allen 2013-01-16 03:42:30 pm lunch_return
Anne Bowen 2013-01-17 11:15:59 am lunch_leave
Anne Bowen 2013-01-17 11:42:07 am lunch_return
Delilah Brantley 2013-01-17 11:44:11 am lunch_leave
Ellen Jones 2013-01-17 12:02:17 pm lunch_leave
Delilah Brantley 2013-01-17 12:08:21 pm lunch_return
Ellen Jones 2013-01-17 12:16:01 pm lunch_return
Lychelle Allen 2013-01-17 12:33:09 pm lunch_leave
Lychelle Allen 2013-01-17 01:05:58 pm lunch_return
Delilah Brantley 2013-01-18 11:48:01 am lunch_leave
Delilah Brantley 2013-01-18 12:12:42 pm lunch_return
Josh Sheffield 2013-01-18 12:48:39 pm lunch_leave
Josh Sheffield 2013-01-18 01:09:48 pm lunch_return
Delilah Brantley 2013-01-19 11:47:38 am lunch_leave
Delilah Brantley 2013-01-19 12:11:27 pm lunch_return
Delilah Brantley 2013-01-22 11:45:02 am lunch_leave
Ellen Jones 2013-01-22 12:02:12 pm lunch_leave
Delilah Brantley 2013-01-22 12:14:42 pm lunch_return
Ellen Jones 2013-01-22 12:17:15 pm lunch_return
Lychelle Allen 2013-01-22 12:57:06 pm lunch_leave
Lychelle Allen 2013-01-22 01:37:23 pm lunch_return
Ellen Jones 2013-01-23 11:33:12 am lunch_leave
Delilah Brantley 2013-01-23 11:45:34 am lunch_leave
Ellen Jones 2013-01-23 11:53:27 am lunch_return
Ellen Jones 2013-01-23 11:59:25 am lunch_return
Delilah Brantley 2013-01-23 12:18:07 pm lunch_return
Lychelle Allen 2013-01-23 12:35:26 pm lunch_leave
Chris Woodburn 2013-01-23 12:39:16 pm lunch_leave
Chris Woodburn 2013-01-23 01:05:37 pm lunch_return
Lychelle Allen 2013-01-23 01:06:44 pm lunch_return
Delilah Brantley 2013-01-24 11:50:17 am lunch_leave
Ellen Jones 2013-01-24 11:51:05 am lunch_leave
Ellen Jones 2013-01-24 12:03:11 pm lunch_return
Delilah Brantley 2013-01-24 12:14:12 pm lunch_return
Chris Woodburn 2013-01-24 12:25:49 pm lunch_leave
Chris Woodburn 2013-01-24 12:54:01 pm lunch_return
Anne Bowen 2013-01-25 11:39:35 am lunch_leave
Ellen Jones 2013-01-25 11:52:53 am lunch_leave
Anne Bowen 2013-01-25 12:01:15 pm lunch_return
Ellen Jones 2013-01-25 12:06:20 pm lunch_return

[josh.sheffield]

Here's the code that inserts everything from the original form:

<?php
mysql_connect("localhost", "timeclock", "t1m3cl0ck") or die(mysql_error()); mysql_select_db("timeclock") or die(mysql_error());
$now = date("h:i:s a");
$today = date("Y-m-d");
$uid = $_POST['uid'];
$tru = date("Y-m-d H:i:s");
$insert = "INSERT INTO data (uid, date, time, trutime, reason) VALUES ('$uid','$today', '$now', '$tru', '".$_POST['reason']."')";
if (!mysql_query($insert))
  {
  die('Error: ' . mysql_error());
  }
header('Location: login.php');
?>

[K. Wolfe]

Here's the code that inserts everything from the original form:

<?php
mysql_connect("localhost", "timeclock", "t1m3cl0ck") or die(mysql_error()); mysql_select_db("timeclock") or die(mysql_error());
$now = date("h:i:s a");
$today = date("Y-m-d");
$uid = $_POST['uid'];
$tru = date("Y-m-d H:i:s");
$insert = "INSERT INTO data (uid, date, time, trutime, reason) VALUES ('$uid','$today', '$now', '$tru', '".$_POST['reason']."')";
if (!mysql_query($insert))
  {
  die('Error: ' . mysql_error());
  }
header('Location: login.php');
?>

Perfect! You've got those formatted correctly. If it's not too much work at this stage, you might want to create a test table and see what is like to use the date/time format on the table, you might be able to do an alter table without any loss (make sure you try on a TEST table first )

So another thing is that date, time are mysql commands, and they shouldn't be used as column names I'd try to get to this format:

uid, reason, reason_date

where reason_date is your full date time but not in varchar.

From there you might write a query like such:

select
  ll.uid, ll.reason_date as lunch_leave, lr.reason_date as lunch_return
from
(
  /* Grab clock outs */
  select 
    uid, reason, reason_date
  from
    myTable
  where
    reason = 'lunch_leave'
) ll
left join 
(
  /* Grab clock ins */
  select 
    uid, reason, reason_date
  from
    myTable
  where
    reason = 'lunch_return'
) lr on ll.uid = lr.uid and 
  date(ll.reason_date) = date(lr.reason_date) //join by uid and date portion of reason date (brings in the lunch return for the same day of the lunch leave)

Now you have a view that can manipulated fairly easy, and you can run timediff() on the two new columns, and apply criteria to it

[josh.sheffield]

Going to have to spend some time with this one... I have never had success with AS. But I'm willing to give it a college try.
So you're saying that I can pull date/time information out a simple datetime field and have it display how I want? I wouldn't have to do some funky php trick to take it from 24hr time to 12hr time format?

Again excuse my ignorance...

[josh.sheffield]

Also, where are you getting the lr and ll's? Are you using them in place of table names or can you assign those "in script?"

[r937]

Also, where are you getting the lr and ll's? Are you using them in place of table names or can you assign those "in script?"

yes, those are table aliases

the tables in question are derived tables, which is the term used to describe the functionality of a subquery in the FROM clause

[josh.sheffield]

Thanks! I'll get on learning this and see if I can make it dance like I want it too. I appreciate the patience and quick responses.