Oracle B25-317-01 User Manual

Oracle Database Express Edition®

2 Day Plus PHP Developer Guide

10g Release 2 (10.2)

B25317-01

September 2005

Oracle Database Express Edition 2 Day Plus PHP Developer Guide, 10g Release 2 (10.2)

B25317-01

Contributing Author: Christopher Jones, Simon Law, Glenn Stokol, Simon Watt.

The Programs (which include both the software and documentation) contain proprietary information; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly, or decompilation of the Programs, except to the extent required to obtain interoperability with other independently created software or as specified by law, is prohibited.

The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. This document is not warranted to be error-free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose.

If the Programs are delivered to the United States Government or anyone licensing or using the Programs on behalf of the United States 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, use, duplication, disclosure, modification, and adaptation of the Programs, including documentation and technical data, shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement, and, to the extent applicable, the additional rights set forth in FAR 52.227-19, Commercial Computer Software—Restricted Rights (June 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065

The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup, redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and we disclaim liability for any damages caused by such use of the Programs.

Oracle, JD Edwards, PeopleSoft, and Retek are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Zend is a registered trademark of Zend Technologies Ltd.

The Programs may provide links to Web sites and access to content, products, and services from third parties. Oracle is not responsible for the availability of, or any content provided on, third-party Web sites. You bear all risks associated with the use of such content. If you choose to purchase any products or services from a third party, the relationship is directly between you and the third party. Oracle is not responsible for: (a) the quality of third-party products or services; or (b) fulfilling any of the terms of the agreement with the third party, including delivery of products or services and warranty obligations related to purchased products or services. Oracle is not responsible for any loss or damage of any sort that you may incur from dealing with any third party.

Alpha and Beta Draft documentation are considered to be in prerelease status. This documentation is intended for demonstration and preliminary use only. We expect that you may encounter some errors, ranging from typographical errors to data inaccuracies. This documentation is subject to change without notice, and it may not be specific to the hardware on which you are using the software. Please be advised that prerelease documentation in not warranted in any manner, for any purpose, and we will not be responsible for any loss, costs, or damages incurred due to the use of this documentation.

Preface................................................................................................................................................................ v

Audience....................................................................................................................................................... v

Documentation Accessibility ..................................................................................................................... v

Related Documents ..................................................................................................................................... vi

Conventions ................................................................................................................................................. vi

1 Introducing PHP with Oracle Database XE

Zend Core for Oracle ............................................................................................................................... 1-1

Purpose ....................................................................................................................................................... 1-1

Overview of the Sample Application................................................................................................... 1-1

Resources ................................................................................................................................................... 1-3

2 Getting Started

What You Need ........................................................................................................................................ 2-1

Obtaining Oracle Database Express Edition (Oracle Database XE)............................................ 2-1

Obtaining Apache ............................................................................................................................. 2-1

Obtaining Zend Core for Oracle ...................................................................................................... 2-1

Testing the Oracle Database XE Installation....................................................................................... 2-2

Testing the Apache Installation............................................................................................................. 2-3

Setting Up Zend Core for Oracle........................................................................................................... 2-4

Installing Zend Core for Oracle on Linux ...................................................................................... 2-5

Configuring Zend Core for Oracle .................................................................................................. 2-6

Testing the Zend Core for Oracle Installation .................................................................................... 2-7

3 Getting Connected

Building the Departments Page ............................................................................................................ 3-1

Connecting to the Database.................................................................................................................... 3-4

Other Ways to Connect ..................................................................................................................... 3-5

Disconnecting from the Database......................................................................................................... 3-6

4 Querying Data

Centralizing the Database Application Logic..................................................................................... 4-1

Writing Queries with Bind Variables ................................................................................................... 4-4

Navigating Through Database Records ............................................................................................... 4-6

Beta Draft iii

Extending the Basic Departments Form............................................................................................ 4-10

Building the Basic Employee Form.................................................................................................... 4-12

5 Updating Data

Extending the Basic Employee Form .................................................................................................... 5-1

Combining Departments and Employees ........................................................................................ 5-11

Adding Error Recovery......................................................................................................................... 5-14

Further Error Handling ........................................................................................................................ 5-22

6 Executing Stored Procedures and Functions

Using PL/SQL to Capture Business Logic ........................................................................................... 6-1

Using PL/SQL Ref Cursors to Return Result Sets ............................................................................. 6-5

7 Loading Images

Using Oracle LOBs to Store and Load Employee Images ................................................................ 7-1

Resizing Images........................................................................................................................................ 7-8

8 Building Global Applications

Establishing the Environment between Oracle and PHP................................................................. 8-1

String Manipulation ................................................................................................................................ 8-2

Determining User's Locale ..................................................................................................................... 8-3

Developing Locale Awareness............................................................................................................... 8-3

Encoding HTML Pages............................................................................................................................ 8-4

Specifying the Page Encoding for HTML Pages ........................................................................... 8-4

Specifying the Page Encoding in PHP ............................................................................................ 8-4

Organizing the Content of HTML Pages for Translation ................................................................ 8-5

Strings in PHP .................................................................................................................................... 8-5

Static Files ............................................................................................................................................ 8-5

Data from the Database..................................................................................................................... 8-5

Presenting Data following User's Locale Convention ...................................................................... 8-5

Oracle Date Formats .......................................................................................................................... 8-6

Oracle Number Formats.................................................................................................................... 8-6

Oracle Linguistic Sorts....................................................................................................................... 8-7

Oracle Error Messages ....................................................................................................................... 8-8

Index

iv Beta Draft

The Oracle Database Express Edition 2 Day Plus PHP Developer Guide introduces developers to the use of PHP to access Oracle Database Express Edition.

This preface contains these topics:

Q Audience

Q Documentation Accessibility

Q Related Documents

Q Conventions

Audience

The Oracle Database Express Edition 2 Day Plus PHP Developer Guide is intended as an introduction to application development using Zend Core for Oracle and Oracle Database Express Edition.

This document assumes a basic understanding of the SQL, PL/SQL and PHP.

Documentation Accessibility

Preface

Our goal is to make Oracle products, services, and supporting documentation accessible, with good usability, to the disabled community. 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.

Beta Draft v

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.

Conventions

Q Oracle Database SQL Reference

Q Oracle Call Interface Programmer's Guide

Q Oracle Database Concepts

Q Oracle Database Application Developer's Guide - Fundamentals

Q Oracle Database Globalization Support Guide

Q Oracle Database Error Messages

The examples in this book use the HR sample schema, which is installed by default. See Oracle Database Sample Schemas for information about this schema.

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 or the glossary.

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.

vi Beta Draft

Introducing PHP with Oracle Database XE

Oracle® Database Express Edition (Oracle Database XE) is a relational database that you can use to store, use, and modify data. Zend Core for Oracle enables application development using PHP.

This chapter has the following topics:

Q Zend Core for Oracle

Q Purpose

Q Overview of the Sample Application

Q Resources

Zend Core for Oracle

Zend Core for Oracle, developed in partnership with Zend Technologies provides a seamless out-of-the-box experience delivering a stable, high performance, easy-to-install and supported PHP development and production environment fully integrated with Oracle Database Express Edition.

Purpose

This guide is a tutorial that shows you how to use Zend Core for Oracle to connect to Oracle Database XE, and demonstrates how to use PHP to access and modify data.

Overview of the Sample Application

This document guides you through the development of a simple Human Resources (HR) application for a fictitious company ""AnyCo Corp".

The application manages departmental and employee information stored in the DEPARTMENTS and EMPLOYEES tables in the HR schema provided with Oracle Database XE.

The complete sample application:

1. Establishes a connection to the database using PHP's OCI8 extension

2. Queries the database for department and employee data

3. Displays and navigates through the data

4. Shows how to insert, update, anddelete employee records

5. Handles data exceptions

Beta Draft Introducing PHP with Oracle Database XE 1-1

Overview of the Sample Application

Uploads and displays employee photographs

Figure 1-1 shows the relationship between the files developed for this application:

Figure 1–1 Functionality in the Sample PHP Application

The sample application files are:

anyco.php

This file has the main logic for the AnyCo application. It contains control logic to decide which page is displayed. It manages session data for navigation. The functionality in anyco_cn.inc, anyco_db.inc , and anyco_ui.inc is used by it.

anyco_ui.inc

This file contains the functions used for presentation of data and forms in a HTML page.

anyco_cn.inc

This file contains definitions for database connection information: the database username, password, and database connect identifier.

anyco_db.inc

This file contains database logic to create connections, execute queries, and execute data manipulation statements.

anyco_im.php

This file contains logic to retrieve an image from a database column and send it to a browser for display as a JPEG image.

style.css

This file contains Cascading Style Sheet definitions for various HTML tags generated by the application. It manages the look and feel of the application.

Files with the suffix .inc are PHP code files included in other PHP files.

Files with the suffix .php can be loaded in a browser.

You can create and edit the PHP application source files in a text editor or any tool that supports PHP development.

1-2 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft

Resources

The code for each chapter builds on the files completed in the previous chapter.

This tutorial creates files in your $HOME/public_html directory. This is the default location for web access if the Apache web server configuration has the UserDir directive enabled. If you create files in a different location, you need to change the steps for file editing and execution to match your working directory name and URL.

Q Oracle Database Express Edition Developer Center on the Oracle Technology

Network at: http://www.oracle.com/technology/xe

Q PHP Developer Center on the Oracle Technology Network at:

http://www.oracle.com/technology/tech/php/index.html

Q Zend Core for Oracle Developer Center at:

http://www.oracle.com/technology/tech/php/zendcore/index.html

Q Oracle Database Express Edition documentation on the Oracle Technology

Network at: http://www.oracle.com/technology/xe/documentation

Q The Oracle Database Documentation Library on the Oracle Technology Network

at: http://www.oracle.com/technology/documentation

Beta Draft Introducing PHP with Oracle Database XE 1-3

Resources

1-4 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft

This chapter explains how to install and test your Oracle Database Express Edition (Oracle Database XE) and PHP environment. It has the following topics:

Q What You Need

Q Testing the Oracle Database XE Installation

Q Testing the Apache Installation

Q Setting Up Zend Core for Oracle

Q Testing the Zend Core for Oracle Installation

What You Need

Q Oracle Database Express Edition 10gR2

Q Apache 1.3.x or later

Q Zend Core for Oracle

Getting Started

Q Text editor for editing PHP code.

Obtaining Oracle Database Express Edition (Oracle Database XE)

Oracle Database Express Edition is available from the Oracle Technology Network at:

http://www.oracle.com/technology/xe.

Obtaining Apache

Apache is normally a standard part of the Linux environment. If Apache is not available on your Linux platform, you can download it from

http://httpd.apache.org

Obtaining Zend Core for Oracle

1. To obtain Zend Core for Oracle for the Linux Platform enter the following URL in

your Web Browser:

Beta Draft Getting Started 2-1

Testing the Oracle Database XE Installation

http://www.oracle.com/technology/tech/php/zendcore/index.html

To the right of the "Zend Core for Oracle" Web page, click the Free Download

image:

3. Save the downloaded file in a temporary directory, such as /tmp.

Testing the Oracle Database XE Installation

1. The PHP application connects to the database as the HR user. You may need to

execute the following SQL command, as a user with DBA privileges:

alter user hr account unlock identified by hr;

2. To test that Oracle Database XE is accessible, connect to the database using the

HTML DB web interface, or enter the following commands in a command window:

export ORACLE_HOME= export PATH=$ORACLE_HOME/bin:$PATH

sqlplus hr/hr@localhost

Terminate the SQL*Plus session by entering the exit command at the SQL prompt:

SQL> exit

2-2 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft

See also: For further information about unlocking an Oracle

Database account, see Chapter 6, "Managing Users and Security", in the Oracle Database Express Edition 2 Day DBA guide.

Testing the Apache Installation

1. Start your web browser and enter the following URL:

http://localhost

Your browser should display a page similar to:

Testing the Apache Installation

2. In the default Apache Web server configuration file set up a public virtual

directory as public_html for accessing your PHP files. By using your preferred editor open the Apache configuration file /etc/httpd/conf/httpd.conf and remove the "#" character at the start of the line with the following directive:

#UserDir public_html

This enables a browser to make a HTTP request using a registered user on the system and to serve files from the users $HOME/public_html directory. For example:

http://localhost/~user

For example: your Apache httpd.conf file should contain the following lines:

<IfModule mod_userdir.c> # # UserDir is disabled by default since it can confirm the presence # of a username on the system (depending on home directory # permissions). # #UserDir disable

# # To enable requests to /~user/ to serve the user's public_html # directory, remove the "UserDir disable" line above, and uncomment

Beta Draft Getting Started 2-3

Setting Up Zend Core for Oracle

# the following line instead: # UserDir public_html </IfModule>

3. In a command window, to use the new Apache configuration file restart Apache

by entering the following commands:

su Password: <enter your su (root) password> apachectl restart

4. In the command window, login as a normal (non-root) user and create a

public_html sub-directory in the users $HOME directory, by using the following commands:

su - gstokol Password for gstokol: <enter the password> mkdir $HOME/public_html

5. If Apache is not running and you get an error page or do not get the expected

results. In a command window, start the Apache Web server on your machine using the following commands:

su Password: <enter your su (root) password> apachectl start

If the Apache Web server does not start you may need to check the error log files to determine the cause. It may be a configuration error.

Setting Up Zend Core for Oracle

This tutorial is specific to PHP in Zend Core for Oracle.

For detailed setup information for Zend Core for Oracle, see the Installation Guide under Product Information on the Zend Core for Oracle web page at

http://www.oracle.com/technology/tech/php/zendcore/index.html.

2-4 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft

Installing Zend Core for Oracle on Linux

1. To extract the contents of the downloaded Zend Core for Oracle software as a root

user, which is required to install the software. In a command window enter:

su Password: <enter the root password> cd /tmp tar -zxf ZendCoreForOracle-v1.2.1-Linux-x86.tar.gz

By default, unless specified otherwise, files are extracted to a sub-directory called ZendCoreForOracle-v1.2.1-Linux-x86.

2. To start the Zend Core for Oracle installation process, enter the following

commands:

Setting Up Zend Core for Oracle

cd ZendCoreForOracle-v1.2.1-Linux-x86 ./install

The install command must be executed with root user privileges. After the ./install command is entered the installation process begins as documented in subsequent steps.

3. In the initial "Zend Core for Oracle Installation" page, click OK.

4. In the "Zend Core for Oracle V.1" page, read the license agreement. To continue

with the installation, click Exit.

5. When prompted to accept the terms of the license, click Ye s.

6. When prompted to specify the location for installing Zend Core for Oracle, accept

the default (or enter your preferred location), and click OK.

The installer begins extracting the files required for the installation.

7. When the progress window indicates all the software has been installed you are

prompted to "Please enter the GUI password". In the "Password" field, enter your password, for example oracle, and click OK.

The password specified here allows you to log into the Zend Core for Oracle administration Web pages to enable configuration of Zend Core for Oracle engine directives or property values.

Beta Draft Getting Started 2-5

Setting Up Zend Core for Oracle

When prompted to "Verify the password", enter the same password as specified in

the previous step and click OK.

9. In the Zend Core support page, you may optionally enter a Zend network user ID

and password. In this case, the assumption is that you have already registered a Zend network user ID and password when you downloaded the software, and therefore click No.

If you have not registered, you may still click No, and register at a later time using the Zend Core Web page http://www.zend.com.

10. The next page prompts you to select the web server for Zend Core installation.

Select the first entry Apache 2.0.52 (/etc/httpd/conf/httpd.conf), the default Apache installed with Linux. Click OK.

If you desire, you may continue to install Zend Core with other supported Web servers installed on your system.

11. In the page confirming your Web server selection, at the "Do you wish to

proceed?" prompt, click Yes.

12. In the next installation page, you are prompted to "Please select an installation

method for Apache 2.0.52". Select the first entry Apache module as the method, and click OK.

13. In the next installation page, prompting "Please select a virtual server for the Zend

Core GUI", select the first entry Main Server, click OK.

14. In the next installation page, after selecting the virtual server, at the "Would you

like to restart the Web Server" prompt, click Yes.

15. When prompted "Would you like to configure another Web Server to use Zend

Core", click No.

16. In the final installation page containing "Thank you for installing Zend Core for

Oracle" lists useful configuration commands and Web page for administration of the Zend Core engine. Take note of the information and click EXIT.

17. When the Zend Core installation pages are terminated, a message is displayed in

your command window.

The Zend Core for Oracle installation is now complete.

Configuring Zend Core for Oracle

In this section, you configure environment variables, and Zend Core directives that control default error reporting in web pages.

1. In a web browser, enter the following URL to access the Zend Core administration

page:

http://localhost/ZendCore

The Zend Core for Oracle welcome page is displayed.

2. In the Zend Core for Oracle Welcome page in the Password field, enter the GUI

password, which you provided during Zend Core for Oracle installation. Click the login >>> icon.

3. In the Zend Core for Oracle administration GUI page, the main "Control Center"

tab page is displayed with the "System Overview" tab page selected. To display the configuration options, click the Configuration tab.

2-6 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft

Testing the Zend Core for Oracle Installation

In the PHP tab page, which is selected by default, expand the Error Handling and

Logging configuration entry by clicking the + icon.

5. In the PHP Configuration page, to enable the display of errors in the HTML script

output, set the display_errors directive On.

The GUI application is aware that you have unsaved changes. Under the PHP Configuration page header notice the "Unsaved configuration" message.

6. In the PHP Configuration page, to save the configuration change, click the Save

Settings link.

Saving configuration changes typically requires the Apache server to be restarted. Under the PHP Configuration page header notice the "Please Restart Apache" message.

7. In the PHP Configuration page, to restart the Apache server click the Restart

Server link.

The PHP Configuration page is refreshed when the Apache server has been restarted.

8. In the Zend Core for Oracle administration page, to exit the GUI page click the

Logout link.

Now that the basic configuration changes have been made, you may now proceed to the next section to test the Zend Core for Oracle installation.

Testing the Zend Core for Oracle Installation

1. To get started, create a directory called chap2 as a child of your

$HOME/public_html directory and change directory to $HOME/public_html/chap2 by entering the following commands:

mkdir $HOME/public_html/chap2 cd $HOME/public_html/chap2

2. To check that PHP works, with your preferred editor, create a file called

hello.php which contains the following HTML text:

<?php echo "Hello, world!"; ?>

3. Open a web browser and enter the following URL:

http://localhost/~<username>/chap2/hello.php

The result in the browser is:

Beta Draft Getting Started 2-7

Testing the Zend Core for Oracle Installation

2-8 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft

Getting Connected

In this chapter you create HR application files which implement PHP functions to connect and disconnect with the Oracle Database. You also develop a PHP function which enables you to execute a query to validate that a database connection has been successfully established.

It guides you through the creation and modification of PHP files that call a function to produce the header and footer for the HR application report pages, where the footer section of the page includes a date and time.

This chapter has the following topics:

Q Building the Departments Page

Q Connecting to the Database

Q Disconnecting from the Database

Note: For simplicity, the username and password are written into

this sample application code. For applications that will be deployed, coding the username and password strings directly into your application source code is not recommended. Some other technique, such as implementing a dialog that prompts the end user for the username and password is recommended.

See the Oracle Database Security Guide and documentation for your development environment for details on security features and practices.

Building the Departments Page

1. To create a directory for the application files, in a command window enter the

following command:

mkdir $HOME/public_html/chap3 cd $HOME/public_html/chap3

2. To start creating the PHP application user interface framework create a file called

anyco_ui.inc that contains the two functions ui_print_header() and ui_print_footer() with their parameters to enable web pages with consistent

header and footer sections:

<?php

function ui_print_header($title) {

Beta Draft Getting Connected 3-1

Building the Departments Page

$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; }

Q The design of this application makes use of PHP function definitions to enable

modular reusable code.

Q The PHP functions defined in the anyco_ui.inc file contain parts of the

original HTML contents from the first anyco.php you created.

Q The functions in anyco_ui.inc, make use of 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;

The END; line must not be prefixed with leading spaces otherwise the rest of the document is 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.

Q The PHP function htmlentities() is used to prevent user-supplied text

from containing HTML markup.

3. The PHP file makes use of a Cascading Style Sheet (CSS) file called style.css to

specify presentation style in HTML in the browser.

Use your editor to create style.css in the chap3 directory with the following CSS text:

body { background: #CCCCFF; color: #000000; font-family: Arial, sans-serif; }

3-2 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft

{ 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; }

Building the Departments Page

4. To call the user interface functions create anyco.php with the following text:

<?php

require('anyco_ui.inc');

ui_print_header('Departments'); ui_print_footer(date('Y-m-d H:i:s'));

The anyco.php file uses PHP functions to produce HTML content. The require() PHP function is used to include the code in anyco_ui.inc, such

that the functions defined in it can be called to produce the desired result. If the required file cannot be found, PHP will generate an error and stop running the script.

5. To t e st anyco.php, enter the following URL in your browser:

http://localhost/~<username>/chap3/anyco.php

The resulting Web page produced is:

The date and time appear in the page footer section.

Beta Draft Getting Connected 3-3

Connecting to the Database

1. To form a database connection in your PHP application, you use the

oci_connect() function with three string parameters:

$conn = oci_connect($username, $password, $db)

The first and second parameters are the database username and password, respectively. The third parameter is the database connection identifier. The oci_connect() function returns a connection resource needed for other OCI8 calls, otherwise it returns FALSE if an error occurs. The connection identifier return is stored in a variable called $conn.

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 anyco.php to form a database connection with the following parameter values:

Q Username is hr.

Q Password for this example is hr. Remember to use the actual password of

your HR user.

Q Oracle connect identifier is //localhost/XE.

The file becomes:

<?php // File: anyco.php

require('anyco_ui.inc');

// Create a database connection $conn = oci_connect('hr', 'hr', '//localhost/XE');

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_RETURN_NULLS)) { print '<tr>'; foreach ($row as $item) { print '<td>'. ($item ? htmlentities($item) : ' ').'</td>'; } print '</tr>'; } print '</table>'; }

