How do i fetch date and time from MySQL table row

i found an old blog script (very old) kicking around on my PC. im having troubles with fetching the date and time from my DB to display in PHP. Can someone help me.

This is my MySQL DB setup.

CREATE TABLE blog_posts (
  id int(11) NOT NULL auto_increment,
  title varchar(30) NOT NULL default '',
  news text NOT NULL,
  poster varchar(15) NOT NULL default '',
  date timestamp(14) NOT NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM;

And this is what im using to send a post:

<?php

include "db.php";   
    $title=$_POST['title'];
    $news=$_POST['news'];
    $poster=$_POST['poster'];

    $query="INSERT INTO $blogposts (title, news, poster) VALUES ('$title', '$news', '$poster')";
    $result=mysql_query($query) or die (mysql_error());

    mysql_close();

    header("Location: post.php");
?>

And finally this is what im using to call the date on the front-end:

<?php echo "posted on: - ".$day.".".$month.".".$year." at ".$hour.":".$min; ?>

I'm no expert (clearly) but the call for the d开发者_Python百科ate doesnt look right to me. Anyone have any ideas on how i could make it work, or even make it better??

EDIT::

<?php

    include "db.php";

//query
    $query="SELECT * FROM ".$blogposts." ORDER BY id DESC LIMIT 0,$limit ";
    $result=mysql_query($query) or die (mysql_error());

//loop
    while($row=mysql_fetch_array($result))
    {
$querycomment = "SELECT ID FROM ".$newscomments." WHERE newsid=".$row['id'];
$resultcomment=mysql_query($querycomment) or die (mysql_error());
$num_rows = mysql_num_rows($resultcomment);

    ereg("^([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})",$row['date'],$res);
    $year=$res[1];
    $month=$res[2];
    $day=$res[3];
    $hour=$res[4];
    $min=$res[5];
    $sec=$res[6];


?>

Thanks

As date column has TIMESTAMP data type and got not null constraint so it will have default current_timestamp value if no data posted on date column while inserting. see mysql timestamp ref manual.

So to achieve a result that you ask you can try fetch that data by using code like this:

$sql = "select id, title, news, poster, DATE_FORMAT(date,'%d.%m.%Y at %k:%i') as posteddate from blog_posts";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
    echo $row["title"]."<br/>";
    echo "By : " . $row["poster"]."<br/>";
    echo "posted on: -  " . $row["posteddate"]."<br/>"; //"posted on: - 03.03.2011 at 7:35"
}

You don't seem to be adding any value to the date field in your insert command. You could use now() to add the current time stamp to add the current time during the insert. To extract the time you could either (a) read the date field from the table and format the time using PHP or (b) get formatted fields of the time stamp in the select command using the mysql date and time functions

You also need to have some code to read the values in your PHP to read the values for the fields. This seems to be missing in your question.

Surely you haven't defined any of the date variables, that you're trying to use?