SELECT from MSSQL and INSERT into MySQL

[Anon]

Ok, what I am trying to do is run a query on an MS SQL database, then input the results into another database, either MS Access via ODBC or into a MySQL table.

I have no problem getting the data from the MSSQL database and doing whatever with it in a web page, but what I can't work out is how to put the retrieved data into a different database.

Could someone tell me how to achieve this?

What I want to happen is this:

User clicks button on intranet page ->

PHP script runs query on MSSQL database ->

Results of query get INSERTed into MySQL(or Access) database.

I am currently just using a normal loop to get the data.

Thanks

[sarahk]

I'd use classes to make this simpler.

Have a $dbSource and $dbDest objects which can handle your various database calls. That simplifies your code. The two objects will be based on different classes as each must know about the database they handle.

Then use $dbSource to query the source database and return the rows of data

Loop through the rows and in each loop construct an insert statement for the MySql database. Use $dbDest to run the insert statement(s).

[Anon]

Thanks for the response!

Ok, I understand the general concept, but how do I go about putting this into coding terms?

Do I open the connection to both databases prior to initialising the loop? Or is it a matter of connecting to the mysql database EACH time the loop is passed?

I would appreciate an example of the code required!

Thanks.

[sarahk]

I'd open a single connection to each database at the beginning and recycle.

Hard to give exact examples because
a) it depends on whether you do use a database class object (strongly recommended especially for newbies) and which you use
b) it depends on the data you are retrieving and the various table structures.

Feel free to post the info here and your work in progress for us to take a look at.

[Anon]

Here is the code currently. It works fine for retrieving data from the MSSQL server but it stuffs it up when inserting into mysql, it only inserts the first row.

Most importantly though, is this the right way to connect to two databases at once? Do I need to reconnect with m*sql_pconnect()each time in the loop?

Thanks.

<?

//set variables for connecting to mssql

$mshostname = "MSSQL SERVER";
$msusername = "";
$mspassword = "";
$msdbName = "";

//set variables for connecting to mysql

$myhostname = "MYSQL SERVER";
$myusername = "";
$mypassword = "";
$mydbname = "";
$mytablename = "";

//connect to mssql server and select database

mssql_pconnect($mshostname,$msusername,$mspassword) OR DIE("MSSQL Database connection failed.");

mssql_select_db($msdbName) or DIE("MSSQL DB unavailable");

//connect to mysql server, select database and delete contents

mysql_pconnect($myhostname,$myusername,$mypassword) OR DIE("MySQL Database connection failed.");

mysql_select_db($mydbname) or DIE("MySQL DB unavailable");

$sql_delete = "DELETE FROM paul WHERE id>=0";

mysql_query($sql_delete);

//query to get required fields from finesse

$sql_out = "SELECT Account.[Acc_Reference], Account.[Acc_Description],Acc_Template.[AT_Description],Account_Balances.[AB_Actual], Account_Balances.[AB_Budget], Account_Balances.[AB_On_Order] FROM

{ oj ([Finesse].[dbo].[Account] Account
INNER JOIN [Finesse].[dbo].[Account_Balances] Account_Balances ON Account.[Acc_Id] = Account_Balances.[Acc_Id]) INNER JOIN [Finesse].[dbo].[Acc_Template] Acc_Template ON Account.[AT_Id] = Acc_Template.[AT_Id]}WHERE
Account_Balances.[FY_Id] = 6";

//run the query on the finesse database

$query = mssql_query($sql_out);

printf("<table border=1 width=100%%>");

//loop to get each row from mssql

	while ($line = mssql_fetch_array($query)) {

//print results in html table

	echo "<tr><td>",$line["Acc_Reference"];
	echo "</td><td>",$line["Acc_Description"];
	echo "</td><td>",$line["AB_Budget"];
	echo "</td><td>",$line["AB_Actual"];
	echo "</td><td>",$line["AB_On_Order"];
	echo "</td></tr>";

	$acc_ref = $line["Acc_Reference"];
	$acc_des = $line["Acc_Description"];
	$ab_bud = $line["AB_Budget"];
	$ab_act = $line["AB_Actual"];
	$ab_ono = $line["AB_On_Order"];

//connect to mysql server, select db and insert row

	mysql_pconnect($myhostname,$myusername,$mypassword) OR DIE("MySQL Database connection failed during loop.");

	mysql_select_db($mydbname) or DIE("MySQL DB unavailable during loop.");

	$sql_in = "INSERT INTO $mytablename (Acc_Reference,Acc_Description,AB_Budget,AB_Actual,AB_On_Order) VALUES ('$acc_ref','$acc_des','$ab_bud','$ab_act','$ab_ono')";

	mysql_query($sql_in);

//reconnect to mssql server

	mssql_pconnect($mshostname,$msusername,$mspassword) OR DIE("MSSQL Database connection failed during loop.");

	mssql_select_db($msdbName) or DIE("MSSQL DB unavailable during loop.");

} //close loop

printf("</table>");

?>

[Anon]

Ok, I have the script entering the right data in the right places...BUT... it only writes 254 records into the MySQL database. It shows all the records on screen so I think it must be something to do with settings/limitations in PHP and/or MySQL.

Is there a limit to the number of transactions in a given period of time with PHP? Because I am working with a reasonably large database... around 1700 records at the moment, and making all those connections has to be taking it's toll on the server(s).... is there an easier way??

Thanks.