快速入门:使用 PHP 连接到 Azure Database for MySQL 并查询其中的数据Quickstart: Use PHP to connect and query data in Azure Database for MySQL

备注

将要查看的是 Azure Database for MySQL 的新服务。You are viewing the new service of Azure Database for MySQL. 若要查看经典 MySQL Database for Azure 的文档,请访问此页To view the documentation for classic MySQL Database for Azure, please visit this page.

本快速入门演示了如何使用 PHP 应用程序连接到 Azure Database for MySQL。This quickstart demonstrates how to connect to an Azure Database for MySQL using a PHP application. 同时还介绍了如何使用 SQL 语句在数据库中查询、插入、更新和删除数据。It shows how to use SQL statements to query, insert, update, and delete data in the database. 本主题假设你熟悉如何使用 PHP 进行开发,但不太熟悉 Azure Database for MySQL 的用法。This topic assumes that you are familiar with development using PHP and that you are new to working with Azure Database for MySQL.

先决条件Prerequisites

此快速入门使用以下任意指南中创建的资源作为起点:This quickstart uses the resources created in either of these guides as a starting point:

重要

确保已使用 Azure 门户Azure CLI 将服务器的防火墙规则添加到连接的 IP 地址Ensure the IP address you're connecting from has been added the server's firewall rules using the Azure portal or Azure CLI

安装 PHPInstall PHP

在自己的服务器上安装 PHP,或者创建包括 PHP 的 Azure Web 应用Install PHP on your own server, or create an Azure web app that includes PHP.

MacOSMacOS

Linux (Ubuntu)Linux (Ubuntu)

WindowsWindows

获取连接信息Get connection information

获取连接到 Azure Database for MySQL 所需的连接信息。Get the connection information needed to connect to the Azure Database for MySQL. 需要完全限定的服务器名称和登录凭据。You need the fully qualified server name and login credentials.

  1. 登录到 Azure 门户Log in to the Azure portal.
  2. 在 Azure 门户的左侧菜单中,单击“所有资源”,然后搜索已创建的服务器(例如 mydemoserver)。From the left-hand menu in Azure portal, click All resources, and then search for the server you have created (such as mydemoserver).
  3. 单击服务器名称。Click 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. Azure Database for MySQL 服务器名称Azure Database for MySQL server name

进行连接并创建表Connect and create a table

使用以下代码进行连接,通过 CREATE TABLE SQL 语句创建表。Use the following code to connect and create a table by using CREATE TABLE SQL statement.

代码使用 PHP 中包括的 MySQL 改进的扩展 (mysqli) 类。The code uses the MySQL Improved extension (mysqli) class included in PHP. 代码调用 mysqli_initmysqli_real_connect 方法连接到 MySQL。The code calls methods mysqli_init and mysqli_real_connect to connect to MySQL. 然后,代码调用 mysqli_query 方法来运行查询。Then it calls method mysqli_query to run the query. 然后,代码调用 mysqli_close 方法来关闭连接。Then it calls method mysqli_close to close the connection.

将 host、username、password 和 db_name 参数替换为你自己的值。Replace the host, username, password, and db_name parameters with your own values.

<?php
$host = 'mydemoserver.mysql.database.chinacloudapi.cn';
$username = 'myadmin@mydemoserver';
$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

使用以下代码进行连接,并使用 INSERT SQL 语句插入数据。Use the following code to connect and insert data by using an INSERT SQL statement.

代码使用 PHP 中包括的 MySQL 改进的扩展 (mysqli) 类。The code uses the MySQL Improved extension (mysqli) class included in PHP. 代码使用 mysqli_prepare 方法来创建已准备的 insert 语句,然后使用 mysqli_stmt_bind_param 方法绑定每个已插入列值的参数。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. 代码使用 mysqli_stmt_execute 方法运行语句,然后使用 mysqli_stmt_close 方法关闭语句。The code runs the statement by using method mysqli_stmt_execute and afterwards closes the statement by using method mysqli_stmt_close.

将 host、username、password 和 db_name 参数替换为你自己的值。Replace the host, username, password, and db_name parameters with your own values.

<?php
$host = 'mydemoserver.mysql.database.chinacloudapi.cn';
$username = 'myadmin@mydemoserver';
$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

使用以下代码进行连接,并使用 SELECT SQL 语句读取数据。Use the following code to connect and read the data by using a SELECT SQL statement. 代码使用 PHP 中包括的 MySQL 改进的扩展 (mysqli) 类。The code uses the MySQL Improved extension (mysqli) class included in PHP. 代码使用 mysqli_query 方法执行 SQL 查询,并使用 mysqli_fetch_assoc 方法提取生成的行。The code uses method mysqli_query perform the sql query and method mysqli_fetch_assoc to fetch the resulting rows.

将 host、username、password 和 db_name 参数替换为你自己的值。Replace the host, username, password, and db_name parameters with your own values.

<?php
$host = 'mydemoserver.mysql.database.chinacloudapi.cn';
$username = 'myadmin@mydemoserver';
$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

使用以下代码进行连接,并使用 UPDATE SQL 语句更新数据。Use the following code to connect and update the data by using an UPDATE SQL statement.

代码使用 PHP 中包括的 MySQL 改进的扩展 (mysqli) 类。The code uses the MySQL Improved extension (mysqli) class included in PHP. 代码使用 mysqli_prepare 方法来创建已准备的 update 语句,然后使用 mysqli_stmt_bind_param 方法绑定每个已更新列值的参数。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. 代码使用 mysqli_stmt_execute 方法运行语句,然后使用 mysqli_stmt_close 方法关闭语句。The code runs the statement by using method mysqli_stmt_execute and afterwards closes the statement by using method mysqli_stmt_close.

将 host、username、password 和 db_name 参数替换为你自己的值。Replace the host, username, password, and db_name parameters with your own values.

<?php
$host = 'mydemoserver.mysql.database.chinacloudapi.cn';
$username = 'myadmin@mydemoserver';
$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

使用以下代码进行连接,并使用 DELETE SQL 语句读取数据。Use the following code to connect and read the data by using a DELETE SQL statement.

代码使用 PHP 中包括的 MySQL 改进的扩展 (mysqli) 类。The code uses the MySQL Improved extension (mysqli) class included in PHP. 代码使用 mysqli_prepare 方法来创建已准备的 delete 语句,然后使用 mysqli_stmt_bind_param 方法绑定语句中的 where 子句的参数。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. 代码使用 mysqli_stmt_execute 方法运行语句,然后使用 mysqli_stmt_close 方法关闭语句。The code runs the statement by using method mysqli_stmt_execute and afterwards closes the statement by using method mysqli_stmt_close.

将 host、username、password 和 db_name 参数替换为你自己的值。Replace the host, username, password, and db_name parameters with your own values.

<?php
$host = 'mydemoserver.mysql.database.chinacloudapi.cn';
$username = 'myadmin@mydemoserver';
$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