mktime value database

I run a charitable site that allows users to create their own site in one of several languages, I'm trying to get the date to display in the language of the specific site, rather than just using English dates.

I've **almost** got there, but the database values aren't being returned using the following code - hopefully someone can sort me out!

The test query is:

$query_rsTest = "SELECT pages.PgID, pages.PgTitle, DATE_FORMAT(pages.EvStDate, '0, 0, 0, %c, %e, %Y')AS EvStDate FROM pages WHERE pages.EvStDate <>'' limit 5";

which appears to return the dates in the rquired format, eg 0, 0, 0, 9, 17, 2014

This bit of code I found elsewhere works fine:

<?php
setlocale(LC_TIME, 'fr_FR.UTF8');
echo strftime("%A %e %B %Y", mktime(0, 0, 0, 8, 22, 2014));
?>

returning the date in French.

However, when I try to use this example with database values, it defaults to today's date:

<?php do { ?>
<p>Tests: <?php
setlocale(LC_TIME, 'fr_FR.UTF8');
echo strftime("%a %e %b %Y", mktime($row_rsTest['EvStDate']));
?></p>
<?php } while ($row_rsTest = mysql_fetch_assoc($rsTest)); ?>

I'm guessing that some additonal code is needed around $row_rsTest['EvStDate'] - but I'm clutching at straws!

Any help much appreciated

Thanks
Chris

Remove the date confversion from your SQL statement and just use:


$query_rsTest = "SELECT pages.PgID, pages.PgTitle, EvStDate FROM pages WHERE pages.EvStDate <>'' limit 5";

Then use this code:

<?php do { ?>

<p>Tests: <?php

setlocale(LC_TIME, 'fr_FR.UTF8');

echo strftime("%a %e %b %Y", strtotime($row_rsTest['EvStDate']));

?></p>

<?php } while ($row_rsTest = mysql_fetch_assoc($rsTest)); ?>

Many thanks Ray.

Over the weekend, someone suggested using TIMESTAMP, which worked until I tried to make it all dynamic, where it fell apart due to the ' '

Anyway, got is all working now by: (thee 'es_ES.UTF8' will be changed on the final page to a database value)

$query_rsTest = "SELECT pages.PgID, pages.PgTitle, UNIX_TIMESTAMP(pages.EvStDate) AS EvStDate, DATE_FORMAT(pages.EvStDate, '%H:%i') AS StTime, 'es_ES.UTF8' AS 'locsetting' FROM pages WHERE pages.EvStDate <>'' limit 5";

then

<?php do { ?>
<p> <?php
setlocale(LC_TIME, $row_rsTest['locsetting']);
echo strftime("%a %e %b %Y", $row_rsTest['EvStDate']);
?><?php /*START*/ if ($row_rsTest['StTime'] <>'00:00'){ ?> at <?php echo $row_rsTest['StTime']; ?><?php /*endT*/ } ?></p>
<?php } while ($row_rsTest = mysql_fetch_assoc($rsTest)); ?>
<?php
// reset the recordset after a repeat region
mysql_data_seek($rsTest, 0);

// get the first row from the recordset
$row_rsTest = mysql_fetch_assoc($rsTest);
?>

I've separated the time values and will have a translation field for 'at'.

Job sorted!

Many thanks once more,

Cheers
Chris