Use PHP with Azure Database for MySQL - Flexible Server

APPLIES TO: Azure Database for MySQL - Flexible Server

This quickstart demonstrates how to connect to Azure Database for MySQL flexible server using a PHP application. It shows how to use SQL statements to query, insert, update, and delete data in the database. This article assumes that you are familiar with development using PHP and that you are new to working with Azure Database for MySQL flexible server.

Prerequisites

This quickstart uses the resources created in either of these guides as a starting point:

• Create an Azure Database for MySQL flexible server instance using Azure portal
• Create an Azure Database for MySQL flexible server instance using Azure CLI

Preparing your client workstation

1. If you created your flexible server with Private access (VNet Integration), you will need to connect to your server from a resource within the same VNet as your server. You can create a virtual machine and add it to the VNet created with your flexible server. Refer to Create and manage an Azure Database for MySQL flexible server virtual network using Azure CLI.

2. If you created your flexible server with Public access (allowed IP addresses), you can add your local IP address to the list of firewall rules on your server. Refer to Create and manage Azure Database for MySQL flexible server firewall rules using the Azure CLI.

Install PHP

Install PHP on your own server, or create an Azure web app that includes PHP. Refer to create and manage firewall rules to learn how to create firewall rules.

Get connection information

Get the connection information needed to connect to the Azure Database for MySQL flexible server instance. You need the fully qualified server name and sign in credentials.

1. Sign in to the Azure portal.
2. From the left-hand menu in Azure portal, select All resources, and then search for the server you have created (such as mydemoserver).
3. Select the server name.
4. From the server's Overview panel, make a note of the Server name and Server admin login name. If you forget your password, you can also reset the password from this panel.

Connecting to flexible server using TLS/SSL in PHP

To establish a encrypted connection to your flexible server over TLS/SSL from your application, refer to the following code samples. You can download the certificate needed to communicate over TLS/SSL from https://dl.cacerts.digicert.com/DigiCertGlobalRootCA.crt.pem

$conn = mysqli_init();
mysqli_ssl_set($conn,NULL,NULL, "/var/www/html/DigiCertGlobalRootCA.crt.pem", NULL, NULL);
mysqli_real_connect($conn, 'mydemoserver.mysql.database.azure.com', 'myadmin', 'yourpassword', 'quickstartdb', 3306, MYSQLI_CLIENT_SSL);
if (mysqli_connect_errno($conn)) {
die('Failed to connect to MySQL: '.mysqli_connect_error());
}

Connect and create a table

Use the following code to connect and create a table by using CREATE TABLE SQL statement.

The code uses the MySQL Improved extension (mysqli) class included in PHP. The code calls methods mysqli_init and mysqli_real_connect to connect to MySQL. Then it calls method mysqli_query to run the query. Then it calls method mysqli_close to close the connection.

Replace the host, username, password, and db_name parameters with your own values.

<?php
$host = 'mydemoserver.mysql.database.azure.com';
$username = 'myadmin';
$password = 'your_password';
$db_name = 'your_database';

//Establishes the connection
$conn = mysqli_init();
mysqli_real_connect($conn, $host, $username, $password, $db_name, 3306);
if (mysqli_connect_errno($conn)) {
die('Failed to connect to MySQL: '.mysqli_connect_error());
}

