convert datetime format

[SeenGee]

i'm using this code to retrieve results from mySQL and send them as an array over to flash MX:

<?php
include 'db.php';

$qr = @mysql_query("SELECT * FROM `events` ORDER BY dateWhen LIMIT 5"); 
$nrows = mysql_num_rows($qr);

print"myarray=";

for ($i=0; $i < $nrows; $i++) {
	$row = mysql_fetch_array($qr);
	print $row['name']."`";
	print $row['dateWhen']."`";
	print $row['styles']."`";
	print $row['price']."`";
	print $row['imageSrc']."|";

}
?>

The code works perfectly but i want to convert the date format used in the var dateWhen to a european style,
e.g, day:month:year time
before i send the data over to flash without altering the syntax of the output in the array, any ideas please?

[webdes03]

You could use a series of substr() statements to break the timestamp into the correct sets then reassemble them in the correct order...

so if you had an 8 character timestamp:

$year = substr("$timestamp", 0, -4);
$month = substr("$timestamp", 4, -2);
$day = substr("$timestamp", 6);

$newdate = $day.$month.$year;

$newdate would then be a timestamp in a different order...

[Trent_Hatred]

I believe you could do this within the MySQL select query using the DATE_FORMAT() function.

eg.

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%d %m %Y');
-> '04-10-1997'

I'm sure you can change the '-' to ':' somehow as well.

DATE_FORMAT() in conjunction with GET_FORMAT() should do the trick. Take a look at

http://www.mysql.com/doc/en/Date_and_time_functions.html

On second look, this would present you with a European date format:

mysql> SELECT DATE_FORMAT('2003-10-03', GET_FORMAT(DATE, 'EUR')
-> '03.10.2003'

Cheers,
B

[SeenGee]

Thanks but..
how could either of these be implemented with my code so that the output of the array is not interrupted and so that each instance of the datetime variable will be assigned this new value?
If you could adjust my code accordingly it would be hugely appreciated.

cheers.

[Trent_Hatred]

Perhaps you could use something like:

<?php 
include 'db.php'; 

$qr = @mysql_query("SELECT name, styles, price, imageSrc,  DATE_FORMAT(dateWhen, '%d:%m:%Y') AS newDate FROM `events` ORDER BY dateWhen LIMIT 5");  

$nrows = mysql_num_rows($qr); 

print"myarray="; 

for ($i=0; $i < $nrows; $i++) { 
    $row = mysql_fetch_array($qr); 
    print $row['name']."`"; 
    print $row['newDate']."`"; 
    print $row['styles']."`"; 
    print $row['price']."`"; 
    print $row['imageSrc']."|"; 

} 
?> 

Not entirely sure if that'll work because I can't test it, but it might. 😉

B

[SeenGee]

yeah that works great thanks, how could i also add the time into the new date variable?

[Trent_Hatred]

What type of field is the date/time stored as in your DB? datetime?

If so, then change the query to:

$qr = @("SELECT name, styles, price, imageSrc, DATE_FORMAT(dateWhen, '%d:%m:%Y %r') AS newDate FROM events ORDER BY dateWhen LIMIT 5");

$nrows = mysql_num_rows($qr);

Which I believe will include the time in a 12 hour format.

To customize the display, take a look at the link I posted above. About halfway down the page, it shows you all the possible % values you can use to display the date and time.

Cheers,
B