How to query oracle data in php

PHP query Oracle data

With the development of Internet technology, more and more websites and applications need to query and operate the database. Oracle is a widely used relational database management system that can store and manage large amounts of data. PHP is a widely used server-side scripting language that can communicate with Oracle databases. In this article, we will learn how to query an Oracle database using PHP.

1. Environment preparation

Before using PHP to query the Oracle database, we need to prepare the corresponding tools and environment. The specific steps are as follows:

1.1 Install PHP

First you need to install PHP. The PHP official website provides installation programs for Windows and Linux versions. You can download the corresponding program for installation according to your own operating system. During the installation process, you need to pay attention to choosing to install the Oracle database driver.

1.2 Install Oracle client software

After installing PHP, you need to install Oracle client software so that PHP can connect to the Oracle database. Oracle's official website provides client software installation programs for Windows and Linux versions. You can download the corresponding program for installation according to your own operating system. During the installation process, you need to pay attention to choosing to install the Oracle client software.

1.3 Configure the PHP.ini file

There is a php.ini file in the PHP installation directory, which needs to be configured so that PHP can connect to the Oracle database. The specific steps are as follows:

• Search for "extension=php_oci8.dll" or "extension=php_oci8.so" in the php.ini file, remove the semicolon in front of it, and enable the OCI8 extension module.
• Add the following code in the php.ini file:

[OCI8]
extension=php_oci8.dll（或php_oci8.so）
oci8.privileged_connect = Off
oci8.max_persistent = -1
oci8.persistent_timeout = -1
oci8.ping_interval = 60
oci8.statement_cache_size = 20
oci8.default_prefetch = 100

2. Connect to Oracle database

After configuring the environment, we can use PHP connects to Oracle database. The specific steps are as follows:

2.1 Prepare connection information

When connecting to the Oracle database, you need to provide the following information:

• The host name or IP address of the Oracle database
• Port number of Oracle database (default is 1521)
• SID or service name of Oracle database
• User name and password of Oracle database

This information can Get it from your Oracle database administrator.

2.2 Use OCI8 extension to connect to Oracle database

In PHP code, we can use OCI8 extension to connect to Oracle database. The specific steps are as follows:

$conn = oci_connect($user, $password, $host . '/' . $sid);

Among them, $user and $password are the user name and password of the Oracle database, $host is the host name or IP address of the Oracle database, and $sid is the SID or service name of the Oracle database. After the connection is successful, $conn is the connection object.

3. Perform query operations

After the connection is successful, we can use the OCI8 extension to perform query operations. The specific steps are as follows:

3.1 Prepare query statements

Query statements can be written in SQL language, and its syntax is the same as Oracle SQL.

SELECT column1, column2, ... columnN
FROM table_name
WHERE [condition]

Among them, column1, column2, ... columnN is the query column name, table_name is the query table name, and [condition] is the query condition.

3.2 Use OCI8 extension to perform query operations

In PHP code, we can use OCI8 extension to perform query operations. The specific steps are as follows:

$stid = oci_parse($conn, $query);
oci_execute($stid);

Among them, $conn is the connection object, $query is the query statement, and $stid is the query result object.

4. Processing query results

Query results can be processed using functions in the OCI8 extension. The specific steps are as follows:

4.1 Get the query result column name

You can use the oci_num_fields() function to get the query result column number, and use the oci_field_name() function to get the column name.

$ncols = oci_num_fields($stid);
for ($i = 1; $i <= $ncols; ++$i) {
    $colname = oci_field_name($stid, $i);
    echo "$colname\t";
}

4.2 Get query result records

You can use the oci_fetch_array() function to get a row of records and store it as an array.

while (($row = oci_fetch_array($stid, OCI_ASSOC + OCI_RETURN_NULLS)) != false) {
    foreach ($row as $item) {
        echo $item."\t";
    }
}

5. Complete code

The following is a complete code example for using PHP to query the Oracle database:

$user = "用户名";
$password = "密码";
$host = "主机名/IP地址";
$sid = "SID/服务名";
$conn = oci_connect($user, $password, $host . '/' . $sid);
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$query = "SELECT * FROM table_name WHERE condition";
$stid = oci_parse($conn, $query);
oci_execute($stid);
$ncols = oci_num_fields($stid);
for ($i = 1; $i <= $ncols; ++$i) {
    $colname = oci_field_name($stid, $i);
    echo "$colname\t";
}
while (($row = oci_fetch_array($stid, OCI_ASSOC + OCI_RETURN_NULLS)) != false) {
    foreach ($row as $item) {
        echo $item."\t";
    }
}
oci_free_statement($stid);
oci_close($conn);

The above are the detailed steps for using PHP to query the Oracle database . Through studying this article, I believe that readers have understood how to connect and query Oracle database in PHP.

The above is the detailed content of How to query oracle data in php. For more information, please follow other related articles on the PHP Chinese website!