php/mysql date display problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

theperfectdrug
Forum Newbie
Posts: 24
Joined: Tue May 25, 2004 10:15 am

php/mysql date display problem

Post by theperfectdrug »

Hi!
I'm making articles section using php/mysql; so I've got a problem in displaying dates;
In some of the articles I don't have complete date, like in date(DATE type 0000-00-00) field there is only a year or year and month; some of them are complete(year-month-day).
I'm using following query to format dates:
$sql = "select headline, body, sid, date_format(date, '%M %e, %Y') as fdate from articles order by date desc";
So dates look like this(in case if date is complete year-month-day):

EX: October 3, 2002

But if date is not complete (month-year) script returns:

EX: October 0, 2002

In case when there is only a year in date field (2002-00-00) it shows no date at all.

I just want script to return complete date when it's available, when there is only -month and year- in date field I want it to return date in following format, EX: October, 2002 instead of October 0, 2002...In case if there is only a year in Date field EX: 2002; I want script to show year, instead of empty space.

I've tryed a few methods, in all cases if I managed to format date (month day, year) or (month, year), I'm still getting empty space instead of year.

Any ideas?

Thanks
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

What format is your date column in?
theperfectdrug
Forum Newbie
Posts: 24
Joined: Tue May 25, 2004 10:15 am

Post by theperfectdrug »

What format is your date column in?
colum is DATE type(0000-00-00), so format is somethin' like this year-month-day, EX: 2002-01-25.
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

You could just do it like this?

Code: Select all

select date,
date_format(date, '%Y') as year,
date_format(date, '%m') as month,
date_format(date, '%d') as day
from dates
Which would give you 3 new columns from which you can work out on a code level what to do?
theperfectdrug
Forum Newbie
Posts: 24
Joined: Tue May 25, 2004 10:15 am

Post by theperfectdrug »

thanks!
now can you tell me what would be the best way to work out with this individual columns, maybe IF/else....?
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

Just check to see if month/day == 00? :)
theperfectdrug
Forum Newbie
Posts: 24
Joined: Tue May 25, 2004 10:15 am

Post by theperfectdrug »

Thanks a lot,
I'll give it a try...thx again
theperfectdrug
Forum Newbie
Posts: 24
Joined: Tue May 25, 2004 10:15 am

Post by theperfectdrug »

aha everything works fine, but now I want to convert dates (EX: 05 21, 2002 to May 21, 2002) ? :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

%M I believe.
theperfectdrug
Forum Newbie
Posts: 24
Joined: Tue May 25, 2004 10:15 am

Post by theperfectdrug »

ohh...stupid question... :D
theperfectdrug
Forum Newbie
Posts: 24
Joined: Tue May 25, 2004 10:15 am

Post by theperfectdrug »

I wrote lil' code, with if/else stuff, it works fine but I'm getting ',' before year (In case when there's only year in Date column EX: 2002-00-00)

Code: Select all

if ($day == ""){
echo "$month, $year";
}
elseif ($month == "" || $day == ""){
echo "$year";
}
else{
echo "$month $day, $year";
}
Here's Example:
, 2002

is there any way to fix this problem?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

might want to check against "00" instead of empty strings.. I dunno for sure though.
theperfectdrug
Forum Newbie
Posts: 24
Joined: Tue May 25, 2004 10:15 am

Post by theperfectdrug »

might want to check against "00" instead of empty strings.. I dunno for sure though.
hm...I'm getting same result with 00.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

with quotes?
theperfectdrug
Forum Newbie
Posts: 24
Joined: Tue May 25, 2004 10:15 am

Post by theperfectdrug »

same result in all cases, both with 00 and quotes. :(
Post Reply