0

i have mysql databse and it's charset id latin1_swedish_ci and as example one column apear like that ( ?Œ???­?? ?‡???£?¤?¦???‡?? )

this is example of table

-- phpMyAdmin SQL Dump
-- version 4.0.10deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 27, 2017 at 02:49 PM
-- Server version: 5.5.44-0ubuntu0.14.04.1
-- PHP Version: 5.5.9-1ubuntu4.21

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `a`
--

-- --------------------------------------------------------

--
-- Table structure for table `block`
--

CREATE TABLE IF NOT EXISTS `block` (
  `catid` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `rtl` int(12) NOT NULL DEFAULT '1',
  `ratteb` int(11) NOT NULL DEFAULT '0',
  `comment` text NOT NULL,
  `showcat` int(11) NOT NULL DEFAULT '0',
  `nostyle` varchar(255) NOT NULL,
  `tab` varchar(255) NOT NULL,
  `additional_locs` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`catid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=285 ;

--
-- Dumping data for table `block`
--

INSERT INTO `block` (`catid`, `name`, `rtl`, `ratteb`, `comment`, `showcat`, `nostyle`, `tab`, `additional_locs`) VALUES
(198, '???â€،?†?آ£?‰ ?آ£?â€‍?????‰', 1, 18, '', 2, '', '', NULL),
(2, '?â€،???ث†???‹', 2, 1, '', 2, '', '', NULL),
(3, '?????â€،?? ?â€،???’?آ¦?â€،?â€ک ?â€،???آ£???آ¦?â€،?إ’???آ­?آ¤', 1, 8, '', 2, '', '', NULL);

i know how to change the charset but when i change it to uft8 the current data did not changed. i want to know is it possible to repair the old data to correct one?

thanks

UPDATE:

the charset in php is windows-1256

Ahmed
  • 112
  • 2
  • 12
  • It depends on how the original text was converted from utf8 to latin1. If byte wise the text was preserved, then you can restore the original text. However, it was not, then it is not possible to restore the original text. – Shadow Mar 27 '17 at 11:57

2 Answers2

1

Since you have question marks coming out, the data is lost. It was lost during INSERT because latin1 had no encoding for some of your characters.

Start over.

  • Have your editor/etc. set to UTF-8.
  • HTML forms should start like <form accept-charset="UTF-8">.
  • Have your bytes encoded as UTF-8.
  • Establish UTF-8 as the encoding being used in the client.
  • Have the column/table declared CHARACTER SET utf8mb4 (Check with SHOW CREATE TABLE.)
  • <meta charset=UTF-8> at the beginning of HTML output.

More discussion: Trouble with utf8 characters; what I see is not what I stored

Possibly you needed CHARACTER SET cp1256, which I think is equivalent to Windows-1256.

Since INSERT INTO block... looks like the output from mysqldump, it may have been done incorrectly. Again, start over, but all the way back before the dump.

Do not use latin1 for Arabic. It would be better to use CHARACTER SET utf8mb4, but utf8 or cp1256 would work if you don't go beyond English and Arabic.

Community
  • 1
  • 1
Rick James
  • 135,179
  • 13
  • 127
  • 222
0

Your Database encoding should be the same as your HTML/PHP webpages (let's say UTF-8). If that is the case your webpages should display the characters correctly, even if it's stored weirdly on you database.

Victor GUTT
  • 63
  • 1
  • 2
  • 7
  • yes it's appear good in interface or in HTML/PHP but i want to change it to UTF8 and convert old data – Ahmed Mar 27 '17 at 12:17
  • I'm not sure I understood correctly. You want your Database to be encoded in UTF-8 and the data already stored in it to change automatically after changed was made ? If so, I don't think it's possible for the data stored to just change to the characters you'd want. Though if you set it to "utf8_general_ci" for example and your HTML/PHP also to charsert="UTF-8" you shouldn't have any problems what so ever to display the webpage. – Victor GUTT Mar 27 '17 at 12:49