Post
Ask Question

Integrate Azure SQL Database With 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 SQL Server.
  • How to create SQL Database.
  • How to connect SQL Database to teh Visual Studio.

Getting Started with SQL

To learn how to create SQL database on Microsoft Azure, please refer to the below article:

Getting Started with PHP Website

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

All resources

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 on “All Settings” option & select “Firewall” from the list. Add client ip to the firewall rules & save it.

All Settings

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

MySQL Database

MySQL Database

Step 4: Start Visual Studio 2013 or 2015. Open Server Explorer (if not available select View Menu -> Server Explorer).

Right click on Data Connections & select “Add Connection…” option.

Add Connection

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

Choose Data source

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

Add New Table

Step 7: First, click on Id column & change the Identity to True value.

Identity

Complete Table Definition, available below.

Table

Update Database.

Database

Just right click on Tables folder & select refresh option to get new table.

Tables folder
 
Step 8: Open notepad or any other editor. Copy the below code and save it as index.php.

  1. <html>  
  2. <head>  
  3. <Title>Azure SQL Database - PHP Website</Title>  
  4. </head>  
  5. <body>  
  6. <form method="post" action="?action=add" enctype="multipart/form-data" >  
  7. Emp Id <input type="text" name="t_emp_id" id="t_emp_id"/></br>  
  8. Name <input type="text" name="t_name" id="t_name"/></br>  
  9. Education <input type="text" name="t_education" id="t_education"/></br>  
  10. E-mail address <input type="text" name="t_email" id="t_email"/></br>  
  11. <input type="submit" name="submit" value="Submit" />  
  12. </form>  
  13. <?php  
  14. /*Connect using SQL Server authentication.*/  
  15. $serverName = "tcp:servername.database.windows.net,1433";  
  16. $connectionOptions = array(  
  17.     "Database" => "DBName",  
  18.     "UID" => "Username",  
  19.     "PWD" => "Password"  
  20. );  
  21. $conn = sqlsrv_connect($serverName, $connectionOptions);  
  22.   
  23. if ($conn === false)  
  24.     {  
  25.     die(print_r(sqlsrv_errors() , true));  
  26.     }  
  27.   
  28. if (isset($_GET['action']))  
  29.     {  
  30.     if ($_GET['action'] == 'add')  
  31.         {  
  32.         /*Insert data.*/  
  33.         $insertSql = "INSERT INTO empTable (emp_id,name,education,email)   
  34. VALUES (?,?,?,?)";  
  35.         $params = array(&$_POST['t_emp_id'], &$_POST['t_name'], &$_POST['t_education'], &$_POST['t_email']  
  36.         );  
  37.         $stmt = sqlsrv_query($conn, $insertSql, $params);  
  38.         if ($stmt === false)  
  39.             {  
  40.             /*Handle the case of a duplicte e-mail address.*/  
  41.             $errors = sqlsrv_errors();  
  42.             if ($errors[0]['code'] == 2601)  
  43.                 {  
  44.                 echo "The e-mail address you entered has already been used.</br>";  
  45.                 }  
  46.   
  47.             /*Die if other errors occurred.*/  
  48.               else  
  49.                 {  
  50.                 die(print_r($errors, true));  
  51.                 }  
  52.             }  
  53.           else  
  54.             {  
  55.             echo "Registration complete.</br>";  
  56.             }  
  57.         }  
  58.     }  
  59.   
  60. /*Display registered people.*/  
  61. /*$sql = "SELECT * FROM empTable ORDER BY name"; 
  62. $stmt = sqlsrv_query($conn, $sql); 
  63. if($stmt === false) 
  64. { 
  65. die(print_r(sqlsrv_errors(), true)); 
  66. } 
  67.  
  68. if(sqlsrv_has_rows($stmt)) 
  69. { 
  70. print("<table border='1px'>"); 
  71. print("<tr><td>Emp Id</td>"); 
  72. print("<td>Name</td>"); 
  73. print("<td>education</td>"); 
  74. print("<td>Email</td></tr>"); 
  75.  
  76. while($row = sqlsrv_fetch_array($stmt)) 
  77. { 
  78.  
  79. print("<tr><td>".$row['emp_id']."</td>"); 
  80. print("<td>".$row['name']."</td>"); 
  81. print("<td>".$row['education']."</td>"); 
  82. print("<td>".$row['email']."</td></tr>"); 
  83. } 
  84.  
  85. print("</table>"); 
  86. }*/  
  87. ?>  
  88. </body>  
  89. </html>  
Step 9: Now, host the PHP website on Azure. Again, navigate to Azure Portal.

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

See all
 
Search for “PHP”.

In search results, select “PHP Empty Web App”.

Click on “Create” button.

Create

Step 10: Enter the Web App name, resource group, and app service plan.

Enter Web App
Click on “Get Publish Settings” option. Publish Settings file will be downloaded. Use FileZilla or file explorer to host the PHP file on Azure.

Get Publish Settings

Get Publish Settings
 
Step 11: Run the Website.

Run
Run

Step 12: To check the data, right click on Table & select “Show Table Data” option.

Show Table Data

Congratulations! You have successfully inserted the data using PHP Website on Microsoft Azure!

 


Similar Articles