Moving Data from mssql to mysql

UrNsO · Feb 22, 2009

I have 2 separate DB's one is on MSSQL and the other is on MySQL. I was wondering if there was a PHP way to copy about 25,000 records (contaning only 3 fields) from one to the other?

Currently I'm able to connect to MSSQL and create an array that contains the 25,000 records. If I echo my $array I get a page full of comma separated values. Nice and neat. (just the way I set it up )

What I can't seem to do is get is the array to dump into mysql.

Now please tell me if this is something I shouldn't even be doing because the number of records. I wasn't sure when I started if this was going to work. I've been exporting a CSV using Excel and Query. Then just importing into MySQL from phpmyadmin. Very time consuming considering it has to be done multiple times a day.

Here is my code that doesn't work (except for the MSSQL part works fine).

<?php

//Selecting all WADOCO, WALITM and WADL01 records

$dbserver="removed";
$dbuser="removed";
$dbpass="removed";
$dbname="PD_Repository";

$dbconn=mssql_connect($dbserver,$dbuser,$dbpass);
mssql_select_db($dbname, $dbconn) or die("Unable to Open The Database");

$query="SELECT F4801.WADOCO, F4801.WALITM, F4801.WADL01
FROM PD_Repository.PRODDTA.F4801 F4801
ORDER BY F4801.WADOCO";

$qt=mssql_query($query);
while($nt=mssql_fetch_array($qt)){
echo "$nt[WADOCO], $nt[WALITM], $nt[WADL01]";
echo "<br>";
} 

$mydbserver="removed";
$mydbuser="removed";
$mydbpass="removed";
$mydbname="jdewo";

$mydbconn=mysql_connect($mydbserver,$mydbuser,$mydbpass);
mysql_select_db($mydbname, $mydbconn) or die("Unable to Open The Database");

mysql_query("INSERT INTO workorders(WADOCO, WALITM, WADL01) VALUES('".$nt[WADOCO]."', '".$nt[WALITM]."','".$nt[WADL01]."') ");  


mssql_close($dbconn);
mysql_close($mydbconn);


?>

I can't get this to insert anything into the DB. Can anyone assist me on this? Thx!

Weedpacket · Feb 23, 2009

All the MSSQL part does is output what it selects onto the screen. You don't do anything with MySQL until you've finished with MSSQL.

Instead of just echoing the MSSQL results, save them to a CSV file (using [man]fputcsv[/man]), and then have MySQL read the CSV file directly.

UrNsO · Feb 23, 2009

Thx! I went to that link and took some of the code. I am still doing something wrong though. I made a .csv file in the same directory as this script. The page loads when called but nothing is dumped to .csv file.

Here's my code:

<html>
<head>
<title>JDE To Mysql Dump Script</title>
</head>
<body>
<?php

//Selecting all WADOCO, WALITM and WADL01 records

//connection removed
$dbname="PD_Repository";

$dbconn=mssql_connect($dbserver,$dbuser,$dbpass);
mssql_select_db($dbname, $dbconn) or die("Unable to Open The Database");

$query="SELECT F4801.WADOCO, F4801.WALITM, F4801.WADL01
FROM PD_Repository.PRODDTA.F4801 F4801
ORDER BY F4801.WADOCO";

$qt=mssql_query($query);
while($nt=mssql_fetch_array($qt)){
echo "$nt[WADOCO], $nt[WALITM], $nt[WADL01]";
echo "<br>";
} 


$list = array($nt);

$fp = fopen('file.csv', 'w');

foreach ($list as $line) {
    fputcsv($fp, split(',', $line));
}

fclose($fp);


mssql_close($dbconn);


?> 
 </body>
</html>

I having a hard time understanding my array. I know the data is there because it is showing on my page. I tried also $nt[field] and still got nothing. Any ideas?

tomhath · Feb 23, 2009

You never had an array, just a bunch of echo statements. Move the fputcsv into the while loop (untested, not sure about the call to fputcsv this way):

<html>
<head>
<title>JDE To Mysql Dump Script</title>
</head>
<body>
<?php

//Selecting all WADOCO, WALITM and WADL01 records

//connection removed
$dbname="PD_Repository";

$dbconn=mssql_connect($dbserver,$dbuser,$dbpass);
mssql_select_db($dbname, $dbconn) or die("Unable to Open The Database");

$query="SELECT F4801.WADOCO, F4801.WALITM, F4801.WADL01
FROM PD_Repository.PRODDTA.F4801 F4801
ORDER BY F4801.WADOCO";

$qt=mssql_query($query);
$fp = fopen('file.csv', 'w');
while($nt=mssql_fetch_array($qt)){
  echo "$nt[WADOCO], $nt[WALITM], $nt[WADL01]";
  echo "<br>";
  fputcsv($fp, $nt);
}
fclose($fp);


mssql_close($dbconn);

