PHP: Connect to SQL Server

Summary: in this tutorial, you will learn how to connect to SQL Server from PHP using PHP Data Objects (PDO).

To connect PHP applications to SQL Server using PHP PDO, you need to properly install the necessary drivers and configure your PHP environments. You can follow these steps to connect PHP to SQL Server on Windows.

Step 1: Verify Your PHP Version

Open a terminal on your computer and execute the following command to check your PHP version:

php -vCode language: PHP (php)

The output will look like:

PHP 8.2.12 (cli) (built: Oct 24 2023 21:15:15) (ZTS Visual C++ 2019 x64)
...Code language: Parser3 (parser3)

The output indicates the PHP version 8.2.12. You’ll use this version to install compatible SQL Server drivers.

Step 2: Install Microsoft ODBC Driver for SQL Server

ODBC stands for Open Database Connectivity, which is a standard API for accessing various types of database management systems (DBMS).

To access the SQL Server from PHP, you need to install the ODBC driver.

1. Go to the ODBC Driver for SQL Server download page.
2. Download the appropriate driver for your system architecture (x86 or x64).
3. Run the installer and follow the on-screen instructions to complete the installation.
4. Restart your computer.

Step 3: Install PHP Extensions for SQL Server

1. Go to the Microsoft Drivers for PHP for SQL Server download page.
2. Download the driver zip file and extract it to a directory. The output directory includes many .dll files.
3. Copy appropriate .dll files for your PHP version (php_pdo_sqlsrv_82_ts_x64.dll and php_sqlsrv_82_ts_x64.dll) to the ext directory of your PHP installation such as D:\xampp\php\ext.

Please refer to the following page to select the right .dll files for your PHP version.

Step 4: Enable the extensions in php.ini file

First, locate your php.ini file by running the following command on your terminal:

php --iniCode language: PHP (php)

It returns the output like the following:

Configuration File (php.ini) Path:
Loaded Configuration File:         D:\xampp\php\php.ini
Scan for additional .ini files in: (none)
Additional .ini files parsed:      (none)Code language: PHP (php)

The output indicates that the php.ini file is located in the D:\xampp\php\ directory.

Second, open the php.ini file in a text editor and add the following lines to enable the SQL Server driver extensions:

extension=php_pdo_sqlsrv_82_ts_x64.dll
extension=php_sqlsrv_82_ts_x64.dllCode language: PHP (php)

Third, save the changes and exit the text editor.

Finally, restart your web server to apply the changes.

Step 5: Connect to SQL Server Using PDO

1) Create a new file config.php to store the SQL Server:

<?php

$db_server = 'localhost';
$db_name = 'BookStore';
$db_user = 'joe';
$db_password = 'YourPassword';Code language: PHP (php)

Please replace YourPassword with your secure password.

2) Create a new file connect.php that connects to the SQL Server:

<?php

require_once 'config.php';

function connect($db_server, $db_name, $db_user, $db_password) {
    try {
        // Construct a data source name
        $dsn = "sqlsrv:server=$db_server;Database=$db_name";
        
        // Connect to the SQL Server
        $conn = new PDO($dsn, $db_user, $db_password);

        // Set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        return $conn;
    } catch (PDOException $e) {
        die($e);
    }
}

return connect($db_server, $db_name, $db_user, $db_password);Code language: PHP (php)

How it works.

First, define a function connect() that connects to the SQL Server:

function connect($db_server, $db_name, $db_user, $db_password) {Code language: PHP (php)

Second, construct a data source name (DNS) for connecting to the SQL Server:

$dsn = "sqlsrv:server=$db_server;Database=$db_name";Code language: PHP (php)

Third, create a new PDO object with dsn, database user, and password. The PDO object represents a connection to the SQL Server:

$conn = new PDO($dsn, $db_user, $db_password);Code language: PHP (php)

The $conn will not be null if the connection is established successfully.

Fourth, set the error mode to exception so that if an error occurs, you can quickly detect it. This is the default error mode so you can ignore it:

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);Code language: PHP (php)

Fifth, return the PDO object:

return $conn;Code language: PHP (php)

Sixth, stop the script and show the error if it occurs during the connection to the SQL Server:

die($e);Code language: PHP (php)

Finally, call the connect() function with parameters specified in the config.php and return the PDO object:

return connect($db_server, $db_name, $db_user, $db_password);Code language: PHP (php)

3) Create a new file index.php:

<?php

$conn = require_once 'connect.php';
if($conn) {
    echo "Successfully connected to SQL Server database.";
}Code language: PHP (php)

In the index.php file, load the connect.php script, get the PDO object ($conn), and display a message if the application successfully connects to the SQL Server.

4) Open the index.php on the web browser. If you see the following output, meaning that you have successfully connected PHP to SQL Server:

Successfully connected to SQL Server database.Code language: PHP (php)

Summary

• Create a new PDO object to connect to the SQL Server database.