快速入门:使用 PHP 连接到 Azure Database for PostgreSQL 并查询其中的数据 - 单一服务器Quickstart: Use PHP to connect and query data in Azure Database for PostgreSQL - Single Server

本快速入门演示了如何使用 PHP 应用程序连接到 Azure Database for PostgreSQL。This quickstart demonstrates how to connect to an Azure Database for PostgreSQL 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 PostgreSQL。The steps in this article assume that you are familiar with developing using PHP, and are new to working with Azure Database for PostgreSQL.

先决条件Prerequisites

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

安装 PHPInstall PHP

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

WindowsWindows

  • 下载 PHP 7.1.4 非线程安全 (x64) 版本Download PHP 7.1.4 non-thread safe (x64) version
  • 安装 PHP 并参阅 PHP 手册以获取更多配置Install PHP and refer to the PHP manual for further configuration
  • 代码使用 PHP 安装中包括的 pgsql 类 (ext/php_pgsql.dll)。The code uses the pgsql class (ext/php_pgsql.dll) that is included in the PHP installation.
  • 通过编辑 php.ini 配置文件(通常位于 C:\Program Files\PHP\v7.1\php.ini 中)启用 pgsql 扩展。Enabled the pgsql extension by editing the php.ini configuration file, typically located at C:\Program Files\PHP\v7.1\php.ini. 配置文件应包含文本为 extension=php_pgsql.so 的行。The configuration file should contain a line with the text extension=php_pgsql.so. 如果未显示,请添加该文本并保存文件。If it is not shown, add the text and save the file. 如果文本存在,但已被分号前缀注释掉,请通过删除分号来取消注释该文本。If the text is present, but commented with a semicolon prefix, uncomment the text by removing the semicolon.

Linux (Ubuntu)Linux (Ubuntu)

  • 下载 PHP 7.1.4 非线程安全 (x64) 版本Download PHP 7.1.4 non-thread safe (x64) version
  • 安装 PHP 并参阅 PHP 手册以获取更多配置Install PHP and refer to the PHP manual for further configuration
  • 代码使用 pgsql 类 (php_pgsql.so)。The code uses the pgsql class (php_pgsql.so). 通过运行 sudo apt-get install php-pgsql 来安装它。Install it by running sudo apt-get install php-pgsql.
  • 通过编辑 /etc/php/7.0/mods-available/pgsql.ini 配置文件启用 pgsql 扩展。Enabled the pgsql extension by editing the /etc/php/7.0/mods-available/pgsql.ini configuration file. 配置文件应包含文本为 extension=php_pgsql.so 的行。The configuration file should contain a line with the text extension=php_pgsql.so. 如果未显示,请添加该文本并保存文件。If it is not shown, add the text and save the file. 如果文本存在,但已被分号前缀注释掉,请通过删除分号来取消注释该文本。If the text is present, but commented with a semicolon prefix, uncomment the text by removing the semicolon.

MacOSMacOS

获取连接信息Get connection information

获取连接到 Azure Database for PostgreSQL 所需的连接信息。Get the connection information needed to connect to the Azure Database for PostgreSQL. 需要完全限定的服务器名称和登录凭据。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 PostgreSQL 服务器名称

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

使用以下代码进行连接,使用 CREATE TABLE SQL 语句创建表,然后使用 INSERT INTO SQL 语句将行添加到表中。Use the following code to connect and create a table using CREATE TABLE SQL statement, followed by INSERT INTO SQL statements to add rows into the table.

代码通过调用 pg_connect() 方法来连接到 Azure Database for PostgreSQL。The code call method pg_connect() to connect to Azure Database for PostgreSQL. 然后,它会通过多次调用 pg_query() 方法来运行多个命令,并通过调用 pg_last_error() 来查看详细信息(如果每次都发生错误)。Then it calls method pg_query() several times to run several commands, and pg_last_error() to check the details if an error occurred each time. 然后,它会调用方法 pg_close() 来关闭连接。Then it calls method pg_close() to close the connection.

$host$database$user$password 参数替换为你自己的值。Replace the $host, $database, $user, and $password parameters with your own values.

<?php
    // Initialize connection variables.
    $host = "mydemoserver.postgres.database.chinacloudapi.cn";
    $database = "mypgsqldb";
    $user = "mylogin@mydemoserver";
    $password = "<server_admin_password>";

    // Initialize connection object.
    $connection = pg_connect("host=$host dbname=$database user=$user password=$password") 
        or die("Failed to create connection to database: ". pg_last_error(). "<br/>");
    print "Successfully created connection to database.<br/>";

    // Drop previous table of same name if one exists.
    $query = "DROP TABLE IF EXISTS inventory;";
    pg_query($connection, $query) 
        or die("Encountered an error when executing given sql statement: ". pg_last_error(). "<br/>");
    print "Finished dropping table (if existed).<br/>";

    // Create table.
    $query = "CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);";
    pg_query($connection, $query) 
        or die("Encountered an error when executing given sql statement: ". pg_last_error(). "<br/>");
    print "Finished creating table.<br/>";

    // Insert some data into table.
    $name = '\'banana\'';
    $quantity = 150;
    $query = "INSERT INTO inventory (name, quantity) VALUES ($1, $2);";
    pg_query($connection, $query) 
        or die("Encountered an error when executing given sql statement: ". pg_last_error(). "<br/>");

    $name = '\'orange\'';
    $quantity = 154;
    $query = "INSERT INTO inventory (name, quantity) VALUES ($name, $quantity);";
    pg_query($connection, $query) 
        or die("Encountered an error when executing given sql statement: ". pg_last_error(). "<br/>");

    $name = '\'apple\'';
    $quantity = 100;
    $query = "INSERT INTO inventory (name, quantity) VALUES ($name, $quantity);";
    pg_query($connection, $query) 
        or die("Encountered an error when executing given sql statement: ". pg_last_error()). "<br/>";

    print "Inserted 3 rows of data.<br/>";

    // Closing connection
    pg_close($connection);
