two different mysql servers in one script?

[garyed]

Is it possible to run a single script that can select from a mysql database from one server & then transfer the results to another server. The reason is that my webhost (1and1) does not have a way to import from their phpmyadmin program so I can't transfer the work I do on my localhost mysql databases.  I've written a simple script that will copy tables from one database into a new database but I can't figure out if there's a way to copy from my localhost to my webhost.

include "login_host.php";
$db = "glass";
$_table = "shade";
$db_new= "test3";
$db_create = "create database $db_new";
if (mysql_query($db_create)){  echo "OK - database";}
else{ echo "database not created".mysql_error();}
mysql_select_db($db_new);
if (mysql_query("create table $_table like glass.$_table"))
{ echo "OK - table"; }
else { echo "table not created".mysql_error();}
mysql_query ("insert into $_table select * from $db.$_table");  
mysql_close($connect);

 

   

[jcbones]

Export your database into CSV files (1 table per file).

Edit: Upload file to the server.

 

Use this command to import.

 

LOAD DATA INFILE 'table.csv' REPLACE INTO TABLE `table` FIELDS TERMINATED BY ',' IGNORE 1 LINES

[garyed]

I'm not sure where the code goes.

Do I put it in a php file or do i use it in phpmyadmin or what?

[garyed]

Does this mean that it can't be done through a php script?

 

 

 

 

[Mchl]

It can't be done the way you would like it to do ("create table $_table like glass.$_table") because for such query to work, both databases NEED to be on one server.

Your best bet is probably to contact your hosting, and ask them if they will import data for you if you send them a sql dump.

[jcbones]

Does this mean that it can't be done through a php script?

 

No, It can be done.

 

You would need a script that looks something like:

<?php
if(isset($_POST['submit'])) {
include('config.php');  //path to your database connection.
$save_path = 'upload/'; //path you would like to save your database.sql file.
$target = $save_path . $_FILES['file']['name'];
if(move_uploaded_file($_FILES['file']['tmp_name'], $target)) {

} 
else	{
		echo "Sorry, there was a problem uploading your file. <br/> Importing Data has been halted!<br/>";	
}

$file = file_get_contents($target);
//removing the standard comments added to a .sql file.
$pattern[] = '/--(.*)$/m';
$pattern[] = '/^\/\*(.*?)$/m';
$pattern[] = '/SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";/';
//replacing the ';' with a newline statement.  This will let us seperate the inserts without bothering any semi-colons inside the queries.
$pattern[] = '/;$/m';

$replace[] = '';
$replace[] = '';
$replace[] = '';
$replace[] = '__NEWLINE__';

$file = preg_replace($pattern,$replace,$file);

//split the queries.
$sql = explode('__NEWLINE__',$file);
foreach($sql as $value) {
	$value = trim($value);
	if($value != '') {
		if(mysql_query($value)) {
			$import[] = 1;
		}
		else {
			echo 'Query failed: ' . trim($value) . '<br/>';
			$import[] = 2;
		}
	}
}
if(in_array(2,$import)) {
	echo 'A part of the import process has failed! Please try again!';
}
else {
	echo 'Database Updated!';
}
}

?>

<form action="" method="post" enctype="multipart/form-data">
<input type="file" name="file"/>
<input type="submit" name="submit" value="Upload"/>
</form>

 

And then do a db dump to a non-compressed file, one table per file.

 

Of course, there could be errors in there.  But, it will get you started.