| Re: Sort using utf characters in mysql or php ? best solutions... [message #172823 is a reply to message #172818] |
Fri, 04 March 2011 03:11   |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma:
|
Senior Member |
|
|
On 3/3/2011 7:58 PM, SM wrote:
> Hello,
> Using MySQL, i'm selecting a list of songs in spanish that i would
> like to sort. Here's a list of names that is returned by the query:
>
> ¡Decirevilla!
> Alhambra
> 123 pasitos
> África
> Arroz
> Decir
>
> The sorted list should look like this:
>
> 123 pasitos
> África
> Alhambra
> Arroz
> ¡Decirevilla!
> Decir
>
>
> After all of the research i've read, i've concluded that there is no
> reasonable way to achieve this using MySQL. I've tried collation,
> charset, etc... but there is no way the character ¡, ?, etc... can by
> sorted accordingly to my desired result. Even the Á is not sorted the
> way i want to...
>
> Question 1: Is this a reasonable conclusion?
>
You're asking this question in the wrong newsgroup. Try
comp.databases.mysql.
> I believe the only way to achieve this is by passing the results to an
> array in php and then sort the array using a custom function... all
> this using the function usort (need to sort by value and i don't care
> about maintaning the key association). Something similar to this:
>
>
> function normalize($a, $b) {
> if ($a == $b) {
> return 0;
> }
>
> return ($a< $b) ? -1 : 1;
> }
>
>
> $tracks = array();
>
> while ($row = $result->fetch_assoc()) {
> $tracks[] = $row;
> }
>
> usort($tracks, 'normalize');
>
>
> Question 2: Is this the best way to achieve a custom sorting?
>
Possibly - but first ensure it can't be done in MySQL (see my response
to Question 1).
>
> Here's where i'm hitting a wall:
>
> Question 3: I have no idea how to create the normalize function to
> sort the names accordingly to my needs. How do i ignore certain
> characters (¡, ?, ', !, ¿) and how do i replace other characters with
> the natural equivalent (Á -> A, É -> E, etc..)
> I believe that by ignoring certain characters and replacing others, i
> can achieve the sorting i'm loojing for...
>
Replacing characters is one way. Another way would be to create your
own collating sequence in an array of characters. There are many other
ways.
> Question 4: All this make sense? Am i on the right path?
>
>
> Thanks in advance for all your advice.
> Marco
>
I'd still try to do it in MySQL if it's possible.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]