Tricky PHP MYSQL copy database over

[WeddingLink]

I have two databases on the same server, but separate accounts

ie.

 

user1_database

user2_database

 

I have all rights to access both. databases are exactly the same. I am attempting to copy user1 database to user2 database, where the title is not the same (id's don't work for this one, we will check against titles).

 

so it will be something like

 

INSERT INTO user1_database.table (user1_database.table.value1, user1_database.table.value2, user1_database.table.value3)

SELECT user2_database.table.value1,user2_database.table.value2,user2_database.table.value3

FROM user2_database.table

WHERE NOT EXISTS (SELECT * FROM user2_database.table WHERE user2_database.table.value2 != user1_database.table.value2)

 

Not sure if it is correct. We want to get data on user1 db over to user2 db, and check against value2 (which are business titles) so we don't copy a record to user2 db if it is already there.

 

We get confused with what to put first in the last select statement, in what order?

[WeddingLink]

Correction in my grammar... "databases are exactly the same" should read, "database structures are exactly the same, data on user1 db has many more records we need to move to user2 db, but some on user2 db may be duplicates, which we can tell by checking value2"

[CodeMaster]

Why not giving the tables from database1 a prefix and copy them to database2? Then create new tables with UNION?

Or, if you can access both databases within the same user account, you just have to use UNION to create new tables.

 

So:

 

CREATE TABLE db2.new_table1 AS SELECT * FROM db1.table1 UNION SELECT * FROM db2.table1

UNION will remove duplicate records.

[Mchl]

INSERT INTO user1_database.table (value1, value2, value3) 
SELECT
  t2.value1,t2.value2,t2.value3
FROM
  user2_database.table AS t2
LEFT JOIN 
  user1_database.table AS t1
USING (value2)
WHERE t1.value2 IS NULL

[WeddingLink]

Why not giving the tables from database1 a prefix and copy them to database2? Then create new tables with UNION?

Or, if you can access both databases within the same user account, you just have to use UNION to create new tables.

 

So:

 

CREATE TABLE db2.new_table1 AS SELECT * FROM db1.table1 UNION SELECT * FROM db2.table1

UNION will remove duplicate records.

 

HOw does this work, will it work with a certain column, or does it check them all, can you specify a column?

[WeddingLink]

INSERT INTO user1_database.table (value1, value2, value3) 
SELECT
  t2.value1,t2.value2,t2.value3
FROM
  user2_database.table AS t2
LEFT JOIN 
  user1_database.table AS t1
USING (value2)
WHERE t1.value2 IS NULL

 

Not sure if this is quite right? So, if I want to use a vendor_title column from both databases, where d2 is the lesser db with less data, so database 1 has all the data I want to move to db 2, using the vendor_title column on both databases.vendors table to check if it already exists in table 2 which will be the case for some records, is this correct?

 

Is the way I have it checking vendors.vendor_title from DB 1 against all in DB 2, and then inserting if it doesn't exist on DB 2 into DB 2?

 

INSERT INTO db2.vendors (vendor_id,vendor_firstname,vendor_lastname,vendor_title,vendor_address1,vendor_address2,vendor_city,vendor_state,vendor_zip,vendor_phone1,vendor_phone2,vendor_phone3,vendor_contact_phone1,vendor_contact_phone2,vendor_contact_phone3,vendor_fax1,vendor_fax2,vendor_fax3,vendor_email,vendor_contact_email,vendor_url,vendor_landing_page,vendor_description,vendor_datecreated,vendor_logo,member_type,vendor_active,vendor_email_activated,vendor_username,vendor_password,vendor_sorting,vendor_title_html,vendor_not_to_send_letter,vendor_service_areas_type,trans_declined_error,autosave )
SELECT 
,t1.vendor_id,t1.vendor_firstname,t1.vendor_lastname,t1.vendor_title,t1.vendor_address1,t1.vendor_address2,t1.vendor_city,t1.vendor_state,t1.vendor_zip,t1.vendor_phone1,t1.vendor_phone2,t1.vendor_phone3,t1.vendor_contact_phone1,t1.vendor_contact_phone2,t1.vendor_contact_phone3,t1.vendor_fax1,t1.vendor_fax2,t1.vendor_fax3,t1.vendor_email,t1.vendor_contact_email,t1.vendor_url,t1.vendor_landing_page,t1.vendor_description,t1.vendor_datecreated,t1.vendor_logo,t1.member_type,t1.vendor_active,t1.vendor_email_activated,t1.vendor_username,t1.vendor_password,t1.vendor_sorting,t1.vendor_title_h™l,t1.vendor_not_to_send_letter,t1.vendor_service_areas_type,t1.trans_declined_error,t1.autosave
FROM
db1.vendors as t1
LEFT JOIN
db2.vendors as t2
USING (vendor_title)
WHERE t2.vendor_title != t1.vendor_title

