String comparison in PHP is the same as MySQL

Question

I store varchar in the utf8 MySQL table and using the utf8_general_ci command. I have a unique index on varchar. I would like to do string comparisons in PHP, which is equivalent to what MySQL will do with the index.

A concrete example is that I would like to find that “a” is considered equivalent to “À” in PHP before:

mysql> insert UniTest (str) values ('a'); Query OK, 1 row affected (0.00 sec) mysql> insert UniTest (str) values ('À'); ERROR 1062 (23000): Duplicate entry 'À' for key 1 

Answer 1

Comparison has nothing to do with storage. You need to set the encoding to determine the storage encoding. The sort order determines how the comparison and sorting should take place. The combination must be an encoding, but otherwise it has nothing to do with the encoding.

To answer your question, you can use iconv to transliterate the text, and then compare it. For example:

 function compare($s1, $s2) { return strcmp( iconv('UTF-8', 'ISO-8859-1//TRANSLIT', $s1), iconv('UTF-8', 'ISO-8859-1//TRANSLIT', $s2)); } 

This is basically what MySql will do for you, although it is probably faster, and may have a slightly different sort table than ISO-8859-1//TRANSLIT . Not quite sure about that.

It will probably be easier to use a database, though, as others have already pointed out.

Answer 2

It would be wise to just let MySQL do this work by sending a query to MySQL, for example:

 SELECT CASE WHEN '$a' = '$b' THEN 1 ELSE 0 END 

---

Invention of the invention:

You can iterate over the entire character set of interest cartesian once attached to yourself and build a standard php-associative array of equivalence sets.

  for each $ char1 in $ charset {  
         for each $ char2 in $ charset {  
             $ charmatch [$ char1] [$ char2] = mysqlTestMatch ($ char1, $ char2));  
         }  
     }  

Then you will need to test each string character by character to see if they are the same or not, b) they are equivalent.

Answer 3

Why don't you let MySQL decide if an entry already exists with the same key?

You can run a SELECT query to ask if there is already an entry with this attribute:

 SELECT 1 FROM UniTest WHERE str = "À" 

Or just try inserting a new record and use the mysql_error () and mysql_errno () functions to see if an error has occurred.

Answer 4

So, if I get it right, you want to do a similar comparison with PHP, how would you get a check for checking the general UTF-8 index in MySQL?

The easiest way would be to create a helper function that converts the string according to the utf8_general_ci rules used by MySSQL, which is mainly designed to convert certain letters to a base letter.

The rules for this MySQL sort are listed here:

 http://www.collation-charts.org/mysql60/mysql604.utf8_general_ci.european.html 

For example, if you scroll a little to “gold A” on the left, you will see all the characters that are converted to this.

For a helper function called, for example, utf8g_to_ascii() , you can write a function:

 function utf8_compare($s1, $s2) { $a = utf8g_to_ascii($s1); $b = utf8g_to_ascii($s2); return strcmp( $a, $b ); } 

I would simulate my code after:

 http://dev.splitbrain.org/view/darcs/dokuwiki/inc/utf8.php 

Answer 5

Use the built-in Collator or Transliterator.

 $s1 = 'a'; $s2 = 'À'; var_dump( is_same_string($s1, $s2), $s1 === transliterator_transliterate('Any-Latin; Latin-ASCII; Lower()', $s2) ); function is_same_string($str, $str2, $locale = 'en_US') { $coll = collator_create($locale); collator_set_strength($coll, Collator::PRIMARY); return 0 === collator_compare($coll, $str, $str2); }