Getting one row from database and replacing some things in it

Question

I need to get a row from my database and replace some things in it, but it's not replacing for some reason. What am I doing wrong?

row from database:

&#8216;Shut up,â€™ &#8216;shut your mouth,â€™ and &#8216;be quiet' all mean to stop talking, but &#8216;be quiet' is nice. You don't want to be rude.

test.php:

include("connect.php"); //sql connection
include("functionsX.php"); //just some functions

$response = gd1("response/allData/where response like '%all mean to stop talking%'"); //get one row from database
echo $response."<br>";

$response = str_replace("&#8216;", "'", $response);
$response = str_replace("â€™", "'", $response);
echo $response;

result:

‘Shut up,â€™ ‘shut your mouth,â€™ and ‘be quiet' all mean to stop talking, but ‘be quiet' is nice. You don't want to be rude.
'Shut up,â€™ 'shut your mouth,â€™ and 'be quiet' all mean to stop talking, but 'be quiet' is nice. You don't want to be rude.

need result:

‘Shut up,â€™ ‘shut your mouth,â€™ and ‘be quiet' all mean to stop talking, but ‘be quiet' is nice. You don't want to be rude.
'Shut up,' 'shut your mouth,' and 'be quiet' all mean to stop talking, but 'be quiet' is nice. You don't want to be rude.

if I had the time, frosty, I would look into it. But I must say you have a nice record of giving out Accepted Answers. So someone help em if ya can — Drew, Nov 15 '15 at 18:19
@frosty Look into phpMyAdmin and see how the tables are encoded. — Jan, Nov 15 '15 at 18:33

score 1 · Accepted Answer · edited May 23 '17 at 12:03

1

It seems the question shouldn't be asking how to replace odd characters in your database output, but how to prevent them from being there in the first place.

Usually, when you have odd characters in your output, it means you haven't set the correct database character set and collation in your database. Make sure that your database, the tables and the columns use a character set like utf8_general_ci or utf8_unicode_ci. I tend to use the last one.

To learn the differences between the two collations, please read this SO question: What's the difference between utf8_general_ci and utf8_unicode_ci.

You can alter your databease and/or table's character set and collation with queries like this:

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Or you can do the same thing in PHPMyAdmin, shown in the images below:

You should also tell MySQLi to connect to the database using UTF-8 like this:

$mysqli->set_charset("utf8");

And you can also tell your PHP pages to output UTF-8 like this:

header('Content-Type: text/html; charset=utf-8');

When you keep character sets aligned like this, you shouldn't have any problems with odd characters like â€™, and then you can run this:

html_entity_decode($str);

Which will convert HTML entities such as ‘ to their proper meanings, in this case, '.

edited May 23 '17 at 12:03

Community

1
1

answered Nov 15 '15 at 18:41

TheCarver

19,391
25
99
149

I use the default collation, which is: latin1_swedish_ci. – frosty Nov 15 '15 at 19:02
I just changed the collation to utf8_unicode_ci, but it's still not outputting the correct result. – frosty Nov 15 '15 at 19:08
Is there a way to replace the odd characters already in my database? Because there's a lot of them, and I need to replace them. – frosty Nov 15 '15 at 19:09
@frosty: that's your problem; Latin-1 has only 256 possible characters and UTF-8 has well over a million. Unfortunately, asking how to change the database content to UTF-8 will have to be asked in a new question, but when you accomplish it, you shouldn't have problems with odd characters again. Have a look at other questions on converting existing database data to UTF8 - there's a lot of info already there for you http://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8 – TheCarver Nov 15 '15 at 19:15
@frosty: This is also very useful for converting existing data to utf8 from latin1 http://stackoverflow.com/questions/17049903/converting-mysql-table-with-incorrectly-encoded-data-to-utf-8 – TheCarver Nov 15 '15 at 19:18
I just used the below code to convert. However, the output is still wrong. ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; – frosty Nov 15 '15 at 19:22
Nevermind. Found this google plugin where it can find and replace all texts on a page. I just used it to replace all the rows in my database. – frosty Nov 15 '15 at 19:40
Of course I added my own tableDatabaseName and tableName. I just copied and pasted it from the other answer here just for explanation purposes. – frosty Nov 15 '15 at 19:42

score 0 · Answer 2 · answered Nov 15 '15 at 18:31

This works for me:

$str = "&#8216;Shut up,â€™ &#8216;shut your mouth,â€™ and &#8216;be quiet' all mean to stop talking, but &#8216;be quiet' is nice. You don't want to be rude.";
$replacements = array('&#8216;', 'â€™');
$nstr = str_replace($replacements, "'", $str);
echo $nstr;

Please be aware that str_replace() can accept an array as first parameter (in my case, $replacements). So maybe an encoding issue? When it is saved back to the database?

Getting one row from database and replacing some things in it

2 Answers2