how to select from one database and insert into another Database in a Single query with different Database connections in php

Question

mysql_connect("localhost","root","");
mysql_select_db("database1");
mysql_query("INSERT INTO database2.categories (category_name,description,group_id,created)
  SELECT table1.name, table1.description, 12, UNIX_TIMESTAMP()
  FROM GiveArang_categories Where `p_id`= '225' ");

I need to insert data into the second database which is selected from the first database. Both databases have different connections. It needs to done in php in a single query .

You can't pass two link_identifiers at the same time. So I think it's pretty impossible. Well, it's just me. — Leri, Jul 07 '12 at 11:35

Secko · Answer 1 · 2013-07-21T12:21:42.080

1

The only way of doing this (that I know of) is by two separate connections.

Something like this:

mysql_connect("localhost","root","");
mysql_select_db("database2");
mysql_query("INSERT INTO database2.categories (category_name,description,group_id,created)");
mysql_close();

mysql_connect("localhost","root","");
mysql_select_db("database1");
mysql_query("SELECT table1.name, table1.description, 12, UNIX_TIMESTAMP() FROM GiveArang_categories Where `p_id`= '225' ");
mysql_close();

edited Jul 21 '13 at 12:21

answered Jul 07 '12 at 11:48

Secko

7,664
5
31
37

This is Working mysql_connect($host1,$username1,$password1); mysql_select_db($db_name1); $cat_name=mysql_query("SELECT table1.name FROM table1 Where `p_id`= '1234' "); if(mysql_num_rows($cat_name)) { while($var = mysql_fetch_array($cat_name)){ mysql_connect($host2,$username2,$password2); mysql_select_db($db_name1); mysql_query("INSERT INTO table2.name2 (description,gid,created) values ('". $var['name']."','". $var['name']."','15',UNIX_TIMESTAMP())"); } } mysql_close(); – Mari Muthu Jul 09 '12 at 12:25

score 0 · Answer 2 · edited May 23 '17 at 12:04

0

Unfortunately there's no Oracle dblink (which is what you need to connect to another database in a single query in Oracle Database) equivalent in MySql.

See also this for further information.

edited May 23 '17 at 12:04

Community

1
1

answered Jul 07 '12 at 11:34

Vincenzo Maggio

3,787
26
42

Oracle has a functionality called dblink which let a single query to connect to different databases with different connections, which is the exact feature he would need in MySql – Vincenzo Maggio Jul 07 '12 at 11:36

score 0 · Answer 3 · answered Jul 07 '12 at 11:35

0

You can pop a MySQL Proxy to sit between your server and database boxes which will allow you to treat them as if they are on the same server. You can then use databasename.tablename within your query to identify your different databases via the same connection.

answered Jul 07 '12 at 11:35

Fluffeh

33,228
16
67
80

Interesting, but physically there are nevertheless executed several queries. – David Jul 22 '17 at 10:55

score 0 · Answer 4 · answered Jul 22 '17 at 11:24

If both databases are on the same mysql-server you can note it like this:

mysql_connect("localhost","root","");
mysql_query("INSERT INTO database2.categories (category_name,description,group_id,created)
  SELECT database1.table1.name, database1.table1.description, 12, UNIX_TIMESTAMP()
  FROM database1.GiveArang_categories Where database1.GiveArang_categories.p_id= '225' ");

how to select from one database and insert into another Database in a Single query with different Database connections in php

4 Answers4