1

I moved database from mysql to ms sql server, and now I need to migrate the PHP code. I'm using PHP 7.0.33.

My problem is that mysql code worked on single rows of data, so after fetching data into a resource, inside the loop each row was returned via $r->data_seek($i) and then worked on via a series of procedures.

For my new ms sql server code, I need to use functions sqlsrv_fetch_array, sqlsrv_fetch_object, etc., and seems like there is no concept of a row.

For example, I replaced original code with this and it works:

$r = sqlsrv_query($db_conn, $sql, array(), array( "Scrollable" => 'static' ));    
$col_max = sqlsrv_num_fields($res);
while (sqlsrv_fetch($res)) {
         for($i = 0; $i < $col_max; $i++) { 
             $$key = sqlsrv_get_field($res, $i);
         }                
     }

But changing whole program like this - bit by bit, is just very painful. How can I change original code (mysql based), so that I get to reuse existing row by row passing functionality?

My most current problem is how to replace this mysql code with ms sql server:

$r = result
$i = row number to return
function fetch_sql_mysqli_row ($r, $i, $db_conn = null)
{
  if ($i >= $r->num_rows)
  {
    $r->close();
    return 0;
  }
  $r->data_seek($i);
  return $r->fetch_row();
}

I'm new to PHP, my background is in Oracle world, hence I apologise in advance if my question can be perceived as dumb or naive.

Many thanks in advance.

user3783243
  • 5,368
  • 5
  • 22
  • 41
jacknomiz
  • 13
  • 1
  • 3
  • 1
    You'd probably be better off learning PDO. That can handle mssql ,mysql, and other dbs. Just set its platform, and update the query syntax. http://php.net/manual/en/ref.pdo-sqlsrv.php – user3783243 Jan 17 '19 at 03:44
  • The bigger problem you're going to face is changing the actual queries themselves to work with MSSQL, which is something that would have been handled by using Doctrine which adds essentially an abstraction layer (DQL) to your queries so they work across different platforms. – Marty Jan 17 '19 at 04:49
  • How many queries are you looking at migrating? You could look at moving to `PDO` PHP Data Objects. If you do that on MySQL and test your application, it should just be a matter of switching out the PDO driver to MS SQL. – Lex Jan 17 '19 at 05:13
  • Sounds like PDO is a way to go. It's not a big system - half a dozen of tables, 20-30 queries which I already converted and tested. I like the idea of moving existing code to PDO PHP Data Objects while it's still referencing mysql. I'll try my luck with PDO, many thanks for all your comments. Cheers. – jacknomiz Jan 17 '19 at 05:32

0 Answers0