How to connect PHP with SQL on Azure Web Apps


Connecting a PHP web app with SQL server on Azure Web Apps is really simple:

We have two options here:

  1. Use PDO drivers (php_pdo_sqlsrv.dll) that is already configured in PHP version (5.4, 5.5 and 5.6) for 7.0 you need to upload the drivers from here Microsoft Drivers for PHP for SQL Server

Code example:


//SQL database information
$host = "tcp:<YOUR_SQL_SERVERNAME>.database.windows.net,1433";
$user = "<USER>@<SQLSERVERNAME>";
$pwd = "<YOUR PASSWORD>";
$db = "<DB NAME>";

try {
    $conn = new PDO( "sqlsrv:Server= $host ; Database = $db ", $user, $pwd);
    $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

    $sqlQuery = $conn->query("SELECT * FROM [TABLE_NAME]");
    $result = $sqlQuery->fetchAll();
    echo "Row count: " . count($result) . ".";
}
catch(Exception $e){
    die(var_dump($e));
}

Use SQLSRV drivers (php_sqlsrv.dll) that is already configured in PHP version (5.4, 5.5 and 5.6) for 7.0 you need to upload the drivers from here Microsoft Drivers for PHP for SQL Server

Code example:


$host = "<YOUR_SQL_SERVERNAME>.database.windows.net,1433";
$user = "<USER>@<SQLSERVERNAME>";
$pwd = "<YOUR PASSWORD>";
$db = "<DB NAME>";

try
{
    $connectionOptions = array("Database"=> $db,"Uid"=>$user, "PWD"=>$pwd);
    $conn = sqlsrv_connect($serverName, $connectionOptions);
    if($conn == false)
      die( print_r( sqlsrv_errors(), true));

    $sqlQuery = sqlsrv_query($conn,"SELECT * FROM [TABLE_NAME]");

    if ($sqlQuery == FALSE)
      die( print_r( sqlsrv_errors(), true));

    $rowCount = 0;

    while($row = sqlsrv_fetch_array($sqlQuery, SQLSRV_FETCH_ASSOC)){
      echo($row['COLUMN_NAME']);
      $rowCount++;
    }

    sqlsrv_free_stmt($sqlQuery);
    sqlsrv_close($conn);
  }
  catch(Exception $e)
  {
    echo("Error!");
  }

If you are planning to connect a PHP 7.0 web app to a SQL Azure database, you may need to follow these steps:

Microsoft has published the RC of Microsoft Drivers 4.0 for PHP for SQL Server you can find the reference here: RC for PHP drivers for SQL Server
You can find it here the source code and dll to configure it on Azure Web App: GitHub source code for MS Azure PHP

Steps:

  1. Download the php_sqlsrv_7_nts.dll file and go to your Azure Web App on portal.azure.com
  2. Click in “All settings” -> Application settings -> Change to PHP 7 version -> Platform 32 bit.demo1
  3. In the same Application Settings, create a PHP_EXTENSIONS key in App Settings section. The value for this key would be a path relative to website root: bin\php_sqlsrv_7_nts.dll . We are going to upload this file and create the folder later. DEMO2
  4. Click Save at the top.
  5. Go to Kudu Console https://YOUWEBSITENAME.scm.azurewebsites.net/DebugConsole
  6. Navigate inside site\wwroot\ and create the folder with the following command:
    mkdir bin
    

    demo3

  7. Go inside the created folder and you can drag and drop the dll file to this folder as shown.demo4demo5
  8. Then you can test the sample code above with PHP version 7

Happy coding!

 

4 thoughts on “How to connect PHP with SQL on Azure Web Apps

  1. Quick word to the wise here. Using these settings for PHP 7 as-is will result in your site breaking as soon as Azure Web Apps begins delivering the new driver as part of the base package. To get around that, the DLL that you upload needs to be renamed to php_sqlsrv.dll

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s