Migrating PHP code from MSSQL to MySQL

Question

I currently have a large web application that connects to MSSQL to retrieve its data. This web application contains hundreds of .php files. I have a new client, and he does not want to use MSSQL, but MySQL instead.

I searched and found many articles on how to transfer data. But that is not my problem. I know how to do this, in addition, we will start with an empty database, with the exception of a few default directories.

I need to know if there is a way to automatically change the mssql_query code to mysqli code. For instance:

$SqlText = 'SELECT a, b, c FROM table WHERE x=y'; if (!$result = mssql_query($SqlText) { die('Error'); } while ($row = mssql_fetch_assoc($result) { echo '<td>' . $row['a'] . '</td>'; echo '<td>' . $row['b'] . '</td>'; echo '<td>' . $row['c'] . '</td>'; } 

In something like:

 $SqlText = 'SELECT a, b, c FROM table WHERE x=y'; if (!$result = mssqli_query($db, $SqlText) { die('Error'); } while ($row = mssqli_fetch_assoc($result) { echo '<td>' . $row['a'] . '</td>'; echo '<td>' . $row['b'] . '</td>'; echo '<td>' . $row['c'] . '</td>'; } 

Also, as you can see, I used the old mssql_query code, which is not even supported in newer versions of PHP. Is there at least a tool that can change my code from mssql_query to a new version of sqlserv_query commands?

Any help is greatly appreciated.

Answer 1

I don’t think there is an absolutely automatic way. Because MySQL has a "LIMIT", for example, where MSSQL has a "TOP". Of course, this should not be a problem, but I'm not sure if complex MSSQL queries will translate 1: 1 to MySQL.

And suggest using the signs,, to point to MySql, it is a member of the database name, table or column. Example:

 $SqlText = 'SELECT a, b, c FROM table WHERE x=y'; 

Changed to

 $SqlText = 'SELECT `a`, `b`, `c` FROM `table` WHERE `x`=y'; 

Now you can create queries such as

 'SELECT `select` FROM `datetime` WHERE `limit` = 1' 

Otherwise, the words select, datetime and limit will affect the syntax of MySql.

Answer 2

You can use find & replace with your favorite IDE, for example, in netbeans, click in the project window, then edit, replace and enter the name of your function

You can also use php script as follows:

 $config = array('msssql_query'=>'mysqli_query' /*... all the functions*/) $dirs = array('./','subDirectory1','subDirectory2');/*all your sub dirs*/); foreach($dirs as $dir){ foreach(glob($dir.'/*.php') as $filename){ $filecontent = file_get_contents($filename); file_put_contents(str_replace($config,$filecontent,$filename)); } } 

Also, as @endyourif said, it’s better to wrap the database connection inside the class and interfaces.

 interface DAOInterface{ public function query($string); public function prepare($string); public function quote($string);//etc } interface DAOStatementInterface{ public function execute($array = array()); public function bind($value,$param,$type); /*etc*/ } 

or you can use PDO

Answer 3

I am really facing this problem recently. Discovered using MYSQL Workbench solved the problem of migrating the database from MSSQL to MYSQL. However, there is still a problem that although there are not many changes to the database, the PHP side codes are often used due to certain problems. Thus, it was difficult to transfer each file one at a time on a daily basis. Therefore, I researched and came up with a function that is still not 100%, and has its drawbacks, but solved my problem.

I used the Sublime Text Editor, and using this, I replaced all occurrences of mssql_ with sql_, and then turned on this function.

At first I tried to define the mssql_ functions, but it turns out I should actually disable mssql from the PHP module. This is normal if there is only one application using this PHP, but since there are a lot of them, I had to rewrite each page to a new function.

You can find my code here . But I can not guarantee that you will get the full result.