3-4 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft

Connecting to the Database

The oci_parse() function prepares the query for execution, and is supplied the connection identifier and query string as the first and second parameters, respectively. The oci_parse() function returns a statement identifier needed to execute the query and fetch the resulting data rows, otherwise it returns FALSE on error.

The oci_execute() function executes the statement associated with the statement identifier provided in the first parameter. The second parameter specifies the execution mode. OCI_DEFAULT is used to indicate you do not want to 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.

A while loop is used to fetch all the rows for the query executed. The oci_fetch_array() returns the next row from the result data, otherwise it returns FALSE if there are no more rows. The second parameter to oci_fetch_array() of OCI_RETURN_NULLS indicates that NULL database fields will be returned as PHP NULL values.

Each row of data is return as an associative or numeric array of column values. The code uses a PHP foreach construct to loop through the array and print each column value in a HTML table cell, inside a table row element. If the item value is NULL then a non-breaking space is printed, otherwise the item value is printed.

2. To test the changes made to $HOME/public_html/chap3/anyco.php, save the

modified anyco.php file and in a browser window enter the following URL:

http://localhost/~<username>/chap3/anyco.php

The page returned in the browser window should resemble the following page:

If you wanted to query the EMPLOYEES data you could change the query in the

do_query() function call to:

do_query($conn, 'SELECT * FROM EMPLOYEES');

