[RESOLVED] more formating date issue..

[vinny199]

Hello,

I have a date field (for example):

$event_date = '2007/03/04'

and I need it to output as 04/03/2007

I have tried using explode, but that doesnt work as I understand my date is already "exploded", I've also tried "flattening" it as a unix date and then explode it, but I get date results from another time dimension it seems..

any idea pls?

Thanks,

Vinny

[leatherback]

Hi Vinny,

Please explain "field". Is it in a database?

Then:

"select *, DATE_FORMAT(N_date, '%m %d, %Y') as date, ";
see http://www.upwebmaestro.com/mysqldatetime.php

Else you could use [man]explode/man and have a play with the [man]date[/man] functions, and/or try [man]date_parse[/man]

[vinny199]

Thanks,

yes, it is a retrived database row:

$event_date = $row_rs_updated_oerder['StartDate'];

and it ouputs as for example 2005/03/04 (this is how the dates in this table column were inputed) if the row retrieved is today's date.

Does that help?

Thanks,

V.

[leatherback]

As I posted:

Let MySQL do the work:
"select *, DATE_FORMAT(StartDate, '%m %d, %Y') as date from... ";

and use the date formatting codes in the link I provided

[vinny199]

ok

thanks i'm going to try that, but I was hoping not to have to modify my sql statment.

this is all part of a loop that sends out multiple emails according to my SQL statment.

its not just retieving a value and printing it on the page.

it took me forever to get that loop working and the staments right.

All I was hopping to do was get my
2005/03/04 and change it to 04/03/2005 by using somethign like

$d = explode("/",$$event_date);
$newdate = $d[2]."/".$d[1]."/".$d[0];

WHITHIN my loop, except I just cant work out the explode as my date in the table row is already "exploded" with /

But a workaournd that would be perfect if you could point me towards where I'm going wrong in my explode function to achieve the desired result.

I have no doubt what you suggested works great, but I'm not too sure how to make it work within my current code.

Thanks,

Vinny

[leatherback]

Ok, I still think you should just use the code I suggested in you mysql. You could just place ", DATE_FORMAT(StartDate, '%m %d, %Y') as date2"
in the existing sql statement, and $row_rs_updated_oerder['date2'] should hold your modified date.

Anyway..
If your data is already exploded, you have the pieces in some variable. If you canshow me how it is exploded, I can help you put it together.
The code you just showed has a double $ sign, which might be why the initial attempts didn't work?

[vinny199]

Thanks,

I've tried a few trhings but cant get my statment right. keep getting errors

$query_rs_updated_oerder = "SELECT * FROM order_ticket  DATE_FORMAT(StartDate, '%m %d, %Y') as date2 WHERE order_ticket.OrderID = '".$cartID."' ";

and then my html formated email will send out: <td width=\"349\" height=\"25\" valign=\"top\">".$date2."</td>

and the date is formated as YYYY/MM/DD?

[leatherback]

try:

$query_rs_updated_oerder = "SELECT *, DATE_FORMAT(StartDate, '%m/%d/%Y') as date2  FROM order_ticket WHERE order_ticket.OrderID = '".$cartID."' "; 

You need to check whether month and day are in the right order, as from your example I cannot tell which is which

[Installer]

It's weird that MySQL is returning a date field in the format "yyyy/mm/dd" while it stores and retrieves dates formatted "yyyy-mm-dd". If the date string wasn't somehow getting messed up like that, then you could easily do what you want to in PHP:

