Quickstart: Use PHP to connect and query data in Azure Database for MySQL

APPLIES TO: Azure Database for MySQL - Single Server

Important

Azure Database for MySQL single server is on the retirement path. We strongly recommend that you upgrade to Azure Database for MySQL flexible server. For more information about migrating to Azure Database for MySQL flexible server, see What's happening to Azure Database for MySQL Single Server?

This quickstart demonstrates how to connect to an Azure Database for MySQL using a PHP application. It shows how to use SQL statements to query, insert, update, and delete data in the database.

Prerequisites

For this quickstart you need:

• An Azure account with an active subscription. Create an account for free.

• Create an Azure Database for MySQL single server using Azure portal
  or Azure CLI if you do not have one.

• Based on whether you are using public or private access, complete ONE of the actions below to enable connectivity.

  Action	Connectivity method	How-to guide
  Configure firewall rules	Public	Portal CLI
  Configure Service Endpoint	Public	Portal CLI
  Configure private link	Private	Portal CLI

• Create a database and non-admin user

• Install latest PHP version for your operating system

  • PHP on macOS
  • PHP on Linux
  • PHP on Windows

Note

We are using MySQLi library to manage connect and query the server in this quickstart.

Get connection information

You can get the database server connection information from the Azure portal by following these steps:

1. Log in to the Azure portal.
2. Navigate to the Azure Databases for MySQL page. You can search for and select Azure Database for MySQL servers.
3. Select your MySQL server (such as mydemoserver).
4. In the Overview page, copy the fully qualified server name next to Server name and the admin user name next to Server admin login name. To copy the server name or host name, hover over it and select the Copy icon.

Important

• If you forgot your password, you can reset the password.
• Replace the host, username, password, and db_name parameters with your own values**

Step 1: Connect to the server

SSL is enabled by default. You may need to download the DigiCertGlobalRootG2 SSL certificate to connect from your local environment. This code calls:

• mysqli_init to initialize MySQLi.
• mysqli_ssl_set to point to the SSL certificate path. This is required for your local environment but not required for App Service Web App or Azure Virtual machines.
• mysqli_real_connect to connect to MySQL.
• mysqli_close to close the connection.

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

//Initializes MySQLi
$conn = mysqli_init();

mysqli_ssl_set($conn,NULL,NULL, "/var/www/html/DigiCertGlobalRootG2.crt.pem", NULL, NULL);

// Establish the connection
mysqli_real_connect($conn, $host, $username, $password, $db_name, 3306, NULL, MYSQLI_CLIENT_SSL);

//If connection failed, show the error
if (mysqli_connect_errno())
{
    die('Failed to connect to MySQL: '.mysqli_connect_error());
}

Step 2: Create a Table

Use the following code to connect. This code calls:

• mysqli_query to run the query.

// 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");
}

Step 3: Insert data

Use the following code to insert data by using an INSERT SQL statement. This code uses the methods:

• mysqli_prepare to create a prepared insert statement
• mysqli_stmt_bind_param to bind the parameters for each inserted column value.
• mysqli_stmt_execute
• mysqli_stmt_close to close the statement by using method

//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);
}

Step 4: Read data

Use the following code to read the data by using a SELECT SQL statement. The code uses the method:

• mysqli_query execute the SELECT query
• mysqli_fetch_assoc to fetch the resulting rows.

//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);
 }

Step 5: Delete data

Use the following code delete rows by using a DELETE SQL statement. The code uses the methods:

• mysqli_prepare to create a prepared delete statement
• mysqli_stmt_bind_param binds the parameters
• mysqli_stmt_execute executes the prepared delete statement
• mysqli_stmt_close closes the statement

//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);
}

Clean up resources

To clean up all resources used during this quickstart, delete the resource group using the following command:

az group delete \
    --name $AZ_RESOURCE_GROUP \
    --yes

Next steps

Manage Azure Database for MySQL server using Portal

Manage Azure Database for MySQL server using CLI