Mysql INCOMPLETE SORTING

[alexcpp]

Hello, this is a very strange problem. I have a table with rows with cyrillic characters. The field collation of "products_name" field is "latin1_swedish_ci". When I run "select * from test order by products_name" it outputs the product_names. the list is not completely sorted. the top items do approach the top and the bottom items do approach the bottom, but the order is shuffled a little. The number of items is in the thousands. PLEASE HELP!!!


This is my php script:

mysql_connect("localhost",$username,$password);
mysql_select_db($database) or die( "Unable to select database");

$query = "select * from test order by products_name";
$result = mysql_query($query);

while($row = mysql_fetch_array($result))
{
echo $row['products_name']."<br>";
}

 

[TonyGroves]

You can specify the desired collation order in your query:[tt]
ORDER BY products_name COLLATE collationname[/tt]
However, it would be better to have the field's character set (and default collation) right in the first place. latin1_swedish_ci would seem to be wrong for a Cyrillic field. You could fix it with something like:[tt]
ALTER TABLE test
MODIFY products_name VARCHAR(100) CHARSET cp1251
[/tt]

 

[alexcpp]

but all my existing cyrillic data turns into question marks.

i tried to covert with php but nothing works.

how can i convert from latin1_swedish to cp1251 without loosing my data?