Transfer data between two databases

Hi,

Is there a way to select data from one database and insert them into another but using the same php page? I want to copy a table from one database to another but doing it with export or backup will not work (I have a strange problem with encoding).

Thanks!
infodiggerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
steelseth12Connect With a Mentor Commented:
I dont think the above query can  run from php.

If it doesnt work try the code below
<?
### CONNECT TO 1ST DATABASE ########
 
$db1 = mysql_connect("localhost","username","pass");
 
mysql_select_db("dbname",$db1);
 
$table = "table_name"; // put name of your table here
 
 
$q = mysql_query("SHOW CREATE TABLE ".$table);
 
 
$create = mysql_fetch_row($q);
 
 
	$value = '';
	
	$result = mysql_query('SELECT * FROM `'.$table.'` ');
	
	$i = 0;
	
	while ($row = mysql_fetch_row($result)){
		
		$values = '';
		
		foreach ($row as $data){
		
			$values .= '\'' . addslashes($data) . '\', ';
		
		}
		
		$values = substr($values, 0, -2);
		
		if(!$i) {
		
			$value .= ' VALUES (' . $values . ') ' ;
		
		}else{
		
			$value .= ' ,(' . $values . ') ' ;
		
		}
		$i++;
		
	}
		
	$value = "INSERT INTO `".$table."` ".$value.";";
	
 
 
### CONNECT TO 2ST DATABASE ########
 
 
$db2 = mysql_connect("localhost","username","pass");
 
mysql_select_db("dbname",$db2);
 
	
mysql_query($create[1]) or die(mysql_error());
 
mysql_query($value) or die(mysql_error());

Open in new window

0
 
Aleksandar BradarićConnect With a Mentor Software DeveloperCommented:
Try this:
CREATE TABLE db2.your_table SELECT * FROM db1.your_table_too

Open in new window

0
 
infodiggerAuthor Commented:
Thanks!
0
All Courses

From novice to tech pro — start learning today.