Oracle Database 2 Day + PHP Developer's Guide introduces developers to using PHP to access Oracle Database. For more information, visit the Oracle Accessibility Program website at http://www.oracle.com/accessibility/.
What You Need
Installing Oracle Database
Unlocking the HR User
Installing Apache HTTP Server
Installing Apache on Windows
Starting and Stopping Apache on Windows
Testing the Apache Installation on Windows
Installing Apache on Linux
Starting and Stopping Apache on Linux
These are the same variables set by $ORACLE_HOME/bin/oracle_env.sh or.
Testing the Apache Installation on Linux
Installing PHP
Installing PHP on Windows
Installing PHP on Linux
Testing the PHP Installation
In this chapter, you create HR application files that implement PHP functions to connect and disconnect from the Oracle database. This chapter also walks you through creating and modifying PHP files that call a function to produce a header and footer for the department page, with the footer section containing a date and time.
Building the Departments Page
To start developing your application's user interface, use your favorite text editor to create a file called anyco_ui.inc that contains the two ui_print_ functions. The PHP file uses a Cascading Style Sheet (CSS) file called style.css to specify the style of HTML presentation in the browser.
Connecting to the Database
Edit the anyco.php file to retrieve all the rows for the executed query. Add some time. The oci_fetch_array() function returns the next row from the result data; it returns FALSE if there are no more rows.
If You Have Connection Problems
Other Ways to Connect
Disconnecting from the Database
Centralizing the Database Application Logic
Use the following logic, including some error handling that is managed by calling an additional function called db_error. Add 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.
Writing Queries with Bind Variables
In addition, the length component is passed as -1 so that the OCI8 layer can determine the length. If the connection variable was used to return output data from the database, an explicit size would be required. Call OCI8's oci_bind_by_name() call to bind the PHP values specified in the $bindvars parameter to the bind variables in the query.
For each entry in the $bindvars array, the first element contains the name of the query's bind variable, which is used to create a PHP variable of the same name; it is,. The oci_bind_by_name() function accepts four parameters: $time as a resource, a string representing the name of the bind variable in the query derived from the first element of the array input, the PHP variable containing the value to associate with the bind variable, and the length of the input value. To test the results of the previous changes, save the anyco.php and anyco_db.inc files and enter the following URL:.
Navigating Through Database Records
The query is used as a subquery within 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 returned for the specified query. The db_get_page_data() function comes with the connection resource, the query string, a value in $current that specifies the first row on the next page of required data rows, and the number of rows per page (in this case, one row per page).
After calling db_get_page_data() to get a page of rows, the value of $current is stored in the application's session state. The structure of the query in the db_get_page_data() function allows navigation through a set (or page) of database rows. Note that the query in $q1 retrieves an ordered set of rows, which is filtered by the corresponding query to return all rows from the first row to the following page size ($rowsperpage) rows.
ROWNUM vs ROW_NUMBER()
Extending the Basic Departments Page
The query string is enclosed in double quotes to simplify writing this statement, which contains SQL literal strings in single quotes. In this chapter, you extend the Anyco HR application with forms that allow you to insert, update, and delete an employee record.
Building the Basic Employees page
There is no need to pass the $bindargs parameter to the db_do_query() call because this query does not use bind variables.
Extending the Basic Employees Page
The JOBS table $query obtains a list of all existing job IDs and their descriptions to build a list for selecting a job type in the HTML form generated by the ui_print_insert_employee() function. Updating Data 5-5 Two types of dates are used in the application code, the PHP date(). In the construct_employees() function, modify the db_do_query() call to specify OCI_FETCHSTATEMENT_BY_ROW as the last parameter and specify $_SERVER['SCRIPT_NAME'] as the second parameter in the ui_print_employees() call.
Inside the db_get_page_data() function, insert OCI_FETCHSTATEMENT_BY_ROW as the third parameter value in. On the Insert New Employee page, the Department ID field contains 10 (default), HireDate contains the current date (in standard database date format), Salary contains 1, and Commission ( %) contains 0. On the Edit Employee page, change the Email Address field to JBOND, increase the Salary to 7100, and click Save:.
Combining Departments and Employees
This stores the current department ID from the Departments page as a session parameter which is used on the Employees page. Modify the construct_insert_emp() function so that the default department is obtained from the session parameter passed in.
Adding Error Recovery
Preventing errors from appearing may hide incorrect parameter usage, which may prevent testing of the changes in this section. Change the other four db_do_query() calls in anyco.php, remembering to keep the existing parameter values for each specific call. Use === so empty array at end of fetch is not matched handle_error('Unable to fetch departments', $err);.
Modify the insert_new_emp() function to handle errors. Emploee_id, first_name, last_name, email, hire_date, job_id, salary, promise_pct, department_id). 34;SELECT Emploee_id, first_name, last_name, email, hire_date, salary, nvl(commission_pct,0) as commission_pct FROM employees. Leading spaces in the END line cause the rest of the document to be treated as part of the text to be printed.
Further Error Handling
On the Insert New Employee page, enter the employee information as shown, set the Department ID to 99, and click Save. Click Return to Departments to return to the Departments page, and then click Show Employees to verify that the new employee record has not been added to the Administrative Department. This chapter shows how to implement stored procedures and functions using PHP and Oracle Database.
Using PL/SQL to Capture Business Logic
Using PL/SQL Ref Cursors to Return Result Sets
Using PL/SQL reference cursors to return result sets. Executing Stored Procedures and Functions 6-5 employees outside empinfotyp); CREATE OR REPLACE PACKAGE BODY cv_types AS PROCEDURE get_employee ( few in number , . employee OUT empinfotyp ) IS . All records retrieved - no pages in this example function db_get_employees_rc($conn, $deptid, &$e).
The PL/SQL statement inside the BEGIN END block calls the stored procedure of the PL/SQL package cv_types.et_employees(). This chapter shows how to modify the application to load a JPEG image for new employee records and display it on the Employees page.
Using BLOBs to Store and Load Employee Images
The PHP header() function sets the MIME type in the HTTP response header to ensure that the browser interprets the data as a JPEG image. To tie the new EMPLOYEE_PHOTOS and EMPLOYEES tables together, use the same employee ID in both tables. 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_.
Because the value of NEWID is being retrieved by the RETURNING clause in the INSERT statement, its initial value is set to NULL. The Insert New Employee form allows you to select a thumbnail image on your system to load into the database. The employee page appears with the new employee data, including the image, which is displayed in its original size:.
Resizing Images
Data from the original image is sampled into it using 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 released. The existing code in the db_insert_thumbnail() function uploads the image file to the database as in the previous implementation.
It addresses the fundamental tasks associated with developing and implementing global Internet applications, including developing locale awareness, constructing HTML content in the user's preferred language, and presenting data in accordance with the cultural conventions of the user's locale. The application itself must be aware of the user's local preference and be able to present content according to the cultural conventions that the user expects. Oracle Database is fully internationalized to provide a global platform for developing and deploying global applications.
Establishing the Environment Between Oracle and PHP
It is important to present data with appropriate local characteristics, such as correct date and number formats.
Manipulating Strings
Determining the Locale of the User
Building Global Applications 8-3 A common method of determining a user's locale is from the browser's default ISO locale setting. Normally, a browser sends its locale preference setting to the HTTP server with the Accept Language HTTP header. If the Accept Language header is NULL, no locale preference information is 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.
Developing Locale Awareness
Encoding HTML Pages
The browser needs to 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 need to know about the HTML page encoding in order to process input data from an HTML form.
Specifying the Page Encoding for HTML Pages
Specifying the Encoding in the HTTP Header
Specifying the Encoding in the HTML Page Header
Specifying the Page Encoding in PHP
Organizing the Content of HTML Pages for Translation
Strings in PHP
Static Files
Data from the Database
Presenting Data Using Conventions Expected by the User
Oracle Date Formats
Oracle Number Formats
Oracle Linguistic Sorts
Oracle Error Messages
Index
Symbols
