MYSQL, PHP two connections inside while loop

Question

I have two databases on the same MYSQL server.

I want to be able to query one table on one database, and use the results for an insert into the other table (on the other data base). I've tried moving the mysql_select_db lines around to no avail. Please note this is a one off internal script so security is not a concern (Don't want to us mysqli)

<?php
// Connecting, selecting database
$link1 = mysql_connect('127.0.0.1', 'username', 'password', true)
or die('Could not connect: ' . mysql_error());
$link2 = mysql_connect('127.0.0.1', 'username', 'password', true)
or die('Could not connect: ' . mysql_error());
mysql_select_db('db1', $link1) or die('Could not select database');
mysql_select_db('db2', $link2) or die('Could not select database');

// Performing SQL query
$query = "select fields from table";
$result = mysql_query($query,$link1) or die('Query failed: ' . mysql_error());

while ($row = mysql_fetch_array($result)){

$querynew = "insert into table (blah,blah) values ('$row['name']',$row['name2']')";
mysql_query($querynew, $link2);
}

Despite your insert string is broken and will show T_ENCAPSED_AND_WHITESPACE, you can try different approach, naming databases in the query - SELECT fields FROM db1.table ... INSERT INTO db2.table... — Ivan Yonkov
– Ivan Yonkov, Commented Feb 19, 2014 at 20:36
Thanks Royal Bg - The insert was an example, which I typed badly! I will try your suggestion. — Robert Fakes
– Robert Fakes, Commented Feb 19, 2014 at 20:39
@user3329860 think for using mysqli / PDO because mysql_ lib is gone.. Accessing to second DB from the first one is option but its only if you are sure that they will be together with the same user... If its such case perhaps you can merge them into 1 DB.. — Svetoslav
– Svetoslav, Commented Feb 19, 2014 at 20:48

MonkeyZeus · Accepted Answer · 2014-02-19 21:05:02Z

3

You can use plain SQL for this to minimize traffic across the wire:

INSERT INTO `db2.tbl1` (`field1`,`field2`)
    SELECT `field1`, `field2` FROM `db1.tbl2` WHERE `someCondition`='IsMet'

edited Feb 19, 2014 at 21:05

answered Feb 19, 2014 at 20:37

MonkeyZeus

20.8k4 gold badges41 silver badges83 bronze badges

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

1 Comment

Robert Fakes Over a year ago

Thank you MonkeyZeus and RoyalBG

Collectives™ on Stack Overflow

MYSQL, PHP two connections inside while loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related