Format Date on output ¦ PHP

formatting a variable using strftime

[ukgimp]

I am trying to format a variable that is in date format during a loop.

If I leave the variable untouched the page works but I would like to have

Jan 01 instead of yyyy-01-01

So I have been trying to get it to work using

$variable = mysql_result($qResult,$j,"VariableField");;
$variable = strftime("%b %d" ,$variable);

but when the page executes all the dates appear as the same.

Is strftime() the way forward or should I be using a function that I dont know of. Thanks for your time.

Cheers

[Nick_W]

I think it's the right function for a TimeStamp but, your date doesn't look like one so I guess you need something else...

Why not do it direct with mysql?
Checkout the DATE_FORMAT function [mysql.com]

I'm pretty sure that will do it ;)

Nick

[ukgimp]

Got me stumped I have to admit.

As said above it worked for the first one and then repeats that value for the rest.

Checked out out that page nick, cant seem to find what I sm looking for. I would have thought that this would have been easy. Perhaps it is :)

[Nick_W]

Well on that page, about half way down you'll see the function: DATE_FORMAI()

But looking at your code again it looks like you are just reseting the variable on each iteration. Try using an array...

Nick

[ergophobe]

As said above it worked for the first one and then repeats that value for the rest.

Well that can't be caused by the formatting, that's caused by the data, so your problem must be in your queries. I would guess that you are returning the same result set every time you loop through and you are printing out the first row every time.

Can you put the query outside the loop and return a whole result set and then loop through it using mysql_fetch_array()?

Tom

[lorax]

that's caused by the data

Yup, I agree. It sounds like you're not indexing the result set or pointing to the appropriate item in an array. (not sure how you built your date var)

[ukgimp]

If I dont try and reformat the date it works fine. If I do it repeats

I am working towards a solution at the moment where I make extra SELECTS in the sql. Seems wastefull but at the moment I can get it to work with full month name (wanted MMM) and a day format (wanted dd).

I thought as you had already extracted the date in full you could format its output, perhaps i want too much :)

I will look at all your suggestions 8 am tommorrow. I'm off to the pub now. (9:30pm, bring on the lager)

Cheers

[lorax]

Hey ukgimp,
Off to the pub? 'ave one for me.

Is $variable a valid timestamp? If it isn't that would explain the repeating date. Although I can't think of why it would be correct for one and not for the others! I would expect them all to be incorrect - probably December 31, 1969 or some such.

[ergophobe]

So just to clarify: changing just that one line - i.e. adding the strftime() call - is the only change you make and it goes from working to not working?

By the way. I assume it's a cut and paste error, but you have an extra semi-colon in the code you posted.

Tom

[ukgimp]

Hi all

Is $variable a valid timestamp?

Yes it is set to DATE, not DATETIME.

If I leave it as the $variable straight from the

mysql_result($qResult,$j,"VariableField");

it works, all the repeats are the correct dates in full yyyy-mm-dd format. It just throws a mental when I try to reformat it in PHP using the strftime(). Perhaps that is it, it is not a time as such? If so how does one go abvout reformatting it?

By creating extra selects eg MONTHNAME and DAYNAME I can get it to work OK, but that is not the exact format I want and seems wasteful on resources, eg adding extra to the SQL for information that is already being used in a different format sucessfully.

Still largely baffled and wondering if the users can deal with :

"April Wednesday" instead of "Apr 02" :)

Cheers

[lorax]

Hey ukgimp,
You could format it right within the query itself by using DATE_FORMAT()

[mysql.com...]

Alternately you could try date() with strtotime() like this: $variable = date("M j" ,strtotime($variable));

I'm not sure why but it seems I must have something formatted improperly whenever I use a DATE field in MYSQL because whenever I try to display it I end up having to use date and strtotime to make it work so I won't say that's the best way to get what you're after - I just know it worked for me. ;)

[lorax]

An afterthought to my last post:

My issue with the DATE field in MySQL may be because it is not a full Unix Timestamp?

[ukgimp]

Found a simple solution

//////
$string = $variable;
$stringArray = explode("-", $string);

$date = mktime(0,0,0,$stringArray[1],$stringArray[2],$stringArray[0]);

$convertedDate = date("M j", $date);

///////

Then call the convertedDate variable. By changing the M and J in accordance with DATE_FORMAT depending on what you are after.

Thanks all for helping me along.

Cheers