Sort by after fetching

Some time ago I was helped by Bob with this code

<?php
//Fetch a list of different dates with registrations
$db = JFactory::getDBO();
$query = 'SELECT %2$s FROM %1$s GROUP BY %2$s';
$pst = sprintf($query,
  $db->nameQuote('jos_chronoforms_TABLE'),
  $db->nameQuote('Wedstrijddatum')
);
$db->setQuery($pst);
$items = $db->loadObjectList();
?>
<div class="form_item">
<div class="form_element cf_textbox">
<label class="cf_label" style="width: 120px;">Wedstrijddatum</label>
<select name="datum">
<option value="">Kies wedstrijddatum</option>

<?php
foreach ($items as $item) {
  echo('<option value="' . $item->Wedstrijddatum . '">' . $item->Wedstrijddatum . '</option>');
}
?>

But how to sort the Wedstrijddates (now it picks up randomly

I added SORT BY

$query = 'SELECT %2$s FROM %1$s GROUP BY %2$s' SORT BY %2$s' ;

But the dates in the HTML code are set as
20-01-2011
23-02-2011
19-03-2011

After sorting
19-03-2011
20-01-2011
23-02-2011

So, it only sort on the first two digits.
Can I tackle this??

Hi SPABO,

It's sorting on the whole 'date' but is treating it correctly as a string. MySQL has no way of knowing that you think this is date.

It's a good example of why it is almost always better to save dates in MySQL DATE or DATETIME format (or at least as a yyyy-mm-dd string).

Bob

Tried "DATE" storage in the table, but then the output was 0000-00-00
Problem : The Dutchies are not familiar with YYYY-MM-DD🙂
I will do some other testing with DATETIME , will let you know

Hi SPABO,

You;'ll find the same issues with DATETIME as with DATE - you have to add the code to re-format before you display (and maybe before you save too).

Bo

Something like this Bob

$then = JFactory::getDate(JRequest::getString('Wedstrijddatum', 'now'));
$now = JFactory::getDate();
$datum = $then->toUnix();

Hi SPABO,

Well, a little like that except (a) that getDate() is not a valid method (b)$then isn't an object and so can't take a method with -> and (c) as far as I can see toUnix() isn't a valid PHP method.

I'm afraid that you can't just invent code and expect that it will work. There are plenty of reference documents freely available that you can use to check your code.

Bob

Hi,
Just to pitch in..
In the rare cases I have to re-format dates in PHP, I use the native DateTime class: http://se2.php.net/manual/en/class.datetime.php
(and when needed, DateInterval: http://se2.php.net/manual/en/class.dateinterval.php )

<?php
$date = JRequest::getString('Wedstrijddatum', 'now')
//Parse a date in a standard understood by PHP: http://www.php.net/manual/en/datetime.formats.date.php
$then = new DateTime($date);
//Parse a custom date, in this case MM-DD-YYYY
$then2 = DateTime::createFromFormat('m-d-Y', $date);

//Get the unix-time for either:
$unix = $then->getTimestamp();

//Output a date formatted to your likings:
echo($then->format('m-d-Y'));

//Compare the time between $then and 'now'
//This is an instance of DateInterval
$diff = $then->compare(new DateTime());
echo($diff->format('%m months, %d days ago'));

/Fredrik