Display correct characters when Select on column containing HTML

Question

In my database I store some html code (from CKEditor), and I would like to be able to check these values in PhpMyAdmin.

There is one small problem : the HTML is written in french so there are a lot ar &eacute: or other kind of html characters that make the values hard to read.

I know there are settings to avoid CKEditor replacing the characters with &eacute:, but I do not want to change anything in the code or configuration tight now.

Is there any MySQL command that would allow to display the text correctly?

Note : doing a str_replace or kind is not wanted.

Edit : For exemple for an element stored as follow :

<div>
    &eacute:l&eacute:phant
</div>

I would like to read in my result column :

<div>
    éléphant
</div>

http://stackoverflow.com/questions/11591757/100-safe-way-of-storing-html-in-mysql — Reza-S4, Dec 15 '14 at 15:41
Can you write an input & output data (that you wish to show)? — Reza-S4, Dec 15 '14 at 23:10
When you echo it, you could wrap it in the `htmlentities()` function — Mattigins, Dec 16 '14 at 13:33
I am actually trying to display it in phpmyadmin, so I'd rather look for a MySQL function — realUser404, Dec 17 '14 at 20:08
Please check out this may help you http://stackoverflow.com/questions/4777900/how-to-display-utf-8-characters-in-phpmyadmin — Shah Rukh, Dec 18 '14 at 09:47
`UPDATE TABLE_NAME SET FIELD_NAME = REPLACE(FIELD_NAME, 'é', 'é')` it's will replace all `é` to `é` in your table — waki, Dec 18 '14 at 09:48

score 3 · Accepted Answer · answered Dec 18 '14 at 09:52

3

I'm afraid there is no way to do directly what you are asking for.

The best way I've found would be to create a function which decodes the text, and then use it in select statements:

mysql> SELECT HTML_UnEncode('Dr.H&uuml;bner');
+---------------------------------+
| HTML_UnEncode('Dr.H&uuml;bner') |
+---------------------------------+
| Dr.Hübner |
+---------------------------------+

Here is the link to that function:

http://forums.mysql.com/read.php?98,246527,246527

answered Dec 18 '14 at 09:52

javier_domenech

5,995
6
37
59

Too bad, but thank you for linking me to that function! – realUser404 Dec 19 '14 at 14:22
My pleasure. I think it's not your case, but remember that if you use the function in 'where', 'sort', 'group by'... indexes won't help to accelerate results. BTW in selected columns you shouldn't have problems with this :) – javier_domenech Dec 19 '14 at 14:51

score 0 · Answer 2 · answered Dec 18 '14 at 15:21

This is easily accomplished with phpMyAdmin's transformation feature.

My answer assumes you already have a properly configured phpMyAdmin Configuration Storage, if not you just have to create the tables by editing the file in examples/create_tables.sql to set a username, database name, and password, then import that to your MySQL server. Edit config.inc.php to add the directives you can find listed beginning at http://docs.phpmyadmin.net/en/latest/config.html#cfg_Servers_pmadb .

Before:

Before transformations

Anyway, edit your table by going to the Structure tab.

Structure tab

Click the pencil icon/Change link for the column(s) containing the HTML entities.

On the resulting page, look for the "Browser display transformation" dropdown. One of the built in transformations is exactly what you're after: "Text/Plain: Formatted"

Text/Plain: Formatted dropdown

Click Save, then you can Browse the table and should see the transformed output.

After:

After transformations

Actually I am not trying to change the display of 1 column only. I am doing select on many columns of different tables, which are encrypted to complicate the problem, so I am already doing a aes_decrypt(mycolumn, pass), and I am looking for another mySQL function to wrap that : display_html_correctly(aes_decrypt(my_column, pass)) — realUser404, Dec 19 '14 at 14:06
You can apply the transformation to many columns by following the same process for each (you can edit them all at the same time, it doesn't have to be one at a time). Sorry about the aes_decrypt() part but you didn't mention that as part of your question so I couldn't have known. Cheers. — Isaac Bennetch, Dec 21 '14 at 23:24

Display correct characters when Select on column containing HTML

2 Answers2