SitePoint Sponsor

User Tag List

Page 2 of 3 FirstFirst 123 LastLast
Results 26 to 50 of 60
  1. #26
    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)
    not quite -- datetimes don't have a size
    Code:
    orderdate DATETIME NOT NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  2. #27
    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 the help guys. Great to learn from you!

  3. #28
    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)
    Guys, still getting the same result.

    The column is now DATETIME, but it still retrieves all of the rows of the database.

    PHP Code:
    <?php 
    $result 
    mysql_query("select count(*) as orders_today from orders where orderdate >= timestamp(current_date)");
    echo 
    mysql_result($result0); 
                                      
    ?>

  4. #29
    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)
    did you try the query outside of your php script, like in phpmyadmin?

    what do you get for this --
    Code:
    select max(orderdate) as m from orders
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #30
    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 r937
    did you try the query outside of your php script, like in phpmyadmin?

    what do you get for this --
    Code:
    select max(orderdate) as m from orders
    Hola! GraciaS!

    here's what I got:

    Showing rows 0 - 0 (1 total, Query took 0.0009 sec)
    SQL query:
    SELECT max( orderdate ) AS m
    FROM orders

  6. #31
    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 just have no idea what I might be doing wrong. Just checked the database and the values are all correcty stored like so: 2006-05-23 01:08:38.

    Like I said previously, the query counts all of the entried. It almost seems it is ignoring the WHERE.

  7. #32
    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)
    what was m?

    if you have converted the column to datetime, then change this --

    ... where orderdate >= timestamp(current_date)

    to this --

    ... where orderdate >= current_date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #33
    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)
    Rudy, that did it! Muchas gracias!

  9. #34
    SitePoint Wizard Pedro Monteiro's Avatar
    Join Date
    Sep 2002
    Location
    Lisbon
    Posts
    1,393
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Johana, this pretty much covers everthing up on the subject.

    http://www.tomjewett.com/dbdesign/db...imgsize=medium

  10. #35
    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 Pedro Monteiro
    Johana, this pretty much covers everthing up on the subject.

    http://www.tomjewett.com/dbdesign/db...imgsize=medium
    be careful, those date functions are oracle-specific
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #36
    SitePoint Wizard Pedro Monteiro's Avatar
    Join Date
    Sep 2002
    Location
    Lisbon
    Posts
    1,393
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ouch! Missed that. Well noted indeed rudy.

  12. #37
    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)
    niiiiiiiiiiiiiice.

    r937, if I wanted to say.... show the results from yesterday, is there a functiom like yesterday_date ?

  13. #38
    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)
    no, there isn't, you would have to use
    Code:
    date_add(current_date, interval -1 day)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #39
    SitePoint Wizard Pedro Monteiro's Avatar
    Join Date
    Sep 2002
    Location
    Lisbon
    Posts
    1,393
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Johana Solendhal
    niiiiiiiiiiiiiice.

    r937, if I wanted to say.... show the results from yesterday, is there a functiom like yesterday_date ?
    Nice one Johana.

    What about the old infamous function hate_my_mother_in_law_murder_her_for_me_date

    Ever used it?

  15. #40
    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 Pedro Monteiro
    Nice one Johana.

    What about the old infamous function hate_my_mother_in_law_murder_her_for_me_date

    Ever used it?

  16. #41
    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 r937
    no, there isn't, you would have to use
    Code:
    date_add(current_date, interval -1 day)
    your a frieking GOD!

    should i use it like so?

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

  17. #42
    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
    should i use it like so?
    only if you want all the orders since yesterday (including today and all future orders)

    but the table name orders_today makes me wonder what else you're doing that you aren't telling us...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #43
    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 see. What if I want to count yesterday's added records?

    Meaning, only the number of yesterday's orders?

  19. #44
    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 r937
    only if you want all the orders since yesterday (including today and all future orders)

    but the table name orders_today makes me wonder what else you're doing that you aren't telling us...
    PHP Code:
    <?php 
    $result 
    mysql_query("select count(*) as orders_today from orders where orderdate >= date_add(current_date, interval -1 day"); 
    echo 
    mysql_result($result0); 
                                      
    ?>
    Nah, just left that by mistake.

    this is for a personal project, a mini ecommerce store to sell my photos.

    I have a table with stats of the orders.

    Today's orders:
    Yersterday's orders:
    Average of orders per day:

  20. #45
    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)
    you have a table for today's orders, and another table for yesterday's orders?

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

  21. #46
    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)
    hehehe nah, only have one table!

  22. #47
    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 thought that orders_today was a generated temporary field. BLUSH!

  23. #48
    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)
    Just tried this, and naturally, didn't work!

    PHP Code:
    <?php 
    $result_ontem 
    mysql_query("select date_add(current_date, interval -1 day) from orders"); 
    $var_ontem_stats mysql_result($result_ontem0); 
    $result_final_ontem mysql_query("select date_add(current_date, interval -1 day) from orders"); 
    $resultb mysql_query("select count(*) as orders_yesterday from orders where orderdate >= 'result_final_ontem'");
    echo 
    mysql_result($resultb0); 

    ?>

  24. #49
    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)
    big tip: test your queries outside of php

    write them in a text editor if you have to -- i do, because i insist on formatting the sql into multiple lines

    then when you've finished writing the query, paste it into a front end app like phpmyadmin, mysql query browser, whatever
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #50
    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 rudy, will be sure to do that in the future.


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
  •