PK
**Aoa, mimetypeapplication/epub+zipPK **A iTunesMetadata.plistS
This chapter shows you how to run stored procedures and functions using PHP and Oracle Database. It has the following topics:
The Anyco application is extended with a PL/SQL function to calculate remuneration for each employee, and is further extended with a PL/SQL procedure to return a REF CURSOR of employee records.
Oracle PL/SQL procedures and functions enable you to store business logic in the database for any client program to use. They also reduce the amount of data that must be transferred between the database and PHP and can help improve performance.
In this section, you will create a PL/SQL stored function to calculate and display the total remuneration for each employee.
To display the total remuneration of each employee, perform the following steps:
The PHP application connects to the database as the HR user. You may need to unlock the HR
account as a user with DBA privileges. To unlock the HR
user:
Open SQL Developer and open a connection to your Oracle database.
Login to your Oracle database as system.
Open SQL Worksheet or SQL*Plus and run the following grant
statement to assign the create procedure
privilege to the HR
user:
grant create procedure to hr;
Login to your HR sample schema as hr
.
Open SQL Worksheet or SQL*Plus and enter the following text to create a calc_remuneration()
function:
create or replace function calc_remuneration( salary IN number, commission_pct IN number) return number is begin return ((salary*12) + (salary * 12 * nvl(commission_pct,0))); end;
Create the chap6
directory, copy the application files from chap5
, and change to the newly created directory:
On Windows:
mkdir c:\program files\Apache Group\Apache2\htdocs\chap6 cd c:\program files\Apache Group\Apache2\htdocs\chap6 copy ..\chap5\* .
On Linux:
mkdir $HOME/public_html/chap6 cd $HOME/public_html/chap6 cp ../chap5/* .
Edit the anyco.php
file. Modify the query in the construct_employees()
function to call the PL/SQL function for each row returned:
$query = "SELECT employee_id, substr(first_name,1,1) || '. '|| last_name as employee_name, hire_date, to_char(salary, '9999G999D99') as salary, nvl(commission_pct,0) as commission_pct, to_char(calc_remuneration(salary, commission_pct),'9999G999D99') as remuneration FROM employees WHERE department_id = :did ORDER BY employee_id ASC";
Edit the anyco_ui.inc
file. In the ui_print_employees()
function, add a Remuneration
column to the table, and modify the foreach
loop to display the remuneration field for each employee:
echo <<<END <form method="post" action="$posturl"> <table> <tr> <th> </th> <th>Employee<br>ID</th> <th>Employee<br>Name</th> <th>Hiredate</th> <th>Salary</th> <th>Commission<br>(%)</th> <th>Remuneration</th> </tr> END; // Write one row per employee foreach ($employeerecords as $emp) { echo '<tr>'; echo '<td><input type="radio" name="emprec" value="'.htmlentities($emp['EMPLOYEE_ID']).'"></td>'; echo '<td align="right">'.htmlentities($emp['EMPLOYEE_ID']).'</td>'; echo '<td>'.htmlentities($emp['EMPLOYEE_NAME']).'</td>'; echo '<td>'.htmlentities($emp['HIRE_DATE']).'</td>'; echo '<td align="right">'.htmlentities($emp['SALARY']).'</td>'; echo '<td align="right">'.htmlentities($emp['COMMISSION_PCT']).'</td>'; echo '<td align="right">'.htmlentities($emp['REMUNERATION']).'</td>'; echo '</tr>'; }
Save the changes to your application files. In a browser, enter the following URL to test the application:
On Windows:
http://localhost/chap6/anyco.php
On Linux:
http://localhost/~<username>/chap6/anyco.php
In the Departments page, click Show Employees.
In the Employees page for the department, the employee remuneration is displayed in the last column:
Query data can be returned as REF CURSORS from PL/SQL blocks and displayed in PHP. This can be useful where the data set requires complex functionality or where you want multiple application programs to use the same query.
A REF CURSOR in PL/SQL is a type definition that is assigned to a cursor variable. It is common to declare a PL/SQL type inside a package specification for reuse in other PL/SQL constructs, such as a package body.
In this section, you will use a REF CURSOR to retrieve the employees for a specific department.
To create a PL/SQL package specification and body, with a REF CURSOR to retrieve employees for a specific department, perform the following steps:
Open SQL Developer and login to your HR sample schema as hr
.
Open SQL Worksheet or SQL*Plus and enter the following text to create the cv_types
PL/SQL package:
CREATE OR REPLACE PACKAGE cv_types AS TYPE empinfotyp IS REF CURSOR; PROCEDURE get_employees(deptid in number, employees in out empinfotyp); END cv_types;
Click Run:
In SQL Worksheet enter the following text to create the cv_types
PL/SQL package body:
CREATE OR REPLACE PACKAGE BODY cv_types AS PROCEDURE get_employees(deptid in number, employees in out empinfotyp) IS BEGIN OPEN employees FOR SELECT employee_id, substr(first_name,1,1) || '. '|| last_name as employee_name, hire_date, to_char(salary, '999G999D99') as salary, NVL(commission_pct,0) as commission_pct, to_char(calc_remuneration(salary, commission_pct), '9999G999D99') as remuneration FROM employees WHERE department_id = deptid ORDER BY employee_id ASC; END get_employees; END cv_types;
Click Run:
Edit the anyco_db.inc
file. Create a new PHP function that calls the PL/SQL packaged procedure:
// Use ref cursor to fetch employee records // All records are retrieved - there is no paging in this example function db_get_employees_rc($conn, $deptid, &$e) { // Execute the call to the stored procedure $stmt = "BEGIN cv_types.get_employees($deptid, :rc); END;"; $stid = @oci_parse($conn, $stmt); if (!$stid) { $e = db_error($conn, __FILE__, __LINE__); return false; } $refcur = oci_new_cursor($conn); if (!$stid) { $e = db_error($conn, __FILE__, __LINE__); return false; } $r = @oci_bind_by_name($stid, ':RC', $refcur, -1, OCI_B_CURSOR); if (!$r) { $e = db_error($stid, __FILE__, __LINE__); return false; } $r = @oci_execute($stid); if (!$r) { $e = db_error($stid, __FILE__, __LINE__); return false; } // Now treat the ref cursor as a statement resource $r = @oci_execute($refcur, OCI_DEFAULT); if (!$r) { $e = db_error($refcur, __FILE__, __LINE__); return false; } $r = @oci_fetch_all($refcur, $employeerecords, null, null, OCI_FETCHSTATEMENT_BY_ROW); if (!$r) { $e = db_error($refcur, __FILE__, __LINE__); return false; } return ($employeerecords); }
The db_get_employees_rc()
function executes the following anonymous (unnamed) PL/SQL block:
BEGIN cv_types.get_employees($deptid, :rc); END;
The PL/SQL statement inside the BEGIN END block calls the stored PL/SQL package procedure cv_types.et_employees()
. This returns an OCI_B_CURSOR
REF CURSOR bind variable in the PHP variable $refcur
.
The $refcur
variable is treated like a statement handle returned by oci_parse()
. It is used for execute and fetch operations just as if the SQL query had been done in PHP.
Edit the anyco.php
file. In the construct_employees()
function, remove the query text and the bind arguments. The function becomes:
function construct_employees()
{
$deptid = $_SESSION['deptid'];
$conn = db_connect($err);
if (!$conn) {
handle_error('Connection Error', $err);
}
else {
$emp = db_get_employees_rc($conn, $deptid, $err);
if (!$emp) {
handle_error('Cannot fetch Employees', $err);
}
else {
$deptname = get_dept_name($conn, $deptid);
ui_print_header('Employees: '.$deptname);
ui_print_employees($emp, $_SERVER['SCRIPT_NAME']);
ui_print_footer(date('Y-m-d H:i:s'));
}
}
}
Save the changes to your application files. In a browser, enter the following URL to test the application:
On Windows:
http://localhost/chap6/anyco.php
On Linux:
http://localhost/~<username>/chap6/anyco.php
In the Departments page, click Next to navigate to the Marketing department page.
In the Marketing department page, click Show Employees.
In the Employees page for the Marketing department, the employee pages displays as previously:
This chapter shows you how to change the application to upload a JPEG image for new employee records and display it on the Employees page. It has the following topics:
In this section, you will modify your application code to enable a photo to be stored in the record of an employee.
To enable images of employees to be stored in the employee records, perform the following tasks:
Create the chap7
directory, copy the application files from chap6
, and change to the newly created directory:
On Windows:
mkdir c:\program files\Apache Group\Apache2\htdocs\chap7 cd c:\program files\Apache Group\Apache2\htdocs\chap7 copy ..\chap6\* .
On Linux:
mkdir $HOME/public_html/chap7 cd $HOME/public_html/chap7 cp ../chap6/* .
Open SQL Developer and open a connection to your HR sample schema.
Login to your HR sample schema as hr.
Open SQL Worksheet and enter the following CREATE TABLE
statement to create a new table for storing employee images:
CREATE TABLE employee_photos( employee_id NUMBER, employee_thumbnail BLOB);
The HR
user must have the CREATE TABLE
privilege to perform this command. If you get an "insufficient privileges" error message, then log out as the HR
user, log in as system
, and execute the following GRANT
command:
GRANT create table TO hr;
Then log in as HR
again to execute the CREATE TABLE
statement.
Edit the anyco_ui.inc
file. Add a Photograph column to the EMPLOYEES
table in the ui_print_employees()
function:
<th>Commission<br>(%)</th>
<th>Remuneration</th>
<th>Photograph</th>
The data for the Photograph column is populated with an <img>
tag whose src
attribute is defined as a URL reference to a new anyco_im.php
file, which will display the image for each employee record.
Edit the anyco_ui.inc
file. Add code in the ui_print_employees()
function to generate an <img>
tag referencing the anyco_im.php
file with the employee identifier as a parameter:
echo '<td align="right">' .htmlentities($emp['REMUNERATION']).'</td>'; echo '<td><img src="anyco_im.php?showempphoto='.$emp['EMPLOYEE_ID'] .'" alt="Employee photo"></td>';
Edit the anyco_ui.inc
file. To enable images to be uploaded when a new employee record is created, add an enctype
attribute to the <form>
tag in the ui_print_insert_employee()
function:
<form method="post" action="$posturl" enctype="multipart/form-data">
At the bottom of the form add an upload field with an input type of file
:
<tr> <td>Commission (%)</td> <td><input type="text" name="commpct" value="0" size="20"></td> </tr> <tr> <td>Photo</td> <td><input type="file" name="empphoto"></td> </tr>
Create the anyco_im.php file.
This file accepts an employee identifier as a URL parameter, reads the image from the Photograph column for that employee record, and returns the thumbnail image to be displayed:
<?php // anyco_im.php require('anyco_cn.inc'); require('anyco_db.inc'); construct_image(); function construct_image() { if (!isset($_GET['showempphoto'])) { return; } $empid = $_GET['showempphoto']; $conn = db_connect($err); if (!$conn) { return; } $query = 'SELECT employee_thumbnail FROM employee_photos WHERE employee_id = :eid'; $stid = oci_parse($conn, $query); $r = oci_bind_by_name($stid, ":eid", $empid, -1); if (!$r) { return; } $r = oci_execute($stid, OCI_DEFAULT); if (!$r) { return; } $arr = oci_fetch_row($stid); if (!$arr) { return; // photo not found } $result = $arr[0]->load(); // If any text (or whitespace!) is printed before this header is sent, // the text is not displayed. The image also is not displayed properly. // Comment out the "header" line to see the text and debug. header("Content-type: image/JPEG"); echo $result; } ?>
The construct_image()
function uses the OCI-Lob->load()
function to retrieve the Oracle LOB data, which is the image data. The PHP header()
function sets the MIME type in the HTTP response header to ensure the browser interprets the data as a JPEG image.
If you want to display other image types, then the Content-type
needs to be changed accordingly.
Edit the anyco_db.inc
file. Add a new function db_insert_thumbnail()
to insert an image into the EMPLOYEE_PHOTOS
table:
function db_insert_thumbnail($conn, $empid, $imgfile, &$e) { $lob = oci_new_descriptor($conn, OCI_D_LOB); if (!$lob) { $e = db_error($conn, __FILE__, __LINE__); return false; } $insstmt = 'INSERT INTO employee_photos (employee_id, employee_thumbnail) VALUES(:eid, empty_blob()) RETURNING employee_thumbnail into :etn'; $stmt = oci_parse($conn, $insstmt); $r = oci_bind_by_name($stmt, ':etn', $lob, -1, OCI_B_BLOB); if (!$r) { $e = db_error($stid, __FILE__, __LINE__); return false; } $r = oci_bind_by_name($stmt, ':eid', $empid, -1); if (!$r) { $e = db_error($stid, __FILE__, __LINE__); return false; } $r = oci_execute($stmt, OCI_DEFAULT); if (!$r) { $e = db_error($stid, __FILE__, __LINE__); return false; } if (!$lob->savefile($imgfile)) { $e = db_error($stid, __FILE__, __LINE__); return false; } $lob->free(); return true; }
To tie the new EMPLOYEE_PHOTOS
and EMPLOYEES
tables together, you must use the same employee id in both tables.
Edit the anyco_db.inc
file. Change the $bindvars
parameter in the db_execute_statement()
function to &$bindvars
so that OUT
bind variable values are returned from the database. At the bottom of the function, add a loop to set any return bind values:
function db_execute_statement($conn, $statement, &$e, &$bindvars = array()) { ... $r = @oci_execute($stid); if (!$r) { $e = db_error($stid, __FILE__, __LINE__); return false; } $outbinds = array(); foreach ($bindvars as $b) { $outbinds[$b[0]] = $$b[0]; } $bindvars = $outbinds; return true; }
Edit the anyco.php
file. Change the INSERT statement in the insert_new_emp()
function so that it returns the new employee identifier in the bind variable :neweid
. This value is inserted with the image into the new EMPLOYEE_PHOTOS
table.
$statement =
'INSERT INTO employees
(employee_id, first_name, last_name, email, hire_date,
job_id, salary, commission_pct, department_id)
VALUES (employees_seq.nextval, :fnm, :lnm, :eml, :hdt,
:jid, :sal, :cpt, :did)
RETURNING employee_id into :neweid';
Also in the insert_new_emp()
function, add a call to the array_push()
function to set a new bind variable NEWEID
at the end of the list of array_push()
calls:
array_push($bindargs, array('CPT', $newemp['commpct'], -1));
array_push($bindargs, array('DID', $newemp['deptid'], -1));
array_push($bindargs, array('NEWEID', null, 10));
Because the value of NEWID
is being retrieved with the RETURNING clause in the INSERT statement, its initial value is set to NULL
. The length is set to 10 to allow enough digits in the return value.
Edit the anyco.php
file. In the insert_new_emp()
function, add a call between the db_execute_statement()
and construct_employees()
calls to insert the thumbnail image:
$r = db_execute_statement($conn, $statement, $err, $bindargs); if ($r) { $r = db_insert_thumbnail($conn, $bindargs['NEWEID'], $_FILES['empphoto']['tmp_name'], $e); construct_employees(); }
In a browser, enter the following application URL:
On Windows:
http://localhost/chap7/anyco.php
On Linux:
http://localhost/~<username>/chap7/anyco.php
In the Departments page, click Show Employees to navigate to the Employees page:
In the Employees page, to insert a new employee record click Insert new employee:
The Insert New Employee form allows you to choose a thumbnail image on your system to be uploaded to the database. Enter your own values in the fields or use the values as shown. Click Browse:
In the File Upload window, browse for and select a JPEG image file, and click Open:
In the Insert New Employee page, click Save:
The Employees page is displayed with the new employee record, including the image, which is displayed at its original size:
In this section, you will further modify your application code to create a thumbnail image from a supplied image, and store the thumbnail image in the record of an employee.
You can use the PHP GD graphics extension to resize employee images.
Restart Apache. You can either use the ApacheMonitor utility, or you can use Windows Services.
To use the ApacheMonitor utility, navigate to the Apache bin
directory and double click ApacheMonitor.exe
. In a default installation, Apache bin
is located at c:\Program Files\Apache Group\Apache2\bin
.
You can access Windows Services from the Windows Start menu at Start > Control Panel > Administrative Tools > Services. Select the Standard tab. Right click the Apache2 HTTP Server and then select Restart.
Edit the anyco_db.inc
file. To resize the image to create a thumbnail image, add the following code before the call to $lob->savefile($imgfile)
in the db_insert_thumbnail()
function:
$r = oci_execute($stmt, OCI_DEFAULT); if (!$r) { $e = db_error($stid, __FILE__, __LINE__); return false; } // Resize the image to a thumbnail define('MAX_THUMBNAIL_DIMENSION', 100); $src_img = imagecreatefromjpeg($imgfile); list($w, $h) = getimagesize($imgfile); if ($w > MAX_THUMBNAIL_DIMENSION || $h > MAX_THUMBNAIL_DIMENSION) { $scale = MAX_THUMBNAIL_DIMENSION / (($h > $w) ? $h : $w); $nw = $w * $scale; $nh = $h * $scale; $dest_img = imagecreatetruecolor($nw, $nh); imagecopyresampled($dest_img, $src_img, 0, 0, 0, 0, $nw, $nh, $w, $h); imagejpeg($dest_img, $imgfile); // overwrite file with new thumbnail imagedestroy($src_img); imagedestroy($dest_img); } if (!$lob->savefile($imgfile)) { ...
The imagecreatefromjpeg()
function reads the JPEG file and creates an internal representation used by subsequent GD functions. Next, new dimensions are calculated with the longest side no larger than 100 pixels. A template image with the new size is created using the imagecreatetruecolor()
function. Data from the original image is sampled into it with the imagecopyresampled()
function to create the thumbnail image. The thumbnail image is written back to the original file and the internal representations of the images are freed.
The existing code in the db_insert_thumbnail()
function uploads the image file to the database as it did in the previous implementation.
Enter the following URL in your browser to test the changes in your application:
On Windows:
http://localhost/chap7/anyco.php
On Linux:
http://localhost/~<username>/chap7/anyco.php
In the Departments page, navigate to the Employees page by clicking Show Employees:
In the Employees page, to insert a new employee record, click Insert new employee:
Enter the new employee details or use the values shown. To browse for an employee image, click Browse:
Locate and select a JPEG image with a size larger than 100 pixels, and click Open:
In the Insert New Image page, click Save:
The Employees page shows the new uploaded JPEG image with a reduced image size, compared to the image loaded before including the image resize code:
This chapter discusses global application development in a PHP and Oracle Database environment. It addresses the basic tasks associated with developing and deploying global Internet applications, including developing locale awareness, constructing HTML content in the user-preferred language, and presenting data following the cultural conventions of the locale of the user.
Building a global Internet application that supports different locales requires good development practices. A locale refers to a national language and the region in which the language is spoken. The application itself must be aware of the locale preference of the user and be able to present content following the cultural conventions expected by the user. It is important to present data with appropriate locale characteristics, such as the correct date and number formats. Oracle Database is fully internationalized to provide a global platform for developing and deploying global applications.
This chapter has the following topics:
Correctly setting up the connectivity between the PHP engine and the Oracle database is first step in building a global application, it guarantees data integrity across all tiers. Most internet based standards support Unicode as a character encoding, in this chapter we will focus on using Unicode as the character set for data exchange.
PHP uses the OCI8 extension, and rules that apply to OCI also apply to PHP. Oracle locale behavior (including the client character set used in OCI applications) is defined by the NLS_LANG
environment variable. This environment variable has the form:
<language>_<territory>.<character set>
For example, for a German user in Germany running an application in Unicode, NLS_LANG
should be set to
GERMAN_GERMANY.AL32UTF8
The language and territory settings control Oracle behaviors such as the Oracle date format, error message language, and the rules used for sort order. The character set AL32UTF8 is the Oracle name for UTF-8.
For information on the NLS_LANG
environment variable, see the Oracle Database installation guides.
When PHP is installed on Apache, you can set NLS_LANG
in /etc/profile
:
export NLS_LANG GERMAN_GERMANY.AL32UTF8
If PHP is installed on Oracle HTTP Server, you must set NLS_LANG
as an environment variable in $ORACLE_HOME/opmn/conf/opmn.xml
:
<ias-component id="HTTP_Server"> <process-type id="HTTP_Server" module-id="OHS"> <environment> <variable id="PERL5LIB" value="D:\oracle\1012J2EE\Apache\Apache\mod_perl\site\5.6.1\lib"/> <variable id="PHPRC" value="D:\oracle\1012J2EE\Apache\Apache\conf"/> <variable id="NLS_LANG" value="german_germany.al32utf8"/> </environment> <module-data> <category id="start-parameters"> <data id="start-mode" value="ssl-disabled"/> </category> </module-data> <process-set id="HTTP_Server" numprocs="1"/> </process-type> </ias-component>
You must restart the Web listener to implement the change.
PHP was designed to work with the ISO-8859-1 character set. To handle other character sets, specifically multibyte character sets, a set of "MultiByte String Functions" is available. To enable these functions, you must enable the mbstring extension.
Your application code should use functions such as mb_strlen()
to calculate the number of characters in strings. This may return different values than strlen()
, which returns the number of bytes in a string.
Once you have enabled the mbstring extension and restarted the Web server, several configuration options become available. You can change the behavior of the standard PHP string functions by setting mbstring.func_overload
to one of the "Overload" settings.
For more information, see the PHP mbstring reference manual at
In a global environment, your application should accommodate users with different locale preferences. Once it has determined the preferred locale of the user, the application should construct HTML content in the language of the locale and follow the cultural conventions implied by the locale.
A common method to determine the locale of a user is from the default ISO locale setting of the browser. Usually a browser sends its locale preference setting to the HTTP server with the Accept Language HTTP header. If the Accept Language header is NULL, then there is no locale preference information available, and the application should fall back to a predefined default locale.
The following PHP code retrieves the ISO locale from the Accept-Language HTTP header through the $_SERVER
Server variable.
$s = $_SERVER["HTTP_ACCEPT_LANGUAGE"]
Once the locale preference of the user has been determined, the application can call locale-sensitive functions, such as date, time, and monetary formatting to format the HTML pages according to the cultural conventions of the locale.
When you write global applications implemented in different programming environments, you should enable the synchronization of user locale settings between the different environments. For example, PHP applications that call PL/SQL procedures should map the ISO locales to the corresponding NLS_LANGUAGE
and NLS_TERRITORY
values and change the parameter values to match the locale of the user before calling the PL/SQL procedures. The PL/SQL UTL_I18N package contains mapping functions that can map between ISO and Oracle locales.
Table 8-1 shows how some commonly used locales are defined in ISO and Oracle environments.
Table 8-1 Locale Representations in ISO, SQL, and PL/SQL Programming Environments
Locale | Locale ID | NLS_LANGUAGE | NLS_TERRITORY |
---|---|---|---|
Chinese (P.R.C.) |
zh-CN |
SIMPLIFIED CHINESE |
CHINA |
Chinese (Taiwan) |
zh-TW |
TRADITIONAL CHINESE |
TAIWAN |
English (U.S.A) |
en-US |
AMERICAN |
AMERICA |
English (United Kingdom) |
en-GB |
ENGLISH |
UNITED KINGDOM |
French (Canada) |
fr-CA |
CANADIAN FRENCH |
CANADA |
French (France) |
fr-FR |
FRENCH |
FRANCE |
German |
de |
GERMAN |
GERMANY |
Italian |
it |
ITALIAN |
ITALY |
Japanese |
ja |
JAPANESE |
JAPAN |
Korean |
ko |
KOREAN |
KOREA |
Portuguese (Brazil) |
pt-BR |
BRAZILIAN PORTUGUESE |
BRAZIL |
Portuguese |
pt |
PORTUGUESE |
PORTUGAL |
Spanish |
es |
SPANISH |
SPAIN |
The encoding of an HTML page is important information for a browser and an Internet application. You can think of the page encoding as the character set used for the locale that an Internet application is serving. The browser must know about the page encoding so that it can use the correct fonts and character set mapping tables to display the HTML pages. Internet applications must know about the HTML page encoding so they can process input data from an HTML form.
Instead of using different native encodings for the different locales, Oracle recommends that you use UTF-8 (Unicode encoding) for all page encodings. This encoding not only simplifies the coding for global applications, but it also enables multilingual content on a single page.
You can specify the encoding of an HTML page either in the HTTP header, or in HTML page header.
To specify HTML page encoding in the HTTP header, include the Content-Type HTTP header in the HTTP specification. It specifies the content type and character set. The Content-Type HTTP header has the following form:
Content-Type: text/html; charset=utf-8
The charset parameter specifies the encoding for the HTML page. The possible values for the charset parameter are the IANA names for the character encodings that the browser supports.
Use this method primarily for static HTML pages. To specify HTML page encoding in the HTML page header, specify the character encoding in the HTML header as follows:
<meta http-equiv="Content-Type" content="text/html;charset=utf-8">
The charset parameter specifies the encoding for the HTML page. As with the Content-Type HTTP Header, the possible values for the charset parameter are the IANA names for the character encodings that the browser supports.
You can specify the encoding of an HTML page in the Content-Type HTTP header by setting the PHP configuration variable as follows:
default_charset = UTF-8
This setting does not imply any conversion of outgoing pages. Your application must ensure that the server-generated pages are encoded in UTF-8.
Making the user interface available in the local language of the user is a fundamental task in globalizing an application. Translatable sources for the content of an HTML page belong to the following categories:
Text strings included in the application code
Static HTML files, images files, and template files such as CSS
Dynamic data stored in the database
You should externalize translatable strings within your PHP application logic, so that the text is readily available for translation. These text messages can be stored in flat files or database tables depending on the type and the volume of the data being translated.
Static files such as HTML and GIF files are readily translatable. When these files are translated, they should be translated into the corresponding language with UTF-8 as the file encoding. To differentiate the languages of the translated files, stage the static files of different languages in different directories or with different file names.
Dynamic information such as product names and product descriptions is typically stored in the database. To differentiate various translations, the database schema holding this information should include a column to indicate the language. To select the desired language, you must include a WHERE clause in your query.
Data in the application must be presented in a way that conforms to the expectation of the user. Otherwise, the meaning of the data can be misinterpreted. For example, the date '12/11/05' implies '11th December 2005' in the United States, whereas in the United Kingdom it means '12th November 2005'. Similar confusion exists for number and monetary formats of the users. For example, the symbol '.' is a decimal separator in the United States; in Germany this symbol is a thousand separator.
Different languages have their own sorting rules. Some languages are collated according to the letter sequence in the alphabet, some according to the number of stroke counts in the letter, and some languages are ordered by the pronunciation of the words. Presenting data not sorted in the linguistic sequence that your users are accustomed to can make searching for information difficult and time consuming.
Depending on the application logic and the volume of data retrieved from the database, it may be more appropriate to format the data at the database level rather than at the application level. Oracle Database offers many features that help to refine the presentation of data when the locale preference of the user is known. The following sections provide examples of locale-sensitive operations in SQL.
The three different date presentation formats in Oracle Database are standard, short, and long dates. The following examples illustrate the differences between the short date and long date formats for both the United States and Germany.
SQL> alter session set nls_territory=america nls_language=american; Session altered. SQL> select employee_id EmpID, 2 substr(first_name,1,1)||'.'||last_name "EmpName", 3 to_char(hire_date,'DS') "Hiredate", 4 to_char(hire_date,'DL') "Long HireDate" 5 from employees 6* where employee_id <105; EMPID EmpName Hiredate Long HireDate ---------- --------------------------- ---------- ----------------------------- 100 S.King 06/17/1987 Wednesday, June 17, 1987 101 N.Kochhar 09/21/1989 Thursday, September 21, 1989 102 L.De Haan 01/13/1993 Wednesday, January 13, 1993 103 A.Hunold 01/03/1990 Wednesday, January 3, 1990 104 B.Ernst 05/21/1991 Tuesday, May 21, 1991
SQL> alter session set nls_territory=germany nls_language=german; Session altered. SQL> select employee_id EmpID, 2 substr(first_name,1,1)||'.'||last_name "EmpName", 3 to_char(hire_date,'DS') "Hiredate", 4 to_char(hire_date,'DL') "Long HireDate" 5 from employees 6* where employee_id <105; EMPID EmpName Hiredate Long HireDate ---------- --------------------------- -------- ------------------------------ 100 S.King 17.06.87 Mittwoch, 17. Juni 1987 101 N.Kochhar 21.09.89 Donnerstag, 21. September 1989 102 L.De Haan 13.01.93 Mittwoch, 13. Januar 1993 103 A.Hunold 03.01.90 Mittwoch, 3. Januar 1990 104 B.Ernst 21.05.91 Dienstag, 21. Mai 1991
The following examples illustrate the differences in the decimal character and group separator between the United States and Germany.
SQL> alter session set nls_territory=america; Session altered. SQL> select employee_id EmpID, 2 substr(first_name,1,1)||'.'||last_name "EmpName", 3 to_char(salary, '99G999D99') "Salary" 4 from employees 5* where employee_id <105 EMPID EmpName Salary ---------- --------------------------- ---------- 100 S.King 24,000.00 101 N.Kochhar 17,000.00 102 L.De Haan 17,000.00 103 A.Hunold 9,000.00 104 B.Ernst 6,000.00 SQL> alter session set nls_territory=germany; Session altered. SQL> select employee_id EmpID, 2 substr(first_name,1,1)||'.'||last_name "EmpName", 3 to_char(salary, '99G999D99') "Salary" 4 from employees 5* where employee_id <105 EMPID EmpName Salary ---------- --------------------------- ---------- 100 S.King 24.000,00 101 N.Kochhar 17.000,00 102 L.De Haan 17.000,00 103 A.Hunold 9.000,00 104 B.Ernst 6.000,00
Spain traditionally treats ch, ll as well as ñ as unique letters, ordered after c, l and n respectively. The following examples illustrate the effect of using a Spanish sort against the employee names Chen and Chung.
SQL> alter session set nls_sort=binary; Session altered. SQL> select employee_id EmpID, 2 last_name "Last Name" 3 from employees 4 where last_name like 'C%' 5* order by last_name EMPID Last Name ---------- ------------------------- 187 Cabrio 148 Cambrault 154 Cambrault 110 Chen 188 Chung 119 Colmenares 6 rows selected. SQL> alter session set nls_sort=spanish_m; Session altered. SQL> select employee_id EmpID, 2 last_name "Last Name" 3 from employees 4 where last_name like 'C%' 5* order by last_name EMPID Last Name ---------- ------------------------- 187 Cabrio 148 Cambrault 154 Cambrault 119 Colmenares 110 Chen 188 Chung 6 rows selected.
The NLS_LANGUAGE
parameter also controls the language of the database error messages being returned from the database. Setting this parameter prior to submitting your SQL statement ensures that the language-specific database error messages will be returned to the application.
Consider the following server message:
ORA-00942: table or view does not exist
When the NLS_LANGUAGE
parameter is set to French, the server message appears as follows:
ORA-00942: table ou vue inexistante
For more discussion of globalization support features in Oracle Database, see "Working in a Global Environment" in Oracle Database 2 Day Developer's Guide.
In this chapter, you extend the Anyco HR application from Chapter 3 by adding information to the Departments page. You also implement the functionality to query, insert, update, and delete employee records in a specific department.
This chapter has the following topics:
In this section, you will modify your application code by moving the database access logic into separate files for inclusion in the PHP application.
Copy the files that you completed in Chapter 3 to a new chap4
directory, and change to the newly created directory:
On Windows:
mkdir c:\program files\Apache Group\Apache2\htdocs\chap4 cd c:\program files\Apache Group\Apache2\htdocs\chap4 copy ..\chap3\* .
On Linux:
mkdir $HOME/public_html/chap4 cd $HOME/public_html/chap4 cp ../chap3/* .
Using your preferred editor, create a file called anyco_cn.inc
that defines named constants for the database connection information. This file enables you to change connection information in one place.
<?php // File: anyco_cn.inc define('ORA_CON_UN', 'hr'); // User name define('ORA_CON_PW', 'hr'); // Password define('ORA_CON_DB', '//localhost/orcl'); // Connection identifier ?>
For simplicity, the user name and password are written into this sample application code. For applications that will be deployed, coding the user name and password strings directly into your application source code is not recommended. Oracle recommends that you use a more secure technique, such as implementing a dialog that prompts the user for the user name and password.
See Oracle Database Security Guide and the documentation for your development environment for details on security features and practices.
Create a file called anyco_db.inc
that declares functions for creating a database connection, executing a query, and disconnecting from the database. Use the following logic, which includes some error handling that is managed by calling an additional function called db_error ()
:
<?php // File: anyco_db.inc function db_connect() { // use constants defined in anyco_cn.inc $conn = oci_connect(ORA_CON_UN, ORA_CON_PW, ORA_CON_DB); if (!$conn) { db_error(null, __FILE__, __LINE__); } return($conn); } function db_do_query($conn, $statement) { $stid = oci_parse($conn, $statement); if (!$stid) { db_error($conn, __FILE__, __LINE__); } $r = oci_execute($stid, OCI_DEFAULT); if (!$r) { db_error($stid, __FILE__, __LINE__); } $r = oci_fetch_all($stid, $results, null, null, OCI_FETCHSTATEMENT_BY_ROW); return($results); } // $r is the resource containing the error. // Pass no argument or false for connection errors function db_error($r = false, $file, $line) { $err = $r ? oci_error($r) : oci_error(); if (isset($err['message'])) { $m = htmlentities($err['message']); } else { $m = 'Unknown DB error'; } echo '<p><b>Error</b>: at line '.$line.' of '.$file.'</p>'; echo '<pre>'.$m.'</pre>'; exit; } ?>
The db_do_query()
function in this example uses the oci_fetch_all()
OCI8 function. The oci_fetch_all()
function accepts the following five parameters:
$results
, the output array variable containing the data returned for the query
The null
in the third parameter for the number of initial rows to skip is ignored.
The null
in the fourth parameter for the maximum number of rows to fetch is ignored. In this case, all the rows for the query are returned. For this example where the result set is not large, it is acceptable.
The last parameter flag OCI_FETCHSTATEMENT_BY_ROW
indicates that the data in the $results
array is organized by row, where each row contains an array of column values. A value of OCI_FETCHSTATEMENT_BY_COLUMN
causes the results
array to be organized by column, where each column entry contains an array of column values for each row. Your choice of value for this flag depends on how you intend to process the data in your logic.
To examine the structure of the result array, use the PHP var_dump()
function after the query has been executed. This is useful for debugging. For example:
print '<pre>'; var_dump($results); print '</pre>';
The db_error()
function accepts three arguments. The $r
parameter can be false or null for obtaining connection errors, or a connection resource or statement resource to obtain an error for those contexts. The $file
and $line
values are populated by using __FILE__
and __LINE__
, respectively, as the actual parameters to enable the error message to display the source file and line from which the database error is reported. This enables you to easily track the possible cause of errors.
The db_ error()
function calls the oci_error()
function to obtain database error messages.
The db_error()
function calls the isset()
function before printing the message. The isset()
function checks if the message component of the database error structure is set, or if the error is unknown.
Edit anyco_ui.inc
. To format the results of a single row from the DEPARTMENTS
table query in an HTML table format, insert the following function:
function ui_print_department($dept) { if (!$dept) { echo '<p>No Department found</p>'; } else { echo <<<END <table> <tr> <th>Department<br>ID</th> <th>Department<br>Name</th> <th>Manager<br>Id</th> <th>Location ID</th> </tr> <tr> END; echo '<td>'.htmlentities($dept['DEPARTMENT_ID']).'</td>'; echo '<td>'.htmlentities($dept['DEPARTMENT_NAME']).'</td>'; echo '<td>'.htmlentities($dept['MANAGER_ID']).'</td>'; echo '<td>'.htmlentities($dept['LOCATION_ID']).'</td>'; echo <<<END </tr> </table> END; } }
As noted in Chapter 3, do not prefix END;
lines with leading spaces. If you do, the rest of the document will be treated as part of the text to be printed.
Edit the anyco.php
file. Include the anyco_ui.inc
and anyco_db.inc
files, and call the database functions to query and display information for a department with a department_id
of 80 by using the following code. The file becomes:
<?php // File: anyco.php require('anyco_cn.inc'); require('anyco_db.inc'); require('anyco_ui.inc'); $query = 'SELECT department_id, department_name, manager_id, location_id FROM departments WHERE department_id = 80'; $conn = db_connect(); $dept = db_do_query($conn, $query); ui_print_header('Departments'); ui_print_department($dept[0]); ui_print_footer(date('Y-m-d H:i:s')); ?>
To test the resulting changes to the application, enter the following URL in a browser window:
On Windows:
http://localhost/chap4/anyco.php
On Linux:
http://localhost/~<username>/chap4/anyco.php
The page returned in the browser window should resemble the following page:
Using queries with values included in the WHERE clause may be useful for some situations. However, if the conditional values in the query are likely to change it is not appropriate to encode a value into the query. Oracle recommends that you use bind variables.
A bind variable is a symbolic name preceded by a colon in the query that acts as a placeholder for literal values. For example, the query string created in the anyco.php
file could be rewritten with the bind variable :did
as follows:
$query = 'SELECT department_id, department_name, manager_id, location_id FROM departments WHERE department_id = :did';
By using bind variables to parameterize SQL statements:
The statement is reusable with different input values without needing to change the code.
The query performance is improved through a reduction of the query parse time in the server, because the Oracle database can reuse parse information from the previous invocations of the identical query string.
There is protection against "SQL Injection" security problems.
There is no need to specially handle quotation marks in user input.
When a query uses a bind variable, the PHP code must associate an actual value with each bind variable (placeholder) used in the query before it is executed. This process is known as run-time binding.
To enable your PHP application to use bind variables in the query, perform the following changes to your PHP application code:
Edit the anyco.php
file. Modify the query to use a bind variable, create an array to store the value to be associated with the bind variable, and pass the $bindargs
array to the db_do_query()
function:
<?php // File: anyco.php ... $query = 'SELECT department_id, department_name, manager_id, location_id FROM departments WHERE department_id = :did'; $bindargs = array(); // In the $bindargs array add an array containing // the bind variable name used in the query, its value, a length array_push($bindargs, array('DID', 80, -1)); $conn = db_connect(); $dept = db_do_query($conn, $query, $bindargs); ... ?>
In this example, the bind variable, called DID, is an input argument in the parameterized query, and it is associated with the value 80. Later, the value of the bind variable will be dynamically determined. In addition, the length component is passed as -1 so that the OCI8 layer can determine the length. If the bind variable was used to return output from the database an explicit size would be required.
Edit the anyco_db.inc
file. Modify the db_do_query()
function to accept a $bindvars
array variable as a third parameter. Call the oci_bind_by_name()
OCI8 call to associate the PHP values supplied in $bindvars
parameter with bind variables in the query. The function becomes:
function db_do_query($conn, $statement, $bindvars = array()) { $stid = oci_parse($conn, $statement); if (!$stid) { db_error($conn, __FILE__, __LINE__); } // Bind the PHP values to query bind parameters foreach ($bindvars as $b) { // create local variable with caller specified bind value $$b[0] = $b[1]; // oci_bind_by_name(resource, bv_name, php_variable, length) $r = oci_bind_by_name($stid, ":$b[0]", $$b[0], $b[2]); if (!$r) { db_error($stid, __FILE__, __LINE__); } } $r = oci_execute($stid, OCI_DEFAULT); if (!$r) { db_error($stid, __FILE__, __LINE__); } $r = oci_fetch_all($stid, $results, null, null, OCI_FETCHSTATEMENT_BY_ROW); return($results); }
The binding is performed in the foreach
loop before the oci_execute()
is done.
For each entry in $bindvars
array, the first element contains the query bind variable name that is used to create a PHP variable of the same name; that is, $$b[0]
takes the value DID in $b[0]
and forms a PHP variable called $DID
whose value is assigned from the second element in the entry.
The oci_bind_by_name()
function accepts four parameters: the $stid
as the resource, a string representing the bind variable name in the query derived from the first element in the array entry, the PHP variable containing the value to be associated with the bind variable, and the length of the input value.
To test the results of the preceding modifications, save the anyco.php
and anyco_db.inc
files and enter the following URL:
On Windows:
http://localhost/chap4/anyco.php
On Linux:
http://localhost/~<username>/chap4/anyco.php
The page returned in the browser window should resemble the following page:
Adding navigation through the database records requires several important changes to the application logic. The modifications require the combination of:
Including an HTML form to provide Next and Previous navigation buttons to step through database records.
Detecting if the HTTP request for the page was posted by clicking the Next or Previous button.
Tracking the last row queried by using the HTTP session state. A PHP session is started to maintain state information for a specific client between HTTP requests. The first HTTP request will retrieve the first data row and initialize the session state. A subsequent request, initiated with navigation buttons, combined with the session state from a previous HTTP request, enables the application to set variables that control the next record retrieved by the query.
Writing a query that returns a subset of rows based on a set of conditions whose values are determined by the application state.
To add navigation through database rows, perform the following steps:
Edit the anyco_ui.inc
file. Add Next and Previous navigation buttons to the Departments page. Change the ui_print_department()
function to append a second parameter called $posturl
that supplies the value for the form attribute action
. After printing the </table>
tag include HTML form tags for the Next and Previous buttons:
<?php // File: anyco_ui.inc ... function ui_print_department($dept, $posturl) { ... echo <<<END </tr> </table> <form method="post" action="$posturl"> <input type="submit" value="< Previous" name="prevdept"> <input type="submit" value="Next >" name="nextdept"> </form> END; } } ?>
Edit the anyco.php
file. To detect if the Next or Previous button was used to invoke the page and track the session state, call the PHP function session_start()
, and create a function named construct_departments()
:
Move and modify the database access logic into a new construct_departments()
function, which detects if navigation has been performed, manages the session state, defines a subquery for the database access layer to process, and connects and calls a function db_get_page_data()
. The file becomes:
<?php // File: anyco.php require('anyco_cn.inc'); require('anyco_db.inc'); require('anyco_ui.inc'); session_start(); construct_departments(); function construct_departments() { if (isset($_SESSION['currentdept']) && isset($_POST['prevdept']) && $_SESSION['currentdept'] > 1) { $current = $_SESSION['currentdept'] - 1; } elseif (isset($_SESSION['currentdept']) && isset($_POST['nextdept'])) { $current = $_SESSION['currentdept'] + 1; } elseif (isset($_POST['showdept']) && isset($_SESSION['currentdept'])) { $current = $_SESSION['currentdept']; } else { $current = 1; } $query = 'SELECT department_id, department_name, manager_id, location_id FROM departments ORDER BY department_id asc'; $conn = db_connect(); $dept = db_get_page_data($conn, $query, $current, 1); $deptid = $dept[0]['DEPARTMENT_ID']; $_SESSION['currentdept'] = $current; ui_print_header('Department'); ui_print_department($dept[0], $_SERVER['SCRIPT_NAME']); ui_print_footer(date('Y-m-d H:i:s')); } ?>
The if
and elseif
construct at the start of the construct_departments()
function is used to detect if a navigation button was used with an HTTP post request to process the page, and tracks if the currentdept
number is set in the session state. Depending on the circumstances, the variable $current
is decremented by one when the previous button is clicked, $current
is incremented by one when the Next button is clicked, otherwise $current
is set to the current department, or initialized to one for the first time through.
A query is formed to obtain all the department rows in ascending sequence of the department_id
. The ORDER BY
clause is an essential part of the navigation logic. The query is used as a subquery inside the db_get_page_data()
function to obtain a page of a number of rows, where the number of rows per page is specified as the fourth argument to the db_get_page_data()
function. After connecting to the database, db_get_page_data()
is called to retrieve the set of rows obtained for the specified query. The db_get_page_data()
function is provided with the connection resource, the query string, a value in $current
specifying the first row in the next page of data rows required, and the number of rows per page (in this case one row per page).
After db_get_page_data()
has been called to obtain a page of rows, the value of $current
is stored in the application session state.
Between printing the page header and footer, the ui_print_department()
function is called to display the recently fetched department row. The ui_print_department()
function uses $_SERVER['SCRIPT_NAME']
to supply the current PHP script name for the $posturl
parameter. This sets the action attribute in the HTML form, so that each Next or Previous button click calls the anyco.php
file.
Edit the anyco_db.inc
file. Implement the db_get_page_data()
function to query a subset of rows:
// Return subset of records function db_get_page_data($conn, $q1, $current = 1, $rowsperpage = 1, $bindvars = array()) { // This query wraps the supplied query, and is used // to retrieve a subset of rows from $q1 $query = 'SELECT * FROM (SELECT A.*, ROWNUM AS RNUM FROM ('.$q1.') A WHERE ROWNUM <= :LAST) WHERE :FIRST <= RNUM'; // Set up bind variables. array_push($bindvars, array('FIRST', $current, -1)); array_push($bindvars, array('LAST', $current+$rowsperpage-1, -1)); $r = db_do_query($conn, $query, $bindvars); return($r); }
The structure of the query in the db_get_page_data()
function enables navigation through a set (or page) of database rows.
The query supplied in $q1
is nested as a subquery inside the following subquery:
SELECT A.*, ROWNUM AS RNUM FROM $q1 WHERE ROWNUM <= :LAST
Remember that the query supplied in $q1
retrieves an ordered set of rows, which is filtered by its enclosing query to return all the rows from the first row to the next page size ($rowsperpage
) of rows. This is possible because the Oracle ROWNUM
function (or pseudocolumn) returns an integer number starting at 1 for each row returned by the query in $q1
.
The set of rows, returned by the subquery enclosing query $q1
, is filtered a second time by the condition in the following outermost query:
WHERE :FIRST <= RNUM
This condition ensures that rows prior to the value in :FIRST
(the value in $current
) are excluded from the final set of rows. The query enables navigation through a set rows where the first row is determined by the $current
value and the page size is determined by the $rowsperpage
value.
The $current
value is associated with the bind variable called :FIRST.
The expression $current+$rowsperpage-1
sets the value associated with the :LAST
bind variable.
To test the changes made to your application, save the changed files, and enter the following URL in your Web browser:
On Windows:
http://localhost/chap4/anyco.php
On Linux:
http://localhost/~<username>/chap4/anyco.php
When you request the anyco.php
page, the first DEPARTMENT
table record, the Administration department, is displayed:
To navigate to the next department record (Marketing), click Next:
To navigate back to the first department record (Administration), click Previous:
You may continue to test and experiment with the application by clicking Next and Previous to navigate to other records in the DEPARTMENTS
table, as desired.
Note: If you navigate past the last record in theDEPARTMENTS table, an error will occur. Error handling is added in Adding Error Recovery in Chapter 5. |
If you were writing a PHP function with a hard coded query, the ROW_NUMBER() function may be a simpler alternative for limiting the number of rows returned. For example, a query that returns the last name of all employees:
SELECT last_name FROM employees ORDER BY last_name;
could be written to select rows 51 to 100 inclusive as:
SELECT last_name FROM
SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name R FROM employees)
where
R BETWEEN 51 AND 100;
The Departments page is extended to include the following additional information:
The name of the manager of the department
The number of employees assigned to the department
The country name identifying the location of the department
The additional information is obtained by modifying the query to perform a join operation between the DEPARTMENTS
, EMPLOYEES
, LOCATIONS
, and COUNTRIES
tables.
To extend the Departments page, perform the following tasks:
Edit the anyco_ui.inc
file. Modify the ui_print_department()
function by replacing the Manager ID and Location ID references with the Manager Name and Location, respectively, and insert a Number of Employees field after Department Name. Make the necessary changes in the table header and data fields. The function becomes:
function ui_print_department($dept, $posturl) { if (!$dept) { echo '<p>No Department found</p>'; } else { echo <<<END <table> <tr> <th>Department<br>ID</th> <th>Department<br>Name</th> <th>Number of<br>Employees</th> <th>Manager<br>Name</th> <th>Location</th> </tr> <tr> END; echo '<td>'.htmlentities($dept['DEPARTMENT_ID']).'</td>'; echo '<td>'.htmlentities($dept['DEPARTMENT_NAME']).'</td>'; echo '<td>'.htmlentities($dept['NUMBER_OF_EMPLOYEES']).'</td>'; echo '<td>'.htmlentities($dept['MANAGER_NAME']).'</td>'; echo '<td>'.htmlentities($dept['COUNTRY_NAME']).'</td>'; echo <<<END </tr> </table> <form method="post" action="$posturl"> <input type="submit" value="< Previous" name="prevdept"> <input type="submit" value="Next >" name="nextdept"> </form> END; } }
Edit the anyco.php
file. Replace the query string in construct_departments()
with:
$query = "SELECT d.department_id, d.department_name, substr(e.first_name,1,1)||'. '|| e.last_name as manager_name, c.country_name, count(e2.employee_id) as number_of_employees FROM departments d, employees e, locations l, countries c, employees e2 WHERE d.manager_id = e.employee_id AND d.location_id = l.location_id AND d.department_id = e2.department_id AND l.country_id = c.country_id GROUP BY d.department_id, d.department_name, substr(e.first_name,1,1)||'. '||e.last_name, c.country_name ORDER BY d.department_id ASC";
The query string is enclosed in double quotation marks to simplify writing this statement, which contains SQL literal strings in single quotation marks.
Save the changes to your files, and test the changes by entering the following URL in a Web browser:
On Windows:
http://localhost/chap4/anyco.php
On Linux:
http://localhost/~<username>/chap4/anyco.php
The Web page result should resemble the following output:
2 Day + PHP Developer's Guide
11g Release 2 (11.2)
E10811-01
July 2009
Oracle Database 2 Day + PHP Developer's Guide, 11g Release 2 (11.2)
E10811-01
Copyright © 2009, Oracle and/or its affiliates. All rights reserved.
Primary Author: Simon Watt
Contributors: Christopher Jones, Simon Law, Glenn Stokol
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this software or related documentation is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.
This software is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications which may create a risk of personal injury. If you use this software in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software in dangerous applications.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
This software and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.
In this chapter, you create HR application files that implement PHP functions to connect and disconnect to the Oracle Database. You also develop a PHP function that enables you to execute a query to validate that a database connection has been successfully established.
This chapter also guides you through the creation and modification of PHP files that call a function to produce the header and footer for the Departments page, where the footer section of the page includes a date and time.
This chapter has the following topics:
Note: For simplicity, the user name and password are written into this sample application code. For applications that will be deployed, coding the user name and password strings directly into your application source code is not recommended. Oracle recommends that you use a more secure technique, such as implementing a dialog that prompts the user for the user name and password.See Oracle Database Security Guide and the documentation for your development environment for details on security features and practices. |
In this section, you will create the functions and styles for the first screen of your application.
Follow these steps to build the Departments page:
To create a directory for your application files, and to change to the newly created directory, enter the following commands in a command window:
On Windows:
mkdir c:\program files\Apache Group\Apache2\htdocs\chap3 cd c:\program files\Apache Group\Apache2\htdocs\chap3
On Linux:
mkdir $HOME/public_html/chap3 cd $HOME/public_html/chap3
If you create files in a different location, you must change the steps for file editing and execution to match your working directory name and URL.
To start developing your application user interface, use your preferred text editor to create a file called anyco_ui.inc
that contains the two functions ui_print_header()
and ui_print_footer()
with their parameters to enable your application Web pages to have consistent header and footer sections:
<?php function ui_print_header($title) { $title = htmlentities($title); echo <<<END <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <link rel="stylesheet" type="text/css" href="style.css"> <title>Any Co.: $title</title> </head> <body> <h1>$title</h1> END; } function ui_print_footer($date) { $date = htmlentities($date); echo <<<END <div class="footer"> <div class="date">$date</div> <div class="company">Any Co.</div> </div> END; } ?>
This application design uses PHP function definitions to enable modular reusable code.
The functions in anyco_ui.inc
use a PHP language construct called a "here document." This enables you to place any amount of HTML formatted text between the following two lines:
echo <<<END END;
Do not put leading spaces in the END;
line. If you do, the rest of the document will be treated as part of the text to be printed.
Any PHP parameters appearing inside the body of a "here document" are replaced with their values, for example, the $title
or $date
parameters.
The PHP function htmlentities()
is used to prevent user-supplied text from accidentally containing HTML markup and affecting the output formatting.
The PHP file uses a Cascading Style Sheet (CSS) file called style.css
to specify the presentation style in HTML in the browser.
Create a style.css
file in the chap3
directory with the following CSS text:
body { background: #CCCCFF; color: #000000; font-family: Arial, sans-serif; } h1 { border-bottom: solid #334B66 4px; font-size: 160%; } table { padding: 5px; } td { border: solid #000000 1px; text-align: left; padding: 5px; } th { text-align: left; padding: 5px; } .footer { border-top: solid #334B66 4px; font-size: 90%; } .company { padding-top: 5px; float: right; } .date { padding-top: 5px; float: left; }
To call the user interface functions, create the anyco.php
file with the following text:
<?php require('anyco_ui.inc'); ui_print_header('Departments'); ui_print_footer(date('Y-m-d H:i:s')); ?>
The require()
PHP command is used to include anyco_ui.inc
. The new functions can be called to produce HTML output.
To test the anyco.php
file, enter the following URL in your browser:
On Windows:
http://localhost/chap3/anyco.php
On Linux:
http://localhost/~<username>/chap3/anyco.php
The resulting Web page is similar to the following:
The date and time appear in the page footer section.
In this section, you will add a database connection to your Departments screen so that you can display Department data.
Follow these steps to add a database connection to your application.
To form a database connection, you use the oci_connect()
function with three string parameters:
$conn = oci_connect($username, $password, $db)
The first and second parameters are the database user name and password, respectively. The third parameter is the database connection identifier. The oci_connect()
function returns a connection resource needed for other OCI8 calls; it returns FALSE if an error occurs. The connection identifier returned is stored in a variable called $conn
.
Edit the anyco.php
file to add a database connection with the following parameter values:
Username is hr
.
Password for this example is hr
. Remember to use the actual password of your HR
user.
Oracle connection identifier is //localhost/orcl
.
Edit the anyco.php
file to validate that the oci_connect()
call returns a usable database connection, write a do_query()
function that accepts two parameters: the database connection identifier, obtained from the call to oci_connect()
, and a query string to select all the rows from the DEPARTMENTS
table.
Edit the anyco.php
file to prepare the query for execution, add an oci_parse()
call. The oci_parse()
function has two parameters, the connection identifier and the query string. It returns a statement identifier needed to execute the query and fetch the resulting data rows. It returns FALSE
if an error occurs.
Edit the anyco.php
file to execute the query, add a call to the oci_execute()
function. The oci_execute() function executes the statement associated with the statement identifier provided in its first parameter. The second parameter specifies the execution mode. OCI_DEFAULT
is used to indicate that you do not want statements to be committed automatically. The default execution mode is OCI_COMMIT_ON_SUCCESS
. The oci_execute()
function returns TRUE
on success; otherwise it returns FALSE
.
Edit the anyco.php
file to fetch all the rows for the query executed, add a while
loop and a call to the oci_fetch_array()
function. The oci_fetch_array()
function returns the next row from the result data; it returns FALSE
if there are no more rows. The second parameter of the oci_fetch_array()
function, OCI_RETURN_NULLS
, indicates that NULL
database fields will be returned as PHP NULL values.
Each row of data is returned as a numeric array of column values. The code uses a PHP foreach
construct to loop through the array and print each column value in an HTML table cell, inside a table row element. If the item value is NULL
then a nonbreaking space is printed; otherwise the item value is printed.
After the edits in Steps 1 to 5, the anyco.php
file becomes:
<?php // File: anyco.php require('anyco_ui.inc'); // Create a database connection $conn = oci_connect('hr', 'hr', '//localhost/orcl'); ui_print_header('Departments'); do_query($conn, 'SELECT * FROM DEPARTMENTS'); ui_print_footer(date('Y-m-d H:i:s')); // Execute query and display results function do_query($conn, $query) { $stid = oci_parse($conn, $query); $r = oci_execute($stid, OCI_DEFAULT); print '<table border="1">'; while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) { print '<tr>'; foreach ($row as $item) { print '<td>'. ($item!== null ? htmlentities($item) : ' ').'</td>'; } print '</tr>'; } print '</table>'; } ?>
To test the changes made to anyco.php
, save the modified anyco.php
file. In a browser window, enter the following URL:
On Windows:
http://localhost/chap3/anyco.php
On Linux:
http://localhost/~<username>/chap3/anyco.php
The page returned in the browser window should resemble the following page:
If you want to query the EMPLOYEES data, you can optionally change the query in the do_query()
function call to:
do_query($conn, 'SELECT * FROM EMPLOYEES');
Check that the username, password and connection string are valid. The connect string '//localhost/orcl'
uses the Oracle Easy Connect syntax. If you are using an Oracle Net tnsnames.ora
file to specify the database you want to connect to, then use the network alias as the third parameter to the oci_connect()
function.
If you are not seeing errors, set the PHP directive display_errors
to ON,
and the error_reporting
directive to E_ALL|E_STRICT
.
If you have a PHP code problem and are not using a debugger, you can examine variables using the PHP var_dump()
function. For example:
print '<pre>'; var_dump($r); print '</pre>';
In some applications, using a persistent connection improves performance by removing the need to reconnect each time the script is called. Depending on your Apache configuration, this may cause a number of database connections to remain open simultaneously. You must balance the connection performance benefits against the overhead on the database server.
Persistent connections are made with the OCI8 oci_pconnect()
function. Several settings in the PHP initialization file enable you to control the lifetime of persistent connections. Some settings include:
oci8.max_persistent - This controls the number of persistent connections per process.
oci8.persistent_timeout - This specifies the time (in seconds) that a process maintains an idle persistent connection.
oci8.ping_interval - This specifies the time (in seconds) that must pass before a persistent connection is "pinged" to check its validity.
For more information, see the PHP reference manual at
http://www.php.net/manual/en/ref.oci8.php
For information about connection pooling, see Connection Pooling in OCI in the Oracle Call Interface Programmer's Guide and the Oracle Database Net Services Administrator's Guide.
The PHP engine automatically closes the database connection at the end of the script unless a persistent connection was made. If you want to explicitly close a non-persistent database connection, you can call the oci_close()
OCI function with the connection identifier returned by the oci_connect()
call. For example:
<?php
$conn = oci_connect('hr', '<your_password>', '//localhost/orcl');
...
oci_close($conn);
...
?>
Because PHP uses a reference counting mechanism for tracking variables, the database connection may not actually be closed until all PHP variables referencing the connection are unset or go out of scope.
Oracle Database 2 Day + PHP Developer's Guide introduces developers to the use of PHP to access Oracle Database.
This preface contains these topics:
Oracle Database 2 Day + PHP Developer's Guide is an introduction to application development using PHP and Oracle Database.
This document assumes that you have a cursory understanding of SQL, PL/SQL, and PHP.
Our goal is to make Oracle products, services, and supporting documentation accessible to all users, including users that are disabled. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Accessibility standards will continue to evolve over time, and Oracle is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For more information, visit the Oracle Accessibility Program Web site at http://www.oracle.com/accessibility/
.
Accessibility of Code Examples in Documentation
Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace.
Accessibility of Links to External Web Sites in Documentation
This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites.
Deaf/Hard of Hearing Access to Oracle Support Services
To reach Oracle Support Services, use a telecommunications relay service (TRS) to call Oracle Support at 1.800.223.1711. An Oracle Support Services engineer will handle technical issues and provide customer support according to the Oracle service request process. Information about TRS is available at http://www.fcc.gov/cgb/consumerfacts/trs.html
, and a list of phone numbers is available at http://www.fcc.gov/cgb/dro/trsphonebk.html
.
For more information, see these Oracle resources:
The following text conventions are used in this document:
Convention | Meaning |
---|---|
boldface | Boldface type indicates graphical user interface elements associated with an action, or terms defined in text. |
italic | Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values. |
monospace | Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter. |