Connecting Another Database in MySQL while loop

Question

I'm trying to copy a database data to another database. I'm trying to use the below procedure which is not working. I'm new to MySQL and PHP

<?php 
require_once('conf.php');
?>
<?php 
$q=mysql_query("SELECT * FROM navroop_mlm.".memberlogtbl." WHERE totalleft >='7' AND totalright >='7'");
while($r=mysql_fetch_array($q)){
$id=$r['id'];
$qlnk=mysql_pconnect("localhost", "navroop_mlm", "guwahati0011*");
mysql_select_db("navroop_aip", $qlnk);

mysql_query("INSERT INTO member_login (id, name, username, password,sex) VALUSE ('$id', '".$r['name']."', '".$r['username']."', '".$r['password']."', '".$r['sex']."')");
}

Please help me. Here username , Password and server is same of both database.

Using persistent connections in such a way, may exceed the number of connections allowed by MySQL. are you getting errors? — Lawrence Cherone, Nov 22 '11 at 07:05
Don't connect to a database in a loop, it's going to hammer the performance of your code. And don't use mysql_pconnect either, it's more trouble than it's worth. — GordonM, Nov 22 '11 at 07:07

score 1 · Answer 1 · answered Nov 22 '11 at 07:08

1

Don't ever do this. It will not do what you think it will and it will cause infinite amounts of pain in the future. If you need to replicate data among two databases, use database replication or find another solution. Don't violate the SPOT rule.

answered Nov 22 '11 at 07:08

jmkeyes

3,751
17
20

score 1 · Answer 2 · answered Nov 22 '11 at 07:46

Uhm,

if the connection parameters for both databases are the same, you can just use the MySQL syntax of including the database name in the query:

INSERT INTO database2.member_login (id, name, username, password, sex)
SELECT id, name, username, password, sex from navroop_mlm.member_login;

This does what you want, i.e., select in one database, insert into another.

score 0 · Answer 3 · edited May 23 '17 at 12:27

This question has answers that can help you.

From the answer: "

$dbh1 = mysql_connect($hostname, $username, $password); 
$dbh2 = mysql_connect($hostname, $username, $password, true); 

mysql_select_db('database1', $dbh1);
mysql_select_db('database2', $dbh2);

Then to query database 1, do

mysql_query('select * from tablename', $dbh1);

and for database 2

mysql_query('select * from tablename', $dbh2);

"

I suspect that you can connect to the databases before your loop, then inside the loop use the second connection and your INSERT INTO query.

Furthermore, a superior solution would be to use PDO, which is a better alternative to the standard mysql_ functions, as well as more easily allow you to query a second database while looping through the first query's results. http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html#4.3 (a basic PDO tutorial).

score 0 · Answer 4 · answered Nov 22 '11 at 07:17

You are opening connections inside the loop and not closing them. As stated above , it might lead to exceeding the connection threshold of the mysql server.

Instead you can have separate connections

$conn1=mysql_connect("localhost", "navroop_mlm", "guwahati0011*");
$conn2=mysql_connect("localhost", "navroop_mlm", "guwahati0011*");
mysql_connect_db("navroop_mlm",$conn1);
mysql_connect_db("database2",$conn2);

$q=mysql_query("SELECT * FROM $memberlogtbl WHERE totalleft >='7' AND totalright >='7'",$conn1);

while($r=mysql_fetch_array($q))
{
$id=$r['id'];

mysql_query("INSERT INTO member_login (id, name, username, password,sex) VALUES ('$id', '".$r['name']."', '".$r['username']."', '".$r['password']."', '".$r['sex']."')",$conn2);
}

mysql_close($conn1);
mysql_close($conn2);

Connecting Another Database in MySQL while loop

4 Answers4