Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1162
  • Last Modified:

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!
0
infodigger
Asked:
infodigger
2 Solutions
 
Aleksandar BradarićSoftware DeveloperCommented:
Try this:
CREATE TABLE db2.your_table SELECT * FROM db1.your_table_too

Open in new window

0
 
steelseth12Commented:
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
 
infodiggerAuthor Commented:
Thanks!
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now