[WeddingLink]

Also, can you run this from terminal? or can this be run with a php script using two databases?

[WeddingLink]

Doesn't Work: 

Where am I off? Do I have to do this from root SSH? or via php script?

 

INSERT INTO db1.vendors (db2vendors.vendor_firstname,db1.vendors.vendor_lastname,db1.vendors.vendor_title,db1.vendors.vendor_address1,db1.vendors.vendor_address2,db1.vendors.vendor_city,db1.vendors.vendor_state,db1.vendors.vendor_zip,db1.vendors.vendor_phone1,db1.vendors.vendor_phone2,db1.vendors.vendor_phone3,db1.vendors.vendor_contact_phone1,db1.vendors.vendor_contact_phone2,db1.vendors.vendor_contact_phone3,db1.vendors.vendor_fax1,db1.vendors.vendor_fax2,db1.vendors.vendor_fax3,db1.vendors.vendor_email,db1.vendors.vendor_contact_email,db1.vendors.vendor_url,db1.vendors.vendor_landing_page,db1.vendors.vendor_description,db1.vendors.vendor_datecreated,db1.vendors.vendor_logo,db1.vendors.member_type,db1.vendors.vendor_active,db1.vendors.vendor_email_activated,db1.vendors.vendor_username,db1.vendors.vendor_password,db1.vendors.vendor_sorting,db1.vendors.vendor_title_html,db1.vendors.vendor_not_to_send_letter,db1.vendors.vendor_service_areas_type,db1.vendors.trans_declined_error,db1.vendors.autosave )
SELECT 
db2.vendors.vendor_firstname,db2.vendors.vendor_lastname,db2.vendors.vendor_title,db2.vendors.vendor_address1,db2.vendors.vendor_address2,db2.vendors.vendor_city,db2.vendors.vendor_state,db2.vendors.vendor_zip,db2.vendors.vendor_phone1,db2.vendors.vendor_phone2,db2.vendors.vendor_phone3,db2.vendors.vendor_contact_phone1,db2.vendors.vendor_contact_phone2,db2.vendors.vendor_contact_phone3,db2.vendors.vendor_fax1,db2.vendors.vendor_fax2,db2.vendors.vendor_fax3,db2.vendors.vendor_email,db2.vendors.vendor_contact_email,db2.vendors.vendor_url,db2.vendors.vendor_landing_page,db2.vendors.vendor_description,db2.vendors.vendor_datecreated,db2.vendors.vendor_logo,db2.vendors.member_type,db2.vendors.vendor_active,db2.vendors.vendor_email_activated,db2.vendors.vendor_username,db2.vendors.vendor_password,db2.vendors.vendor_sorting,db2.vendors.vendor_title_h™l,db2.vendors.vendor_not_to_send_letter,db2.vendors.vendor_service_areas_type,db2.vendors.trans_declined_error,db2.vendors.autosave
FROM
db2.vendors
LEFT JOIN
db1.vendors
USING (vendor_title)
WHERE db1.vendors.vendor_title != db2.vendors.vendor_title AND db2.vendors.vendor_state = 46

[Mchl]

You can run it from MySQL console, from PHPMyAdmin or create a PHP script to run it.

 

