SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Feb 2001
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i want to query a db and get the second record, here is what i mean:-

    db:

    fields: user | time
    ----------------------
    record 1: tom | 20010225001121
    record 2: tom | 20010225001423

    i want to minus record 2 from record 1,

    my query looks like:

    $sql = "SELECT Time, UserID FROM members_time ";
    $sql .= "WHERE UserID='$UserID' ";

    this sql statement is in a while loop that gets all the users, so i have to get two records in each of the loops so i can minus record 2 from record 1,

    how would i do this?
    ___________________________________

    << Footer under construction >>

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT MAX(Time) - MIN(Time) AS period
    FROM members_time
    WHERE UserID='$UserID'

    I am assuming that there are only two records per user.

  3. #3
    SitePoint Member
    Join Date
    Feb 2001
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no, there are 10+ per user

    thanks!
    ___________________________________

    << Footer under construction >>

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, what you want to do is subtract record 2 from record1, record 3 from record 2, record 4 from record 3, .... ?

    Is that right? I'm not sure what you want to do.

  5. #5
    SitePoint Member
    Join Date
    Feb 2001
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thats exactly what i want to do!!
    ___________________________________

    << Footer under construction >>

  6. #6
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Add one row to your sql query:

    $sql = "SELECT Time, UserID FROM members_time ";
    $sql .= "WHERE UserID='$UserID' ";
    $sql .= "ORDER BY Time";

    The logic will be something like this
    PHP Code:
    $result mysql_query($sql);
    $row mysql_fetch_array($result);
    $smaller $row["Time"];
    while ( 
    $row mysql_fetch_array($result) ) {
       
    $larger $row["Time"];
       
    $diference $larger $smaller;
       
    // what-ever you want to do with $diference goes here
       
       
    $smaller larger;

    You might want to handle an alternate course if there is less that two records in the result set
    PHP Code:
    $result mysql_query($sql);
    $row mysql_fetch_array($result);

    if ( 
    mysql_num_rows($result) > ) {
       while ( 
    $row mysql_fetch_array($result) ) {
          
    $larger $row["Time"];
          
    $diference $larger $smaller;
          
    // what-ever you want to do with $diference goes here
       
          
    $smaller larger;
       }
    } else {
       
    // handle alternate course here


  7. #7
    SitePoint Member
    Join Date
    Feb 2001
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey thanks heaps for your time.
    ___________________________________

    << Footer under construction >>


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
  •