// Run the create table query
if (mysqli_query($conn, '
CREATE TABLE Products (
`Id` INT NOT NULL AUTO_INCREMENT ,
`ProductName` VARCHAR(200) NOT NULL ,
`Color` VARCHAR(50) NOT NULL ,
`Price` DOUBLE NOT NULL ,
PRIMARY KEY (`Id`)
);
')) {
printf("Table created\n");
}

//Close the connection
mysqli_close($conn);
?>

Insert data

Use the following code to connect and insert data by using an INSERT SQL statement.

The code uses the MySQL Improved extension (mysqli) class included in PHP. The code uses method mysqli_prepare to create a prepared insert statement, then binds the parameters for each inserted column value using method mysqli_stmt_bind_param. The code runs the statement by using method mysqli_stmt_execute and afterwards closes the statement by using method mysqli_stmt_close.

Replace the host, username, password, and db_name parameters with your own values.

<?php
$host = 'mydemoserver.mysql.database.azure.com';
$username = 'myadmin';
$password = 'your_password';
$db_name = 'your_database';

//Establishes the connection
$conn = mysqli_init();
mysqli_real_connect($conn, $host, $username, $password, $db_name, 3306);
if (mysqli_connect_errno($conn)) {
die('Failed to connect to MySQL: '.mysqli_connect_error());
}

//Create an Insert prepared statement and run it
$product_name = 'BrandNewProduct';
$product_color = 'Blue';
$product_price = 15.5;
if ($stmt = mysqli_prepare($conn, "INSERT INTO Products (ProductName, Color, Price) VALUES (?, ?, ?)")) {
mysqli_stmt_bind_param($stmt, 'ssd', $product_name, $product_color, $product_price);
mysqli_stmt_execute($stmt);
printf("Insert: Affected %d rows\n", mysqli_stmt_affected_rows($stmt));
mysqli_stmt_close($stmt);
}

// Close the connection
mysqli_close($conn);
?>

Read data

Use the following code to connect and read the data by using a SELECT SQL statement. The code uses the MySQL Improved extension (mysqli) class included in PHP. The code uses method mysqli_query perform the sql query and method mysqli_fetch_assoc to fetch the resulting rows.

Replace the host, username, password, and db_name parameters with your own values.

<?php
$host = 'mydemoserver.mysql.database.azure.com';
$username = 'myadmin';
$password = 'your_password';
$db_name = 'your_database';

//Establishes the connection
$conn = mysqli_init();
mysqli_real_connect($conn, $host, $username, $password, $db_name, 3306);
if (mysqli_connect_errno($conn)) {
die('Failed to connect to MySQL: '.mysqli_connect_error());
}

//Run the Select query
printf("Reading data from table: \n");
$res = mysqli_query($conn, 'SELECT * FROM Products');
while ($row = mysqli_fetch_assoc($res)) {
var_dump($row);
}

//Close the connection
mysqli_close($conn);
?>

Update data

Use the following code to connect and update the data by using an UPDATE SQL statement.

The code uses the MySQL Improved extension (mysqli) class included in PHP. The code uses method mysqli_prepare to create a prepared update statement, then binds the parameters for each updated column value using method mysqli_stmt_bind_param. The code runs the statement by using method mysqli_stmt_execute and afterwards closes the statement by using method mysqli_stmt_close.

Replace the host, username, password, and db_name parameters with your own values.

<?php
$host = 'mydemoserver.mysql.database.azure.com';
$username = 'myadmin';
$password = 'your_password';
$db_name = 'your_database';

//Establishes the connection
$conn = mysqli_init();
mysqli_real_connect($conn, $host, $username, $password, $db_name, 3306);
if (mysqli_connect_errno($conn)) {
die('Failed to connect to MySQL: '.mysqli_connect_error());
}

//Run the Update statement
$product_name = 'BrandNewProduct';
$new_product_price = 15.1;
if ($stmt = mysqli_prepare($conn, "UPDATE Products SET Price = ? WHERE ProductName = ?")) {
mysqli_stmt_bind_param($stmt, 'ds', $new_product_price, $product_name);
mysqli_stmt_execute($stmt);
printf("Update: Affected %d rows\n", mysqli_stmt_affected_rows($stmt));

//Close the connection
mysqli_stmt_close($stmt);
}

mysqli_close($conn);
?>

Delete data

Use the following code to connect and read the data by using a DELETE SQL statement.

The code uses the MySQL Improved extension (mysqli) class included in PHP. The code uses method mysqli_prepare to create a prepared delete statement, then binds the parameters for the where clause in the statement using method mysqli_stmt_bind_param. The code runs the statement by using method mysqli_stmt_execute and afterwards closes the statement by using method mysqli_stmt_close.

Replace the host, username, password, and db_name parameters with your own values.

<?php
$host = 'mydemoserver.mysql.database.azure.com';
$username = 'myadmin';
$password = 'your_password';
$db_name = 'your_database';

//Establishes the connection
$conn = mysqli_init();
mysqli_real_connect($conn, $host, $username, $password, $db_name, 3306);
if (mysqli_connect_errno($conn)) {
die('Failed to connect to MySQL: '.mysqli_connect_error());
}

//Run the Delete statement
$product_name = 'BrandNewProduct';
if ($stmt = mysqli_prepare($conn, "DELETE FROM Products WHERE ProductName = ?")) {
mysqli_stmt_bind_param($stmt, 's', $product_name);
mysqli_stmt_execute($stmt);
printf("Delete: Affected %d rows\n", mysqli_stmt_affected_rows($stmt));
mysqli_stmt_close($stmt);
}

//Close the connection
mysqli_close($conn);
?>

Next steps

• Encrypted connectivity using Transport Layer Security (TLS 1.2) in Azure Database for MySQL flexible server.
• Learn more about Networking in Azure Database for MySQL flexible server.
• Create and manage Azure Database for MySQL flexible server firewall rules using the Azure portal.
• Create and manage Azure Database for MySQL flexible server virtual network using Azure portal.