SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    The TimeClock conundrum continues...now with more MATH

    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:
    Code:
    $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

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,079
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by josh.sheffield View Post
    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:
    Code:
    $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?

  3. #3
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,079
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by josh.sheffield View Post
    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

  5. #5
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,079
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by josh.sheffield View Post
    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.

  7. #7
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So cold. ;-)

    Here's the code:
    PHP 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

  8. #8
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's the code that inserts everything from the original form:
    PHP Code:
    <?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');
    ?>

  9. #9
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,079
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by josh.sheffield View Post
    Here's the code that inserts everything from the original form:
    PHP Code:
    <?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:

    Code MySQL:
    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
    Last edited by K. Wolfe; Feb 8, 2013 at 10:03. Reason: formatting

  10. #10
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...

  11. #11
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?"

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by josh.sheffield View Post
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •