Connect to Ms SQL Server

Question

I've been asked to develop custom plugins which will connect to a database hosted on MS SQL Server in different geographical location, i.e the website is hosted with a hosting service and the database is hosted within the businesses office. I have no idea how to go about remotely connecting the SQL server. Any advice people may offer would be greatly appreciated.

Answer 1

You have probably figured it out by now, but just in case. I was also asked to pull data from an MS SQL server and present the data on a WordPress site. In my plugin, I stored the connection values (encrypted) as options. Here are the basics.

Connection Function:

public function rimsdb() {
        global $rimsdb;
        
        $options = get_option('lwd_gs_plugin_options');

        $serverName = $options['db_host']; //serverName\instanceName
        $connectionInfo = array("Database" => $options['db_name'], "UID" => $options['db_user'], "PWD" => $options['db_password']);
        $rimsdb = sqlsrv_connect($serverName, $connectionInfo);

        if ($rimsdb) {
            echo "Connection established.<br />";
        } else {
            echo "Connection could not be established.<br />";
            die(print_r(sqlsrv_errors(), true));
        }
    }

add_action( 'init', 'rimsdb' );

Below is a sample query

$params = array('my_value');

$sql1 = "SELECT col1, col2, col3
                    FROM mytable
                    WHERE col1 = ?";
        $stmt = sqlsrv_query($rimsdb, $sql1, $params);
        
        sqlsrv_execute($stmt);
        if ($stmt === false) {
            die(print_r(sqlsrv_errors(), true));
        }
        
        while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
            $values = $row;
        }