Integrate Azure SQL Database with PHP Website

Article
1/17/2024

In this article, we will go step by step to use Azure SQL Database to PHP website.

Prerequisites

Microsoft Azure Subscription (MSDN subscribers or sign up for one month free trial)
Visual Studio 2013 or Visual Studio 2015. To download web installer or ISO file Please Click Here

You will Learn

How to create a SQL Server
How to create a SQL Database
How to connect a SQL Database to Visual Studio

Getting Started with SQL

To learn about how to create SQL Database on Microsoft Azure please refer to below article:

Getting Started with SQL Database on Microsoft Azure

Getting Started with PHP Website

Step 1: Click “All resources” and choose MySQL Server.

**Note: **If you do not see the option for Firewall on the blade you are looking at, go back and make sure that you are looking at the blade for the SQL Database logical server and not the blade for a SQL database.

**Step 2: **Click “All Settings” option and select “Firewall” from the list. Add client IP to the firewall rules and save it.

Step 3: Now select MySQL Database and click “Show database Connection strings”. All connection strings available like ADO.NET, ODBC, PHP, JDBC.

**Step 4: **Start Visual Studio 2013 or 2015. Open Server Explorer (if not available select View Menu -> Server Explorer)
Right-click on Data Connections and select “Add Connection…” option

Step 5: Choose Data source as Microsoft SQL Server. Copy server name from Azure Portal. Enter SQL Server Authentication – Username and Password. Within a few seconds, the database name will show in the drop menu.

Step 6: After successful connection, we need to create Table. Right-click Tables folder and select “Add New Table” option

Step 7: Click Id column and change the Identity to a True value.

Complete Table Definition available below:

Update Database

Just right-click Tables folder and select refresh option to get new table

Step 8: Open Notepad or any other notepad editor. Copy below code and save it as index.php
**
**

<html>
<head>
<Title>Azure SQL Database - PHP Website</Title>
</head>
<body>
<form method="post" action="?action=add" enctype="multipart/form-data" >
    Emp Id <input type="text" name="t_emp_id" id="t_emp_id"/></br>
    Name <input type="text" name="t_name" id="t_name"/></br>
    Education <input type="text" name="t_education" id="t_education"/></br>
    E-mail address <input type="text" name="t_email" id="t_email"/></br>
    <input type="submit" name="submit" value="Submit" />
</form>
<?php
/*Connect using SQL Server authentication.*/
$serverName = "tcp:servername.database.windows.net,1433";
$connectionOptions = array("Database"=>"DBName", 
                           "UID"=>"Username", 
                           "PWD" => "Password");
$conn = sqlsrv_connect($serverName, $connectionOptions);
if($conn === false)
{
    die(print_r(sqlsrv_errors(), true));
}
if(isset($_GET['action']))
{
    if($_GET['action'] == 'add')
    {
        /*Insert data.*/
        $insertSql = "INSERT INTO empTable (emp_id,name,education,email) 
                      VALUES (?,?,?,?)";
        $params = array(&$_POST['t_emp_id'], 
                        &$_POST['t_name'], 
                        &$_POST['t_education'],
                        &$_POST['t_email']);
        $stmt = sqlsrv_query($conn, $insertSql, $params);
        if($stmt === false)
        {
            /*Handle the case of a duplicte e-mail address.*/
            $errors = sqlsrv_errors();
            if($errors[0]['code'] == 2601)
            {
                echo "The e-mail address you entered has already been used.</br>";
            }
            /*Die if other errors occurred.*/
            else
            {
                die(print_r($errors, true));
            }
        }
        else
        {
            echo "Registration complete.</br>";
        }
    }
}
/*Display registered people.*/
/*$sql = "SELECT * FROM empTable ORDER BY name";
$stmt = sqlsrv_query($conn, $sql);
if($stmt === false)
{
    die(print_r(sqlsrv_errors(), true));
}
if(sqlsrv_has_rows($stmt))
{
    print("<table border='1px'>");
    print("<tr><td>Emp Id</td>");
    print("<td>Name</td>");
    print("<td>education</td>");
    print("<td>Email</td></tr>");
     
    while($row = sqlsrv_fetch_array($stmt))
    {
         
        print("<tr><td>".$row['emp_id']."</td>");
        print("<td>".$row['name']."</td>");
        print("<td>".$row['education']."</td>");
        print("<td>".$row['email']."</td></tr>");
    }
    print("</table>");
}*/
?>
</body>
</html>

Step 9: Now host PHP website on Azure. Again navigate to Azure Portal.

Click +New -> Web + Mobile -> click “See all” option