MySQL to MSSQL Conversion

[cbo0485]

Hey, not really sure if this belongs in the coding section or in the MySql section, so feel free to move the thread if needed.

 

I have a site I have built using a pretty standard setup, PHP using jquery and MySQL to display data, manipulate, etc...  It's not very complicated, but unfortunately due to circumstances beyond my control, I'm being forced to migrate the site from MySQL to MSSQL. 

 

So I re-configured and re-made PHP(running 5.6.9 on Apache, on Linux) with the following:

'--with-mssql=/path/to/phpDependencies/freetds'

 

Obviously I installed freetds and installed it to the above location.

 

So I migrated my database and when creating a simple test.php page, it works:

<?php
session_start();
include($_SERVER['DOCUMENT_ROOT'].'/dbconnection.php');	
include($_SERVER['DOCUMENT_ROOT'].'/functions.php');

$query = "SELECT * from $schema.table_user_accounts";
$result = mssql_query($query);
$numRows = mssql_num_rows($result);
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>";
echo "<table class='centerTable' border='1'>";
echo "<tr>";
echo "<th>username</th><th>userlevel</th><th>last_login</th>";
echo "</tr>";
while($row = mssql_fetch_array($result))
{
    echo "<tr>";
    
    echo "<td>" . $row['username'] . "</td>";
    echo "<td>" . $row['userlevel'] . "</td>";
    echo "<td>" . $row['last_login'] . "</td>";
    echo "</tr>";
}
echo "</table>";
   

 ?>

So doing something that simple works, created a new page from scratch.  However, in my many other pages I'm doing something more like this from when I had MySQL:

$sql = "SELECT * FROM webserver_information WHERE $whereVar LIKE '%$userInput%'";

    //$rs = mysql_query($sql);
    $rs = $conn->query($sql);
    if ($rs === false) {
        trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);
    } else {
        $numRows = $rs->num_rows;
    }
    //$numRows = mysql_num_rows($rs);
    $numRows = $rs->num_rows;
    $rs->data_seek(0);
    echo "<p align='right'>Total Rows: " . $numRows . "</p>";
    echo "<table class='centerTable' border='1' style='cursor:pointer'>";
    echo "<tr>";
    echo "<th>Instance Name</th><th>Type</th><th>Server List</th>"
    . "<th>Port</th><th>Environment</th><th>Location</th>"
    . "<th>Secure</th><th>Comments</th><th>CorrID</th>";
    if ($admin) {
        echo "<th>Control</th>";
    }
    echo "</tr>";
    while ($row = $rs->fetch_assoc()) {

I want to be able to create the $rs object from my query results from mssql in the same way I did with MySQL.  Am I able to do this with the setup I have now, do I need to switch to something different?  Is the function not query() anymore, is it something else?  I also tried execute().

 

I'm basically just trying to figure out the best way to migrate this site with as little code changes as possible.

[NotionCommotion]

Have you looked into http://php.net/manual/en/book.pdo.php?    Small learning curve, and you will find it much simpler than what you are doing.  I would first convert your site from your existing mysql functions to PDO.   I have never used MSSQL, but I believe you can change from PDO with MySQL to PDO with MSSQL with very little and maybe no changes.

[cbo0485]

Have not tried that, I'll look into that.

[mac_gyver]

i recommend that you start by separating the concerns in your code. separate the database dependent code, that knows how to execute a query and fetch the result, from the presentation code, that knows how to produce the output from the query data.

 

the way to do this is to fetch the data from any query into a php variable, named to indicate the meaning of the data. you would then use the php variable in the presentation code. for an operation like getting the number of rows, where you expect a result set, you would just use count() on the array variable you fetched the data into. you would use a foreach loop to loop over the data.

 

next, if you had used the php PDO extension (as NotionCommotion has linked to) when you converted your code from the old php mysql extension, and had used prepared queries, converting to use a different database, like MS SQL, would have only required that you make any necessary changes to the sql query syntax. the php statements needed to execute and fetch the data from the query would remain the same. so, it would be to your advantage to first convert your code to use the php PDO extension, with prepared queries to supply data values to the query when you execute it, then convert to a different database server.

Edited June 1, 2017 by mac_gyver

[NotionCommotion]

Until you get the hang of it, use prepared statements for everything.

 

Prepared statements allow you to separate the values from your SQL statements.

 

While there are different ways to bind the data, start off with the two easy ways: questionmark placeholders (use only if there is only a small number of values) and array keys (use when more).

 

Here is an example

<?php
  
$c=parse_ini_file(__DIR__.'/config.ini',true);


$pdo = new \PDO(
    "mysql:host={$c['mysql']['host']};dbname={$c['mysql']['dbname']};charset={$c['mysql']['charset']}",
    $c['mysql']['username'],
    $c['mysql']['password'],
    [
        \PDO::ATTR_EMULATE_PREPARES          => false,
        \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY  => true,
        \PDO::ATTR_ERRMODE                   => \PDO::ERRMODE_EXCEPTION,
        \PDO::ATTR_DEFAULT_FETCH_MODE        => \PDO::FETCH_OBJ
    ]
);


$sql = "SELECT * FROM webserver_information WHERE $whereVar LIKE ?";    //$whereVar is not data, so you hardcode it
$stmt=$pdo->prepare($sql);
$stmt->execute(["%$userInput%"]);
var_dump($stmt->fetchAll());


$sql = "SELECT * FROM webserver_information WHERE $whereVar LIKE :a AND b=:b AND c=:c AND d=:d";    //$whereVar is not data, so you hardcode it
$stmt=$pdo->prepare($sql);
$stmt->execute(['a'=>"%$userInput%",'b'=>1,'c'=>2,'d'=>3]);
var_dump($stmt->fetchAll());

 

[benanamen]

questionmark positional placeholders