Jump to content


Photo

latin_swedish_ci retrieve

latin1_swedish_ci retrieve

  • Please log in to reply
10 replies to this topic

#1 Isset1988

Isset1988

    Advanced Member

  • Members
  • PipPipPip
  • 49 posts

Posted 21 February 2014 - 05:17 AM

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, 21 February 2014 - 05:22 AM.


#2 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,631 posts
  • LocationMississauga, Canada

Posted 21 February 2014 - 07:28 AM

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 ;



#3 Isset1988

Isset1988

    Advanced Member

  • Members
  • PipPipPip
  • 49 posts

Posted 21 February 2014 - 08:10 AM

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! :)



#4 Isset1988

Isset1988

    Advanced Member

  • Members
  • PipPipPip
  • 49 posts

Posted 21 February 2014 - 09:49 AM

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

 

Thank you!



#5 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,631 posts
  • LocationMississauga, Canada

Posted 21 February 2014 - 04:26 PM

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. 



#6 Isset1988

Isset1988

    Advanced Member

  • Members
  • PipPipPip
  • 49 posts

Posted 21 February 2014 - 04:28 PM

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

 

Any way...

Thank u my friend! :)


Edited by Isset1988, 21 February 2014 - 04:28 PM.


#7 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,631 posts
  • LocationMississauga, Canada

Posted 21 February 2014 - 04:38 PM

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, 21 February 2014 - 04:40 PM.


#8 Isset1988

Isset1988

    Advanced Member

  • Members
  • PipPipPip
  • 49 posts

Posted 22 February 2014 - 03:35 AM

i will try it. Thanks again!



#9 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,631 posts
  • LocationMississauga, Canada

Posted 22 February 2014 - 09:07 PM

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, 22 February 2014 - 09:23 PM.


#10 Isset1988

Isset1988

    Advanced Member

  • Members
  • PipPipPip
  • 49 posts

Posted 23 February 2014 - 05:07 AM

Perfect :) I will use the above and hope works :)



#11 jazzman1

jazzman1

    Advanced Member

  • Gurus
  • 2,631 posts
  • LocationMississauga, Canada

Posted 23 February 2014 - 08:32 AM

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....
); 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com