Database query needed

[RichieBoy67]

Hey all,

The issue is missing posts. Thread rows, attachments, etc are all there. Normally I would just search the missing post ids and export them as data and just insert them or find the last post id.

In this case though there has been weeks of posting after the data was removed so what I need to do is figure out how to insert just the missing posts without touching the data that is there already.

I have been going by the urls and thread ids and I have fixed a few threads but I need to do this in mass and I do not want to take any chances so can anyone please help me out here??

Thanks,
Rich

[Dave]

Your question is kind of confusing to me.
You are missing posts but want to import them back into the database? Where do you get the missing posts from? From an existing backup?

[RichieBoy67]

Yes, Exactly.

--------------- Added [DATE]1441090577[/DATE] at [TIME]1441090577[/TIME] ---------------

Just to elaborate.

Yes I have a back up that contains the missing posts. The threads are there, it is just posts that are missing.

I have been using google webmaster tools, crawl errors to find the missing posts. I have been getting the thread id from the url and pasting it into the post table search of the back up database which is imported into phpadmin.

I then export only the data and insert it into the post table of the original database. This method works but takes too long.

What I want to do if possible is insert all of the back up post table but only insert the missing or empty tables.

Thanks

[Dave]

The easiest way would be to make a PHP script for this.

1. Connect to both databases, the current live one and the backup one.
2. Using basic SELECT queries you check if posts of the backup database exist in the live database.
3. If not, insert it into the table.

[squidsk]

In order to make dave's suggestion work, you'd need to load the backup of your db into a new database on your mysql server and make sure whatever user has appropriate permissions on the new db.

[Lynne]

You may want to check out this post - http://www.vbulletin.com/forum/showt...1#post1845274:

Originally Posted by Lats

Try this, restore the backup to a different database and using the operations tab in phpmyadmin, rename the thread and post tables to something with a number on the end (post7 and thread7) then copy those 2 tables to the live database.
Run the following queries changing 777 to the threadid of the deleted thread.
Code:

INSERT INTO thread
(SELECT *
FROM thread7
WHERE threadid = 777)

Code:

INSERT INTO post
(SELECT *
FROM post7
WHERE threadid = 777)

[RichieBoy67]

Thanks,

Dave, can you give me an example of the query?

Squid, Yes.. as mentioned I already have the back up database imported into phpadmin.

Lynn, that is basically what I am doing but I am trying to automate the process rather than do each post one at a time.

[squidsk]

You can just grab all threads/posts that don't exist. You can alter the above queries as follows:

INSERT INTO post
(SELECT *
FROM post7
WHERE NOT threadid IN (SELECT threadid FROM thread))

INSERT INTO thread
(SELECT *
FROM thread7
WHERE NOT threadid IN (SELECT threadid FROM thread))

EDIT: You have to do the post query first otherwise the deleted thread will already be restored in the thread table.

[Lynne]

Just want to add.... make a database backup before doing this!!!! Even better, make a database backup, set up a test site and then figure out the best method to do this.

[RichieBoy67]

Originally Posted by squidsk

You can just grab all threads/posts that don't exist. You can alter the above queries as follows:

INSERT INTO post
(SELECT *
FROM post7
WHERE NOT threadid IN (SELECT threadid FROM thread))

INSERT INTO thread
(SELECT *
FROM thread7
WHERE NOT threadid IN (SELECT threadid FROM thread))

EDIT: You have to do the post query first otherwise the deleted thread will already be restored in the thread table.

I am very confused by these. I do not know where to begin with the script. I have the first query run first on which database?

Thanks Guys

Lynne, You are not kidding!