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','[email protected]')"); //insert record to first table
$sql1 =mysql_query("INSERT INTO database2.table2 (contact_first, contact_last, contact_email) VALUES('abc','xyz','[email protected]')"); //insert record to second table

please suggest me corrections for above code to insert data.

this may help you Two db connetion

Ravi Kant Mishra
– Ravi Kant Mishra

2013-07-22 13:21:27 +00:00
Commented Jul 22, 2013 at 13:21 — Ravi Kant Mishra
– Ravi Kant Mishra, Commented Jul 22, 2013 at 13:21

Dhaval Bharadva · Accepted Answer · 2013-07-22 13:31:34Z

2

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.

answered Jul 22, 2013 at 13:31

Dhaval Bharadva

3,0852 gold badges26 silver badges36 bronze badges

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

Comments

zoran404 · Accepted Answer · 2013-07-22 13:37:19Z

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, 2013 at 13:37

answered Jul 22, 2013 at 13:21

zoran404

3,0062 gold badges30 silver badges48 bronze badges

2 Comments

GFP Over a year ago

But he has 1 connection, with 2 different db selections. Actually it should work like this.

M Khalid Junaid Over a year ago

@zoran404 it would be more useful if you add as an example by taking his code , by the way good technique

DevZer0 · Accepted Answer · 2013-07-22 13:24:22Z

0

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);

answered Jul 22, 2013 at 13:24

DevZer0

13.5k7 gold badges29 silver badges54 bronze badges

1 Comment

M Gaidhane Over a year ago

Thanks for you answer it working after adding $connect2 = mysql_connect("localhost","root","",true);

Ivan Yonkov · Accepted Answer · 2013-07-22 13:28:19Z

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','[email protected]')");
    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, 2013 at 13:28

Ivan Yonkov

7,0291 gold badge20 silver badges25 bronze badges

4 Comments

M Khalid Junaid Over a year ago

Why there is a loop for only two databases just to connect/insert and close this is not a generic solution

Ivan Yonkov Over a year ago

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

M Khalid Junaid Over a year ago

But think for a while no one will have the databases name like ...1,..2,..3 and so on

Ivan Yonkov Over a year ago

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

user2605249 · Accepted Answer · 2013-07-22 13:30:19Z

0

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

answered Jul 22, 2013 at 13:30

user2605249

Collectives™ on Stack Overflow

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

5 Answers 5

Comments

2 Comments

1 Comment

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related