SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Jan 2005
    Location
    USA
    Posts
    633
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Reading from MySQl DB

    Hi Guys

    I am entering times from a Biometric device into a DB, that works fine.When I read the times
    which has multiple rows per user for a day. eg..

    ID DATE TIME
    ----------------------------
    900 2012-25-03 07:00
    901 2012-25-03 07:01
    900 2012-25-03 13:00
    900 2012-25-03 14:00
    900 2012-25-03 16:00

    What I want is in order to work out the times that has been worked for ID 900 I have to use the first instance and
    the last instance for ID 900 for a particular day.

    Is there a function available to read only this ?

    Thanks
    DD

  2. #2
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not sure about a 'min' 'max' function for mysql; however, this may get you what you want...

    PHP Code:
    $w_id 900;
    $query ="SELECT id, datetime FROM table_name WHERE id ='$w_id ORDER BY datetime ASC";
    $result mysql_query($query);
    while(
    $row mysql_fetch_array($result)) {
        
    $data[] = $row['datetime'];
    }
    $i count($data) - 1;
    $start_data $data[0];
    $end_data $data[$i]; 

  3. #3
    SitePoint Guru
    Join Date
    Jan 2005
    Location
    USA
    Posts
    633
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by litebearer View Post
    Not sure about a 'min' 'max' function for mysql; however, this may get you what you want...

    PHP Code:
    $w_id 900;
    $query ="SELECT id, datetime FROM table_name WHERE id ='$w_id ORDER BY datetime ASC";
    $result mysql_query($query);
    while(
    $row mysql_fetch_array($result)) {
        
    $data[] = $row['datetime'];
    }
    $i count($data) - 1;
    $start_data $data[0];
    $end_data $data[$i]; 
    Thanks, this is actually a good start for me.I am actually trying to to aviod making multiple SELECT calls, I want to
    call the DB once for all 300 employess.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT id
         , MIN(datetime) as earliest
         , MAX(datetime) AS latest
      FROM daTable
    GROUP
        BY id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Jan 2005
    Location
    USA
    Posts
    633
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT id
         , MIN(datetime) as earliest
         , MAX(datetime) AS latest
      FROM daTable
    GROUP
        BY id
    Thank you Rudy,this works fine


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
  •