SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Hybrid View

  1. #1
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    date math with prepared statements

    I want to add a record to a table with an expire date that is set at runtime. If I do a normal update statement, the following code works fine

    Code:
    update t1 set expiredate = now() + INTERVAL 120 DAY
    But when I use this with a prepared statement in a bind_param, I get an "Incorrect datetime value".

    How should this be formatted for use with a prepared statement if the '120' is assigned at runtime? Also, I would prefer to use the MySQL server date as the base. Thanks

  2. #2
    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)
    Are you trying to bind just the '120', or the entire string? 'now() + INTERVAL 120 DAY'

    Prepared statements (in PDO anyway) can only be bound to values you are inserting and not fields - so I would guess the same goes this part of your statement because it will be escaped as a string '"now() + INTERVAL 120 DAY"' and become invalid.

    I could be wrong though - be interesting to hear if there is a way round this.

  3. #3
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What I am doing now, and it works fine, is to not do the date field update as a ? in the prepared statement. So I create the sql string with
    Code:
    "$sql = "..., ?, ?, now() + INTERVAL " . $days . " DAY, ?, ?..."
    I would just prefer to be consistent in my application and do it with the use of the ? and the bound param value. So yes, if anyone knows if this can be done and how to do it, I would like to hear it also. Thanks for the input.

  4. #4
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bostboy View Post
    I want to add a record to a table with an expire date that is set at runtime. If I do a normal update statement, the following code works fine

    Code:
    update t1 set expiredate = now() + INTERVAL 120 DAY
    But when I use this with a prepared statement in a bind_param, I get an "Incorrect datetime value".

    How should this be formatted for use with a prepared statement if the '120' is assigned at runtime? Also, I would prefer to use the MySQL server date as the base. Thanks
    Binding "120" as a parameter works fine with PDO - are you using something else?
    aaron-fisher.com - PHP articles and more

  5. #5
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just tried it and it didn't work. I am using MySQLi. So you just bind "120" as the date value and it automatically adds 120 days to the current date?

  6. #6
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To be clear, this works

    Code:
    $days = 120;
    insert into t1 (t1name, t1desc, dateexpires) values (?, ?, now() + INTERVAL " . $days . " DAY)"
    $link->prepare($qt_sql);
    $link->bind_param("ss", $name, $desc);
    $link->execute();
    But this doesn't work
    Code:
    $days = 120;
    insert into t1 (t1name, t1desc, dateexpires) values (?, ?, ?)"
    $link->prepare($qt_sql);
    $link->bind_param("sss", $name, $desc, $days);
    $link->execute();
    Not sure if that's what you were recommending?

  7. #7
    SitePoint Guru aamonkey's Avatar
    Join Date
    Sep 2004
    Location
    kansas
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bostboy View Post
    ...But this doesn't work
    Code:
    $days = 120;
    insert into t1 (t1name, t1desc, dateexpires) values (?, ?, ?)"
    $link->prepare($qt_sql);
    $link->bind_param("sss", $name, $desc, $days);
    $link->execute();
    Not sure if that's what you were recommending?
    You can do this in PDO:

    PHP Code:
    $sql "insert into t1 (t1name, t1desc, dateexpires) values (?, ?, now() + INTERVAL ? day)"
    I noticed that you're attempting to bind $days as a string - if you do it as an integer does it work?
    aaron-fisher.com - PHP articles and more

  8. #8
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, tried that and it makes no difference. The date shows up in the db as '2000-1-20...' if I set the days at 120.

  9. #9
    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)
    The bind parameters work slightly differently in mysqli to those in PDO if I understand correctly.

  10. #10
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    PHP Code:
    $link->bind_param("sss"$name$desc$days); 
    Here you're saying that $days should be bound as a string (that's what the 's's are for).
    What if you try this?

    PHP Code:
    $link->bind_param("ssi"$name$desc$days); 

    Edit>>
    Must learn to thoroughly read entire topic before posting!
    Sorry for basically saying what others have already said...


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
  •