My wp_posts table, upon importing to another blog host, has the garbage that folks refer to that the translation between utf8 and latin1 causes. So I tried as an experiment issuing a command like :
CREATE TABLE IF NOT EXISTS wp_postsclean SELECT * FROM wp_posts;
The resulting "clean" table appears to no longer have the garbage (I'll check more records to be sure...).
So, what I'd like to do is delete the rows in my wp_post table and insert the rows from the "clean" table. The question then is does this make sense and how do I do the inserts when the ID field is autonumber (can we turn it off and then back on?) ?
yes you can turn off the auto number and copy the daat back over, then tunr auto number back on, imwould suggest you make a full back up before doing this.
on windows use teh mySQL query browser, open teh db and right click on the table and select edit, uncheck the auto-increment and add a default value of 999 (the field is set to not null so it needs a default value) copy over the clean data then edit the table again, change the default to not null and check the auto-increment
make sure you have a full backup before doing this!
i have not used phpmyadmin so not sure of the commands in that case what you might want to do is a straight update query from the clean data table abd ovewrite the curent bad content
select all records from good table loop over records update bad table with new record values where good tabel id = bad table id
The problem with the above suggestions is that the clean table auto number column values do not necessarily match those in the dirty table.
This is the case since there have been rows deleted from the dirty table prior to my copying that table to the clean table where the clean table did consecutive auto numberings.
I think that sql to turn off the auto numbering on the dirty table, doing inserts from the clean table, followed by turning on the autonumber with a start value = max(autocol) +1 .
>>This is the case since there have been rows deleted from the dirty table prior to my copying that table to the clean table where the clean table did consecutive auto numberings. can you redo the data clean to a new table without an auto number id column?
These auto number values are needed to line up comments with posts, so if the values do not match, we get no comments for a post.
I'm not sure what or why you would want the clean data written to a table that does not have the autonumber field (ID field), but I could build a "clean" table without the auto number column.
no, export all the data to a new table, but the new table id field should not be autonumber to maintian the crossreference, you d\said the numbers in thew clean data table do not match the opriginal table, if that is the case you need toperforma a new export/clean or do manual updates
yes, unless you have corresponding ID's between the two tables you cannot crossreference them and you would need to examine each record and do a manula update.
i used the built in export db command from phpMyAdmin and read the file into textpad. Textpad seems to be very good at figuring out how to read text (PC, UNIX...) and on saving the text file to a pc format, it was cleaned. I then ran the sql the exported and saved textfile for the dirty table, changing the name to wp_posts_clean and that was it.
