The {collecte} criterion, is used to force the SQL query generated by the loop to which it is attached to use a specific charset for the "ORDER BY" clause (specified by the {par ...} criteria).
Example
Let’s suppose that we have a UTF-8 database with tables also in UTF8 (DEFAULT CHARSET = utf8).
Within this character set (CHARACTER_SET_NAME), it is possible to use any one of 21 different collations [1]:
| COLLATION_NAME | CHARACTER_SET_NAME |
|---|---|
| utf8_bin | utf8 |
| utf8_czech_ci | utf8 |
| utf8_danish_ci | utf8 |
| utf8_esperanto_ci | utf8 |
| utf8_estonian_ci | utf8 |
| utf8_general_ci | utf8 |
| utf8_hungarian_ci | utf8 |
| utf8_icelandic_ci | utf8 |
| utf8_latvian_ci | utf8 |
| utf8_lithuanian_ci | utf8 |
| utf8_persian_ci | utf8 |
| utf8_polish_ci | utf8 |
| utf8_roman_ci | utf8 |
| utf8_romanian_ci | utf8 |
| utf8_slovak_ci | utf8 |
| utf8_slovenian_ci | utf8 |
| utf8_spanish2_ci | utf8 |
| utf8_spanish_ci | utf8 |
| utf8_swedish_ci | utf8 |
| utf8_turkish_ci | utf8 |
| utf8_unicode_ci | utf8 |
Based on this, we can imagine that in the "spip_articles" table of this database, which has a default collation of utf8_general_ci,
we have some articles with the following titles:
NananinaNinaNiñaÑiñañinaÑinaNonoNunu
The loop written as:
<BOUCLE_a(ARTICLES) {par titre} {"<br />"}>
#TITRE
</BOUCLE_a>
will return these articles in the alphabetical order that matches the default collation for the table:
NananinaÑinaNinaNiñaÑiñañinaNonoNunu
The loop written as:
#SET{collation, utf8_spanish_ci}
<BOUCLE_a(ARTICLES) {par titre} {collecte #GET{collation}} {"<br />"}>
#TITRE
</BOUCLE_a>
will instead return these articles in the alphabetical order matching the requested collation sequence:
NananinaNinaNiñaNonoNunuÑinañinaÑiña
This shows that the ñ (n tilde) characters are sorted after the n (naked n) characters.
This actually corresponds to the Castilian sorting order:
- "The ñ is the fifteenth letter in the Castilian Latin alphabet,
between the N and the O in alphabetical order." http://en.wikipedia.org/wiki/%C3%91
The MySQL request generated is then:
SELECT articles.titre, articles.lang
FROM spip_articles AS `articles`
WHERE articles.statut = 'publie'
AND articles.date < '9999-12-31'
ORDER BY articles.titre COLLATE utf8_spanish2_ci
Limitation
It is not possible to make this {collecte} criterion work by "directly" assigning its value in the list of arguments for the loop: the {collecte utf8_spanish_ci}, {collecte 'utf8_spanish_ci'}, {collecte{utf8_spanish_ci}}... do not work and will display a PHP error.
It is not possible to only use the #GET{} after
previously having declared it in #SET{}