SitePoint Sponsor

User Tag List

Page 1 of 3 123 LastLast
Results 1 to 25 of 60

Hybrid View

  1. #1
    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)

    Counting record by date - Ex. Today's entry

    Hola hola!

    I would like to know how to output the number of rows that have been added in the current day?

    I mean, I would like to be able to display the number of entries in a dynamic way?

    How my date is formated:
    PHP Code:
             
    $timestamp 
    time()+date("P");
    gmdate("YmdHis"$timestamp);
    $ordertime gmdate("YmdHis"$timestamp); 
    How my date is added to the database:
    PHP Code:

    $query 
    "INSERT INTO orders
     SET 
    orderdate = '
    $ordertime'

    "


  2. #2
    is_empty(2); foofoonet's Avatar
    Join Date
    Mar 2006
    Posts
    1,000
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you would need to discover the timestamp for midnight yesterday, then count the number of rows since then (ie are greater than midnight).

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select count(*) as orders_today
      from orders 
     where orderdate >= current_date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    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 all.

    But is this righ:
    PHP Code:
    <?php 
                                      $result 
    mysql_query("select count(*) as orders_today from orders where orderdate >= current_date'"); 
    $blah mysql_query($result); 
    echo 
    "$blah"
                                      
    ?>

  5. #5
    Obey the Purebreed trib4lmaniac's Avatar
    Join Date
    Dec 2004
    Location
    Cornwall, UK
    Posts
    594
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need
    PHP Code:
    echo mysql_result($blah0); 

  6. #6
    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)
    Getting this:
    PHP Code:
    Warningmysql_result(): supplied argument is not a valid MySQL 
    This is wha I'm using:

    PHP Code:
    <?php 
                                      $result 
    mysql_query("select count(*) as orders_today from orders where orderdate >= current_date'"); 
    $blah mysql_query($result); 
    echo 
    mysql_result($blah0); 
                                      
    ?>
    Can someone just explain to me the logic of the query? I mean, orders_today represents what? I mean, should it be one of the fields of my database?

    Appologize for being a ZERO at this

  7. #7
    Obey the Purebreed trib4lmaniac's Avatar
    Join Date
    Dec 2004
    Location
    Cornwall, UK
    Posts
    594
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $result mysql_query("select count(*) as orders_today from orders where orderdate >= current_date'"); 
    An extra quote has crept in at the end of the string there.

    orderstoday is a temporary "column" specific to that query. It is known as an alias.
    It is created at "select count(*) as orders_today"
    The where clause makes sure that only jobs from that day are selected. current_date is a function that returns the current date.

  8. #8
    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)
    Thanks for all your help.

    I am still getting the same error.

    Is the mysql_result echo right?

    The problem seems to be coming from there.

    PHP Code:

     $result 
    mysql_query("select count(*) as orders_today from orders where orderdate >= current_date"); 
    $blah mysql_query($result); 
    echo 
    mysql_result($blah0); 

  9. #9
    Obey the Purebreed trib4lmaniac's Avatar
    Join Date
    Dec 2004
    Location
    Cornwall, UK
    Posts
    594
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Doh! Sorry, my fault for not paying attention
    PHP Code:
     $result mysql_query("select count(*) as orders_today from orders where orderdate >= current_date");
    echo 
    mysql_result($result0); 

  10. #10
    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)
    hey thanks!! not getting any errors anymore.

    My strangly, it's not outputing anything despite the fact that i added entries today.

  11. #11
    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)
    Not even outputing "0". :S

  12. #12
    Obey the Purebreed trib4lmaniac's Avatar
    Join Date
    Dec 2004
    Location
    Cornwall, UK
    Posts
    594
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What happens if you change the first line to
    PHP Code:
    $result mysql_query("select count(*) as orders_today from orders where orderdate >= current_date") or die(mysql_error()); 

  13. #13
    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)
    I get all of the rows in the table.

  14. #14
    Obey the Purebreed trib4lmaniac's Avatar
    Join Date
    Dec 2004
    Location
    Cornwall, UK
    Posts
    594
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Could you paste the output?

  15. #15
    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)
    PHP Code:
    <?php 
    $result 
    mysql_query("select count(*) as orders_today from orders where orderdate >= current_date") or die(mysql_error());
    echo 
    mysql_result($result0); 
                                      
    ?>
    And what I get is - 16 ( total number of rows )

    ps. gracias for taking the time to help me out!

  16. #16
    Obey the Purebreed trib4lmaniac's Avatar
    Join Date
    Dec 2004
    Location
    Cornwall, UK
    Posts
    594
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh right I see. Well that particular piece of code seems to be functioning correctly.
    If you are sure 16 is the wrong answer, perhaps it is a timezone issue.

    In your first post you seem to be doing something strange to get the current date - are you trying to get it to GMT?
    If so, you need to calculate it with:
    PHP Code:
    $ordertime gmdate("YmdHis"time()); 
    Edit:

    Or it could be the comparison in the where clause of the SQL. I don't think a DATE to DATETIME comparison would play out very well. Perhaps:
    PHP Code:
    $result mysql_query("select count(*) as orders_today from orders where orderdate >= timestamp(current_date)"); 

  17. #17
    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)
    Quote Originally Posted by trib4lmaniac
    Oh right I see. Well that particular piece of code seems to be functioning correctly.
    If you are sure 16 is the wrong answer, perhaps it is a timezone issue.

    In your first post you seem to be doing something strange to get the current date - are you trying to get it to GMT?
    If so, you need to calculate it with:
    PHP Code:
    $ordertime gmdate("YmdHis"time()); 
    Edit:

    Or it could be the comparison in the where clause of the SQL. I don't think a DATE to DATETIME comparison would play out very well. Perhaps:
    PHP Code:
    $result mysql_query("select count(*) as orders_today from orders where orderdate >= timestamp(current_date)"); 
    Tried it, but it still outputs the number of all of the rows.

    I agree with you, it surely has something to do with the way I store the date in the database.

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Johana Solendhal
    it surely has something to do with the way I store the date in the database.
    well, i wouldn't store it as a string, i'd use a DATETIME column, for one thing because it then requires no conversion in order to do date arithmetic or date comparisons, and secondly because it's a smaller size column, but actually there's no problem using that format, because, after the conversion, mysql can recognize it

    for confirmation, you can do this --
    Code:
    select date_add('20060518002200', interval 15 minute) as d
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    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)
    Thanks for that.

    I checked the database and the time seems to be getting stored correctly.

    This is the format stored in the database orderdate field: 20060518002200

  20. #20
    Obey the Purebreed trib4lmaniac's Avatar
    Join Date
    Dec 2004
    Location
    Cornwall, UK
    Posts
    594
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is your orderdate column type a string?

  21. #21
    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)
    Quote Originally Posted by trib4lmaniac
    Is your orderdate column type a string?
    What does that mean?

  22. #22
    Obey the Purebreed trib4lmaniac's Avatar
    Join Date
    Dec 2004
    Location
    Cornwall, UK
    Posts
    594
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What column type does the orderdate column in your database have?

  23. #23
    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 it:

    PHP Code:
    `orderdatevarchar(50NOT NULL default ''

  24. #24
    Obey the Purebreed trib4lmaniac's Avatar
    Join Date
    Dec 2004
    Location
    Cornwall, UK
    Posts
    594
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, that's the problem then. The comparison will only work if the column is of type DATETIME.

    It should be safe to convert it straight to DATETIME and have all the rows update themselves from the current value.
    I'd recommend doing a backup first though if there's any information in there you need to keep.

  25. #25
    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)
    ah I see.

    So can I create the column like so:

    `orderdate` DATETIME(50) NOT NULL default '',


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
  •