Php Date Format Issue

[sgr55]

This isnt as simple as the title makes it sound.

I'm coding my own news script and have now started work on the configuration which is stored in mysql

I can now update the dateformat via the admin and when i add new news the new date format is present on the new item.

My problem is all the old news items still have the old date format and i'm wondering if theres an easy way of fixing this.

I was thinking i should add a piece of code in my news retrieval script to change the date format if it isnt in the currently set format but i'm not sure how to do this!

	

$date_query=mysql_query("SELECT * FROM config");

$row=mysql_fetch_array($date_query);

$date_format = $row[date_format];

$news_date= date($date_format);

So the above is from my add news code... as you can see it retrieves the date format from the database and enters the date for the current news post in that format.... simple enough

for retrieviing the new i have the query and a loop so that all the news items are displayed in there own little box

<?php	
	include ('admin/functions/db.php');
	db_connect ();
	$query=mysql_query("SELECT * FROM news WHERE news_active='1' ORDER BY news_date DESC ");
	while ($row=mysql_fetch_array($query)) {
		echo"<div class=\"contentBox\">";
			echo"<div class=\"BoxTitle\">";
			echo $row[news_title];
			echo"</div>";

		echo"<div class=\"BoxContent\">";
		echo $row[news_body];
		echo"</div>";

		echo"<div class=\"BoxFooter\">";
		echo"Posted by ";
		echo $row[news_poster];
		echo" on the ";
		echo $row[news_date];
		echo"</div>";
	echo"</div>";
	echo"<br />";
}
?>

This is part of the news retrival code minus the code for putting news of x pages.

Can anyone suggest the best way of getting all the news to have the same date format... rather than only the latest news having the format updated.

Thanks in advance

[devinemke]

it's not clear from your post: what is the difference between the old and new date formats?

[sgr55]

well you know how u cant get php to display the date in different formats e.g month / day / year

or day / month / year and mayebe time.

using codes such as Ymdhis etc

http://uk.php.net/manual/en/function.date.php

basically i'm storeing these codes in the mysql database and when the news is printed on screen the data is retrieved from the database in the format in which is was inserted.

When these codes change for a different format only the news items which are posted after the change have the new format... all the rest still have the old format

[devinemke]

why aren't you simply storing the date/time in the db in the standard mySQL format (YYY-MM-DD HH:MM:SS). you can format it anyway you want for display purposes using either mySQL or PHP date/time functions. am i missing something obvious here?

[sgr55]

hmm that got me thinking you are right.
i think i've been rather stupid here.

Yeh so basically i was trying to do something here that i dont think you can do that easily.

anyway when the news is added its now added in the standard mysql format YYY-MM-DD HH:MM:SS.

to retrieve the date i use the mysql query

and echo the date using $row[date_format];

that works. Now the fun part.

How do i put this in a different format... e.g D/M/Y without the time.

// The MySQL query
$date_query=mysql_query("SELECT * FROM config");

// Gets the information in the Date row
$row=mysql_fetch_array($date_query);

// Sets the Date Format Varible
$date_format = $row[date_format];

I'm using the above code to retrieve the php date format i want to use from the database.

So how do i format the output date in the format i've retrieved from the database?

[devinemke]

like i said you can either use mySQL's date functions:

$result = mysql_query("SELECT *, DATE_FORMAT(date_format, '%d/%m/%y') AS date FROM config");

or PHP's date function:

$date_format = date('d/m/y', $row['date_format']);

[sgr55]

ok you just confused me there

what i have is
the $news_date varible which has the date in the format of

YYY-MM-DD HH:MM:SS

and $date_format which has the information from the database... currently set to

d.m.y

what i need is a bit of code to convert the date in the $news_date varible into the format which is stored in the $date_format varibile

---

$date_format = date('d/m/y', $row['date_format']);

this doesnt have the date from the database so thus the date will change every day. Well the data is static... set when the poster posted the news.

sorry if i'm being an idiot and missing something.... i'm usually very slow at picking up my stupidily at times.

[sgr55]

btw just to let u know i'm developing under windows not unix although the script shoudl run under both wheh complete

[Weedpacket]

Now I'm confused. What is the old format? (I know what the new format is; ISO8601, and the field type in the database is a date/time type).

Assuming the old format is 'd/m/y', and you're trying to convert it into ISO8601 in preparation for putting it into the new date-type field in the database....

list($day,$month,$year)=explode('/',$date_in_dmy_format);
$date_in_iso8601_format = "$year-$month-$day";

[sgr55]

sorry...
when the news is retrieved the date is stored in varible
$news_date

the format in which it is to be stored is in the varible
$date_format

lets simplify this a bit... i'll remove the mysql stuff.

<?php

$date_format = "d.m.y";
$news_date = "2004-08-13 04:12:17";

// just an example of the date in the standard mysql format. its normally retrieved from the database along with the date format which is just sorted as text

//Now i want to print $news_date in the format of $date_format
// this is the bit where i've managed to confuse myself.

?>

[devinemke]

is there a specific reason that you are storing the actual date formatting data in the database itself?

[Weedpacket]

Originally posted by sgr55

//Now i want to print $news_date in the format of $date_format
// this is the bit where i've managed to confuse myself.

?>

[man]strtotime[/man] can deal with ISO8601 dates. [man]date[/man] can deal with strtotime and formatting strings.

[sgr55]

is there a specific reason that you are storing the actual date formatting data in the database itself?

Yes.... although i could have it set as a varible in the file this just makes it harder to change... so i just stored it in mysql which can be retrieved by any file.

Also i didnt really like the idea of saving it to a file useing the fp functions (+i've never done any work saving to files before... i prefer database as its usually easier to work with)

[devinemke]

so then i assume that different records will have different date formatting strings? is this why you are stroring the actual formatting in database record itself? if so you might want to think about using an ENUM field instead of text. i'll assume that there are probably a limited number of formatting options.

[sgr55]

errm no forget the mysql for a moment

Take this example below

<?php 
//
// Config
//


// Enter the format in which you want the date to appear
// you can change this to any valid php date format
$date_format = "d.m.y"; 

// enter The date in the format of YYY-MM-DD HH:MM:SS
$news_date = "2004-08-13 04:12:17"; 


//
// The actualy convertion code 
//

Code to change 2004-08-13 04:12:17 into 13-08-2004 
Basically change the $news_date so it displays in the format of $date_format


?>

this is what i want to do, but in my code the two varibles are fetched from the database rather then being set in the php code itself and this part works fine... its just the date format conversion i'm having trouble with

[sgr55]

ok i got it working now

echo date($date_format,strtotime($news_date));

sorry for being a bit of an idiot :p