[SOLVED] strange weird characters showing up after a db dump

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

[SOLVED] strange weird characters showing up after a db dump

Post by Burrito »

I just dumped my database from one machine to another...(I set up mysql identically on both) and when I imported my data to the new machine and hit the site (from the new machine...I have several weird chars showing up).

ie: Let’s was supposed to be Let's...I. there are several others that are showing up the same way.

I'm assuming that they are "reserved" chars like the " ' " and " \ " etc but I've never had this happen before.

I dumped the db from phpMyAdmin and inserted it using mysql from the command line.

any ideas here?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

I just looked at the sql file and it has Let's ( the way it should be ) so it's dying on my data import.

is there a switch or something I can use to make it escape those chars?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it sounds more like a character encoding mismatch issue.

Check the phpMyAdmin dump to make sure they are correct (it should look like '')

if these were copied in from Word, for instance, they are likely binary data and as such are screwy and should be converted to usable entities or characters.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

ok I found my problem.

instead of having "let''s learn about them" or "let''s learn about them" I have: "Let’s learn about them"

the funky single quote... it might be as you suspected feyd in that it came from word with some strange char. My question then becomes, can I convert my .sql file to some other format that will import all of those correctly?

I really can't do a find and replace of all of those because I'm not even sure of what all the chars are that are having problems.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

changing the character set output by the server can hide it, however they will still be binary characters (unless you run them through htmlentities or its siblings.) Also, switching character sets may only work for one country.. so it's best to hunt them all down.. and slap the person that pasted directly from Word :P
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

finally got this figured out.

I ended up dumping from phpmyadmin to a .sql file.

I then opened my .sql file with notepad and did a replace of all the defaultcharset=latin1 with defaultcharset=utf8.

I then *tried* to do my import from the mysql command line but saving it out from notepad made the thing bomb on the first line.

Soooo, I had to copy and paste all of the creates, inserts etc from my notepad to phpmyadmin (on the new machine) and it worked great.

thx for the help Feyd,

Burr
Post Reply