RE: [PHP] order by what?
[snip] 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. [/snip] Definitely an SQL question. What character set are you using in your database? Is the accent the first character of the string or is this a multi-byte character? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] order by what?
Jay Blanchard wrote: [snip] 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. [/snip] Definitely an SQL question. What character set are you using in your database? Is the accent the first character of the string or is this a multi-byte character? utf8 general the accents are French - including first character and others in the words... e.g. Érable comes up before Autres; and Céréales appears before Café... I imagine it's a collation (character set) problem. I don't know what to use to eliminate this problem. Should the collation be latin1 swedish_ci ? I have never received a definitive response even on this list. :-( And this is an ever recurring problem as I work with several languages which all have accents... -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] order by what?
[snip] [snip] 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. [/snip] Definitely an SQL question. What character set are you using in your database? Is the accent the first character of the string or is this a multi-byte character? utf8 general the accents are French - including first character and others in the words... e.g. Érable comes up before Autres; and Céréales appears before Café... I imagine it's a collation (character set) problem. I don't know what to use to eliminate this problem. Should the collation be latin1 swedish_ci ? I have never received a definitive response even on this list. :-( And this is an ever recurring problem as I work with several languages which all have accents... [/snip] Have you considered asking on the list of the database of your choice? Like the MySQL list? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] order by what?
2009/6/11 PJ af.gour...@videotron.ca 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
Re: [PHP] order by what?
PJ wrote: Jay Blanchard wrote: [snip] 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. [/snip] Definitely an SQL question. What character set are you using in your database? Is the accent the first character of the string or is this a multi-byte character? utf8 general the accents are French - including first character and others in the words... e.g. Érable comes up before Autres; and Céréales appears before Café... I imagine it's a collation (character set) problem. I don't know what to use to eliminate this problem. Should the collation be latin1 swedish_ci ? I have never received a definitive response even on this list. :-( And this is an ever recurring problem as I work with several languages which all have accents... There are some options which all need some sort of adjustment. If , e.g. É is used in the db instead of Eacute; the output onscreen is a little black diamond with a quesion mark inside. The order is correct, but the diamond is not acceptable... obviously, one cannot expect people to change their browser or whatever adjustments just for my little diamonds. Again, it probably means changing the collation - but where, in my database? TIA -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] order by what?
Jay Blanchard wrote: [snip] [snip] 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. [/snip] Definitely an SQL question. What character set are you using in your database? Is the accent the first character of the string or is this a multi-byte character? utf8 general the accents are French - including first character and others in the words... e.g. Érable comes up before Autres; and Céréales appears before Café... I imagine it's a collation (character set) problem. I don't know what to use to eliminate this problem. Should the collation be latin1 swedish_ci ? I have never received a definitive response even on this list. :-( And this is an ever recurring problem as I work with several languages which all have accents... [/snip] Have you considered asking on the list of the database of your choice? Like the MySQL list? They're not as nice as the guys on this list... usually ;-) -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] order by what?
Robin Vickery wrote: 2009/6/11 PJ af.gour...@videotron.ca mailto:af.gour...@videotron.ca 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. -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] order by what?
On Thu, Jun 11, 2009 at 11:27 AM, PJaf.gour...@videotron.ca wrote: Robin Vickery wrote: 2009/6/11 PJ af.gour...@videotron.ca mailto:af.gour...@videotron.ca 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. -- Hervé Kempf: Pour sauver la plančte, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php Are you storing the actual characters in the database or are you storing the HTML entities? It WON'T sort correctly if you are storing the entities. Make sure you are storing the actual characters in your database, and then make sure that you are sending the correct content-type header when you serve the page. If you try to serve UTF-8 characters but your server is sending a Content-Type: ISO-8859-1; header, you will get placeholders in FF for characters that aren't recognized. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] order by what?
Robin Vickery wrote: 2009/6/11 PJ af.gour...@videotron.ca mailto:af.gour...@videotron.ca 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 Obviously there is another solution, albeit rather silly: re-enter all the fields in alphabetical order with corresponding id fields in numerical order... great, if you're not going to change the list and if it's quite short... not too clever, though is it? -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] order by what?
PJ wrote: PJ wrote: There are some options which all need some sort of adjustment. If , e.g. É is used in the db instead of Eacute; the output onscreen is a little black diamond with a quesion mark inside. The order is correct, but the diamond is not acceptable... obviously, one cannot expect people to change their browser or whatever adjustments just for my little diamonds. Set the correct character set for your output. It's in the HTTP header. /Per -- Per Jessen, Zürich (20.2°C) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] order by what?
On Thu, Jun 11, 2009 at 11:35 AM, PJ af.gour...@videotron.ca wrote: Robin Vickery wrote: 2009/6/11 PJ af.gour...@videotron.ca mailto:af.gour...@videotron.ca 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 Obviously there is another solution, albeit rather silly: re-enter all the fields in alphabetical order with corresponding id fields in numerical order... great, if you're not going to change the list and if it's quite short... not too clever, though is it? -- Hervé Kempf: Pour sauver la plančte, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php PFT just run a cron job to re--order the table every two minutes. Easily the best solution.
Re: [PHP] order by what?
2009/6/11 PJ af.gour...@videotron.ca Robin Vickery wrote: 2009/6/11 PJ af.gour...@videotron.ca mailto:af.gour...@videotron.ca 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
