SPIP

[ar] [ast] [bg] [br] [ca] [co] [cpf] [cs] [da] [de] [en] [eo] [es] [eu] [fa] [fon] [fr] [gl] [id] [it] [ja] [lb] [nl] [oc] [pl] [pt] [ro] [ru] [sk] [sv] [tr] [vi] [zh] Espace de traduction

Download

{collecte}

August 2010

All the versions of this article:

The {collecte} criteria is used to specify the "collation" to use for the request generated by the loop (by making a call with the "COLLATE" clause in MySQL [1]).


The {collecte} criterion, (since SPIP 2.0), 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 [2]:

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:

Nana
nina
Nina
Niña
Ñiña
ñina
Ñina
Nono
Nunu

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:

Nana
nina
Ñina
Nina
Niña
Ñiña
ñina
Nono
Nunu

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:

Nana
nina
Nina
Niña
Nono
Nunu
Ñ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 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{}

Footnotes

[1Refer to the official documentation about the "COLLATE" clause in MySQL: http://dev.mysql.com/doc/refman/5.0... and an example of the effects of using the collation

[2In the latin1 character set, there are 8 such collations available:

COLLATION_NAME CHARACTER_SET_NAME
latin1_bin latin1
latin1_danish_ci latin1
latin1_general_ci latin1
latin1_general_cs latin1
latin1_german1_ci latin1
latin1_german2_ci latin1
latin1_spanish_ci latin1
latin1_swedish_ci latin1

Show the template of this page Site powered by SPIP | Translation area | Private area