Data PHP: How to do?

Question

I'm going crazy to figure out how to do to make a php script that allows me to know the date of the entered data in the database (text). The query that adds the data is structured in this way:

$query = "INSERT INTO column (Data) VALUES ( NOW() )";

In the database I created a date field of type timestamp. Instead the script that retrieves the data from the database is structured so

$query = mysql_query("SELECT Data FROM table WHERE id = ".$_REQUEST['id'].";");
while ($Row = mysql_fetch_array($query)) {
    echo "Created:".$Row[0];
}

The output is always 01/01/1970 that is the Unix Date. How to solve? please I'm going crazy!!! could you post the correct code? Many Thanks!

Holy [SQL injections](http://stackoverflow.com/q/60174), Batman!! — John Conde, Jun 06 '13 at 13:53
Have you checked in the database to make sure that the data is being added correctly? — andrewsi, Jun 06 '13 at 13:53
Predictable comment about not using `mysql_` php extension is predictable. — Ethan, Jun 06 '13 at 13:54
you shoud really stop using mysql_ crappy function and use prepared statement with either mysqli or PDO — Fabio, Jun 06 '13 at 13:57
Are you sure the column is called 'Data' in the table called column? BTW is column a good name for a database table? — Matt Harrison, Jun 06 '13 at 13:58
And never ever ever directly concatenate user input into a SQL statement, you're asking for trouble.http://en.wikipedia.org/wiki/SQL_injection — Matt Harrison, Jun 06 '13 at 13:59
@Ethan yet appropriate. "Take care of the little things . . and the big things will take care of themselves." — Matt Harrison, Jun 06 '13 at 14:01
01/01/1970 doesn't mean it's a UNIX time stamp necessarily. It just means you've probably sent a NULL or 0 to a type `date` field in your table. That date. January First 1970 is 0 day for UNIX. — TheSnooker, Jun 06 '13 at 14:16

score 0 · Answer 1 · answered Jun 06 '13 at 14:08

A better solution would be to create the table with a timestamp default. That way you would not have to insert anything into the table as far as the timestamp column is concerned.

To do this you can either do the following on table creation,

 `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

The above will auto set the created column to the time that the row data was inserted into the column.

If you would like to have the column row auto update itself whenever the row is updated you can also do the following; this would be useful for a column which say for a forum displays the last updated or edited time of that post.

`updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

You can read more about this here, http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html.

Upon retrieving the value, to get a date like output from php use the date function.

More information on the date function can be found here http://php.net/manual/en/function.date.php.

It is also worth noting that your current code is not very well written and is open to SQL injection (http://en.wikipedia.org/wiki/SQL_injection), I would advise you to learn php's PDO (http://php.net/manual/en/book.pdo.php).

PDO also has the advantage that if in the future you change databases it is not a very hard to do task while using PDO.

Hi, i had already set the current_timestamp default. http://i41.tinypic.com/eumpf5.png . I do not care modification date. could you tell me the complete instruction to convert the date in readable format. The date is stored into database in this format 2013-06-06 16:30:41 P.S. How to understand if the code is open to SQL Injection? Sorry for my english — Simone Cognom, Jun 06 '13 at 14:29

score 0 · Answer 2 · answered Jun 06 '13 at 14:23

0

could try this

$query = "INSERT INTO table_name (Data) VALUES (CURRENT_TIMESTAMP())";

The problem seems to be that you're not inserting data properly which is why you're getting back that 0 Day time stamp.

answered Jun 06 '13 at 14:23

TheSnooker

935
2
11
24

Data PHP: How to do?

2 Answers2