?>

读取数据Read data

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

代码通过调用 pg_connect() 方法来连接到 Azure Database for PostgreSQL。The code call method pg_connect() to connect to Azure Database for PostgreSQL. 然后,它会通过调用 pg_query() 方法来运行 SELECT 命令,将结果保存在结果集中,并通过调用 pg_last_error() 来查看详细信息(如果发生错误)。Then it calls method pg_query() to run the SELECT command, keeping the results in a result set, and pg_last_error() to check the details if an error occurred. 若要读取结果集,请在循环中调用 pg_fetch_row() 方法,每行一个循环,并在数组 $row 中检索行数据,每列一个数据值(在每个数组位置)。To read the result set, method pg_fetch_row() is called in a loop, once per row, and the row data is retrieved in an array $row, with one data value per column in each array position. 若要释放结果集,请调用 pg_free_result() 方法。To free the result set, method pg_free_result() is called. 然后,它会调用方法 pg_close() 来关闭连接。Then it calls method pg_close() to close the connection.

$host$database$user$password 参数替换为你自己的值。Replace the $host, $database, $user, and $password parameters with your own values.

<?php
    // Initialize connection variables.
    $host = "mydemoserver.postgres.database.chinacloudapi.cn";
    $database = "mypgsqldb";
    $user = "mylogin@mydemoserver";
    $password = "<server_admin_password>";
    
    // Initialize connection object.
    $connection = pg_connect("host=$host dbname=$database user=$user password=$password")
                or die("Failed to create connection to database: ". pg_last_error(). "<br/>");

    print "Successfully created connection to database. <br/>";

    // Perform some SQL queries over the connection.
    $query = "SELECT * from inventory";
    $result_set = pg_query($connection, $query) 
        or die("Encountered an error when executing given sql statement: ". pg_last_error(). "<br/>");
    while ($row = pg_fetch_row($result_set))
    {
        print "Data row = ($row[0], $row[1], $row[2]). <br/>";
    }

    // Free result_set
    pg_free_result($result_set);

    // Closing connection
    pg_close($connection);
?>

更新数据Update data

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

代码通过调用 pg_connect() 方法来连接到 Azure Database for PostgreSQL。The code call method pg_connect() to connect to Azure Database for PostgreSQL. 然后,它会通过调用 pg_query() 方法来运行命令,并通过调用 pg_last_error() 来查看详细信息(如果发生错误)。Then it calls method pg_query() to run a command, and pg_last_error() to check the details if an error occurred. 然后,它会调用方法 pg_close() 来关闭连接。Then it calls method pg_close() to close the connection.

$host$database$user$password 参数替换为你自己的值。Replace the $host, $database, $user, and $password parameters with your own values.

<?php
    // Initialize connection variables.
    $host = "mydemoserver.postgres.database.chinacloudapi.cn";
    $database = "mypgsqldb";
    $user = "mylogin@mydemoserver";
    $password = "<server_admin_password>";

    // Initialize connection object.
    $connection = pg_connect("host=$host dbname=$database user=$user password=$password")
                or die("Failed to create connection to database: ". pg_last_error(). ".<br/>");

    print "Successfully created connection to database. <br/>";

    // Modify some data in table.
    $new_quantity = 200;
    $name = '\'banana\'';
    $query = "UPDATE inventory SET quantity = $new_quantity WHERE name = $name;";
    pg_query($connection, $query) 
        or die("Encountered an error when executing given sql statement: ". pg_last_error(). ".<br/>");
    print "Updated 1 row of data. </br>";

    // Closing connection
    pg_close($connection);
?>

删除数据Delete data

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

代码通过调用 pg_connect() 方法来连接到 Azure Database for PostgreSQL。The code call method pg_connect() to connect to Azure Database for PostgreSQL. 然后,它会通过调用 pg_query() 方法来运行命令,并通过调用 pg_last_error() 来查看详细信息(如果发生错误)。Then it calls method pg_query() to run a command, and pg_last_error() to check the details if an error occurred. 然后,它会调用方法 pg_close() 来关闭连接。Then it calls method pg_close() to close the connection.

$host$database$user$password 参数替换为你自己的值。Replace the $host, $database, $user, and $password parameters with your own values.

<?php
    // Initialize connection variables.
    $host = "mydemoserver.postgres.database.chinacloudapi.cn";
    $database = "mypgsqldb";
    $user = "mylogin@mydemoserver";
    $password = "<server_admin_password>";

    // Initialize connection object.
    $connection = pg_connect("host=$host dbname=$database user=$user password=$password")
            or die("Failed to create connection to database: ". pg_last_error(). ". </br>");

    print "Successfully created connection to database. <br/>";

    // Delete some data from table.
    $name = '\'orange\'';
    $query = "DELETE FROM inventory WHERE name = $name;";
    pg_query($connection, $query) 
        or die("Encountered an error when executing given sql statement: ". pg_last_error(). ". <br/>");
    print "Deleted 1 row of data. <br/>";

    // Closing connection
    pg_close($connection);
?>

后续步骤Next steps