Different result for MySQL and PHP string comparison

[kevingarnett2000]

Hi,

 

I have issued an SQL statement "SELECT email FROM mailing_list ORDER BY email ASC";

 

By right the order of the email should be sorted from smallest to largest. However I got different results when

I used record that was ranked higher to compare against record that was ranked lower. I got opposite results.

 

Records from DB

============

bi***[email protected]

b_****[email protected]

 

But when I issued a command like:

 

if(strcmp('b_****[email protected]', 'bi***[email protected]' ) < 0) {

    print "Print argument 1 is less than argument two!"

}

 

It surprisingly returns true! Why is that so?

 

Thank you

[recklessgeneral]

Hi,

 

This is to do with the collation on your email field. By default, at least when I create a table, the ordering (collation) is set to latin1_general_ci, which produces the results you notice in your post, ie it seems to demote the punctuation characters below alphabetic characters. The strcmp function is strictly ascii on the other hand, so it produces the 'expected' (from a traditional programming background) results where the underscore character appears earlier that the lower case letters.

 

Now, to get the two in sync you can change the collation type on the email field (indeed all fields where you'll want to do comparisons of this nature). To do this with phpMyAdmin, log in and select your database. Click on your mailing_list table and select the Structure tab. Edit the 'email' field and find the drop down box for 'Collation'. I tested with the value 'ascii_bin' and that worked a charm.

 

There are a whole bunch of different collation types, and I don't know if there is a more appropriate one, but this one will work with the sample you provided.

 

Cheers,

Darren.