How to insert data in two different tables of two different database in php

Question

I have to insert data in two different database's table. I have created database1 and table1 for database1, also i have created database2 and table2 for database2.

For inserting data i have written code,

$connect = mysql_connect("localhost","root",""); //database connection

mysql_select_db("database1",$connect); // select database1 
mysql_select_db("database2",$connect); // select database2 

$sql = mysql_query("INSERT INTO database1.table1 (contact_first, contact_last, contact_email) VALUES('abc','xyz','abc@abc.com')"); //insert record to first table
$sql1 =mysql_query("INSERT INTO database2.table2 (contact_first, contact_last, contact_email) VALUES('abc','xyz','abc@abc.com')"); //insert record to second table

please suggest me corrections for above code to insert data.

this may help you [Two db connetion](http://stackoverflow.com/questions/274892/how-do-you-connect-to-multiple-mysql-databases-on-a-single-webpage) — Ravi Kant Mishra, Jul 22 '13 at 13:21

score 2 · Accepted Answer · answered Jul 22 '13 at 13:31

Try the following code:

$connect1 = mysql_connect("localhost","root","");
mysql_select_db("database1", $connect1);
$res1 = mysql_query("query",$connect1);

$connect2 = mysql_connect("localhost","root","",true);
mysql_select_db("database2", $connect2);
$res2 = mysql_query("query",$connect2);

Note: So mysql_connect has another optional boolean parameter which indicates whether a link will be created or not. as we connect to the $connect2 with this optional parameter set to 'true', so both link will remain live.

zoran404 · Answer 2 · 2013-07-22T13:37:19.943

1

Simply connect to 1 database, insert new row, disconnect, connect to the other database, insert row into that one and disconnect.

Or you can use $connect1 and $connect2 to refer to each of them separately and do the insertion parallely.

EDIT: Btw you can select the database with the 4'th parameter of mysql_connect, no need to use mysql_select_db

And very important, you should write mysqli not mysql. Because mysql functions are not going to be supported for much longer.

edited Jul 22 '13 at 13:37

answered Jul 22 '13 at 13:21

zoran404

1,682
2
20
37

But he has 1 connection, with 2 different db selections. Actually it should work like this. – GFP Jul 22 '13 at 13:24
@zoran404 it would be more useful if you add as an example by taking his code , by the way good technique – M Khalid Junaid Jul 22 '13 at 13:28

score 0 · Answer 3 · answered Jul 22 '13 at 13:24

first create two database connections

$connect1 = mysql_connect("localhost","root",""); 
$connect2 = mysql_connect("localhost","root","");

Then select the database for each connection.

mysql_select_db("database1",$connect1); // select database1 
mysql_select_db("database2",$connect2); // select database2

Then pass in a second argument for mysql_query which is the respective connection for the query.

mysql_query("SELECT ... ", $connect1);
mysql_query("SELECT ... ", $connect2);

Thanks for you answer it working after adding $connect2 = mysql_connect("localhost","root","",true); — M Gaidhane, Jul 23 '13 at 06:12

score 0 · Answer 4 · answered Jul 22 '13 at 13:28

0

Well, if there's a pattern in db names, tables and queries are exactly the same, you can use a loop:

for ($i = 1; $i <=2; $i++) {
    mysql_select_db("database".$i, $connect); 
    $sql = mysql_query("INSERT INTO table".$i." (contact_first, contact_last, contact_email) VALUES('abc','xyz','abc@abc.com')");
    mysql_close;
}

However, using mysql_* is strongly NOT recommended, as it is deprecated from the last stable PHP release and is considered unsafe. Use PDO or MySQLi instead. PHP's official site suggests the article "Choosing an API": http://www.php.net/manual/en/mysqlinfo.api.choosing.php

answered Jul 22 '13 at 13:28

Royal Bg

6,988
1
18
24

Why there is a loop for only two databases just to connect/insert and close this is not a generic solution – M Khalid Junaid Jul 22 '13 at 13:32
If the architecture increases, only the loop will be changed. What do you suggest as a solution, manually build each query? I would, but if the queries are exactly the same, I would use a loop – Royal Bg Jul 22 '13 at 13:37
But think for a while no one will have the databases name like `...1,..2,..3 and so on` – M Khalid Junaid Jul 22 '13 at 13:46
Why not :) We have a game which has local versions in 5 different countries. Every local version has its own database. i.e. game_en, game_fr, game_de, etc. which contains `players`, `profiles` etc same tables. If I want to update player's credits in each version, I would iterate through the language tags, append to `game_` and run the query – Royal Bg Jul 22 '13 at 13:49

score 0 · Answer 5 · answered Jul 22 '13 at 13:30

Well, that's how i do it...

1 - connect --> that you are doing right 2 - check for errors 3 - USE a database (1) you want to put data in (and not 'SELECT') 4 - check for errors 5 - now INSERT items into the database THAT IS BEING USED - that is (1) 6 - check for errors 7 - USE the other database (2) 8 - check for errors 9 - INSERT the data into (2) - because that is the one in use now 10 - check for errors

Yes, be paranoid :P Hope this helps

How to insert data in two different tables of two different database in php

5 Answers5

Linked