Re: [PHP] order by what?

2009/6/11 PJ <[email protected]>

> Robin Vickery wrote:
> >
> >
> > 2009/6/11 PJ <[email protected] <mailto:[email protected]>>
> >
> >     How can order by be forced to order alphabetically and ignore accents
> >     without stripping the accents for printout? This is a problem for
> both
> >     caps & normal letters.
> >
> >
> > Depends on the database.
> >
> > If you're using mysql, the order is governed by the collation used. To
> > get the order you want, you need a case-insensitive and
> > accent-insensitive collation. Exactly which one you use will depend on
> > the character set that you're using, but if you're character set is
> > utf8, then the utf8_general_ci collation should work:
> >
> > SELECT fieldname FROM tablename ORDER BY fieldname COLLATE
> > utf8_general_ci;
> >
> > -robin
> Nice thought, Robin. My collation is already uft8_general_ci.
> Adding that condition to the query changes nothing; and specifying
> another collation return a blank or null and the list is not echoed.
> Even changing the collation in the db does not change anything. Same
> wrong results.
> Thanks anyway.


Hiyah,

Well the mysql docs confirm that utf8_general_ci is accent-insensitive and
case-insensitive (
http://dev.mysql.com/doc/refman/5.4/en/charset-collation-implementations.html).
Which implies that maybe you don't actually have utf8 text in that field.

If you do a SHOW VARIABLES LIKE 'character_set%'; what do you get for
character_set_client, character_set_results and character_set_connection?

-robin