CODEWITHSUNDEEP
phpdatabasecomparison
Lorenz Meyer
Lorenz Meyer

Reputation: 19915

String comparison equivalence between mysql database and php code

I wonder if it is possible to compare two strings using MySQL and PHP and obtain the same results. In MySQL I have:

a = b collate utf8_general_ci

For PHP I found a promising way to do it with the Transliterator class

transliterator_transliterate($a, 'NFD; [:Nonspacing Mark:] Remove; NFC; Lower();') = transliterator_transliterate($b, 'NFD; [:Nonspacing Mark:] Remove; NFC; Lower();')

Both will do the same most of the time, but it seems that there is no way compare two strings in PHP, both accent insensitive and case insensitive, that is compatible with MySQL.

Does anybody have a better solution to get the same result in PHP and MySQL, or is this something that should be avoided anyway, moving all comparison either to the database or into the application code?

The context is a replication between MySQL and a proprietary database. I have a unique key in the database on a text field in MySQL. On the other side I have thousands of rows in memory, and I would like to filter unique values in PHP without trying to insert them into the MySQL table first. My approach is to use these values as keys in an array, to keep them unique. The problem now boils down to normalizing these keys in a way to treat the same values as the same as the unique index in MySQL is.

Upvotes: 4

Views: 981

Answers (1)

Selay
Selay

Reputation: 6464

Even if you achieve it and confirm by some tests, it may not be reliable due to different internal implementation of php and mysql string comparison.

Unlike integers and mathematical calculations, string comparison is usually implemented in a way to satisfy particular needs and can be biased on different languages and platforms. So, there is no guarantee that they will work exactly the same at all times, given you use two different systems.

Secondly, php would be slower for such operations, as compared to mysql. You can use mysql features to do filtering unique values. For example, you can create a view or temporary table to execute the operation. For example, views and temporary tables are suitable and a proper way to do such operations.

You can read more here and here

Upvotes: 1

Related Questions