INSERT INTO db2.vendors (vendor_id,vendor_firstname,vendor_lastname,vendor_title,vendor_address1,vendor_address2,vendor_city,vendor_state,vendor_zip,vendor_phone1,vendor_phone2,vendor_phone3,vendor_contact_phone1,vendor_contact_phone2,vendor_contact_phone3,vendor_fax1,vendor_fax2,vendor_fax3,vendor_email,vendor_contact_email,vendor_url,vendor_landing_page,vendor_description,vendor_datecreated,vendor_logo,member_type,vendor_active,vendor_email_activated,vendor_username,vendor_password,vendor_sorting,vendor_title_html,vendor_not_to_send_letter,vendor_service_areas_type,trans_declined_error,autosave )
SELECT
  t1.vendor_id,t1.vendor_firstname,t1.vendor_lastname,t1.vendor_title,t1.vendor_address1,t1.vendor_address2,t1.vendor_city,t1.vendor_state,t1.vendor_zip,t1.vendor_phone1,t1.vendor_phone2,t1.vendor_phone3,t1.vendor_contact_phone1,t1.vendor_contact_phone2,t1.vendor_contact_phone3,t1.vendor_fax1,t1.vendor_fax2,t1.vendor_fax3,t1.vendor_email,t1.vendor_contact_email,t1.vendor_url,t1.vendor_landing_page,t1.vendor_description,t1.vendor_datecreated,t1.vendor_logo,t1.member_type,t1.vendor_active,t1.vendor_email_activated,t1.vendor_username,t1.vendor_password,t1.vendor_sorting,t1.vendor_title_h™l,t1.vendor_not_to_send_letter,t1.vendor_service_areas_type,t1.trans_declined_error,t1.autosave
FROM
  db1.vendors as t1
LEFT JOIN
  db2.vendors as t2
USING (vendor_title)
WHERE t2.vendor_title IS NULL

[WeddingLink]

Great! Worked as follows:

 

INSERT INTO db1.vendors (db1.vendors.vendor_firstname,db1.vendors.vendor_lastname,db1.vendors.vendor_title,db1.vendors.vendor_address1,db1.vendors.vendor_address2,db1.vendors.vendor_city,db1.vendors.vendor_state,db1.vendors.vendor_zip,db1.vendors.vendor_phone1,db1.vendors.vendor_phone2,db1.vendors.vendor_phone3,db1.vendors.vendor_contact_phone1,db1.vendors.vendor_contact_phone2,db1.vendors.vendor_contact_phone3,db1.vendors.vendor_fax1,db1.vendors.vendor_fax2,db1.vendors.vendor_fax3,db1.vendors.vendor_email,db1.vendors.vendor_contact_email,db1.vendors.vendor_url,db1.vendors.vendor_landing_page,db1.vendors.vendor_description,db1.vendors.vendor_datecreated,db1.vendors.vendor_logo,db1.vendors.member_type,db1.vendors.vendor_active,db1.vendors.vendor_email_activated,db1.vendors.vendor_username,db1.vendors.vendor_password,db1.vendors.vendor_sorting,db1.vendors.vendor_title_html,db1.vendors.vendor_not_to_send_letter,db1.vendors.vendor_service_areas_type,db1.vendors.trans_declined_error,db1.vendors.autosave )
SELECT 
db2.vendors.vendor_firstname,db2.vendors.vendor_lastname,db2.vendors.vendor_title,db2.vendors.vendor_address1,db2.vendors.vendor_address2,db2.vendors.vendor_city,db2.vendors.vendor_state,db2.vendors.vendor_zip,db2.vendors.vendor_phone1,db2.vendors.vendor_phone2,db2.vendors.vendor_phone3,db2.vendors.vendor_contact_phone1,db2.vendors.vendor_contact_phone2,db2.vendors.vendor_contact_phone3,db2.vendors.vendor_fax1,db2.vendors.vendor_fax2,db2.vendors.vendor_fax3,db2.vendors.vendor_email,db2.vendors.vendor_contact_email,db2.vendors.vendor_url,db2.vendors.vendor_landing_page,db2.vendors.vendor_description,db2.vendors.vendor_datecreated,db2.vendors.vendor_logo,db2.vendors.member_type,db2.vendors.vendor_active,db2.vendors.vendor_email_activated,db2.vendors.vendor_username,db2.vendors.vendor_password,db2.vendors.vendor_sorting,db2.vendors.vendor_title_html,db2.vendors.vendor_not_to_send_letter,db2.vendors.vendor_service_areas_type,db2.vendors.trans_declined_error,db2.vendors.autosave
FROM
db2.vendors
LEFT JOIN
db1.vendors
USING (vendor_title)
WHERE db1.vendors.vendor_email != db2.vendors.vendor_email AND db2.vendors.vendor_state = 46

[WeddingLink]

Now, the really tricky  part I can't seem to get...

 

So within the above working example, how do now do the following.

 

