0

I have a mysql table with a text field containing German text with German quotes. E.g. „Freda“.

If I read the database directly via php the result is correct. But when I generate a shortcode in wordpress that contains the same php query of the database, the german quotes disappear. All other characters are displayed.

The db-field and the DB is set to 'utf8mb4_unicode_ci', the html site is set to 'charset="UTF-8"' . When i read out the data with php and show them on a website all german special chars like Ä,ä,Ü,ü,ß are shown correct. But the german quotation marks disapear. For example when this is in the DBfield: " Uhr „jeweils zur vollen Stunde“ in der" the Browser shows: " Uhr jeweils zur vollen Stunde in der". When i copy the text from the browser and paste it in this textarea you see: " Uhr jeweils zur vollen Stunde in der" I tried also to set "mysqli_set_charset('utf8mb4', $link);" before i set the "query mysqli_query( $db, $sql )" but this does not help. If i set "SET NAMES 'utf8mb4'" i got this "Uhr âjeweils zur vollen Stundeâ in "

For this reason i want to change the german quotation marks in the DBfield to '„' '“'.

With the following command I can replace the quotes with the entity '„' or '“' using phpmyadmin:

UPDATE `TableName` SET `fieldname` = REPLACE(`fieldname`, '„', '„') WHERE `fieldname` LIKE '%„%' COLLATE utf8mb4_bin

But when I want to do this directly using php, I can't manage it. I can change other characters or words but nothing happens with the quotes. How must the command be formulated in the php file so that I can change it?

I was trying the following codes // as given me from phpmyadmin:

$sql = "UPDATE `TableName` SET `fieldname` = REPLACE(`fieldname`, \'„\', \'„\') WHERE `fieldname` LIKE \'%„%\' COLLATE utf8mb4_bin";
$sqlRes = mysqli_query( $db, $sql );

I changed it to:

$sql = "UPDATE `TableName` SET `fieldname` = REPLACE(`fieldname`, '„', '„') WHERE `fieldname` LIKE '%„%' COLLATE utf8mb4_bin";
$sqlRes = mysqli_query( $db, $sql );

I changed it to:

$sql = "UPDATE TableName SET fieldname = REPLACE(fieldname, '„', '„') WHERE fieldname LIKE '%„%' COLLATE utf8mb4_bin";
$sqlRes = mysqli_query( $db, $sql );

I changed it to:

$sql = "UPDATE TableName SET fieldname = REPLACE(fieldname, \'„\', \'„\') WHERE fieldname LIKE \'%„%\' COLLATE utf8mb4_bin";
$sqlRes = mysqli_query( $db, $sql );

But nothing is working.

Hoschigil
  • 1
  • 2
  • 5
    Is there a reason you want to convert your text to HTML in the database? As long as you use [UTF-8 all the way through](https://stackoverflow.com/a/279279/231316) there shouldn't be a need for this – Chris Haas Sep 09 '22 at 15:37
  • Perhaps you didn't set the right connection charset? – Dharman Sep 09 '22 at 16:22
  • utf8mb4_unicode_ci **is not** a charset – Your Common Sense Sep 11 '22 at 07:54
  • This is the first time we hear that the quotes disappear. A bit late. Perhaps saying "nothing is working" isn't the best statement of a problem? – KIKO Software Sep 11 '22 at 07:55
  • Obviously, with all charset options properly set, [all quotes are shown all right](https://phpize.online/sql/mysql57/undefined/php/php81/d653c0067b2fd458d4f26052d6329347/) – Your Common Sense Sep 11 '22 at 08:00
  • There's no real need for the `WHERE \`fieldname\` LIKE '%„%'` condition in your query, because you will only be replacing that single character. If it isn't there, it won't get replaced. Whether a query with, or without, this condition will execute faster remains to be seen. – KIKO Software Sep 11 '22 at 08:00
  • `` **is not** how encoding must be set for the HTTP client – Your Common Sense Sep 11 '22 at 08:07
  • @ Kiko Software: It want to say "but no attempt i have tried has worked" – Hoschigil Sep 11 '22 at 09:10
  • i think this is a problem with wordpress – Hoschigil Sep 11 '22 at 09:47
  • In PHP, see `htmlentities()`. – Rick James Sep 12 '22 at 04:48
  • _"I have a mysql table with a text field containing German text with German quotes."_ - are you absolute _sure_, that's what you actually have there? WordPress has a mechanism to replace standard `"` quote pairs, with typographic ones. My guess is, that you actually _have_ `"` quotes in your database - and your shortcode output simply does not get run through the filter function that does this replacement, whereas when it gets output "normally" via your template, it does. https://www.webnots.com/4-ways-to-disable-smart-or-curly-quotes-in-wordpress/ – CBroe Sep 12 '22 at 08:26
  • I found the solution: If I use mysqli for getting the content of the field, the german quotation marks are lost. Buf if I use WPDB for this, the quotation marks are shown. – Hoschigil Sep 16 '22 at 06:00

0 Answers0