HELP with a wordpress database issue, please ? :)
(5 votes, average: 2.60 out of 5)
Loading...Hi there, that’s a post for the phpmyadmin, sql or php wise persons, I need help 
I made various operations last week with phpmyadmin, and I think something has gone wrong : I noticed yesterday that the English Translated blog category had disappeared. I re-created it, just in case, but, no, it really didn’t exist anymore O_o
Not the posts — just the fact that some posts belonged in a category called English Translated. The posts belonging to that category lost their belonging to this category, while that category ceased being mentioned/stored anywhere, that’s all.
Still, it pissed me off that I screwed off my database like that without even noticing it, seriously, how could I achieve something so stupid ?!? >_<
So, here I am, coming to ask for help
My server performs weekly full .sql database dumps so I DO have backups. But I don’t want to restore an old backup, that would mean rolling back in time, and thus losing a week of activity
Do you know if there is a way to “export” from an old database dump a list of which posts belonged to the specified category, and import to the current database this list of relations, so that the proper post IDs belong once again to this category ?
I can’t do it by hand in the blog editor, there are THOUSANDS of posts.
Thank you VERY MUCH if you know how to do it ! ^_^
(the comments might break SQL syntax, so you’d better either post pastebin links than post code)
Additional info, if that may be required, I can restore the database dump to a local install or to another test blog in the blink of an eye.
Really, thanks a lot if there's a way to achieve this, and you can help with it
Sorry I can’t help with the problem, but when was the most recent backup made? I wanna know if I need to make a copy of my most recent post or not on Nopepad or something just in case (as well as anything else I’ve edited since then). You said you make them every week, but not on which day or time :V
If you don’t wanna tell me the exact time, at least tell me if it was before or after my last post (if you look at my post’s edit page you’ll know what time it was posted).
I think the last backup was last tuesday. Anyway, Hurp, I am NOT rollbacking in time. That was the most useless category, since 99% of the blog posts belonged to it, losing a week's posts just for this is pointless, I'll only restore it if that doesn't cause a rollback.
why not restore a backup in your desktop, execute a mysql query for all the threads marked with the tag “english translation”, create a new update query with the results and runned in your active mysql database?
Yeah, sure. I'm precisely asking how that can be done, I'm not a mysql genius
If you restore the latest backup as a new instance you can export the data you've lost and import it into the current one.
I'm not familiar with wordpress/phpmyadmin/mysql. So I'm not sure how accurate the below information will be.
However, if you get the ID of the 'english-translated' category you've added back to the CURRENT database (either by viewing the table or running a query resembling: "SELECT cat_ID FROM wp_categories WHERE cat_name = 'english-translated'"), then you can replace "<NEWCATID>" in the below query and run that on the RESTORED database.
SELECT pc.post_id, <NEWCATID> FROM wp_post2cat pc INNER JOIN wp_categories cat ON cat.cat_ID = pc.category_id WHERE cat.cat_name = 'english-translated'
You should then be able to export those results as SQL from phpmyadmin, which should generate a script containing a data insert script which you can then import on your CURRENT database (in theory), and that should link the new category to the posts it was originally linked to. NOTE: When you export you will need to use the advanced settings and dump all rows, and you can limit the script to data only, etc.
Of course you should backup you CURRENT database before you attempt any of that, because like I said earlier, that information could be quite inaccurate. Someone one with more MySQL/Wordpress knowledge could confirm if it would possibly work, or even if there is a better way.
Hope that was a little helpful at least.
I develop software using SQL Server databases on a daily basis, so I know it is possible to get that data restored.
you need to provide the database structure for someone to write the script or delivere one of your backups, which I don’t think you would so …. no idea how can someone can help you, sorry.
i work with mysql all day, but without knowing what you have done, its hard to repair the database.
one would have to guess what the database looks like and how the relations work.
what did you do in phpmysql that you think it harmed your database?
did you delete some rows? did you change a table? did you drop a field from a table?
@moochew if there is my db dump by mysqldump, its easy to restore the database info into another database. also with mysqldump you can easily dump all stuff single tables or even rows because you can apply a where clause. of course phpmadmin lets you select stuff und export the results as sql file with insert scripts
this one might help: http://www.noupe.com/wordpress/wordpress-database…
Actually the category "Redirection Pages" also got deleted and I'm kinda sad now D:
hm i think i would check the "wp_postmeta" table first.
try "repair table wp_postmeta" no harm if the table is no damaged.
try "SELECT COUNT(*) FROM wp_postmeta" to see how many wors are in there.
Compare them with your backup DB.
next:
check if "wp_postmeta" is the right one:
SELECT FROM wp_postmeta" WHERE meta_key LIKE "%English Translated%";
since ET is lost try to find another category.
Considering following http://codex.wordpress.org/images/9/9e/WP3.0-ERD….
since you can find the posts the table "wp_posts" is fine.
i would think the problem could be in "wp_links" , "wp_term_relationships" or in "wp_postmeta"
get your backupdump into a test system and search for the "English Translated" keyword