$event_date = date('d-m-Y', strtotime($row['my_date']);

Or, even better, then you could use leatherback's solution.

[leatherback]

Hm.. Instaler made me think of something.. You ARE storing the date in a date, datetime, or timestamp field, correct!? NOT as a string (text/varchar)

[leatherback]

In case all else fails.. this works fine too:

$date = "2007/02/03";

$expdate = explode("/", $date);

print_r($expdate);

echo  $expdate[2]."/".$expdate[1]."/".$expdate[0]

[vinny199]

Thanks installer, unfortunatly nothing is "messed" up.

It is just how the dates where entered into the databse. It wasn't done using Unix date format, but the dates went into the databse as yyyy/mm/dd using a "date imput" calentar javascript.

Now I'm left with those dates store in that format and badly need to send them out as emails in the UK format

THANKS leatherback, but not quite there yet.

Now the SQL query does not give any error but $date2 comes out blank.

no values show anymore.

any idea?

thanks,

V.

[leatherback]

Vinny: Is the date stored in a adate kind-a field? Or as text.
Also: See my previous post

[vinny199]

plain text, varchar

[leatherback]

OK, that is your problem, and a reasonabl design flaw.

Having said that..
WHY did the explode not work?
Did you try the code I showed you?

e.g.:

$expdate = explode("/",$row_rs_updated_oerder['StartDate']);
echo  $expdate[2]."/".$expdate[1]."/".$expdate[0] 

[Installer]

That's different. When you said, in you first post

date field

we thought you meant "date field" (which would be a reasonable way to store a date).

Edit: nm

[vinny199]

yes,

that was exactly my starting point. that was the code I was using before posting a thread.

I've used exactly the code you provided and I get the smae result:

Date is echo as:

//2007/03/08

All it does is add an extra / at the begining of the date.

(I'm sorry if I confused matters in my first thread,

My terminology was not correct.)

[leatherback]

Please post the relevant section of your code:
From the query, to the point where it does not produce the right echo.

[vinny199]

ok,

Im trying to post just the relevant bits.

But everything works as far asthe query, triggering multiple emails to multiple recipients (the correct ones too) and all the $values print correctly in the sent out emails too, including that date, except it is in the wrong format.

I have not posted the mail() section as it is a massivley long HTML $message but it echoes the values from the code below:

mysql_select_db($database_to_octo, $to_octo);
$query_rs_updated_oerder = "SELECT * FROM order_ticket WHERE order_ticket.OrderID = '".$cartID."' ";
$rs_updated_oerder = mysql_query($query_rs_updated_oerder, $to_octo) or die(mysql_error());
$row_rs_updated_oerder = mysql_fetch_assoc($rs_updated_oerder);
$totalRows_rs_updated_oerder = mysql_num_rows($rs_updated_oerder);

$item_reference = $row_rs_updated_oerder['ItemID'];

mysql_select_db($database_to_octo, $to_octo);
$query_rs_adverts = "SELECT * FROM adverts WHERE adverts.AID = '".$item_reference."'";
$rs_adverts = mysql_query($query_rs_adverts, $to_octo) or die(mysql_error());
$row_rs_adverts = mysql_fetch_assoc($rs_adverts);
$totalRows_rs_adverts = mysql_num_rows($rs_adverts);

 if ($row_rs_updated_oerder['transStatus']=="Y"){ 

//update ticket count

do { 
 $ad_substraction = $row_rs_adverts['ad5'] - $row_rs_updated_oerder['Quantity'];
 $cust_email = $row_rs_updated_oerder['email'];
 $cust_name = $row_rs_updated_oerder['cust_name'];
 $cust_phone = $row_rs_updated_oerder['phone'];
 $tickets_quantity = $row_rs_updated_oerder['Quantity'];
 $order_number = $row_rs_updated_oerder['cartID'];
 $transaction_ID = $row_rs_updated_oerder['transID'];

  $Organiser = $row_rs_updated_oerder['Organiser'];
  $event_name = $row_rs_updated_oerder['Name'];
  $event_time = $row_rs_updated_oerder['StartTime'];
  $sub_category = $row_rs_updated_oerder['Sub_category'];
  $event_date = $row_rs_updated_oerder['StartDate'];
  $style = $row_rs_updated_oerder['Style'];
  	  	  $email = $row_rs_updated_oerder['Description'];
	  $venue = $row_rs_updated_oerder['Venue'];
	  $postcode = $row_rs_updated_oerder['Postcode'];

	  $expdate = explode("/",$row_rs_updated_oerder['StartDate']); 
echo  $expdate[2]."/".$expdate[1]."/".$expdate[0];

// or of course I tried $newdate = $expdate[2]."/".$expdate[1]."/".$expdate[0]; and printed that off in the sent emails with the results mentioned previously

if ($result = mysql_query ($query_rs_adverts) ) 

while ($row = mysql_fetch_assoc($result))     { 
  $ad5 = $row['ad5'];

// here starts the mail() functions that I have not included

[leatherback]

I can see no reason why the explode would not work.

Can you humour me, and paste this code in your script, and post back what that ouputs:

echo "
<pre>
--
Orig:  [".$row_rs_updated_oerder['StartDate']."]";
$expdate = explode("/",$row_rs_updated_oerder['StartDate']);

echo "expoded:";

print_r($expdate);

echo "
Formatted: [".$expdate[2]."/".$expdate[1]."/".$expdate[0]."]
--
</pre>"; 

// let's stop the email from being sent for now
die();