vb.org Archive - Database query needed

Page 1 of 3

Show 40 post(s) from this thread on one page

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB4 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=252)

- - Database query needed (https://vborg.vbsupport.ru/showthread.php?t=320079)

RichieBoy67

08-31-2015 12:21 PM

Database query needed

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	08-31-2015 04:36 PM

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

08-31-2015 07:09 PM

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	09-01-2015 09:30 AM

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

09-01-2015 01:24 PM

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

09-01-2015 02:55 PM

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

Quote:

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

09-02-2015 07:29 AM

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

09-02-2015 01:24 PM

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

Code:

INSERT INTO post

(SELECT *

FROM post7

WHERE NOT threadid IN (SELECT threadid FROM thread))

Code:

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

09-02-2015 03:06 PM

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

09-03-2015 03:06 AM

Quote:

Originally Posted by squidsk (Post 2554112)

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

Code:

INSERT INTO post

(SELECT *

FROM post7

WHERE NOT threadid IN (SELECT threadid FROM thread))

Code:

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! :)

All times are GMT. The time now is 02:22 PM.

Page 1 of 3

Show 40 post(s) from this thread on one page

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01159 seconds Memory Usage 1,740KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (4)bbcode_code_printable (2)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (2)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: