Go Back   vb.org Archive > vBulletin 4 Discussion > vB4 Programming Discussions

Reply
 
Thread Tools Display Modes
  #1  
Old 08-31-2015, 12:21 PM
RichieBoy67's Avatar
RichieBoy67 RichieBoy67 is offline
 
Join Date: Apr 2004
Location: CT - Down in a hole..
Posts: 3,057
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default 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
Reply With Quote
  #2  
Old 08-31-2015, 04:36 PM
Dave Dave is offline
 
Join Date: May 2010
Posts: 2,583
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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?
Reply With Quote
  #3  
Old 08-31-2015, 07:09 PM
RichieBoy67's Avatar
RichieBoy67 RichieBoy67 is offline
 
Join Date: Apr 2004
Location: CT - Down in a hole..
Posts: 3,057
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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
Reply With Quote
  #4  
Old 09-01-2015, 09:30 AM
Dave Dave is offline
 
Join Date: May 2010
Posts: 2,583
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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.
Reply With Quote
Благодарность от:
RichieBoy67
  #5  
Old 09-01-2015, 01:24 PM
squidsk's Avatar
squidsk squidsk is offline
 
Join Date: Nov 2010
Posts: 969
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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.
Reply With Quote
  #6  
Old 09-01-2015, 02:55 PM
Lynne's Avatar
Lynne Lynne is offline
 
Join Date: Sep 2004
Location: California/Idaho
Posts: 41,180
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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)
Reply With Quote
  #7  
Old 09-02-2015, 07:29 AM
RichieBoy67's Avatar
RichieBoy67 RichieBoy67 is offline
 
Join Date: Apr 2004
Location: CT - Down in a hole..
Posts: 3,057
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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.
Reply With Quote
  #8  
Old 09-02-2015, 01:24 PM
squidsk's Avatar
squidsk squidsk is offline
 
Join Date: Nov 2010
Posts: 969
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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.
Reply With Quote
Благодарность от:
RichieBoy67
  #9  
Old 09-02-2015, 03:06 PM
Lynne's Avatar
Lynne Lynne is offline
 
Join Date: Sep 2004
Location: California/Idaho
Posts: 41,180
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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.
Reply With Quote
Благодарность от:
RichieBoy67
  #10  
Old 09-03-2015, 03:06 AM
RichieBoy67's Avatar
RichieBoy67 RichieBoy67 is offline
 
Join Date: Apr 2004
Location: CT - Down in a hole..
Posts: 3,057
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Quote:
Originally Posted by squidsk View Post
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!
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 11:42 AM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.04501 seconds
  • Memory Usage 2,264KB
  • Queries Executed 13 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (4)bbcode_code
  • (2)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (2)pagenav_pagelink
  • (10)post_thanks_box
  • (3)post_thanks_box_bit
  • (10)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (3)post_thanks_postbit
  • (10)post_thanks_postbit_info
  • (10)postbit
  • (10)postbit_onlinestatus
  • (10)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.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/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php 

Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • fetch_musername
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • post_thanks_function_fetch_thanks_bit_start
  • post_thanks_function_show_thanks_date_start
  • post_thanks_function_show_thanks_date_end
  • post_thanks_function_fetch_thanks_bit_end
  • post_thanks_function_fetch_post_thanks_template_start
  • post_thanks_function_fetch_post_thanks_template_end
  • pagenav_page
  • pagenav_complete
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete