[SOLVED] Sort help needed

[drisate]

Hey guys i need to sort by title asc articles in a french website. The problem i am facing is that the titles are HTML encoded before they're added to the database. Some foreign special characters are causing the SQL order by argument not usable.

 

Ex: É is encoded to É

 

The order by only sees the first character & and places that article on top instead on sorting them with the E ...

 

So this is what i tryed as an alternat methode to the sql sorting but it's obviously not working ...

 

$select = mysql_query("SELECT * FROM $table where semaine!='0' and publier = '1'  limit $offset,$nombre_par_page") or die(mysql_error());
$page = mysql_fetch_array($select);

foreach ($page as $value){
    $value=html_entity_decode($value);
}

$page = sort($value);

while ($page) {
// The SQL result loop
echo "$page[titre]<br>";
}

[drisate]

I tryed this

 

SELECT * FROM page where semaine!='0' and publier = '1' order by REPLACE('É','E', REPLACE('é','e', REPLACE('È','E', REPLACE('è','e', REPLACE('À','A', REPLACE('à','a', REPLACE('Ê','E', REPLACE('ê','e', REPLACE('Î','I', REPLACE('î','i', REPLACE('Ç','C', REPLACE('ç','c', REPLACE('Ï','I', REPLACE('ï','i', REPLACE('Û','U', REPLACE('û','u', REPLACE('Ù','u', REPLACE('ù','u', titre)))))))))))))))))) asc limit 0,10

 

But that did not work as well ...

[simshaun]

You need to modify the system so that the text is not htmlencoded before its added to the database.  Always do this in your presentation logic.

[drisate]

Found a work arround

 

This is what i did:

SELECT * , replace( 
replace( 
replace( 
replace( 
replace( 
replace( 
replace( 
replace( 
replace( 
replace( 
replace( 
replace( 
replace( 
replace( 
replace( 
replace( 
replace( 
replace( titre, 'É', 'E' ) , 
'é', 'e' ) , 
'È', 'E' ) , 
'è', 'e' ) , 
'À', 'A' ) , 
'à', 'a' ) , 
'Ê', 'E' ) , 
'ê', 'e' ) , 
'Î', 'I' ) , 
'î', 'i' ) , 
'Ç', 'C' ) , 
'ç', 'c' ) , 
'Ï', 'I' ) , 
'ï', 'i' ) , 
'Û', 'U' ) , 
'û', 'u' ) , 
'Ù', 'U' ) , 
'ù', 'u' ) AS tt
FROM page
ORDER BY tt ASC 
LIMIT 0 , 10

 

Hope that helps somebody else in the future ;-)

Cheers

[simshaun]

What you did works, but do you not think it would be easier to change the system so the field is not htmlencoded until you go to display it?

[drisate]

Thats what i will do in the future but the projet is very very big and changing that would require more time then it did to comme up with that solution.