SitePoint Sponsor

User Tag List

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

    BETWEEN query not working just right...

    Hey Guys,
    I've got a time clock database and I'm trying to run a report of people who were late for a shift. I'm pulling data from two tables (data and users) I have two time fields. One is time (VARCHAR with AM/PM, etc.) and the other is trutime (DATETIME field).
    Here's the query:
    Code:
    SELECT data.date, data.time, data.trutime, data.reason, users.FirstName, users.LastName FROM data, users WHERE data.reason = 'morning_arrival' AND data.trutime BETWEEN '08:45:00' AND '10:00:00' ORDER BY data.date");
    Here's what I'm getting:

    Overages Report for month of January
    Name Date Time Reason
    Delilah Brantley 2013-01-16 08:51:49 am morning_arrival
    Lychelle Allen 2013-01-16 08:51:49 am morning_arrival
    Chris Woodburn 2013-01-16 08:51:49 am morning_arrival
    Ellen Jones 2013-01-16 08:51:49 am morning_arrival
    Heath Lee 2013-01-16 08:51:49 am morning_arrival
    Anne Bowen 2013-01-16 08:51:49 am morning_arrival
    Josh Sheffield 2013-01-16 08:51:49 am morning_arrival
    Lychelle Allen 2013-01-17 08:45:02 am morning_arrival
    Chris Woodburn 2013-01-17 08:45:02 am morning_arrival
    Ellen Jones 2013-01-17 08:45:02 am morning_arrival
    Heath Lee 2013-01-17 08:45:02 am morning_arrival
    Anne Bowen 2013-01-17 08:45:02 am morning_arrival
    Josh Sheffield 2013-01-17 08:45:02 am morning_arrival
    Delilah Brantley 2013-01-17 08:45:02 am morning_arrival
    As you can see, it's adding every user to every overage...I know it's a simple something, but blast if I can see it.
    Just for reference, here's the php code to go with:
    PHP Code:
    <?php
        

            
    //find overages     
            
    $query mysql_query("SELECT data.date, data.time, data.trutime, data.reason, users.FirstName, users.LastName FROM data, users WHERE data.reason = 'morning_arrival' AND data.trutime BETWEEN '08:45:00' AND '10:00:00' ORDER BY data.date");
            
    $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>";
            
    ?>
    Any help would be GREATLY appreciated!!!
    Thanks in advance guys

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    You forgot the join criteria in your query. Right now it's giving you a cartesian product.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by josh.sheffield View Post
    ...and the other is trutime (DATETIME field).
    something's not quite right here because a DATETIME column includes a date component combined with a time component, yet your WHERE condition is
    Code:
     data.trutime BETWEEN '08:45:00' AND '10:00:00'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    You forgot the join criteria in your query. Right now it's giving you a cartesian product.
    Can you please explain a little further? This is obviously above my current skillset, but would love to learn this!

  5. #5
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry it's a TIME column NOT a DATETIME...my mistake. Sorry for adding to the confusion.

  6. #6
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,024
    Mentioned
    211 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by josh.sheffield View Post
    Can you please explain a little further? This is obviously above my current skillset, but would love to learn this!
    When you query more than one table, you need to specify the relationship between the tables else you will have a cartesian product, that is, the result of the query will be that for every row of table A, it will show all the records from table B.

    When there's a relationship between two tables, you only get the rows of each table that have something in common.

    Let me put an example:

    You have a table customers

    Code:
    cust_ID    Name
      1             Me
      2             You
    and a table where you information about their invoices (I'll forget that there could be a table with the products just to keep it simple)

    Code:
    invoice_nbr   product        cust_ID
    1                milk             2
    2                chocolate     2
    3                bread          1
    as you can see, in both tables there's a customer ID, there is a relation between them. If I don't JOIN them (that is, I don't add the relationship in my query), I will get 2 customers x 3 invoices = 6 records

    But if I use a JOIN, I will only get the true number of records, 3 which is the number of invoices that it is really associated to a customer.


    You can see the relationship when you see how they JOIN. He doesn't have one.

  7. #7
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Worked like a charm! Thanks! Now here's another conundrum:

    what about finding records where the time between lunch_leave and lunch_return is greater than 30 minutes? I don't think I can run it all in one big query and be able to have it make sense on the output just yet. But is there a calculation methodology? I can see how to see if a time stamp is greater than a predetermined time, but we all leave for lunch at various times...so it'll be comparing leaving and return. I guess I'll need to do some maths as well?
    Thanks in adavance...If i need to create another thread, let me know.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    SELECT ( TIME_TO_SEC(end_lunch) - TIME_TO_SEC(start_lunch) ) / 60 AS lunch_minutes
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    SELECT ( TIME_TO_SEC(end_lunch) - TIME_TO_SEC(start_lunch) ) / 60 AS lunch_minutes
    man...I think I owe you a beer.


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
  •