time in database?

[fristi]

Hi,

I went through the sticky in this section and I couldn't find it, sorry if this problem was in there but I really didn't see it.

My problem is with adding the time to a database entry through PHP.

My table with fields:

newsID
author
title
news
date

the date is a timestamp(14)
For some reason added phpmyadmin the default value of NULL.

I'm using this script:

<?php

$dbh=mysql_connect ("localhost", "USER", "PASSW") or die ('I cannot connect to the database because: ' . mysql_error());
$db=mysql_select_db ("newsdb",$dbh);

$query = "INSERT INTO News(author , title , news , date)
VALUES('$_POST[author]', '$_POST[title]','$_POST[desc]', 'now()')";
$result = mysql_query($query);
echo ("news item entered");
?>

Everything is entered corectly into the table, except for the time, it just keeps NULL.

Can someone tell me what I'm doing wrong?

Also when I get an error when I add the next line to the script:

mysql_close($db);

And I don't know why.

Thank you for any help.

[tsinka]

Hi,

remove the ' chars around now(). It is interpreted as a timestamp string otherwise and the string now() is not a valid date and will be set to 0 by MySQL.

Try mysql_close($dbh);

But you don't need it either since php closes the connection automatically as soon as parsing is done.

Thomas

[Yamakazi]

I use this:

$date = date("YmdHis");

It returns the 14digit date as mysql needs it to be.

Hope that helps

[tsinka]

Hi,

quite some time has gone by since I read the MySQL manual but AFAIK you can use now() with timestamp fields as long as you don't put ' around it.

Thomas

[fristi]

Hi,

Thank you both for your replies; I removed the ' around the now() and it worked. Now All I have to do is get it to show like a normal date hehe.

I have an other question, I have read some on the addslaches function. Is it really required that you use this function or isn't it important?

[Yamakazi]

Yeah you're right

I was a bit confused with that one. Deleted that from my post above. But yeah you can use now() to insert in a timestamp field.

So just ignore whatever i said 🙂

[Yamakazi]

If you don't add slashes and insert this text in a query you'll have a problem

polly's book

insert into test (title) values ('polly's book');

You see where that will go wrong.

You can use add slashes or if it's HTML

replace ' with ´
and " with "

I wrote a class that handles a timestamp and has a outPutString method. Wich parses the timestamp and returns a 'decent' readable date.

Happy coding 🙂

[tsinka]

Hi,

if you want to insert data into the table that e.g. contains ' or something like that that would mess up the query you might do e.g.

$value = "something ' like ' this";

$query = "INSERT INTO thetable (thefield) VALUES('".mysql_escape_string($value)."')";

mysql_escape_string escapes the given string so it wouldn't mess up your query.

If you want to print the date you could do e.g.

$sql = "SELECT UNIX_TIMESTAMP(thedatefield) AS dt FROM ....";

// fetch the row with e.g. mysql_fetch_assoc

// print it (german style in this case)
echo date("d.m.Y H:i:s",$row['dt']);

One more hint:

The first timestamp column in a table will be set automatically to the current date/time when doing an update. To prevent this do

UPDATE table SET datefield=datefield,otherfield='value' WHERE ...

Thomas

[fristi]

Hi,

Thanks again for your replies.
I see where the addslashes and mysql_escape_string come place 🙂

But when I tried this:

<?php

 $dbh=mysql_connect ("localhost", "USER", "PASSW") or die ('I cannot connect to the database because: ' . mysql_error());
$db=mysql_select_db ("newsdb", $dbh);

$query = "SELECT * FROM News ORDER BY datum DESC LIMIT 10";
$result = mysql_query($query);
echo ("<table border = '0' width = '90%'>");
while ($rows = mysql_fetch_row($result))
{
echo ("<tr bgcolor='violet'><td>$rows[2]</td><td>author : $rows[1]</td></tr>");
echo ("<tr><td colspan ='2' bgcolor='lavender'>$rows[3]</td></tr>");
echo ("<tr bgcolor='violet'><td colspan=2>Submitted on :");
echo date("d.m.Y H:i:s",$row[4]);
echo "</td></tr>";
}
echo "</table>";
?>

the date always shows up as 01.01.1970 01:00:00
As if the value of the timestamp is 0. And I'm sure it isn't 0, I checked.

And tsinka, what do you mean by
"UPDATE table SET datefield=datefield,otherfield='value' WHERE ... "

I don't know where I have to put this code or what the otherfield is.

Sorry.

But thanks for all the help so far 🙂

[TheDefender]

Do yourself a couple of favors...

1) Call your variables out of the $row array by name...

echo date("d.m.Y H:i:s",$row[4]);
echo date("d.m.Y H:i:s",$row['date']);

This way if you add columns later (in front of the date column), it won't affect this line...

2) Change your date colum to INT(12) type instead of timestamp, and store your variables in a UNIX Timestamp format (seconds since the epoch). It is just easier to work with, manipulate, etc...

To store the current date and time, just create a variable before your insert, and insert the value of that variable (instead of inserting now().)

$todaydate = time();
$query = "INSERT INTO News(author , title , news , date)
VALUES('$POST[author]', '$POST[title]','$_POST[desc]', '$todaydate')";

IIIIIIIII maynot seem that big of owanted to make comparisons, or other date/time manipulation s later, it saves you steps, effort, energy and time, since you don't have to convert it to UNIX Timestamp format, do the math, and convet ito a readable format later... Just do the math, and make it readable...

That's my 2 cents worth...

[Yamakazi]

If he shows Jan 1st 1970 it means the value you tried to insert is wrong or the date you tried to insert is prior Jan 1st 1970.
Try checking the value on the mysql prompt. What does that state?

What the other poster meant was:

If you update a record that has timestamp fields in it the first timestamp field is automaticly updated. Even if your update statement doesnt contain the field. This is a behaviour that i think sometimes comes in handy. But let's face it most of the time it's just unwanted behaviour. (example: if the first timestamp is the day of birth) You don't want that one changing in the time the last update was preformed now would you 🙂

Conclusion. In you update statement use SET datefield=datefield.

This way the initiale date will be kept in tact

Happy coding

[Yamakazi]

TheDefender one question then?

If you want to do time operations on people birth date's how does that work. Epoch time means the ticks since Jan 1st 1970 right? What happens with people born in the sixties?
Damn hippies :p

I just ask 'cause i had problem with that one during my last project

[TheDefender]

Bah! Those old fogey's shouldn't count, since the only thing they know how to do on computers is to play solitaire, and write email... Could've saved themselves 1500 dollars by buying a deck of cards, pen, paper, envelopes and stamps...

Just kidding.. If that's the case, then yes, you would need to use a different format... In which case, I personally would store the data in DATE/TIME type field (YYYY-MM-DD HH:MM:SS), and explode the value into an array to manipulate it later.

[fristi]

Thanks for all the tips.

The defender, what do you mean by store your variables in a UNIX Timestamp format whan I changed it to an int(12).

I'm new to this :bemused:

And Yamakazi, this is the original value of the timestamp: 20040715132018
But when I display it through the code it comes out 01.01.1970 01:00:00

[TheDefender]

Read my previous post... In it, I explain how to create the variable for storing it... (Seconds since the epoch using the strtotime() function).

[fristi]

OK, it took me a while but I figured everything out. 🙂

Thanks for all the help everybody 🙂