MySQL, HTML, and PHP character conflicts

Question

I'm trying to pull text from a database. In the database, it displays as "♦" with no problem.

However, when I pull the record from the database, it displays on the page as a question mark (not the black box with a white ? in the middle), a plain old "end of question" question mark.

For instance:

♦ We Need To Pay You ♦

Will display:

? We Need to Pay You ?

I have the database structure setup as utf8-bin because latin didn't save them right. I've done hours of looking around, reading articles, and cannot find an answer anywhere. There has to be a way to get it to pull from the database the right way. I can insert it but can't retrieve it which makes no sense.

All help is appreciated.

set the database charset to utf-8 and ensure your php version supports non-ascii. Also, make sure the page is UTF-8 with the header `Content-Type: text/html; charset=UTF-8` — Amelia, Apr 10 '13 at 18:56

score 0 · Answer 1 · answered Apr 10 '13 at 18:55

0

Try this in your php file:

echo utf8_encode(text) or utf8_decode(text)

And this in your html head:

<meta http-equiv="content-type" content="text/html; charset=utf-8">

answered Apr 10 '13 at 18:55

Hackerman

12,139
2
34
45

score 0 · Answer 2 · answered Apr 10 '13 at 19:01

0

set the charset with mysql_set_charset.

bool mysql_set_charset ( string $charset [, resource $link_identifier = NULL ] )

and be sure your page content charset is utf-8

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" >

answered Apr 10 '13 at 19:01

Vahid Mehrabi

598
10
19

score 0 · Answer 3 · edited May 23 '17 at 11:56

You can use the answer I gave here to check for the source of the problem: How to fix "Incorrect string value" errors?

In your case most likely the HTML is not UTF-8 encoded. Depending on the HTML version you are using, one of those will fit your needs: http://en.wikipedia.org/wiki/Character_encodings_in_HTML

One thing left: Check, that your IDE/Editor also is set to store the files in UTF-8

score 0 · Accepted Answer · answered Apr 10 '13 at 19:04

0

Save your php file as Encode UTF-8 without BOM
set before your query: mysql_set_charset('utf8',$connect) or array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8") if you are using PDO
set inside your <head> tag: <meta http-equiv="content-type" content="text/html; charset=utf-8">

answered Apr 10 '13 at 19:04

Nik Drosakis

2,258
21
30

1

Scratch that. I added utf8_encode and forgot to remove it. It is finally displaying like it should. You, my friend, are a lifesaver. – Erick Hertlein Apr 10 '13 at 19:09

score 0 · Answer 5 · answered Apr 10 '13 at 19:09

Well, you need to decide what encode you will use on your application, IMHO utf8 is the best encoding, so you need to make you IDE ou Text Editor works with UTF-8 only and tell browser that your page is UTF-8 using:

<meta http-equiv="content-type" content="text/html; charset=utf-8">

And using a modern connection way with PDO:

<?php
$pdo = new PDO(
    'mysql:host=127.0.0.1;dbname=test',
    "username",
    "password",
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

Creating a database using UTF-8:

CREATE DATABASE "test" CHARACTER SET utf8 COLLATE utf8_general_ci

Creating a table using UTF-8:

CREATE TABLE `test` ( 
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , 
`name` VARCHAR( 40 ) NOT NULL
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

Using utf8_encode and utf8_decode is pointless, it will make your code unreadable.

Sorry my english, i'm brazilian :D

MySQL, HTML, and PHP character conflicts

5 Answers5