trying to make script to move data from one mysql to another

[kuper20]

Hello,

So I have two different websites that are using some tables that are the same. One gets the tables updated, and other other needs these updated tables to work correctly. Now I'm trying to make a click script that accesses the updated tables, inserts the new rows, and then empties the updatable table again.

I've gotten to a point where I can echo a dump ( almost the same thing phpmyadmin would output in it's export window) on my second website, and run it in that phpmyadmin sql area to make it insert the info, but if I run mysql_query() on that same echo it it says my sql syntax is wrong or something like that. I'm now confused :p

Here's the code I use to dump my table:

$tab_status = mysql_query("SHOW TABLE STATUS");
while($all = mysql_fetch_assoc($tab_status)):
 $tbl_stat[$all[Name]] = $all[Auto_increment];
endwhile;
unset($backup);
$tables = mysql_list_tables("myDatabase");
$tabs[0] = "myTable"; //this is the table i'm trying to move
$backup .= "CREATE TABLE IF NOT EXISTS `$tabs[0]` (";
$res = mysql_query("SHOW CREATE TABLE $tabs[0]");
while($all = mysql_fetch_assoc($res)):
 $str = str_replace("CREATE TABLE `$tabs[0]` (", "", $all['Create Table']);
 $str = str_replace(",", ",", $str);
 $str2 = str_replace("`) ) TYPE=MyISAM ", "`)) TYPE=MyISAM ", $str);
 $backup .= $str2."; ";
endwhile;
$data = mysql_query("SELECT * FROM $tabs[0]");
while($dt = mysql_fetch_row($data)):
$backup .= "INSERT INTO `$tabs[0]` VALUES('$dt[0]'";
for($i=1; $i<sizeof($dt); $i++):
 $backup .= ", '$dt[$i]'";
endfor;
$backup .= ");\n";
endwhile;
$fName = "mysql ".date("d-m-Y H:i").".txt";
echo $backup;

I didn't write this script, I found it and made some changes though. I know what's going on everyone except for the str_replace functions, I'm not sure what's going down there, just that it outputs it correctly.

Any help would be greatly appreciated.

[eelixduppy]

First off, you said you were getting errors from MySQL - what are they?

Secondly, why are you doing this in the first place? Why not use one database and one set of tables for both sites. Why are you copying data and moving it around?

[kuper20]

well, it just says I have an error in my sql syntax ( which doesn't make sense ) around...and then it displays some part of my sql query.

also, the reason I'm doing this is because one website is on the web and one is internal...it's for security reasons.

[eelixduppy]

Can you give the entire MySQL error so that we can debug this -- the error and the query part, please. :)

[kuper20]

Ok, so here is what is echoed, exactly what I run in mysql_query().

CREATE TABLE IF NOT EXISTS `testMove` ( `test1` int(11) NOT NULL, `test2` text NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `testMove` VALUES('1', 'val1'); INSERT INTO `testMove` VALUES('2', 'val2');

If I copy and paste the above into phpmyadmin sql query area it works, but not when I run it in php in mysql_query()

This is what it says when I run mysql_error() on the query that didn't work:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; INSERT INTO `testMove` VALUES('1', 'val1'); INSERT INTO `testMove` VALUES('2',' at line 4

The code it displayed in the error has changed as I tried to fix it, it always seems to be some random part of the query, I can't tell.

[eelixduppy]

The reason why you are getting this error if you are concatenating two queries together and trying to run them as one using mysql_query(). You cannot do this like this, and you'll have to run them separately:

mysql_query($query_one);
mysql_query($query_two);

Also note that you don't need the trailing semi-colon on your queries when using this function.

[kuper20]

Ok, thanks, that's definitely part of the problem, but now I'm getting a "query is empty" error. I'm doing it like this now: Creating the table works, it's the inserting lines that say the query is empty.

mysql_query("CREATE TABLE IF NOT EXISTS `testMove` ( `test1` int(11) NOT NULL, `test2` text NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1");

mysql_query("INSERT INTO `testMove` VALUES('1', 'val1')");
mysql_query("INSERT INTO `testMove` VALUES('2', 'val2')");

[eelixduppy]

That doesn't make much sense. Your queries look fine. Can you please give the full error again that you are getting from MySQL.

[kuper20]

ok that last part was actually right I was just having some array problems. I finally got it working, thanks for your help

[eelixduppy]

Cool, glad you got everything working correctly :)