SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help needed with saving today's date back to database.

    My echo $update_sql statement shows this

    UPDATE projects SET dex='{FD35CF46-D1F4-A05F-D4C6-16CF86A2E05F}', Name='h', Designation='f', Phone=59, PMC=1, Quality=1, Progress=1, Issues=1, Responsiveness =1, Commitments=1, Safety=1, SLevel=1, Total=8, Improvement='No comment for now', Future='N', Date=2012/06/21 WHERE dex='{135C2B55-B95D-786D-9CBD-9563B3AF04B3}'

    The date shown here is the current date. However, the date stored in my database is 0000-00-00. what could be wrong? I am sanitizing the date as date type.

    PHP Code:
    sanitize($date"date"); 
    I am using the $date=date(Y/m/d) to get today's date. Do let me know if you can help. Thanks.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Why don't you use MySQL's CURDATE() instead?

  3. #3
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,000
    Mentioned
    101 Post(s)
    Tagged
    0 Thread(s)
    Have you tried CURRENT_TIMESTAMP in the query to store the date? I think CURRENT_TIMESTAMP will work across multiple databases systems but can't be sure, I don't have a copy of @r937 ;'s book to hand to check
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  4. #4
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Date=2012/06/21
    There are so many problems with that that I feel I should spell them out to you ...

    Date is a protected word and should be quoted with backticks `date` - just avoid using it, rename the column

    The value should be quoted '2012/06/21'

    Mysql is very forgiving about how you format a date but strictly speaking it should be dash separated.

    Slashes might work, spaces do, but as you are formatting the date yourself then why not sort it out?

    $date=date('Y-m-d');

    Overall though, make a note of all that because some things are going to haunt you otherwise, and just do what Guido2004 says.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Cups View Post
    ... just do what Guido2004 says.
    except it's better to use CURRENT_DATE instead of CURDATE() because the former is standard sql and will work in all database systems
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,809
    Mentioned
    73 Post(s)
    Tagged
    6 Thread(s)
    @immohito

    Check out phpMyAdmin and table structure:

    table date type to TIMESTAMP
    and
    table date field attribute to "on update CURRENT_TIMESTAMP"


    Once set then the date is automatically updated.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    john, that's a nice feature, but it's not appropriate for a project date

    first of all, it'll get updated on any change to the row, e.g. even if you fix a typo on the project title

    but more importantly, it's a datetime value, not appropriate for a date column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    except it's better to use CURRENT_DATE instead of CURDATE() because the former is standard sql and will work in all database systems
    The MySQL reference manual fooled me there:
    CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE().
    I would have expected it to give the definition of the standard sql function, and then say that CURDATE() is a synonym for CURRENT_DATE...


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
  •