Click to See Complete Forum and Search --> : mysql timestamp only showing 0000-00-00
gokou
09-25-2003, 02:36 PM
I am trying to make an automating news script. I want it to display when the script was submitted. Also, the order in which the news is displayed is relying on the timestamp. The timestamp is only showing 0000-00-00.
heres url to sitehttp://www.uogameresources.com/sik
This code is in the news page.
$query = mysql_query("select id,name,title,text,DATE_FORMAT(postdate, '%Y-%m-%d') as date from news order by postdate DESC limit 6");
while($news = mysql_fetch_array($query)) {
echo "<div class='n_title'>Title: ". $news['title']. "<br>";
echo "Posted by: ".$news['name']." on ".$news['date']."</div>";
echo $news['text']. "<br><br>";
This code is in the form page.
mysql_query("insert into news (name,title,postdate,text) values ('$name2','$title','postdate','$text')");
It looks like it could be your insert command. postdate is probably supposed to be $postdate...
gokou
09-25-2003, 03:30 PM
Nope, that wasn't it. Maybe I should of showed the form code?
Am I suppose to add a hidden field to insert the postdate? I don't see why I would though.
<html>
<body>
Submit News<br />
<form name="form2" method="post">
Name:<input type="text" name="name2"><br />
Title:<input type="text" name="title"><br />
Text:<textarea name="text" rows="5"></textarea><br />
<input type="submit" value="submit">
<input type="reset" value="reset">
</form>
</body>
</html>
In the code you originally posted, how is the date suppose to be filled out? I was assuming you would want something like this:
$name2 = $_POST['name2'];
$title = $_POST['title'];
$postdate = time(); #returns current unix timestamp
$text = $_POST['text'];
mysql_query("insert into news (name,title,postdate,text) values ('$name2','$title','$postdate','$text')");
gokou
09-25-2003, 07:04 PM
Sorry. I made a mistake. I don't need to insert it.
I made a mysql timestamp field called postdate. That field is already being used on the page, but it's showing 0000-00-00 instead of a real date.
Look in the database. I'd be willing to bet it is incorrect there as well, which means it is being inserted wrong.
gokou
09-25-2003, 11:31 PM
CREATE TABLE news (
id int(20) NOT NULL auto_increment,
postdate timestamp(14),
name varchar(50) NOT NULL,
title varchar(100) NOT NULL,
text text NOT NULL,
PRIMARY KEY (id)
);
That's my table code. I don't think theres anything wrong.
Hmm... Yes, if you are using a timestamp field, it should be automatically updated. Try this:
mysql_query("insert into news (name,title,postdate,text) values ('$name2','$title','','$text')");
gokou
09-26-2003, 09:30 AM
I thought that was gonna work, but didn't. Maybe it's how I selected it.
Daot Lagorille
09-26-2003, 09:45 AM
Yowie.
I have been doing similar things and there are two ways I have done this:
First, I declare variables:
$varCurrentdte = date ("Y-m-d");
$CurrentTime = date ("h:i");
Then, I use hidden fields in the submission form, with the values set to:
<?php echo $CurrentTime; ?> (and the other one too)
These are inserted into the appropriate fields in the database.
The other way, which I think is what you want to do, is that I have a field in the mysql database with the type set to "TIMESTAMP".
In this case, you need to insert nothing into that field - the field in each record will have the value of whenever it was created, and look like this:
2003080512372
It goes year, month, day, minutes, seconds...
Helps?
Daot Lagorille
09-26-2003, 09:47 AM
mysql_query("insert into news (name,title,postdate,text) values ('$name2','$title','','$text')");
Problem with this code is that you have the timestamp field in the insert line. Don't. Then you will be fine, I think.