UrNsO · Feb 23, 2009

Ok if I do a print_r($nt) I get the following:
Array ( [0] => 100 [WADOCO] => 100 [1] => 300806 [WALITM] => 300806 [2] => Down time Work Order [WADL01] => Down time Work Order )

So I am getting an array from the mssql query. I just don't know how to get it into mysql. I really was looking for something automatic and didn't want to mess with importing a csv file anymore.

I tried your put csv that work fine. Thank you! I was hoping to not have to do that anymore. Importing this file 5-10 times a day will be a tedious process. Can anyone help with a more automatic approach?

UrNsO · Feb 23, 2009

Ok I think I'm getting closer on this. But I still need some help.

I changed the mssql query to return an assoc array. Then I used the following code to show keys and values.

<?php

//connect to Mysql

$mydbconn=mysql_connect($mydbserver,$mydbuser,$mydbpass);
mysql_select_db($mydbname, $mydbconn) or die("Unable to Open The Database");

//connect to msssql and begin query


$dbconn=mssql_connect($dbserver,$dbuser,$dbpass);
mssql_select_db($dbname, $dbconn) or die("Unable to Open The Database");

$query="SELECT F4801.WADOCO, F4801.WALITM, F4801.WADL01
FROM PD_Repository.PRODDTA.F4801 F4801
ORDER BY F4801.WADOCO";

$qt=mssql_query($query); 

while($nt=mssql_fetch_assoc($qt)){ 
foreach($nt as $key => $value)
echo $key." = ".$value."<br />";

}


mysql_close($mydbconn);
mssql_close($dbconn);
?>

What this give me on an html page is

WADOCO = 100
WALITM = 300806 
WADL01 = Down time Work Order 
WADOCO = 1000
WALITM = 300805 
WADL01 = Down Time Work Order

So it is showing me the key and the value and there is a huge amount of this on the page. all 25000 records are being shown.

Now how can I incorporate the mysql insert command for key and value??

mysql_query("INSERT INTO workorders ($key) values ($value)");

this doesn't work of course but please tell me I'm on the right track. :evilgrin:

tomhath · Feb 24, 2009

If you don't want to script the bulk load from csv you could just call the mysql insert inside the while loop. There's nothing special about it. Something like this but the quotes are probably wrong:

mysql_query("INSERT INTO workorders (WADOCO, WALITM, WADL01) values ('$nt[WADOCO]', '$nt[WALITM]', '$nt[WADL01]')");

Weedpacket · Feb 24, 2009

tomhath wrote:
If you don't want to script the bulk load from csv you could just call the mysql insert inside the while loop. There's nothing special about it.

Although note that having MySQL bulk-load the CSV file would be much faster (we are talking about 25,000 records, after all).

UrNsO wrote:
Then I used the following code to show keys and values.

FYI, PHP provides the [man]print_r[/man] and related functions for this sort of debugging.

tomhath · Feb 25, 2009

Weedpacket;10904247 wrote:
Although note that having MySQL bulk-load the CSV file would be much faster (we are talking about 25,000 records, after all).

True, although 25,000 rows isn't really all that much.

Of course if you want to automate the whole thing just set up replication (because that's what you're trying to do here): http://blogs.techrepublic.com.com/howdoi/?p=112

Weedpacket · Feb 25, 2009

tomhath wrote:
True, although 25,000 rows isn't really all that much.

No, 25,000 rows isn't really all that much, but 25,000 consecutive insert statements?

tomhath · Feb 26, 2009

Yea, would probably take several minutes. Good chance the page would time out. Scripting/scheduling a bulk load is definitely better. How about using DTS or SSIS in a SQL Server Agent job? PHP doesn't seem like the right tool for this.

shawty · Feb 26, 2009

Dunno why everyone is trying to write this to a CSV and then read it back....


<?php

  // Set time limit to infinate, stop the PHP
  // script from timing out this overrides
  // anything set in php.ini
  set_time_limit(0);

  // MSSQL Details
  $dbserver = "removed";
  $dbuser   = "removed";
  $dbpass   = "removed";
  $dbname   = "PD_Repository";
  $dbconn   = 0;

  // MySQL Details
  $mydbserver = "removed";
  $mydbuser   = "removed";
  $mydbpass   = "removed";
  $mydbname   = "jdewo";
  $mydbconn   = 0;

  // Connect to MS-Sql server
  $dbconn=mssql_connect($dbserver,$dbuser,$dbpass);
  mssql_select_db($dbname, $dbconn) or die("Unable to Open The MS-Sql Database");

  // Setup static MS-Sql string
  $query="SELECT F4801.WADOCO, F4801.WALITM, F4801.WADL01
  FROM PD_Repository.PRODDTA.F4801 F4801
  ORDER BY F4801.WADOCO";

  // Open MySql connection
  $mydbconn=mysql_connect($mydbserver,$mydbuser,$mydbpass);
  mysql_select_db($mydbname, $mydbconn) or die("Unable to Open The MySql Database");

  // Execute the MS-Sql stament and loop over the data  

  $qt=mssql_query($query);
  while($nt=mssql_fetch_array($qt))
  {
    // DIsplay each row as we retrive it.
    echo "$nt[WADOCO], $nt[WALITM], $nt[WADL01]";
    echo "<br>";

// Take the data and insert it into the MySql database.
//
// Ensure that the table called work order is already set up and that it has
// 3 columns called WADOCO, WALITM and WADL01 set to the correct data types
//
$myquery = INSERT INTO workorders(WADOCO, WALITM, WADL01) VALUES('".$nt[WADOCO]."', '".$nt[WALITM]."','".$nt[WADL01]."');
mysql_query($myquery) or Die("MySQL Query Failed " . mysql_error());
  }

  // Close the connections
  mssql_close($dbconn);
  mysql_close($mydbconn);

