Jump to content

latin_swedish_ci retrieve


Isset1988

Recommended Posts

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 by Isset1988
Link to comment
Share on other sites

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 ;

Link to comment
Share on other sites

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 by jazzman1
Link to comment
Share on other sites

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 by jazzman1
Link to comment
Share on other sites

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....
); 
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.