Saving the date/time in a news system

Anon

Hi!
I'm currently programming a little news system with PHP and MySQL and no I have the problem to save the date and time.
The news are written in a PHP supported formular and are then saved in a MySQL database.
Thereafter they are presented on the website via PHP again.

This works with all normal content but not with the date and time.

My goal:
I just want to take the actual (server) time and date and save it in the Database so I can sort the output(how the news will appear on the website) by date and time.

Could you please post the PHP code I have to write and the format the database field has have?

P.S.: I searched for a solution for hours but unfortunately nothing worked.

Anon

actually, you want to use MySQL to do this.

in the table, create a TIMESTAMP field. This will be automatically set everytime a record is created to the current server time. (Be aware that if you update the record, the timestamp will be updated to the new time).

Then you can "select * from table order by mytimestamp desc" or whatever you want in order to get the records in order.

mithril

Or you can use mktime() in your sql insert to create a UNIX timestamp which is much more flexible and portable than the format used by MySQL. The field type would be INT(11) in this case and can also be ordered on. For more into, read the Date and Time Functions section of the PHP Manual. HTH.

-geoff

Anon

ahh, but the INT field doesn't update automatically. and you can select UNIX_TIMESTAMP(timestamp) from mysql to get the unix variant.

Anon

Because it is as news system I do not only want to sort the news by time and date but of course the time and especially the date has to be presented on the website later.

How to I do this?

Isn't it the case that PHP can not read the MySQL timestamp?(At least it didn't work for me)

Anon

Here is the source code and I hope you can tell me what I did wrong.

$db = mysql_connect();
$vtimedate = getdate('Y-m-d H:i:s');
$sqlab = "insert news";
$sqlab .= "(author, title, date, content) ";
$sqlab .= "values('$username', '$title', '$vtimedate', '$content')";

  	mysql_db_query("tsuniversity", $sqlab);

  	$num = mysql_affected_rows();
  	if ($num<1) echo "An error occured - no news posted";
	else echo "News posted!";
  	mysql_close($db);

The MySQL field date is timestamp, datetime, date, etc. (I tried everything).
Same thing with the command "getdate"('Y-m-d H:i:s');" I tried "date()" and "now()" as well.
The problem I have is that the data is simply not inserted.

Anon

assuming date is a timestamp field, you can do this:

$db = mysql_connect();
$sqlab = "insert news";
$sqlab .= "(author, title, date, content) ";
$sqlab .= "values('$username', '$title', NULL, '$content')";

and it will put the current date/time there.

as for pulling it out in reasonable form, can use MySQL's date functions which are much like PHP's. see this:

http://www.mysql.com/doc/D/a/Date_and_time_functions.html

you want the DATE_FORMAT function.

Anon

Ok, thanks!
I will try it and check out your link.

P.S.: I downloaded the PHP Manual some time ago but didn't find a program to extract the file. Do you know one?

Anon

I've not done that before but I think you open the file (using the file() function) and then read in each line, but that's all I know. Here is some code I found that does something like that:

$data=file('./data.txt');

foreach ($data as $line){
}

hope that helps.