so we have the vendors tables, and we can copy data that doesn't exist from one to another, using the unique EMAIL address (which can't be duplicated on our systems...

 

So how do we use the same vendor tables, but for another table relationship?

 

We have vendors_to_category on the same two tables... so what would we add to the SQL join to check the vendor tables emails as in previous example, but to this time get the vendors_to_category data for those same vendors?

 

INSERT INTO db1.vendors_to_category (db1.vendors_to_category.vendor_id,db1.vendors_to_category.category_id,db1.vendors_to_category.parent_id,db1.vendors_to_category.numb,db1.vendors_to_category.numb_in_main,db1.vendors_to_category.active)
SELECT 
db2.vendors_to_category.vendor_id,db2.vendors_to_category.category_id,db2.vendors_to_category.parent_id,db2.vendors_to_category.numb,db2.vendors_to_category.numb_in_main,db2.vendors_to_category.active
FROM
db2.vendors_to_category
LEFT JOIN
db1.vendors_to_category
USING (vendor_title)
WHERE db1.vendors.vendor_email != db2.vendors.vendor_email AND db2.vendors.vendor_state = 46

[WeddingLink]

OK, the code isn't doing as it should... we are getting wierd results... What do we put in the USING parentheses? We are doing a NOT eQUAL, as we don't want any record where the email is currently in the other database?

[Mchl]

in USING() you should put a column that is to be compared in both tables. If there's no matching value in table after LEFT JOIN, a NULL value will be returned. Then we use WHERE vendor_title IS NULL, to filter only these results, that do not have a match.

[WeddingLink]

in USING() you should put a column that is to be compared in both tables. If there's no matching value in table after LEFT JOIN, a NULL value will be returned. Then we use WHERE vendor_title IS NULL, to filter only these results, that do not have a match.

 