Other Ways to Connect

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

Beta Draft Getting Connected 3-5

Disconnecting from the Database

open simultaneously. The connection performance benefits need to be balanced with the overhead on the database server.

Persistent connections are made with the OCI8 oci_pconnect() function. The lifetime of persistent connections can be controlled by several settings in the PHP initialization file. Some the settings include:

oci8.max_persistent - controls the number of persistent connections per process.

oci8.persistent_timeout - specifies the time (in seconds) that a process maintains an

idle persistent connection.

oci8.ping_interval - specifies the time (in seconds) that must pass before a persistent connection is "pinged" to check its validity.

See the PHP reference manual http://www.php.net/manual/en/ref.oci8.php for more information.

Disconnecting from the Database

The PHP engine will automatically close the database connection at the end of the script unless a persistent connection was made. To explicitly close a database connection you may call the oci_close() OCI function with the connection identifier returned by the oci_connect() call. For example:

<?php

$conn = oci_connect('hr', 'hr', '//localhost/XE'); ... oci_close($conn);

...

3-6 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft

In this chapter you extend the Anyco HR application from chapter 3 by adding additional information to the departments form. You also implement the functionality to query, insert, update, and delete employees in a specific department.

This chapter has the following topics:

Q Centralizing the Database Application Logic

Q Writing Queries with Bind Variables

