SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2003
    Location
    England
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    queries not working because of the date

    Hello,


    Would someone be able to look atthe code and see why the query doesnt return any results (it should)?

    Code:
    $date = getDate();
    
    $day = $date["mday"];
    $month = $date["mon"];
    $year = $date["year"];
    
    // Current month and next month
    
    $current_month = getDate(mktime(0, 0, 0, $month, 1, $year));
    $next_month = getDate(mktime(0, 0, 0, $month + 1, 1, $year));
    
    // Last day of month
    
    $last_day = round(($next_month[0] - $current_month[0]) / (60 * 60 * 24));
    
    
    
    $lastDayDate = date("Y/m/d", (mktime(0, 0, 0, $month, $last_day, $year)));
    
    
    
    $closingThisMonthQuery = "SELECT title, type, entryform, firstprize, closingdate FROM competition WHERE closingdate BETWEEN curdate() AND $lastDayDate";

    if i substitute $lastDatDate with a normal date in the query it works fine so the problem must be with my php.

    I took the php from another forum page and am not very familiar with php's date functions.

    Cheers

  2. #2
    SitePoint Wizard spence_noodle's Avatar
    Join Date
    Jan 2004
    Location
    uk, Leeds (area)
    Posts
    1,264
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    is that all the code?

    just that $next_month[0] and $current_month[0] is from some array.

    more code (if not all of it) please.
    "Don't you just love it when you solve a programming bug only to create another."

  3. #3
    SitePoint Wizard spence_noodle's Avatar
    Join Date
    Jan 2004
    Location
    uk, Leeds (area)
    Posts
    1,264
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    also, the database query at the bottom has no mysql_query, unless you have that below the code.
    "Don't you just love it when you solve a programming bug only to create another."

  4. #4
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    The $next_month[0] and $current_month[0] return timestamps, though I'm not sure why wouldn't you just assign the results of mktime(), the use of getdate() is redundant.

    Anyway, as Spence said, you do not call mysql_query, and that probably is the problem.
    Saul

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You're trying to use a date formatted as Y/m/d in your query. It should be Y-m-d and enclosed in single quotes, or converted to a timestamp.

    Can you briefly explain what date you're trying to get at? It's a little hard to follow. Something like "the last day of this month" or "the last day of next month" can be obtained in a single mktime() call.

  6. #6
    SitePoint Enthusiast
    Join Date
    Nov 2003
    Location
    England
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry, i only included the code i thought relevant.


    All im trying to do is return the entries in the database with a closing date that is between the current date and the last date of the month.

    I have omitted the rest of the sql/database connection code etc as it is not relevant. I know it is being performed and is working. The query however only works when i put in a specific figure.

    BUT, dan's help helped me to get it working. Changing the / to - in the date format and then putting apotrophes around it worked like a charm.

    Dan, could you explain how I can retrieve the last day of the current month in a more succinct way like you said could be done? Like i said, I got this code from another forum and am very unfamiliar with date functions.


    Thanks alot everyone.

  7. #7
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $closingThisMonthQuery "SELECT title, type, entryform, firstprize, closingdate FROM competition WHERE closingdate > CURRENT_TIMESTAMP AND (MONTH(closingdate) = MONTH(CURRENT_TIMESTAMP) AND YEAR(closingdate) = YEAR(CURRENT_TIMESTAMP))"
    That should do it, with no PHP date computation.

    CURRENT_TIMESTAMP is predefined, equivalent to using NOW(), but works in more RDBMS's other than MySQL.

  8. #8
    SitePoint Enthusiast
    Join Date
    Nov 2003
    Location
    England
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that's brilliant. Works great. Thanks alot.

  9. #9
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Might as well give you this too, in case you ever need to do something similar outside the query:

    PHP Code:
    mktime(000date("m")+10date("Y")); 
    The 0th day of the next month is the last day of this month, for PHP at least

    So this:
    PHP Code:
    echo date("Y-m-d"mktime(000date("m")+10date("Y"))); 
    would print 2006-07-31.


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
  •