Forcing PHP to Sort Like MySQL

miqrogroove
2020-10-21T15:08:08-04:00

If you ever have to do a case-insensitive array sort in PHP, you will eventually notice that the results don’t match the MySQL latin1_swedish_ci collation. They just aren’t the same. The difference comes from a set of six characters that fall between the upper-case and lower-case alpha characters of ISO-8859-1.

Specifically, [ \ ] ^ _ ` are the troublemakers. A simple example would involve sorting the phrases “Hello” and “[Hello]”. In MySQL, “Hello” comes first. In PHP, “Hello” comes last.

If this is driving you crazy, all you will need to do is trick PHP into using an upper-case sort instead of a lower-case sort.

$strings = ['hello', '[Hello]', 'Hello'];
usort( $strings, 'mysql_simulator' );
var_dump( $strings );

function mysql_simulator( $a, $b ) {
return strcmp( strtoupper( $a ), strtoupper( $b ) );
}

I hope this will save you some of the research in solving that pesky little difference.

21 Oct 2020

Category:
Systems Engineering

Tags:

Discuss:
Comments Go Here

Write a Comment