CODEWITHSUNDEEP
phpmysql
Haren Sarma
Haren Sarma

Reputation: 2553

Connecting Another Database in MySQL while loop

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.

Upvotes: 0

Views: 1600

Answers (4)

Eljakim
Eljakim

Reputation: 6937

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.

Upvotes: 1

ping localhost
ping localhost

Reputation: 479

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

Upvotes: 0

jmkeyes
jmkeyes

Reputation: 3771

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.

Upvotes: 1

BumbleShrimp
BumbleShrimp

Reputation: 2200

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

Upvotes: 0

Related Questions