[SOLVED] "ORDER BY" hebrew text

[bobbinsbro]

hi guyz.

i am having a rather curious problem:

i have a table with mixed encoding - some columns contain data in english, and some contain data in hebrew. when i try to "ORDER BY" a hebrew column, the order is wrong. when i try to sort by a column in english, the order is correct. i tried creating a test table a preformed the following tests:

* 1 column in hebrew, single letter values - order correct.

* 1 column in hebrew, multi-char values - order correct.

* 1 column in hebrew, multi-char values, white spaces - order correct.

* 2 columns in hebrew - order correct.

* 2 columns 1 hebrew 1 english, order by english - order correct.

* 2 columns 1 hebrew 1 english, order by hebrew - order messed up.

???

my DB defaults to using utf-8 encoding for all tables, unless directed otherwise.

 

help  :-\

 

using mysql 5.0.67 community, php 5.2.6, apache 2.2.9, windows vista home premium 32-bit.

[fenway]

Could you give some real-world examples? And you table structure, too?

[bobbinsbro]

here's an export of my test table using phpMyAdmin (the creation of the table in the db is not done with phpMyAdmin, but by using the phpExcel library to read an excel worksheet, and convert the contents to a mysql table):

 

-- Table structure for table `sheet1`

--

 

CREATE TABLE IF NOT EXISTS `sheet1` (

  `id` bigint(20) unsigned NOT NULL auto_increment,

  `col` varchar(10839) NOT NULL,

  `bla` varchar(10839) NOT NULL,

  PRIMARY KEY  (`id`),

  UNIQUE KEY `id` (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

 

--

-- Dumping data for table `sheet1`

--

 

INSERT INTO `sheet1` (`id`, `col`, `bla`) VALUES

(1, 'א', 'h'),

(2, 'ב', 'g'),

(3, 'ג', 'f'),

(4, 'ד', 'e'),

(5, 'ה', 'd'),

(6, 'ו', 'c'),

(7, 'ז', 'b'),

(8, 'ח', 'a');

 

the columns are set up in opposite order. so when sorting by 'col', the order should remain as it is here. when sorting by 'bla', the order should be exactly reversed.

 

the order i get on my machine when ordering by 'col' (showing row number):

1, 7, 8, 2, 3, 4, 5, 6

 

as mentioned, when ordering by 'bla', the order is 8, 7, 6, 5, 4, 3, 2, 1 as it should be.

[Mchl]

Works fine here

MySQL 5.1.30 on Windows Vista

 

BTW: varchar(10839) is not the best idea imho. Use TEXT perhaps?

[bobbinsbro]

i am using 5.0.67. i will download 5.1.3 and check if that makes a difference.

 

re. the varcher: that is a result of the conversion code from excel to mysql. the reason the varchar is so large is that i only have 2 columns in the excel file. in real use, that should never happen. however, i may add a length check just in case. thanx for the heads up.

[Mchl]

Remember to configure your MySQL server to use utf-8 when you install it.

[bobbinsbro]

i installed 5.1.3 (using utf-8 charset by default). didn't work.

when i do "select * from sheet1 (this is the name of the table) order by col;" in the mysql command line, the order of the output is 1, 7, 8, 2, 3, 4, 5, 6 (same problem as before).

just doing "select * from sheet1;" in the command line gives the proper order (1, 2, 3, 4, 5, 6, 7, 8 ) and doing "select * from sheet1 order by bla;" gives the expected results (8, 7, 6, 5, 4, 3, 2, 1).

 

the table is set to use utf-8 (i posted the creation code perviously).

 

what gives? ???

[Mchl]

I have no idea.

I created the table and data using SQL you posted. I've just pasted it into MySQL Query Browser. It just gives me correct sort order. I tested it in MySQL Query Browser and in phpMyAdmin...

[bobbinsbro]

thanx for your help. i'll have to assume it's something wrong on my machine. i'll create a test run on the server on which things will eventually run.

 

g'day