Go Back   vb.org Archive > vBulletin 3 Discussion > vB3 Programming Discussions

Reply
 
Thread Tools Display Modes
  #1  
Old 07-10-2011, 04:09 AM
kevin.kool kevin.kool is offline
 
Join Date: Nov 2009
Posts: 74
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default [MySQL] How to import a column from db1.table to db2.table

Hello,

I've just downgraded my forum from vb4 to vb3, and would like to keep the "thanked" amount from old db. I looked into the old db and notice that "thanked" is a column in "user" table.

So i would like to ask how can i move (or copy) that column from old database into the table of the new database?

Also, is there anyway to plus 1 value in the "userid" field (of that column)? Because of i've used ImpEx and it's created a new admin with userid = 1, then my old admin account has userid = 2.

Thank you.
Reply With Quote
  #2  
Old 07-10-2011, 04:55 AM
Disasterpiece's Avatar
Disasterpiece Disasterpiece is offline
 
Join Date: Apr 2007
Location: GER
Posts: 765
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

You'd have to create a new column with the exact datatype like the original column (if that istn't already done) then you'd have to create a php script, that queries every user row, builds a new query with updated userid (+1) in the WHERE clause to apply the sql query.

Could look something like this:

Code:
SELECT userid, thankedamount FROM vb4_user
The function will return an array which you'll run through in a foreach loop, then you run a query for every user:

Code:
UPDATE vb4_user SET thankedamount = ".$thankedamount." WHERE userid = ".$userid+1
Depending on how good your knowledge in php is, this will be a quite simple task.
Reply With Quote
Благодарность от:
kevin.kool
  #3  
Old 07-10-2011, 05:39 AM
kevin.kool kevin.kool is offline
 
Join Date: Nov 2009
Posts: 74
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

I have ran this query in phpmyadmin and it's ok, but only for the user who has id=2, how to loop it?
Sorry because my php knowledge is bad.

This is the query that i have used:
Quote:
SELECT userid, post_thanks_thanked_times FROM vb4forum.user;
UPDATE vb3forum.user SET post_thanks_thanked_times = '.$post_thanks_thanked_times.' WHERE userid = '.$userid.+1';
Reply With Quote
  #4  
Old 07-10-2011, 05:52 AM
Disasterpiece's Avatar
Disasterpiece Disasterpiece is offline
 
Join Date: Apr 2007
Location: GER
Posts: 765
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

it's no mysql query at all

there needs to be a php script built around this...
Can you tell me if the two vbulletin installations are stored in the same or in a different database?
And tell me how the exact name of the column is where the amount of thanks is stored.
Reply With Quote
  #5  
Old 07-10-2011, 06:02 AM
kevin.kool kevin.kool is offline
 
Join Date: Nov 2009
Posts: 74
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Two forums are store in 2 different databases. I wrote as 'vb4forum' and 'vb3forum' as above.
And the 'thanked amount' column name is post_thanks_thanked_times

Could you create a sample php page?
Reply With Quote
  #6  
Old 07-10-2011, 06:24 AM
Disasterpiece's Avatar
Disasterpiece Disasterpiece is offline
 
Join Date: Apr 2007
Location: GER
Posts: 765
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

This is my old convert-youtube script. I just adjusted some values and added a 2nd database.

This only works if username and password are the same for the 2 databases.

open the file, enter your credentials at the top of the script. It works like
$db1 reads, $db2 writes. So $db1 is your old db to read from, $db2 is the one where you want the thanks transferred to.

Upload it into the root dir of your site (not forum root) so it can be accessed like "http://someurl.tld/transferthanks.php"
and run it.

didn't test it myself, if you're getting errors, report back. And I suggest to test it in a safe environment first.
Attached Files
File Type: php transferthanks.php (1.4 KB, 1 views)
Reply With Quote
  #7  
Old 07-10-2011, 06:53 AM
kevin.kool kevin.kool is offline
 
Join Date: Nov 2009
Posts: 74
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Thank for the file but when it's got error:

Quote:
Warning: Cannot use a scalar value as an array in C:\AppServ\www\transferthanks.php on line 34

Notice: Undefined variable: id in C:\AppServ\www\transferthanks.php on line 41
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1 (id: )
Line 34:
Code:
		$res['post_thanks_thanked_times'] = 0;
Line 41:
Code:
	else echo "Error: ".mysql_error()." (id: $id)<br />";
Reply With Quote
  #8  
Old 07-10-2011, 06:57 AM
Disasterpiece's Avatar
Disasterpiece Disasterpiece is offline
 
Join Date: Apr 2007
Location: GER
Posts: 765
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

d'ouh, my bad.

Fixed
Attached Files
File Type: php transferthanks.php (1.4 KB, 4 views)
Reply With Quote
  #9  
Old 07-10-2011, 07:02 AM
kevin.kool kevin.kool is offline
 
Join Date: Nov 2009
Posts: 74
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Got this error:

Code:
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1 (id: 1)
For the whole from id 1 to id 509 (i have 509 users) and nothing change in database.
Reply With Quote
  #10  
Old 07-10-2011, 07:07 AM
Disasterpiece's Avatar
Disasterpiece Disasterpiece is offline
 
Join Date: Apr 2007
Location: GER
Posts: 765
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

huh weird... change line 42 from:

PHP Code:
    else echo "Error: ".mysql_error()." (id: $id)<br />"
to:
PHP Code:
    else echo "Error: ".mysql_error()." ($q) (id: $id)<br />"
And post what the first error is, thanks.
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 09: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.07953 seconds
  • Memory Usage 2,281KB
  • Queries Executed 14 (?)
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
  • (5)bbcode_code
  • (2)bbcode_php
  • (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
  • (1)post_thanks_box_bit
  • (10)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (1)post_thanks_postbit
  • (10)post_thanks_postbit_info
  • (10)postbit
  • (2)postbit_attachment
  • (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
  • postbit_attachment
  • pagenav_page
  • pagenav_complete
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete