How to store special charcters as UTF-8 format in database

Question

I am trying to store "£" in database. I also marked this field as utf8_general_ci in collection.But it save as �. I also set UTF-8 in meta tag HTML file. What is missing here

-----------------EDIT-----------

I checked that,there is one editor(Rich Text)on that page.If I use this editor to save data then it generate � but if I use textbox then it works fine. I don't know what's wrong in this and how to debug this issue

$query = "insert into field_master set fk_t_id = '".$_POST['t_id']."', fk_g_id = '".$_POST['g_id']."', fk_f_id = '".$_POST['f_id_'.$inc_i.'_'.$inc_j]."'".$str.", field_value = '".$field_value."', field_required = '".$required."', fk_section_id = '".$_POST['section_id_'.$inc_i]."', section_order = '".$_POST['section_order_'.$inc_i]."', field_order = '".$_POST['temp_order_'.$inc_i.'_'.$inc_j]."'";
$smt = $class->dbh->prepare($query);
$smt->execute();

INSERT INTO table (column) VALUES ('£'); try this manualy in phpmyadmin — devpro, Feb 02 '16 at 09:47
if query execute property it means, u need to fix your code.. show your code.. u must need to use ENT_QUOTE,'utf-8' or SET NAMES utf8 etc.. — devpro, Feb 02 '16 at 09:48
@devpro: I already set $stmt1 = $class->dbh->prepare("SET NAMES 'utf8'"); — kreya, Feb 02 '16 at 09:49
Possible duplicate of [How to display UTF-8 characters in phpMyAdmin?](http://stackoverflow.com/questions/4777900/how-to-display-utf-8-characters-in-phpmyadmin) — Pathik Vejani, Feb 02 '16 at 09:52
great, now plz show the code related to INSERT... and fields.. — devpro, Feb 02 '16 at 09:59

score 0 · Answer 1 · answered Feb 02 '16 at 12:44

The examples shown here assume use of the utf8 character set and utf8_general_ci collation.

Specify character settings per database. To create a database such that its tables will use a given default character set and collation for data storage, use a CREATE DATABASE statement like this:

CREATE DATABASE mydb
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;
Tables created in the database will use utf8 and utf8_general_ci by default for any character columns.

Applications that use the database should also configure their connection to the server each time they connect. This can be done by executing a SET NAMES 'utf8' statement after connecting. The statement can be used regardless of connection method: The mysql client, PHP scripts, and so forth.

In some cases, it may be possible to configure the connection to use the desired character set some other way. For example, for connections made using mysql, you can specify the --default-character-set=utf8 command-line option to achieve the same effect as SET NAMES 'utf8'.

If you change the default character set or collation for a database, stored routines that use the database defaults must be dropped and recreated so that they use the new defaults. (In a stored routine, variables with character data types use the database defaults if the character set or collation are not specified explicitly.

Specify character settings at server startup. To select a character set and collation at server startup, use the --character-set-server and --collation-server options. For example, to specify the options in an option file, include these lines:

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
These settings apply server-wide and apply as the defaults for databases created by any application, and for tables created in those databases.

I hope these information would be useful to you.

score 0 · Answer 2 · answered Feb 02 '16 at 21:59

The question mark in a black diamond when you have really messed up utf8 usage. The simple answer is to mess it up further by saying (in the html) <meta charset=ISO-8859-1> (alias latin1).

The 'right' answer is to use utf8 throughout:

Characters in the client are encoded utf8.
Connection is utf8 -- via executing SET NAMES utf8 or some language-specific syntax when connecting. (See below)
CHARACTER SET utf8 on the column/table.
HTML: <meta charset=UTF-8>

Different things happen depending on which of those you violate.

If those notes are not enough to solve your problem, then do SELECT col, HEX(col) FROM ... to see what is really in the table. For £, you should see hex C2A3. If you get C382C2A3, you have the "double encoding" problem. If you see (not hex) Â£, you have the "Mojibake" problem. If none-of-the-above, well, that is what makes character set problems a challenge.

COLLATION (such as utf8_general_ci) is for sorting; it is not relevant in this discussion, only the CHARACTER SET (utf8 or utf8mb4) is.

mysqli interface: mysqli_set_charset('utf8');

PDO interface: $db = new PDO('dblib:host=host;dbname=db;charset=UTF8', $user, $pwd);

How to store special charcters as UTF-8 format in database

2 Answers2