?>

Will transfer the table just fine...

:-)

Cheers

Shawty

Weedpacket · Feb 26, 2009

shawty wrote:
Will transfer the table just fine...

And would involve 25,000 consecutive insert statements; something I at least am trying to avoid.

shawty · Feb 26, 2009

Hence why your trying to save the file as a CSV then bulk upload it to MySql I assume??

Well the horrible truth is when you do a bulk insert using the MySql bulk functions, all MySql does internally is translate them all to a series of consequence INSERT statements and perform the same task. :-)

I have both MsSql and MySql servers running on my home network, and this is by far the easiest and quickest way i've found of transfering table data from one to the other over the years.

If you have 25,000 rows of data to add into a MySql table, then at some point you will have to perform 25,000 Inserts, as far as i can see there is no way of getting round this.

Cheers

Shawty

UrNsO · Feb 26, 2009

Shawty Thx so much for you help. I have tried your code and I am getting this error:

Parse error: syntax error, unexpected T_STRING in D:\wamp\www\itemexport.php on line 51

It looks like it's somewhere around the mysql insert statement but I can't see anything wrong in your code. Isn't that error usually a missing quote or ;?? everything seems to be in place.

shawty · Feb 26, 2009

UrNso,

It is indeed usually beacuse of a missing punctuation, in this case i missed the " marks at either end of the mysql insert statment.

Change line 48 to:

$myquery = "INSERT INTO workorders(WADOCO, WALITM, WADL01) VALUES('".$nt[WADOCO]."', '".$nt[WALITM]."','".$nt[WADL01]."')";

and that should fix it.

Cheers

Shawty

UrNsO · Feb 26, 2009

Yup sure did. Thx again for your help!

I believe I am in needs of addslashes somewhere here. getting this error on the page.

MySQL Query Failed 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 'S 10GB ')' at line 1

The line that is being imported reads:

Leed's 10gb

I think it's that ' that is throwing it off.

Can you do an addslashes on insert?

shawty · Feb 26, 2009

Your problem there is embedded ' in the data your trying to insert.

There are two ways of approaching this.

AddSlashes on the data before it's inserted should work, or you can use str_replace("'","",$variable);

Personally i don't like embeding the ' char in data that i put in DB's as it causes alsorts of hard to track down and unforseen probs.

Which ever method you use, you'll have to modify the actual data your inserting.

How you do this is up to you, while you cant actually do it as part of the INSERT, you can chain the functions together where you make the Query string EG:

$myquery = "INSERT INTO workorders(WADOCO, WALITM, WADL01) VALUES('".addslashes($nt[WADOCO])."', '".addslashes($nt[WALITM])."','".addslashes($nt[WADL01])."')";

However for cleanliness and readability of code i would suggest doing it the following way:


$newWADOCO = addslashes( $nt[WADOCO] );
$newWALITM = addslashes( $nt[WALITM] );
$newWADL01 = addslashes( $nt[WADL01] );

$myquery = "INSERT INTO workorders(WADOCO, WALITM, WADL01) VALUES('".$newWADOCO."', '".$newWALITM."','".$newWADL01."')";

As you can see it's much more readable, and you can chop and change your functions quickly & easily, as well as combining them...

Cheers

Shawty

UrNsO · Feb 26, 2009

thank you so much for your help! everything works great! I was getting some blank fields in there on occasion (even with PK/Unique) but I just added a delete query for all blank records.

Thx again!

shawty · Feb 26, 2009

Anytime... just happy i could help.

Moving Data from mssql to mysql

UUrNsO

Weedpacket

UUrNsO

Ttomhath

UUrNsO

UUrNsO

Ttomhath

Weedpacket

Ttomhath

Weedpacket

Ttomhath

Sshawty

Weedpacket

Sshawty

UUrNsO

Sshawty

UUrNsO

Sshawty

UUrNsO

Sshawty