from sql server to mysql with php

Asked

Viewed 139 times

4

I have a bank SqlServer and I need to send data from a table to a Mysql database via php script.

OPENQUERY will result within the condition I have (php script)?

What’s the best way to do it?

The code I use to connect the banks:

Sqlserver: 

$conMsSql = odbc_connect('Banco_MsSql','User_MsSql','Pw_MsSql');

Mysql: 

$hostname_mysql = "localhost";
$database_mysql = "banco_mysql";
$username_mysql = "user_mysql";
$password_mysql = "pw_mysql";
$Conn = mysql_pconnect($hostname_mysql, $username_mysql, $password_mysql) or trigger_error(mysql_error(),E_USER_ERROR);
$DBase = mysql_select_db($database_mysql,$Conn) or die(mysql_error());

And it would look something like this:

$query = mysql_query('insert into tabela1 ( col1, col2, col3 )
 select sqlserver.tab1.col1, sqlserver.tab1.col2, sqlserver.tab1.col3 from sqlserver.tab1')
php mysql sql server

  • Are many records? tried to make two connections to or mssql and another to mysql? if you have source code edit the question and ask it.

  • 2

    You need to make a routine of it, or you’ll make this import all at once?

  • Will be a routine.

  • You will need to create a crontab on the server to run your script at the time you set (as the windows task scheduler), then you need to open the two connections in your script and create a routine that runs mysqldump to create the sql file, it can be with a random hash: 123.sql, already processing the data. Then import this 123.sql file into the sql server database, using mssql_query(), or sqlrv_query().

  • When you finish creating the file, you trigger the import event.

  • You don’t necessarily need a php script to do this.

  • You can create a procedure in the MySQL that manages the file in a folder. And capture that file in a procedure in the SQL Server.

  • As it is a routine, it will always be running according to the defined execution space time between the processing.

  • I only have Sqlserver database query permission, then select a table and send to Mysql...

  • These fields, tables are equal?

Show 5 more comments

1 answer

1

If your database is too large, your routine should use a script of backup mysqldump, write this file SQL in a folder and finally read this file in the Sql Server through a procedure.

Due to the size of the database, possibly large, the ideal would be to use a language such as Java and keep that applet/script on your server using thread, thus the Java would deal with such migration without major problems, such as timeout or other controls/limits than languages serve-side possess.

Browser other questions tagged php mysql sql server

You are not signed in. Login or sign up in order to post.