Q Navigating Through Database Records

Q Extending the Basic Departments Form

Q Building the Basic Employee Form

Centralizing the Database Application Logic

Modify your application code by moving the database access logic into separate files for inclusion in the PHP application. Create new files in the $HOME/public_html/chap4 directory.

Querying Data

1. Copy the files completed in chapter 3, to a new chap4 directory:

mkdir $HOME/public_html/chap4 cp $HOME/public_html/chap3/* $HOME/public_html/chap4 cd $HOME/public_html/chap4

2. Using your preferred editor, create a file called anyco_cn.inc, which defines

named constants for the database connection information. This file enables to change connection information in one place.

<?php // File: anyco_cn.inc

define('ORA_CON_UN', 'hr'); // Username define('ORA_CON_PW', 'hr'); // Password define('ORA_CON_DB', '//localhost/XE'); // Connection identifier

3. 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

Beta Draft Querying Data 4-1

Centralizing the Database Application Logic

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() has been written to use the oci_fetch_all() OCI8 function, instead of oci_fetch_array(). The oci_fetch_all() function accepts five parameters.

Q $stid, the statement identifier for the statement executed

Q $results, the output array variable containing the data returned for the

query

Q The null in the third parameter for the number of initial rows to skip is

ignored.

4-2 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft

Centralizing the Database Application Logic

Q 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.

Q 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 isset() function checks if the message component of the database error structure is set before printing the message, or indicating that the error is unknown.

4. Edit anyco_ui.inc. To format the results of single row from the

DEPARTMENTS table query in a 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;

Beta Draft Querying Data 4-3

Writing Queries with Bind Variables

} }

Remember the END; line must not be prefixed with leading spaces otherwise the rest of the document is treated as part of the text to be printed.

5. Edit anyco.php. Include anyco_ui.inc and anyco_db.inc, 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'));

6. To test the resulting changes to the application, in a browser window enter the

following URL:

http://localhost/~<username>/chap4/anyco.php

The page returned in the browser window should resemble the following page:

Writing Queries with Bind Variables

Using queries with hard coded values in the WHERE clause may be useful for some situations. However, if the query conditional values need to change it is not appropriate to encode a value into the query. Oracle recommends you use bind variables in the query as a placeholder replacing literal values in the query conditions.

A bind variable is a symbolic name preceded by a colon in the query that acts as a placeholder for literal values in the WHERE clause. For example, the query string created in the anyco.php file could be rewritten with the bind variable ":did":

$query = 'SELECT department_id, department_name, manager_id, location_id

4-4 Oracle Database Express Edition 2 Day Plus PHP Developer Guide Beta Draft

Writing Queries with Bind Variables

FROM departments WHERE department_id = :did';

By using bind variables to parameterize SQL statements:

Q The statement is reusable with different input values without needing to change

the code.

Q The query performance is improved through a reduction of the query parse time

in the server, since the Oracle database can reuse parse information from the previous invocations of the identical query string.

Q There is protection against "SQL Injection" security problems.

Q There is no need to specially handle quotes 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 execute. This process is known as run-time binding.

To enable you PHP application to use bind variables in the query perform the following changes to your PHP application code:

1. Edit anyco.php. Modify the query to use a bind variable, create an array to store

the value to be associated with the bind variable, and pass $bindargs to db_do_query():

<?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 as the OCI8 layer can determine the length. This is not the case for bind variables accepting output results from a query.

2. Edit anyco_db.inc. 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:

<?php // File: anyco_db.inc ...

function db_do_query($conn, $statement, $bindvars = array()) { $stid = oci_parse($conn, $statement);

Beta Draft Querying Data 4-5

+ 65 hidden pages

Oracle B25-317-01 User Manual

Specifications and Main Features

Frequently Asked Questions

User Manual

Contents

Audience

Documentation Accessibility

Preface

Related Documents

Conventions

Introducing PHP with Oracle Database XE

Zend Core for Oracle

Purpose

Overview of the Sample Application

Resources

What You Need

Getting Started

Obtaining Oracle Database Express Edition (Oracle Database XE)

Obtaining Apache

Obtaining Zend Core for Oracle

Testing the Oracle Database XE Installation

Testing the Apache Installation

Setting Up Zend Core for Oracle

Installing Zend Core for Oracle on Linux

Configuring Zend Core for Oracle

Testing the Zend Core for Oracle Installation

Getting Connected

Building the Departments Page

Connecting to the Database

Other Ways to Connect

Disconnecting from the Database

Centralizing the Database Application Logic

Querying Data

Writing Queries with Bind Variables