PHP + Oracle (OCI) Preliminary

Starting from Oracle (OCI)

As more and more PHP users choose Oracle as their database, it is more and more important to access the Oracle interface in the PHP release environment. We will start our journey with a simple, basic, and clearer Oracle scenario. Oralce and Oralce8 use the OCI8 function library of PHP. In the PHP manual, there is a brief description (http://www.php.net/manual/ref.oci8.php): These functions allow you to access Oracle and Oracle7d databases that use Oracle8 Call-Interface (OCI8 ). You will need the Oracle8 client function library to use these extended features.

These extensions are more flexible than standard Oracle extensions. They support PHP global and local variables and connection with Oracle, with a complete LOB, FILE and ROWID support and allow you to define user-defined supplementary variables.

From here on, I will use "Oracle" to refer to any Oracle version. This article assumes that you have installed and run PHP and Oracle. Oracle help can be found in http://www.oracle.com or http://technet.oracle.com.

The point of this article is:
1. Connect to ORALCLE
2. Create and run SQL statements
3. Display Results
4. Results of the limit/Offset close to the "marked page number"

As an additional feature. You can see how to complete a query that displays the limit/Offset obtained from a large data result.

/* We will display a scheme from a basic usage. The file used in this example is a new version of the article (http://www.phpbuilder.com/columns/rod20000221.php3) written by Rod Kreisler ). New versions of files can be found in the http://php.socket7.net. Set $ offset to 0 if it is set to 0 or empty.
*/
If (empty ($ offset) | $ offset <0 ){
$ Offset = 0;
}

/* We will display a scheme from a basic usage. The file used in this example is a new version of the article (http://www.phpbuilder.com/columns/rod20000221.php3) written by Rod Kreisler ). New versions of files can be found in the http://php.socket7.net. Set $ offset to 0 if it is set to 0 or empty.
*/
If (empty ($ offset) | $ offset <0 ){
$ Offset = 0;
}

$ Limit = 3;
/*
Connect to Oralce. If you want to, you can directly use ORACLE_SID when connecting to the database. Use PHP as CGI to use non-continuous connections. Now let's use this non-continuous connection in this example. In windows, this can be set in the registry, while in unix, you can use the putenv () function putenv ("ORALCE_SID = ORASID ");
Or, if you want to, you can directly refer to your ORACLE_SID when connecting to the database. Let's directly refer to ORACLE_SID in this example.
If you use PHP as the APACHE module, you can use continuous connection. When using non-persistent connections as CGI when using PHP, we use non-persistent connections in this example.
*/

$ Conn = OCILogon ("user_name", "password", "ORASID ");

/*
Error message. If no database is connected, an error message is displayed and the script is exited.
*/

If (! $ Conn ){
Echo "ERROR-cocould not connect to Oracle ";
Exit;
}

/*
If the connection is successful, $ conn is a connector. Otherwise, the script ends and the error message "cocould not connect to Oracle" is output.
Now, you can analyze and create your SQL statements, and obtain records that can be counted by unlimited query results.
Here, the format statement "Select count (*) from table_name" is equivalent to count (), which is an SQL function that will be executed in the database. It is better than the returned results calculated by PHP.
*/

$ SQL = "Select count (*) from table_name ";

$ Stmt = OCIParse ($ conn, $ SQL );
If (! $ Stmt ){
Echo "ERROR-cocould not parse SQL statement .";
Exit;
}

/*
If you have entered an incorrect SQL statement or another error, you will see the error message "unable to analyze SQL". $ stmt is now a Definition Statement.
Execute your analysis statement now
*/

OCIExecute ($ stmt );

/*
The statement has now been executed, but as you can see, no result flag is returned from OCIExecute (), and the result returned by OCIParse () contains all the information required by Oracle.
Select the query result. $ total rows [0] will contain a number. If no row is returned, an error is displayed and the script is exited.
*/

OCIFetchInto ($ stmt, & $ total_rows );

If (! $ Total_rows [0]) {
Echo "Error-no rows returned! ";
Exit;
}

/*
This code is optional, but it will make the returned results clearer. It shows a result similar to "15 records in total, 4th to 15th records ." Annotations
*/

$ Begin = ($ offset + 1 );
$ End = ($ begin + ($ limit-1 ));
If ($ end> $ total_rows [0]) {
$ End = $ total_rows [0];
}

Echo "There are$ Total_rows [0]Results.
N ";
Echo "Now showing results$ BeginTo$ End.
N ";

/*
Now it's time to get down to the truth. Release the original statement identifier and create an SQL statement. Analyze and execute the SQL statement,
Note: Unlike MYSQL, Oracle does not support limit statements in SQL statements. In itself, there are many ways to select a specific row. The best way is to put the selected result in a temporary table or "buffer" all the results. This method is beyond the scope of this tutorial. We will use a simpler method, which will be further explained below.
*/
OCIFreeStatement ($ stmt );

$ SQL = "Select * from table_name ";

$ Stmt = OCIParse ($ conn, $ SQL );

If (! $ Stmt ){
Echo "ERROR-cocould not parse SQL statement .";
Exit;
}

OCIExecute ($ stmt );

/*
The result is displayed. The simplest way is to use this loop in the result set. HTML will be used to display the final result, but this example is very simple, so we will not use one.
Note when writing code: As stated above, it is not restricted in Oracle, therefore, we must retrieve all the results in the result set and terminate the existing results for this loop.
In this section, we will have several different methods. In fact, there should be better ways to write this code. However, I think this method is not easy to understand, and it does run effectively.
*/

$ I = 0;
$ J = 0;
While (OCIFetchInto ($ stmt, & $ result_array )){
If ($ I >=$ offset ){
If ($ j <$ limit ){
For ($ k = 0; $ k <= count ($ result_array); $ k ++ ){
Echo $ result_array [$ k]. "";
}
Echo"
";
$ J ++;
}
}
$ I ++;
}
Echo"
";

/*
The result is displayed on the current page. Now it is time to use NEXT/PREV to click another page for the visitor.
*/

// The number of pages required for the calculation result,
$ Pages = intval ($ total_rows [0]/$ limit );

// $ Pages is the total number of pages except the remaining parts
If ($ total_rows [0] % $ limit ){
// Has remainder so add one page
$ Pages ++;
}

// The PREV connection is not displayed on the first page.
If ($ offset! = 0 ){
$ Prevoffset = $ offset-$ limit;
Echo"