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
– Lawrence Cherone, Commented Nov 22, 2011 at 7: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
– GordonM, Commented Nov 22, 2011 at 7:07

jmkeyes · Accepted Answer · 2011-11-22 07:08:37Z

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, 2011 at 7:08

jmkeyes

3,77119 silver badges20 bronze badges

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

Comments

Eljakim · Accepted Answer · 2011-11-22 07:46:30Z

1

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.

answered Nov 22, 2011 at 7:46

Eljakim

6,9372 gold badges18 silver badges17 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:27:36Z

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

ping localhost · Accepted Answer · 2011-11-22 07:17:44Z

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

Collectives™ on Stack Overflow

Connecting Another Database in MySQL while loop

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related