0

I have been trying this query and researching but I can not understand why it does not work.

The IP record(VARCHAR) is saved in the table on the database, but I can not save the record DATETIME, when the record is inserted.

$sql = "INSERT INTO ips (ip, fecha) VALUES (:ip, :CURRENT_TIMESTAMP)";
$stmt = $pdo->prepare($sql);
$stmt->execute(array(
    ':ip' => htmlentities($_POST['ip']),
    ':CURRENT_TIMESTAMP' => htmlentities(CURRENT_TIMESTAMP)
));
Prix
  • 19,417
  • 15
  • 73
  • 132
  • `CURRENT_TIMESTAMP` is a PHP constant? I would just use `now()` for the value. You also would be better of just using `htmlentities` for outputting, not storage. – user3783243 Aug 15 '18 at 18:52
  • 3
    @juanma you could simple use `INSERT INTO ips (ip, fecha) VALUES (:ip, NOW())` and MySQL will get the current time by itself, you don't need it as a parameter. If you really want to get the time from php and insert that see this https://stackoverflow.com/a/9541074/342740 – Prix Aug 15 '18 at 18:53
  • 2
    Also depending on your mysql version you could even set that column to default to the current timestamp. Then you wouldn't even need that column defined in the `insert`. I think +5.6.5 (it was early than I thought) supports that. – user3783243 Aug 15 '18 at 18:54
  • Possible duplicate of [Insert current date in datetime format mySQL](https://stackoverflow.com/a/9541074/342740) Note: his issue is not with PDO but the variable it self/the way he wants to feed the query his datetime. – Prix Aug 15 '18 at 18:55
  • 1
    Not related to your question, but you don't need to use `htmlentities()`, or any other type of escaping function, when you use SQL query parameters. – Bill Karwin Aug 15 '18 at 18:57
  • @user3783243 "I think +5.6.5 (it was early than I thought) supports that" MySQL supported `DEFAULT CURRENT_TIMESTAMP` column option from MySQL 4.1+.. The newer MySQL versions allowes `DEFAULT CURRENT_TIMESTAMP` and `ON UPDATE CURRENT_TIMESTAMP` on the same column or multple columns at the same time. – Raymond Nijland Aug 15 '18 at 19:09
  • @RaymondNijland I mean the `DEFAULT CURRENT_TIMESTAMP` on a `DATETIME` column. I thought that was a new feature of 5.6 and I saw a thread that said it was the .5 version – user3783243 Aug 15 '18 at 19:13
  • 1
    "I mean the DEFAULT CURRENT_TIMESTAMP on a DATETIME column" True @user3783243 "As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, " source https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html – Raymond Nijland Aug 15 '18 at 19:16
  • @Prix - I could make an argument against using `NOW` or at least a caution when using. DB time and PHP time may not be the same thing depending on timezones and server settings. So if you use the Database TIme, make sure it's configured the same as it is in PHP. When I started my current job (5 years ago) that was not the case and had caused untold amount of bugs in our application, until I rectified it. – ArtisticPhoenix Aug 15 '18 at 19:25
  • @ArtisticPhoenix I could make several arguments, regardless they are unrelated to the actual issue the OP had. – Prix Aug 15 '18 at 23:48
  • Without seeing the schema I cant comment to much on that. I was just giving a word of warning when mixing sources of "time". But I will say that this is bad practice `htmlentities($var)` when saving I can also say `CURRENT_TIMESTAMP` and `DATETIME` implies two related but different data types are being used, but nomenclature aside it's difficult to Know with any certainty without the schema.... – ArtisticPhoenix Aug 16 '18 at 00:28

1 Answers1

0

Use PHP's time function:

$sql = "INSERT INTO ips (ip, fecha) VALUES (:ip, :CURRENT_TIMESTAMP)";
$t = time();
$today = date("Y-m-d", $t);
$stmt = $pdo->prepare($sql);
$stmt->execute(array(
    ':ip' => htmlentities($_POST['ip']),
    ':CURRENT_TIMESTAMP' => $today
Keith Harris
  • 1,118
  • 3
  • 13
  • 25