fao David Powers

[Newsgroup_User]

David,
Can you explain why the top sql does not bring a correct date order?
http://www.justict.co.uk/test.php
thanks

[Newsgroup_User]

ina wrote:
> David, Can you explain why the top sql does not bring a correct date order?
> http://www.justict.co.uk/test.php

It's doing exactly what you have told it to do: order the date column in
descending order. The problem is that you have formatted the dates using
the names of the months. So, in reverse alphabetical order you get
February, August, April.

To get the results that you want, you need to use a different alias for
date like this:

SELECT id, story_short, story_full, image1_name,
DATE_FORMAT(`date`, '%b %D %Y') AS formatted_date
FROM news_stories
ORDER BY `date` DESC

Notice that date is surrounded by backticks in each instance, because
you have used a reserved word as the column name. Your formatted date
will be in your recordset as formatted_date.

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[Newsgroup_User]

Thanks David,
That sorts the recordset out perfectly.
I am exporting it to xml and when I create a spry dataset:

<!--
var ds1 = new Spry.Data.XMLDataSet("export2.php",
"export/row",{sortOnLoad:"formatted_date",sortOrderOnLoad:"descending"});
//-->
</script>

the order becomes messed again!


Unless you can think of anything obvious I will leave spry alone for the
time being.
ina



"David Powers" <david@example.com> wrote in message
news:fm398v$i3s$1@forums.macromedia.com...
> ina wrote:
>> David, Can you explain why the top sql does not bring a correct date
>> order?
>> http://www.justict.co.uk/test.php
>
> It's doing exactly what you have told it to do: order the date column in
> descending order. The problem is that you have formatted the dates using
> the names of the months. So, in reverse alphabetical order you get
> February, August, April.
>
> To get the results that you want, you need to use a different alias for
> date like this:
>
> SELECT id, story_short, story_full, image1_name,
> DATE_FORMAT(`date`, '%b %D %Y') AS formatted_date
> FROM news_stories
> ORDER BY `date` DESC
>
> Notice that date is surrounded by backticks in each instance, because you
> have used a reserved word as the column name. Your formatted date will be
> in your recordset as formatted_date.
>
> --
> David Powers, Adobe Community Expert
> Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
> Author, "PHP Solutions" (friends of ED)
> http://foundationphp.com/

[Newsgroup_User]

ina wrote:
> Thanks David,
> That sorts the recordset out perfectly.
> I am exporting it to xml and when I create a spry dataset:
>
> <!--
> var ds1 = new Spry.Data.XMLDataSet("export2.php",
> "export/row",{sortOnLoad:"formatted_date",sortOrderOnLoad:"descending"});
> //-->
> </script>
>
> the order becomes messed again!

You don't need to sort it for Spry, because the XML is already in the
correct order.

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[Newsgroup_User]

"David Powers" wrote !
>
> You don't need to sort it for Spry, because the XML is already in the
> correct order.

Thanks,
I am getting there slowly :)