latin_swedish_ci retrieve

[Isset1988]

Hello all

 

 

I have a vb mysql database and its collation is "latin_swedish_ci". (i have huge problem on searching greek chars)

 

I'm trying to create a php script to retrieve some data's from a specific table.

 

For example:

header('Content-type: text/html; charset=UTF-8');
$connection = mysql_connect('localhost', 'root', ''); mysql_select_db('database', $connection); 
mysql_query("SET NAMES 'utf8'", $connection);
$query = 'SELECT title FROM thread WHERE threadid = 15';
$result = mysql_query($query, $connection); 
$result = mysql_fetch_assoc($result); 
print_r($result);

 

But i get code like this: Î£ÎºÎ¹Î¬Î¸Î¿Ï‚ - ΚουκουναÏιέ

In my phpmyadmin i get the same code. The english chars shows perfect.

 

From now, i want to retrieve some data's from the above tables on utf8.

Any idea?

Thank you!

 

(If i can retrieve the data's correctly, i will create a new table - mirror of the old table as utf8 and i hope my problem fixed that way)

Edited February 21, 2014 by Isset1988

[jazzman1]

SET NAMES 'UTF8' indicates only what character set the client will use by sending sql statements to the DB server. If the collation of db/table/column isn't set to 'UTF8' in your database you cannot retrieve the correct data. To solve the problem (without creating any duplicate/mirror table) just change the collation only to this problematic table to 'utf8_general_ci'. Before doing anything with your database structure make sure you have a dump (backup). The command is:

ALTER TABLE `db_name`.`thread` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; 

or for specific column in this "thread":

ALTER TABLE `db_name`.`thread` 
CHANGE COLUMN `title` `title` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL ;

[Isset1988]

Thank you for your help.

 

I tried that localhost. I use the first command on my phpmyadmin and done.

When i tried retrieve data, i get the same ungly code.

Can i post here the thread table to take a look?

 

Thank you!

[Isset1988]

I attached here the thread table. Ca you take a look?

 

Thank you!

[jazzman1]

You can't repair the broken content at least I don't know how. You need to re-create a new table under utf8 charset, or just alter the structure as shown in my examples above, truncate it and do a new insertion with normal greek words. 

[Isset1988]

I underastand, the only thing i want is to retrieve at least one row correctly.

 

Any way...

Thank u my friend!

Edited February 21, 2014 by Isset1988

[jazzman1]

Then do insert all greek alphabet under latin_swedish_ci's charset. See, how they are presenting in the colums using this charset and make some comparision using your application language (php).

 

if (this broken word = some symbol ) {

this broken symbol = some greek word, and so on, so, on

}

 

Find all substitutions and do update to this symbols.

Edited February 21, 2014 by jazzman1

[Isset1988]

i will try it. Thanks again!

[jazzman1]

Take a look at my testing script which I wrote for you, it might be useful somehow. For the test I'm using the Greek word - Καληπέσα.

 

1) Create a testing table thread into database named - test!

CREATE  TABLE `test`.`thread` (
  `thread_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `thread_name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`thread_id`) )
DEFAULT CHARACTER SET = latin1;

2) Insert greek words/phrase - (Καληπέσα); (Καληπέσα , good evening 2); (Καληπέσα , good evening 3);

INSERT INTO `test`.`thread` (`thread_name`) VALUES ('Καληπέσα'),('Καληπέσα , good evening 2'),('Καληπέσα , good evening 3')"

// result of this gibberish data after the rows selecting
Array
(
    [0] => Καληπέσα
    [1] => Καληπέσα , good evening 2
    [2] => Καληπέσα , good evening 3
)

3) Change column thread_name to utf8 charset

ALTER TABLE `test`.`thread` CHANGE COLUMN `thread_name` `thread_name` VARCHAR(45) 
CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL;

3) Replace this gibberish content using php language and its preg_replace function

// data before and after replacement: 

Array
(
    [0] => Καληπέσα
    [1] => Καληπέσα , good evening 2
    [2] => Καληπέσα , good evening 3
)

Array
(
    [0] => Καληέσπα
    [1] => Καληέσπα , good evening 2
    [2] => Καληέσπα , good evening 3
)

4) Do an update to these columns.

UPDATE `test`.`thread` SET `thread_name`= CASE `thread_name` 
WHEN 'Καληπέσα' THEN 'Καληέσπα'
WHEN 'Καληπέσα , good evening 2' THEN 'Καληέσπα , good evening 2'
WHEN 'Καληπέσα , good evening 3' THEN 'Καληέσπα , good evening 3'
END 
WHERE thread_id IN (1,2,3)

5) Results from database after the final update:

Array
(
    [0] => Καληέσπα
    [1] => Καληέσπα , good evening 2
    [2] => Καληέσπα , good evening 3
)

6) Application script:

<?php

$username = 'lxc';

$password = 'password';

$dbh = new PDO('mysql:dbname=test;host=::1;charset=utf8', $username, $password); // charset=latin1 when you're inserting values

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

//$stmt = $dbh->prepare("INSERT INTO `test`.`thread` (`thread_name`) VALUES ('Καληπέσα'),('Καληπέσα , good evening 2'),('Καληπέσα , good evening 3')");

//$stmt->execute();

$stmt = $dbh->prepare("SELECT thread.thread_name FROM test.thread");

$stmt->execute();

$data = array();

$arr_r = array();

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

   $data[] = $row['thread_name']; 
}

//create arrays of substitutions
$find = array('~Κ~', '~α~', '~λ~', '~η~', '~έ~', '~σ~', '~Ï€~', '~έ~', '~Ï~');

$replace = array('Κ', 'α', 'λ', 'η', 'σ', 'π', 'έ', 'ρ');

$count = count($data);

for ($i = 0; $i < $count; $i++) {
    
   // replace gibberish characters with greek words
    $arr_r[] = preg_replace($find, $replace, $data[$i]);
}

// display the old data 
//echo '<pre>' . print_r($data, true) . '</pre>';

// display the replaced data
//echo '<pre>'.print_r($arr_r,true).'</pre>';

// re-index array data
$ids = array_combine(range(1, $count), array_values($data));

// update all columns containd gibberish chars
$sql = "UPDATE `test`.`thread` SET `thread_name`="
        . "CASE `thread_name`";
$j = 0;

foreach ($arr_r as $val_r) {
    
$sql .= sprintf(" WHEN '%s' THEN '%s'", $data[$j], $val_r);

 $j++;
 
}
$sql .= " END WHERE thread_id IN (" . implode(", ", array_map('intval', array_keys($ids))) . ")";

// display an UPDATE statement
//echo '<pre>'.$sql.'</pre>';

// prepare a query
$stmt = $dbh->prepare($sql);

// execute it to DB server
$stmt->execute();

$stmt = null;

Edited February 23, 2014 by jazzman1

[Isset1988]

Perfect I will use the above and hope works

[jazzman1]

Yea, it worked for me. Just create two arrays ( find and replace) as the example shown above for Greek alphabet ( capital and lowercase) and make sure that every gibberish chars matched to the correct greek one, or just create one array something below that returns keys and their all associative values of an array.

$array_chr = array(
  '~Κ~'=>'Κ',
  '~α~'=>'α',
  '~λ~'=> 'λ'
  //ect....
);