Date and Time

[Lorddraco98]

I'm creating a news script in PHP/MySQL.
When a new news post is enetered into the DB, the date is stored like this
$date= date('Y-m-d H:i:s');

so the query inserts taht date and the other info into the DB. my DB colum in MySQL is a datetime column
So the data looks like this when stored: 2002-10-17 19:58:05

Now, in my main script that displays the news, I have a which loop that selects all this data from the DB and then displays it. I want to convert the date from the current form it's in, to a form like this: October 17th, 2002, 22:03:45

I tried using this code: $date= date("F j, Y, G:i a", $date);

but i got wierd output like: December 31, 1969, 19:33 pm

which is not the correct date hehe. Any help here?

[JdS]

this might help (but you have to change the column type that you used in your table):

http://www.desilva.biz/mysql/formatdate.html

[Lorddraco98]

That helps a little bit, but I need some help getting the actual data there into the DB. if I use that timestamp column, in my postnews script, what code would I use to set the date and then enter it into the DB along with my other data?
Also, this code seems like Ill have to add another query, because I currently use a Select * from news to get all the news info to display, but if I did that date query, I'd have to use a separeate select date_format() from news
right?? Any code examples could be very helpful!

[JdS]

you normally wouldn't; this is why i suggested TIMESTAMP, when you set the default value in this column to NULL, and if this column is not specified in your INSERT query, it will default to the current date and time - try it. (You may have to watch your UPDATE queries though).

re: your 2nd question,


// instead of

$sql = "SELECT * FROM...";

// you must now do this

$sql = "SELECT col_1, col_2, DATE_FORMAT(col_time,'%b %D, %Y at %T hrs'), col_4 FROM...";

[Lorddraco98]

Originally posted by JdS
you normally wouldn't; this is why i suggested TIMESTAMP, when you set the default value in this column to NULL, and if this column is not specified in your INSERT query, it will default to the current date and time - try it. (You may have to watch your UPDATE queries though).

re: your 2nd question,


// instead of

$sql = "SELECT * FROM...";

// you must now do this

$sql = "SELECT col_1, col_2, DATE_FORMAT(col_time,'%b %D, %Y at %T hrs'), col_4 FROM...";

ok, so they query would work.
Now with the TIMESTAMP. I'll set the DB column to timestamp instead of datetime.
Then, what code should I use for to take the curent date, and then format it?
I assume I'll still be able to create a variable with that formatted timestamp so my insert query will not change.

Lastly, with that select command you gave me that formats the date. can I just echo the date then or will I have to format it again using the date(); function??

[JdS]

ok, since you didn't give me a table to play with, i'll be using my sample table to show you how you would insert a row for that table:


$sql = "INSERT INTO tbl_messages (poster_id) VALUES($poster_id)";


and that's it! You do NOT have to specify or format the date & time in your INSERT query - like I said, try it.

and yes, you just echo the date afer the SELECT query (no formatting required).

$sql = "SELECT msg_id, DATE_FORMAT(msg_time,'%b %D, %Y at %T hrs')
FROM tbl_messages...";
$result = mysql_query( $sql,$conn );
while( $row = mysql_fetch_row($result) )
{
echo $row[1]; // output formatted date
}

[Lorddraco98]

Originally posted by JdS
ok, since you didn't give me a table to play with, i'll be using my sample table to show you how you would insert a row for that table:


$sql = "INSERT INTO tbl_messages (poster_id) VALUES($poster_id)";


and that's it! You do NOT have to specify or format the date & time in your INSERT query - like I said, try it.

and yes, you just echo the date afer the SELECT query (no formatting required).

$sql = "SELECT msg_id, DATE_FORMAT(msg_time,'%b %D, %Y at %T hrs')
FROM tbl_messages...";
$result = mysql_query( $sql,$conn );
while( $row = mysql_fetch_row($result) )
{
echo $row[1]; // output formatted date
}

great, thanks. That seems easy enough hehe. I'll give it a try when I get home, and hopefully that will work :)
Thanks!!