0

Im trying to get the current time in PHP using $sTime = date("Y-m-d H:i:s"); and store it in a mysql table in a datetime column like: $sTQuery = "INSERT INTO Actions(UserID, StartDuration) VALUES ('$UserID' , '$sTime')";

The date echos as expected but in the table it just shows 0000-00-00 00:00:0.

I have tried using now() in the query but still get 0000-00-00 00:00:0 in the table.

Edit: In the mysql table the column the date and time is to be stored in is a datetime

sammcd75
  • 21
  • 6
  • 2
    what's the column type or table schema? Also, read about PDO or at least about SQL Injection – Marcin Orlowski Jan 31 '18 at 11:43
  • if you use `now()`, make sure to use that without quotes. Although depending on the column type, you may need a different function like `current_timestamp()`. – GolezTrol Jan 31 '18 at 11:44
  • Have you looked what has been stored in the database? This is not the same as a date 'print' or 'echo'. – KIKO Software Jan 31 '18 at 11:45
  • @MarcinOrlowski the column is a datetime. I know about SQL injection, just trying to get basic implementation sorted first. – sammcd75 Jan 31 '18 at 11:47
  • Possible duplicate of [PHP date() format when inserting into datetime in MySQL](https://stackoverflow.com/questions/2215354/php-date-format-when-inserting-into-datetime-in-mysql) – Abhigyan Tiwari Jan 31 '18 at 11:48
  • @KIKOSoftware in the table it just appears as 0000-00-00 00:00:0 the echo shows the correct date and time – sammcd75 Jan 31 '18 at 11:48
  • 1
    Why is the last zero in `0000-00-00 00:00:0` missing? – KIKO Software Jan 31 '18 at 11:49
  • @AbhigyanTiwari the suggested answer there doesn't represent the same issues as here as the format i have been using in date() is the same format as the answer – sammcd75 Jan 31 '18 at 11:49
  • @KIKOSoftware just a typo sorry, it is there on the table – sammcd75 Jan 31 '18 at 11:49
  • @GolezTrol is current_timestamp() used in the same way as now in the query? – sammcd75 Jan 31 '18 at 11:50
  • 1
    Based on the code in your question (which is vulnerable to sql injection), this is not possible. The problem lies elsewhere in your code, but you are not providing enough information. Please edit your question with the create table statement, and the `SELECT` query you are testing with, so we can try to reproduce the problem. Also provide the result to `SELECT NOW()`. – rlanvin Jan 31 '18 at 11:57
  • @rlanvin I'm kind of new to php and mysql and understand a bit of injection but on my site there isn't a place for a user to enter any information, my code simply gathers information in the background like about the computer, pages on they visit and time on the site, so how can they inject if there is no where for them to type? – sammcd75 Jan 31 '18 at 12:09
  • @sammcd75 If you are new it would be wise to simply follow the best practices, they exist for a reason. Even if you think it's not exploitable *right now*, one day you will reuse this piece of code somewhere else, forget that your query is vulnerable... and boom. Just don't build SQL queries like this in PHP. – rlanvin Jan 31 '18 at 13:11

1 Answers1

1

Try to use this query... This contains DateTime value by now() as per your need.

$sTQuery = "INSERT INTO Actions(UserID, StartDuration) VALUES ('$UserID' ,now())";
A.D.
  • 2,352
  • 2
  • 15
  • 25