CODEWITHSUNDEEP
phpmysqlpdounix-timestamp
bobafart
bobafart

Reputation: 397

Best Way to Insert a unix timestamp using PHP into a mySQL integer field (INT)?

I have a table and theDate field is structured as INT (32) .

Writing PHP scripts I would like to use mySQL PDO to insert the unix timestamp as an integer into theDate INT table field. What is the best way to do this? And what is the most efficient size for my theDate field? 8, 16, 32?

code:

$theDate = now();  // this returns a 0 in theDate mySQL field
    $stmt = $db->prepare("INSERT INTO countries(name, capital, language, theDate) VALUES (:country, :capital, :language, :theDate)");

$stmt->bindParam(':country', $country, PDO::PARAM_STR, 100);
$stmt->bindParam(':capital', $capital, PDO::PARAM_STR, 100);
$stmt->bindParam(':language', $language, PDO::PARAM_STR, 100);
$stmt->bindParam(':theDate', $theDate );
if($stmt->execute()) {
  echo '1 row has been inserted';  
}

I have googled this and searched this site.. can't find a good answer

thanks

Upvotes: 0

Views: 942

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 158007

The best way to store a datetime information in mysql is to use a datetime field. For which you can use the very now() function you were trying. But of course you have to call it in SQL, not PHP:

$sql = "INSERT INTO countries(name, capital, language, theDate) VALUES (?, ?, ?, now())";
$stmt = $db->prepare($sql);
$stmt->execute([$country,$capital,$language]);

if you insist on using unreliable and outdated unix_timestamp, you have to use unix_timestamp() SQL function instead of now(). The code would be the same as above, save for the function name.

If you want to send an int value to a database via PDO - just send it:

$theDate = time();
$sql = "INSERT INTO countries(name, capital, language, theDate) VALUES (?, ?, ?, ?)";
$stmt = $db->prepare($sql);
$stmt->execute([$country,$capital,$language,$theDate]);

Upvotes: 2

Related Questions