[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.

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:

The function will return an array which you'll run through in a foreach loop, then you run a query for every user:

Depending on how good your knowledge in php is, this will be a quite simple task.

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:

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.

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?

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.

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

Line 34:
Line 41:

d'ouh, my bad.

Fixed

Got this error:

For the whole from id 1 to id 509 (i have 509 users) and nothing change in database.

huh weird... change line 42 from:

to:
And post what the first error is, thanks.

Got this:

Replace line 37 with this:

It's Success. But it's look like it doesn't get thanked amount from database1. The thanked amount is from "write" db and being rewrite again with userid+1.

hum, well it should oO

did you re-check the database credentials?

[S]If you still have the old table in another database, you could try adding the database to the table names of the first query, like[/S]

Edit: never mind, I hadn't looked at the php file linked above where Disasterpiece had already handled the second database.

BTW, I think this could be done via query something like:

(But I haven't tested that exact query). That of course assumes that you've already created a "thanked" column in the new db....and that the dbs are on the same server.

Yes, i've recheck the column after each run.

Could you rewrite the query with those extractly db name, table name and column name as i listed above?

I think this is it:


This sets the vb3forum database from the vb4forum database. I think what I posted above assumed that you were going from vb3 to vb4 (at least as far as the table prefixes were concerned). Speaking of which, if you have table prefixes you need to add them.

I've down from vb4 to vb3, and there is no table prefix on 2 databases, i have full rights to modify them both.

I've just ran the query and there is an error:

Hmm...well, both lines together are supposed to be all one query and it looks like you just have the first line there.

yes, i've ran both of them together, but it shows up just 1 line for error.

The error looks like it's trying to execute only the first line. So I guess try this:

UPDATE vb4forum.user as old LEFT JOIN vb3forum.user as new ON (old.userid = new.userid + 1) SET new.post_thanks_thanked_times = old.post_thanks_thanked_times

Okay, it's success :D kh99 you rock! Most of thanked amouths have right destination, i think i have to manual change few value.

Thank you all, both kh99 and Disasterpiece.