SitePoint Sponsor

User Tag List

Page 3 of 3 FirstFirst 123
Results 51 to 60 of 60
  1. #51
    SitePoint Addict Johana Solendhal's Avatar
    Join Date
    Aug 2004
    Location
    Land of the free
    Posts
    252
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is what Phpmyadmin returns:
    SQL query:
    SELECT count( * ) AS date_add
    FROM orders
    WHERE orderdate >= date_add( current_date, INTERVAL -1
    DAY )
    [ Edit ] [ Explain SQL ] [ Create PHP Code ] [ Refresh ]




    date_add
    26

  2. #52
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    excellent

    so the query works

    i would use a different column alias name besides "date_add" for a count which counts the rows for yesterday

    okay, so this isn't the part that you said "didn't work"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #53
    SitePoint Addict Johana Solendhal's Avatar
    Join Date
    Aug 2004
    Location
    Land of the free
    Posts
    252
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hehe sorry.

    The problem is that it gives mw the total number of rows.

    It's not giving me the rows that were added yesterday

  4. #54
    SitePoint Addict Johana Solendhal's Avatar
    Join Date
    Aug 2004
    Location
    Land of the free
    Posts
    252
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem is that it seems that is not subtracting 1 day to the current day.

    SELECT count(*) as orders_yes FROM orders WHERE orderdate >= date_add(current_date, interval -1 day)

    If I change -1 to -0 it will add me the orders that were added today, but if I change it back to -1 it will just give me the total rows in th table.

  5. #55
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    if you only have 26 rows, please do this --
    Code:
    select orderdate from orders order by orderdate desc
    and let's have a look at them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #56
    SitePoint Addict Johana Solendhal's Avatar
    Join Date
    Aug 2004
    Location
    Land of the free
    Posts
    252
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Gracias for sticking with me!

    Here's the result:

    PHP Code:
          2006-05-23 16:02:18 
          2006
    -05-23 16:02:01 
          2006
    -05-23 01:08:38 
          2006
    -05-23 01:08:29 
          2006
    -05-22 19:55:22 
          2006
    -05-22 19:52:45 
          2006
    -05-22 19:52:20 
          2006
    -05-22 19:48:59 
          2006
    -05-22 19:48:42 
          2006
    -05-22 19:44:32 
          2006
    -05-22 19:40:48 
          2006
    -05-22 19:38:12 
          2006
    -05-22 19:37:55 
          2006
    -05-22 19:36:51 
          2006
    -05-22 19:26:16 
          2006
    -05-22 19:22:15 
          2006
    -05-22 19:21:59 
          2006
    -05-22 19:19:44 
          2006
    -05-22 19:16:15 
          2006
    -05-22 19:16:04 
          2006
    -05-22 19:13:16 
          2006
    -05-22 19:11:00 
          2006
    -05-22 19:09:03 
          2006
    -05-22 19:08:45 
          2006
    -05-22 19:05:18 
          2006
    -05-22 19:02:15 

  7. #57
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    good, and what time is it right now?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #58
    SitePoint Addict Johana Solendhal's Avatar
    Join Date
    Aug 2004
    Location
    Land of the free
    Posts
    252
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Seems the records are one hour behind!!

    Just added a product and the date of storage is 18:00 instead of the real time: 19:00

    How is this affecting everything?

  9. #59
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    how is it affecting everything? simple!

    according to you, it's now 2006-05-23 18-something, or 2006-05-23 19-something

    so yesterday is 2006-05-22, i.e. 2006-05-22 00:00:00

    all your orderdates are greater than that

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #60
    SitePoint Wizard Pedro Monteiro's Avatar
    Join Date
    Sep 2002
    Location
    Lisbon
    Posts
    1,393
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A tad messy, but this should get it working.

    PHP Code:
    $result mysql_query("select count(*) as orders_today from orders where orderdate > date_add(current_date, interval -1 day) AND orderdate < current_date"); 
    echo 
    mysql_result($result0); 


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
  •