GENWiki

Whilst the normal environment is MySQL or MariaDB for a linux/PHP setup, there are times when a query needs to be sent to and received from a Microsoft SQL Server. PHP provides for this with the SQLSRV.so module. Here's now to use it easily.

You first of all need to establish the connection parameters like..

$serverName = "10.1.1.1"; // IP Address of the server. DNS Names can be used here. 
$connectionInfo = array( "UID"=>"SQLUsername", "PWD"=>"password", "Database"=>"myDatabase", "CharacterSet" => "UTF-8");

Then you can send the query and receive the return as an associative array

$query="SELECT * from AGENTS";
$array=SQLServerQuery($serverName, $connectionInfo, $query);

echo "<pre>";
print_r($array);
echo "</pre>";

The function that does all the work is below. If your having trouble connecting uncomment the sqlsrv_configure lines to enable detailed logging to the php error log.

function SQLServerQuery($server, $connection, $query) {
    
//    if (sqlsrv_configure('LogSeverity',-1)) { echo "Set LogSeverity<br>"; } else { echo "Failed to Set LogSeverity<br>"; }
//    if (sqlsrv_configure('LogSubsystems',-1)) { echo "Set LogSubsystems<br>"; } else { echo "Failed to Set LogSubsystems<br>"; }
    
    $conn = sqlsrv_connect( $server, $connection);
    if ($conn) {
        $stmt = sqlsrv_query($conn, $query);
        $ret=array();
        $r=0;
        
        if ($stmt) {
            echo "Statement executed.<br>\n";
            while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC))
            {
                $ret[$r]=$row;
                $r++;
            }
            return $ret;
            
        }  else {
            return (print_r( sqlsrv_errors(), true));
        }
        
        sqlsrv_free_stmt( $stmt);
        sqlsrv_close( $conn);
    
    } else {
        echo "Connection to SQL Server Failed : [";
        print_r(sqlsrv_errors(), true);
        echo "]<br>";
    }
}