How to store time stamp and output it using MySQL and PHP

Question

I have been looking to see if there are any answers for this already, and i haven't found one that answers my question. I am a noob when it comes to php, so please be nice. I am trying to make a somewhat of a CMS and all i have so far is you can make post, but when ever i try to store the time of the post, it just outputs December 31 1969. I am assuming i am not storing the value properly because if its showing that date, it means the timer is at 0.

Here is what i have so far.

<?php
require('config.php'); 

if (isset($_POST['name']) AND isset($_POST['mail']) AND isset($_POST['avatar']) AND isset($_POST['title']) AND isset($_POST['message'])) 
{ 
$name = addslashes($_POST['name']); 
$mail = addslashes($_POST['mail']); 
$avatar = addslashes($_POST['avatar']); 
$title = addslashes($_POST['title']); 
$message = addslashes($_POST['message']); 
// We try to find out if we were going to modify the news ... 
if ($_POST['id_news'] == 0) 
{ 
// No modifs ?? lets put some data on the tables :p 
mysql_query("INSERT INTO news VALUES('', '" . $name . "', '" . $title . "', '" . $message . "', '" . time() . "', '" . $mail . "', '" . $avatar . "')"); 
} 
else 
{ 
// If we want to modify, let's just update all the data 
mysql_query("UPDATE news SET name='" . $name . "', title='" . $title . "', message='" . $message . "', mail='" . $mail . "', avatar='" . $avatar . "' WHERE id=" . $_POST['id_news']); 
} 
} 

/* 
Verify if ever we want to delete the news 
*/ 
if (isset($_GET['delete_news'])) 
{ 
// let's delete data Very Happy 
mysql_query('DELETE FROM news WHERE id=' . $_GET['delete_news']); 
} 
?> 

<table width="487"> 
<tr> 
<th>Modify</th> 
<th>Delete</th> 
<th>Title</th> 
<th>Date</th> 
<th>Author</th> 
</tr> 

<?php 
$res = mysql_query('SELECT * FROM news ORDER BY id DESC'); 
while ($data = mysql_fetch_array($res)) 
{ 
?> 

<tr> 
<td align="center"><?php echo '<a href="write_news.php?modify_news=' . $data['id'] . '">'; ?>Modify</a></td> 
<td align="center"><?php echo '<a href="list_news.php?delete_news=' . $data['id'] . '">'; ?>Delete</a></td> 
<td align="center"><?php echo stripslashes($data['title']); ?></td> 
<td align="center"><?php echo date('d F Y - h:i:s a', $data['time']); ?></td> 
<td align="center"><?php echo stripslashes($data['name']); ?></td> 
</tr> 

<?php 
}  
?>

Thank you in advance.

Oh the ease at which I could destroy your database... Sanitize your variables. — Sterling Archer, Nov 15 '13 at 16:15
[Read this](http://stackoverflow.com/q/60174/1415724) before going LIVE with this project. And if you're really serious, then [read this too](https://www.owasp.org/index.php/Top_10_2013-Top_10) and there's a difference between a "noob" and a "newbie", so don't be a "noob". — Funk Forty Niner, Nov 15 '13 at 16:17
I can see that you're at least trying to sanitize some of your variables, although you should definitely read those links posted above by Fred. `$_POST['id_news']` still stands out, and I'm assuming that this would be a hidden field in the form on the previous page, so a typical user wouldn't interact with it? Doesn't matter, do not trust any $_POST or $_GET variable to contain what you're expecting it to contain, _ever_. — SubjectCurio, Nov 15 '13 at 16:23

score 0 · Answer 1 · answered Nov 15 '13 at 16:23

You can either do a strtotime function or do an explode and do a mktime of the exploded elements.

strtotime may be easier

date('d F Y - h:i:s a', strtotime($date['time]));

As was mentioned in a reply, you will want to Santize the variables when printing them out, and maybe protect it going in (more than an addslashes)

http://www.php.net/manual/en/function.strtotime.php

http://www.php.net/manual/en/function.date.php

Here is the link for the escape string, but you may want to consider the best alternative, and that is to paramterize the inserts

http://www.php.net/manual/en/mysqli.real-escape-string.php

score 0 · Answer 2 · answered Nov 15 '13 at 16:49

I suppose you have field time in datatypes datetime or timestamp, then use:

mysql_query("INSERT INTO news VALUES('', '" . $name . "', '" . $title . "', '" . $message . "', '" . date('Y-m-d H:i:s') . "', '" . $mail . "', '" . $avatar . "')");

or use mysql function NOW

mysql_query("INSERT INTO news VALUES('', '" . $name . "', '" . $title . "', '" . $message . "', NOW(), '" . $mail . "', '" . $avatar . "')");

When you read the value from database, you can convert it to integer and after use date function. For example:

$tableColumn = '2013-10-10 15:10:12';
echo date('Y-m-d H:i:s', strtotime($tableColumn));

Please note, TIMESTAMP datatype is stored as 4-bytes integer for seconds from 1 January 1970 by UTC. MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.

But interaction with datatype TIMESTAMP same as with DATETIME, you need to store data in format YYYY-MM-DD HH:II:SS.

You can use INT datatype in mysql to store timestamp. For inserting you can use variant with php function time()

   $sql = "insert into test values(" . time() . ")";

or use sql function UNIX_TIMESTAMP().

   $sql = "insert into test values(UNIX_TIMESTAMP())";

For displaying values from this column from database, just use php function date($formatOfDate, $databaseValue)

How to store time stamp and output it using MySQL and PHP

2 Answers2