Migrate PHP code from MSSQL to MySQL

Question

I currently have a large web application that connects to MSSQL to fetch its data. This web app has hundreds of .php files. I have a new customer, and he doesn't want to use MSSQL, but MySQL instead.

I've searched and found lots of articles on how to migrate data. But that's not my problem. I know how to do that, besides we'll be starting with a blank database, except for a few default catalogs.

What I need to know is if there's a way to automatically change the mssql_query code to mysqli code. For example:

$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>';
}

Into 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>';
}

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

Any help greatly appreciated.

To avoid future pain, I would suggest creating a database class that will extrapolate this from the core code. This will allow your database class to contain the standard functions and you just need to update only several spots rather than all of your PHP files. — endyourif
– endyourif, Commented Sep 22, 2012 at 9:16
mysqli is from php 5 as PDO. And has the same benefits as PDO for injection and has more features than PDO apart from emulated prepared statement. mysqli uses serverside prepared statement only. But PDO is not "better than" mysqli. mysqli is newer than msyql_ and does better work and it is that he needs. — artragis
– artragis, Commented Sep 22, 2012 at 9:22
I think mssqli_query should be mysqli_query and I don't understand why do you want to replace mssql_query with sqlserv_query. Wasn't the goal to replace SQLServer with MySQL?! — axiac
– axiac, Commented Sep 8, 2017 at 9:37

Mike de Klerk · Accepted Answer · 2012-09-22 09:16:09Z

2

I do not think there is a totally automatic way. Because MySQL has 'LIMIT' for example where MSSQL has 'TOP'. Of course that would not have to be a problem but I am not sure if complex MSSQL queries would translate 1:1 to MySQL.

And suggest you use the `` signs to point out to MySql it is a member of a databaes, table or column name. Example:

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

Would change to

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

Now you can make queries like

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

Otherwise the words, select, datetime and limit would interfere with MySql syntax.

answered Sep 22, 2012 at 9:16

Mike de Klerk

12.5k9 gold badges61 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

artragis Over a year ago

the ` are note mandatory. I never used them apart from the case the one that named the tables gave a reserved word as name.

artragis · Accepted Answer · 2012-09-22 09:25:17Z

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

You can also use a php script like that :

$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));

    }
}

Moreover, as @endyourif said, you'd better wrap your database connexion inside a 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

atj4me · Accepted Answer · 2017-09-08 09:30:19Z

I actually run into this issue recently. Found out using MYSQL Workbench did solve the problem of Migrating the database from MSSQL to MYSQL. However, there remained a problem that while there is not many edits happening in the database, the PHP side codes are being edited frequently due to certain issues. So it made difficult to migrate each files one by one on a daily basis. So I researched and came up with a function which is still not 100%, and has its disadvantages, but solved my problem.

I used Sublime Text Editor, and using that I replaced all occurrences of mssql_ to sql_, and then included the function.

I tried defining mssql_ functions first, but it turns out, I have to actually disable the mssql from the PHP module. It is fine if there is only one application using that PHP, but since there are many, I had to rewrite every single pages to new function.

You can find my code here. But I can't guarantee you get the full result.

Collectives™ on Stack Overflow

Migrate PHP code from MSSQL to MySQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related