mysql copying specific columns from one database table to another using php

Question

I'm trying to create a cron job through php that has mysql queries (NOT for backup purposes), and want it to copy two specific columns from a table from one database to another (the two databases are on the same server, but has different connections).

I've tried

    insert into newDB.your_table select * from oldDB.your_table;

but that didn't work for some reason and i want it to be specific to 2 columns only.

Any help, code, example, tutorial would be much much appreciated.

Thank you for time.

That SQL looks good. Your problem appears to lie elsewhere. Can you run this query successfully from the command line, or WorkBench, or whatever you use? Oh, also, I believe the two tables would have to have the same number of columns, of the same datatypes, as well, for this query to work. — Redbeard011010
– Redbeard011010, Commented Dec 23, 2015 at 2:20
well it showed through the command line that it was successful but nothing is showing up in the database, plus, i don't want o copy all data, only 2 columns. Also the two databases have different number of columns but same datatypes. — user3429778
– user3429778, Commented Dec 23, 2015 at 2:25

Redbeard011010 · Accepted Answer · 2015-12-23 02:43:47Z

2

As shown on this page http://dev.mysql.com/doc/refman/5.7/en/insert-select.html

To selectively copy only specific columns from one table to another, the SQL:

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

So for your example: INSERT INTO newDB.some_table (id, value) SELECT id, value FROM oldDB.some_other_table;

edited Dec 23, 2015 at 2:43

answered Dec 23, 2015 at 2:22

Redbeard011010

9646 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user3429778 Over a year ago

but the table i'm copying to is on another database, not the same one.

tadman Over a year ago

Please avoid using w3schools and instead link directly to the current-version MySQL documentation whenever possible. The canonical source is often the most up-to-date.

user3429778 Over a year ago

Thank you for help, it worked, but now its forming duplicates, how can i prevent that ? and as i mentioned, its for a cron job, so in this case there will be a lot of duplicates

Redbeard011010 Over a year ago

lol...yes, it will create duplicates if you continually select *. I think perhaps you might do yourself a favor by including more information in your question.

Redbeard011010 Over a year ago

If you're tracking users (or really doing anything), it is best practice to declare a PRIMARY KEY, which is almost always an auto incrementing integer. I read a tip recently, which suggested using a SERIAL KEY, which just easily creates an auto incrementing, unsigned BIGINT. The unique id is really useful...for lots of stuff. Like differentiating between users to track their states ;) Read about SERIAL here: stackoverflow.com/questions/20021983/…

|

Collectives™ on Stack Overflow

mysql copying specific columns from one database table to another using php

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related