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:

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:

$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:

// 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:

//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:

//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:

//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