OK, excuse our ignorance (we will donate to your cause for our ignorance but it still doesn't work as we need it to. You see, the USING(vendor_email) is great, but we are attempting to get ONLY THE RECORDS that DO NOT ALREADY exist in db2 using the unique email addresses from both tables... the following code returns ZERO results when ran, when it should return at least 20 to 100 inserted records... ?

 

INSERT INTO db1.vendors (db1.vendors.vendor_firstname,db1.vendors.vendor_lastname,db1.vendors.vendor_title,db1.vendors.vendor_address1,db1.vendors.vendor_address2,db1.vendors.vendor_city,db1.vendors.vendor_state,db1.vendors.vendor_zip,db1.vendors.vendor_phone1,db1.vendors.vendor_phone2,db1.vendors.vendor_phone3,db1.vendors.vendor_contact_phone1,db1.vendors.vendor_contact_phone2,db1.vendors.vendor_contact_phone3,db1.vendors.vendor_fax1,db1.vendors.vendor_fax2,db1.vendors.vendor_fax3,db1.vendors.vendor_email,db1.vendors.vendor_contact_email,db1.vendors.vendor_url,db1.vendors.vendor_landing_page,db1.vendors.vendor_description,db1.vendors.vendor_datecreated,db1.vendors.vendor_logo,db1.vendors.member_type,db1.vendors.vendor_active,db1.vendors.vendor_email_activated,db1.vendors.vendor_username,db1.vendors.vendor_password,db1.vendors.vendor_sorting,db1.vendors.vendor_title_html,db1.vendors.vendor_not_to_send_letter,db1.vendors.vendor_service_areas_type,db1.vendors.trans_declined_error,db1.vendors.autosave )
SELECT 
db2.vendors.vendor_firstname,db2.vendors.vendor_lastname,db2.vendors.vendor_title,db2.vendors.vendor_address1,db2.vendors.vendor_address2,db2.vendors.vendor_city,db2.vendors.vendor_state,db2.vendors.vendor_zip,db2.vendors.vendor_phone1,db2.vendors.vendor_phone2,db2.vendors.vendor_phone3,db2.vendors.vendor_contact_phone1,db2.vendors.vendor_contact_phone2,db2.vendors.vendor_contact_phone3,db2.vendors.vendor_fax1,db2.vendors.vendor_fax2,db2.vendors.vendor_fax3,db2.vendors.vendor_email,db2.vendors.vendor_contact_email,db2.vendors.vendor_url,db2.vendors.vendor_landing_page,db2.vendors.vendor_description,db2.vendors.vendor_datecreated,db2.vendors.vendor_logo,db2.vendors.member_type,db2.vendors.vendor_active,db2.vendors.vendor_email_activated,db2.vendors.vendor_username,db2.vendors.vendor_password,db2.vendors.vendor_sorting,db2.vendors.vendor_title_html,db2.vendors.vendor_not_to_send_letter,db2.vendors.vendor_service_areas_type,db2.vendors.trans_declined_error,db2.vendors.autosave
FROM
db2.vendors
LEFT JOIN
db1.vendors
USING (vendor_email)
WHERE db1.vendors.vendor_email != db2.vendors.vendor_email AND db2.vendors.vendor_state = 46

[Mchl]

It is not the code I posted, is it?

 

SELECT
  t1.vendor_id,t1.vendor_firstname,t1.vendor_lastname,t1.vendor_title,t1.vendor_address1,t1.vendor_address2,t1.vendor_city,t1.vendor_state,t1.vendor_zip,t1.vendor_phone1,t1.vendor_phone2,t1.vendor_phone3,t1.vendor_contact_phone1,t1.vendor_contact_phone2,t1.vendor_contact_phone3,t1.vendor_fax1,t1.vendor_fax2,t1.vendor_fax3,t1.vendor_email,t1.vendor_contact_email,t1.vendor_url,t1.vendor_landing_page,t1.vendor_description,t1.vendor_datecreated,t1.vendor_logo,t1.member_type,t1.vendor_active,t1.vendor_email_activated,t1.vendor_username,t1.vendor_password,t1.vendor_sorting,t1.vendor_title_h™l,t1.vendor_not_to_send_letter,t1.vendor_service_areas_type,t1.trans_declined_error,t1.autosave
FROM
  db1.vendors as t1
LEFT JOIN
  db2.vendors as t2
USING (vendor_email)
WHERE t2.vendor_email IS NULL

[WeddingLink]

Yes, I missed your one part in your code, it works now, but with one issue... How do we put in another table?

 

So we have the two dbs "DB1.vendors" and "DB2.vendors", how do we now insert records to another table, wherever the DB2.vendors.vendor_state = 46 ?

 

ie.

insert into db1.vendors_to_category (values)

select db2.vendors_to_category.* from db2.vendors_to_category where db2.vendors.vendor_state = 46... and db2.vendors.vendor_email IS NOT IN db1.vendors

 

so we have 3 tables involved in the left join ?

[Mchl]

INSERT INTO db2.vendors (vendor_id,vendor_firstname,vendor_lastname,vendor_title,vendor_address1,vendor_address2,vendor_city,vendor_state,vendor_zip,vendor_phone1,vendor_phone2,vendor_phone3,vendor_contact_phone1,vendor_contact_phone2,vendor_contact_phone3,vendor_fax1,vendor_fax2,vendor_fax3,vendor_email,vendor_contact_email,vendor_url,vendor_landing_page,vendor_description,vendor_datecreated,vendor_logo,member_type,vendor_active,vendor_email_activated,vendor_username,vendor_password,vendor_sorting,vendor_title_html,vendor_not_to_send_letter,vendor_service_areas_type,trans_declined_error,autosave )
SELECT
  t1.vendor_id,t1.vendor_firstname,t1.vendor_lastname,t1.vendor_title,t1.vendor_address1,t1.vendor_address2,t1.vendor_city,t1.vendor_state,t1.vendor_zip,t1.vendor_phone1,t1.vendor_phone2,t1.vendor_phone3,t1.vendor_contact_phone1,t1.vendor_contact_phone2,t1.vendor_contact_phone3,t1.vendor_fax1,t1.vendor_fax2,t1.vendor_fax3,t1.vendor_email,t1.vendor_contact_email,t1.vendor_url,t1.vendor_landing_page,t1.vendor_description,t1.vendor_datecreated,t1.vendor_logo,t1.member_type,t1.vendor_active,t1.vendor_email_activated,t1.vendor_username,t1.vendor_password,t1.vendor_sorting,t1.vendor_title_h™l,t1.vendor_not_to_send_letter,t1.vendor_service_areas_type,t1.trans_declined_error,t1.autosave
FROM
  db1.vendors as t1
LEFT JOIN
  db2.vendors as t2
USING (vendor_email)
WHERE t2.vendor_email IS NULL AND t1.vendor_state = 46;