fetch data from db and insert into another db

[fer0an]

hello

I've 2 database and I want fetch mysql rows from db1 then insert it into db2.

anyone can help me.

// Connecting db1, selecting database

$con = mysql_connect($dbhost, $dbuser, $dbpass)

   or die('Could not connect: ' . mysql_error());


//fetch data from db1


mysql_select_db($dbname) or die('Could not select database');

$query = mysql_query("SELECT * FROM  admcomments")
or die(mysql_error());  
$row = mysql_fetch_array( $query );
$id= $row['id'];
$title = $row['comment'];
   
for ($id=1;$id<=10;$id++)

{
//connecting db2 
$con1 = mysql_connect($dbhost1, $dbuser1, $dbpass1)

   or die('Could not connect: ' . mysql_error());
    mysql_select_db($dbname1) or die('Could not select database2');
      
$insert1 = "INSERT INTO `comments` ( `id` , 'title');

}

 

the problem of this code is :

after insert data into db2 , rows 1 -10 is the same title data

[Catfish]

there seems to be a lot wrong with that code:

 

1. you are only fetching data for 1 row and placing it into $id and $title. you are not building an array of data from your row results

2. you are using $id in your for loop, but not the original $id variable - this $id will only exist in the scope of the for loop and will be an integer value from 1 to 10.

3. you are connecting to/selecting the database 10 times via the for loop for no good reason.

4. the INSERT query is incorrect. you are not specifying the values for the fields.

 

[fer0an]

anyone can help me to correcting my code?

[Catfish]

1.

//fetch data from db1
mysql_select_db($dbname) or die('Could not select database');

$query = mysql_query("SELECT * FROM  admcomments")
or die(mysql_error()); 
while ($row = mysql_fetch_array( $query ))
{
$id[] = $row['id'];
$title[] = $row['comment'];
}

2/3. change for loop to a foreach loop, place database connection and select OUTSIDE the loop.

//connecting db2
$con1 = mysql_connect($dbhost1, $dbuser1, $dbpass1)
foreach ($id as $key => $value)
{
   // work on building insert statement in here
   print($title[$key]); // this will give you the title that corresspond with the current value of id
}

4. I haven't checked, but I'd think you can insert a whole heap of data in one query instead of calling INSERT multiple times. this will likely increase the speed of your script, and lower the load of the server. Check out this handy page for MYSQL commands: http://www.bios.niu.edu/johns/bioinform/mysql_commands.htm

See also: http://www.brainbell.com/tutorials/MySQL/Inserting_Multiple_Rows.htm