1st post newbie SQL help

[mikehammond]

Hello all, I'm hoping you guys can help me out with MySql 4.1.19.

For my sins I'm an admin on a phpbb forum and in one section someone managed to delete all the topics & posts.

I have found the tables concerned in a backup database they are phpbb_topics, phpbb_posts and phpbb_posts_text.

The first two (phpbb_topic and phpbb_posts) are easy enough to sort using a field called forum_id which in this case has a value of 39 for all the posts I need to reinsert the data, but the third table (phpbb_posts_text) does not have the forum_id field in it.

phpbb_posts_texts does however share another field with the phpbb_posts table which is post_id (this is a unique reference for each post under a specific topic)

So how do I extract the phpbb_posts_texts data only related to the values in phpbb_posts table for the post_id values that also have forum_id of 39 ?

Many thanks I hope it makes sense ?


Mike

PS I use phpMyAdmin

[Xnuiem]

It makes sense.

Ok, the best thing to do is create a temporary database and put your backup in there.

Then delete all the records from post and topic that are not from the forum you need.

Then run a query to delete all the unrelated post_text from that table. It would look something like:

delete from phpbb_post_text where post_id not in (select post_id from phpbb_post);

Then, export the three remaining tables, import those into your live database, and you are set.

[mikehammond]

Thanks for that I'll have a tinker and see if I can work it out from that.

Cheers

Mike

[mikehammond]

OK cleared out all the stuff out of phpbb_topics and phpbb_posts that don't have a forum_id of 39, that's worked fine so stage one complete.

Then when I try to run the query on phpbb_posts_text I'm getting a syntax error. I'm close but no cigar, can you or anyone ?? see where I'm going wrong ??

DELETE FROM 'phpbb_posts_text' WHERE 'post_id' NOT LIKE (SELECT 'post_id' FROM 'phpbb_posts');

Thanks

Mike

[Xnuiem]

Use NOT IN, not NOT LIKE

[mikehammond]

Originally Posted by Xnuiem

Use NOT IN, not NOT LIKE

It didn't like that either ?

[mikehammond]

Sorted, typo on my part

thanks for your help

Mike