{"id":4378,"date":"2024-07-30T13:33:25","date_gmt":"2024-07-30T06:33:25","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4378"},"modified":"2024-08-16T19:08:25","modified_gmt":"2024-08-16T12:08:25","slug":"php-connect-to-sql-server","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/php-sql-server\/php-connect-to-sql-server\/","title":{"rendered":"PHP: Connect to SQL Server"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to connect to SQL Server from PHP using PHP Data Objects (PDO).<\/p>\n\n\n\n

To connect PHP applications to SQL Server using PHP PDO<\/a>, 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.<\/p>\n\n\n\n

Step 1: Verify Your PHP Version #<\/a><\/h2>\n\n\n\n

Open a terminal on your computer and execute the following command to check your PHP version:<\/p>\n\n\n

php -v<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The output will look like:<\/p>\n\n\n
PHP <\/span>8.2<\/span>.12<\/span> (cli) (built: Oct <\/span>24<\/span> <\/span>2023<\/span> <\/span>21<\/span>:<\/span>15<\/span>:<\/span>15<\/span>) (ZTS Visual C++ <\/span>2019<\/span> x64)\n...<\/span><\/code><\/span>Code language:<\/span> Parser3<\/span> (<\/span>parser3<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates the PHP version 8.2.12<\/code>. You’ll use this version to install compatible SQL Server drivers.<\/p>\n\n\n\n
Step 2: Install Microsoft ODBC Driver for SQL Server #<\/a><\/h2>\n\n\n\n
ODBC stands for Open Database Connectivity, which is a standard API for accessing various types of database management systems (DBMS).<\/p>\n\n\n\n
To access the SQL Server from PHP, you need to install the ODBC driver.<\/p>\n\n\n\n
    \n
  1. Go to the ODBC Driver for SQL Server download page<\/a>.<\/li>\n\n\n\n
  2. Download the appropriate driver for your system architecture (x86 or x64).<\/li>\n\n\n\n
  3. Run the installer and follow the on-screen instructions to complete the installation.<\/li>\n\n\n\n
  4. Restart your computer.<\/li>\n<\/ol>\n\n\n\n
    Step 3: Install PHP Extensions for SQL Server #<\/a><\/h2>\n\n\n\n
      \n
    1. Go to the Microsoft Drivers for PHP for SQL Server download page<\/a>.<\/li>\n\n\n\n
    2. Download the driver zip file and extract it to a directory. The output directory includes many .dll<\/code> files.<\/li>\n\n\n\n
    3. Copy appropriate .dll<\/code> files for your PHP<\/code> version (php_pdo_sqlsrv_82_ts_x64.dll<\/code> and php_sqlsrv_82_ts_x64.dll<\/code>) to the ext<\/code> directory of your PHP installation such as D:\\xampp\\php\\ext<\/code>.<\/li>\n<\/ol>\n\n\n\n
      Please refer to the following page to select the right .dll files for your PHP version<\/a>.<\/p>\n\n\n\n
      Step 4: Enable the extensions in php.ini file #<\/a><\/h2>\n\n\n\n
      First, locate your php.ini<\/code> file by running the following command on your terminal:<\/p>\n\n\n
      php --ini<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
      It returns the output like the following:<\/p>\n\n\n
      Configuration File (php.ini) Path:\nLoaded Configuration File:         D:\\xampp\\php\\php.ini\nScan for<\/span> additional .ini files in: (none)\nAdditional .ini files parsed:      (none)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
      The output indicates that the php.ini<\/code> file is located in the D:\\xampp\\php\\<\/code> directory.<\/p>\n\n\n\n
      Second, open the php.ini<\/code> file in a text editor and add the following lines to enable the SQL Server driver extensions:<\/p>\n\n\n
      extension=php_pdo_sqlsrv_82_ts_x64.dll\nextension=php_sqlsrv_82_ts_x64.dll<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
      Third, save the changes and exit the text editor.<\/p>\n\n\n\n
      Finally, restart your web server to apply the changes.<\/p>\n\n\n\n
      Step 5: Connect to SQL Server Using PDO #<\/a><\/h2>\n\n\n\n
      1) Create a new file config.php<\/code> to store the SQL Server:<\/p>\n\n\n
      <?php<\/span>\n\n$db_server = 'localhost'<\/span>;\n$db_name = 'BookStore'<\/span>;\n$db_user = 'joe'<\/span>;\n$db_password = 'YourPassword'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
      Please replace YourPassword<\/code> with your secure password.<\/p>\n\n\n\n
      2) Create a new file connect.php<\/code> that connects to the SQL Server:<\/p>\n\n\n
      <?php<\/span>\n\nrequire_once<\/span> 'config.php'<\/span>;\n\nfunction<\/span> connect<\/span>($db_server, $db_name, $db_user, $db_password)<\/span> <\/span>{\n    try<\/span> {\n        \/\/ Construct a data source name<\/span>\n        $dsn = \"sqlsrv:server=$db_server;Database=$db_name\"<\/span>;\n        \n        \/\/ Connect to the SQL Server<\/span>\n        $conn = new<\/span> PDO($dsn, $db_user, $db_password);\n\n        \/\/ Set the PDO error mode to exception<\/span>\n        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);\n\n        return<\/span> $conn;\n    } catch<\/span> (PDOException $e) {\n        die<\/span>($e);\n    }\n}\n\nreturn<\/span> connect($db_server, $db_name, $db_user, $db_password);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
      How it works.<\/p>\n\n\n\n
      First, define a function connect()<\/code> that connects to the SQL Server:<\/p>\n\n\n
      function<\/span> connect<\/span>($db_server, $db_name, $db_user, $db_password)<\/span> <\/span>{<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
      Second, construct a data source name (DNS) for connecting to the SQL Server:<\/p>\n\n\n
      $dsn = \"sqlsrv:server=$db_server;Database=$db_name\"<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
      Third, create a new PDO object with dsn, database user, and password. The PDO<\/code> object represents a connection to the SQL Server:<\/p>\n\n\n
      $conn = new<\/span> PDO($dsn, $db_user, $db_password);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
      The $conn<\/code> will not be null if the connection is established successfully.<\/p>\n\n\n\n
      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:<\/p>\n\n\n
      $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
      Fifth, return the PDO object:<\/p>\n\n\n
      return<\/span> $conn;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
      Sixth, stop the script and show the error if it occurs during the connection to the SQL Server:<\/p>\n\n\n
      die<\/span>($e);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
      Finally, call the connect()<\/code> function with parameters specified in the config.php<\/code> and return the PDO<\/code> object:<\/p>\n\n\n
      return<\/span> connect($db_server, $db_name, $db_user, $db_password);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
      3) Create a new file index.php<\/code>:<\/p>\n\n\n
      <?php<\/span>\n\n$conn = require_once<\/span> 'connect.php'<\/span>;\nif<\/span>($conn) {\n    echo<\/span> \"Successfully connected to SQL Server database.\"<\/span>;\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
      In the index.php<\/code> file, load the connect.php<\/code> script, get the PDO object ($conn<\/code>), and display a message if the application successfully connects to the SQL Server.<\/p>\n\n\n\n
      4) Open the index.php<\/code> on the web browser. If you see the following output, meaning that you have successfully connected PHP to SQL Server:<\/p>\n\n\n
      Successfully connected to SQL Server database.<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
      Summary #<\/a><\/h2>\n\n\n\n
        \n
      • Create a new PDO object to connect to the SQL Server database.<\/li>\n<\/ul>\n
        \n\t
        \n\t\t
        Was this tutorial helpful?<\/div>\n\t\t
        \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
        \n\t\t
        \n\t\t\t
        <\/div>\n\t\t\t\n\t\t\t