Developing Web 2.0 Applications with PHP and Oracle Database 11g

Purpose

This tutorial shows you how to generate rich interactive applications. It demonstrates how Oracle can be used with third-party, open source technologies to quickly build highly scable web sites.

Time to Complete

Approximately 1 hour

Topics

This tutorial covers the following topics:

	Overview
	Prerequisites
	Measuring and Improving OCI8 Performance
	Using Database Resident Connection Pooling
	Using Firefox Debugging Tools
	Incorporating AJAX into your Page
	Utilizing a JavaScript Library
	Using a PHP Framework Database Component
	Summary

	Examine Connection Speed with an Explicit Character Set
	Use a Logon Trigger to Set the Date Format
	Improve Query Performance
	Insert with BULK FORALL

Examine Connection Speed with an Explicit Character Set

You will examine the connection speed with a character set specified by performing the following steps:

1.	Review the code as follows that is contained in the conn_perf.php file in the $HOME/public_html directory. <?php define("ITERS", 5000); require('helper.php'); // Let character set default $start = currTime(); for ($i = 0; $i < ITERS; $i++) { $c = oci_pconnect("phpweb", "phpweb", "//localhost/orcl"); if (!$c) { $m = oci_error(); echo $m['message']; exit; } oci_close($c); // has no effect for persistent connections } $t = elapsedTime($start); print "Run 1: Total time is: " . round($t, 3) . " seconds\n<br>"; // Specify character set $start = currTime(); for ($i = 0; $i < ITERS; $i++) { $c = oci_pconnect("phpweb", "phpweb", "//localhost/orcl", "AL32UTF8"); if (!$c) { $m = oci_error(); echo $m['message']; exit; } oci_close($c); // has no effect for persistent connections } $t = elapsedTime($start); print "Run 2: Total time is: " . round($t, 3) . " seconds\n<br>"; ?> This file compares the time taken to connect when explicitly passing a character set to oci_pconnect() versus when letting the character set default. The helper.php file contains the timing functions used.
2.	Open a Web browser and enter the following URL to display the output: http://localhost/~phpweb/conn_perf.php Load the script several times to see the average times. Note that explicitly passing a character set is faster because it removes the need to do some relatively expensive environment lookup calls to choose the PHP OCI8 character set.
3.	Now compare oci_pconnect() persistent connections with standard oci_connect() connections. Modify conn_perf.php. Change the first connect to the following and save the file. $c = oci_connect("phpweb", "phpweb", "//localhost/orcl", "AL32UTF8"); You need to pass in the character set to match the persistent connection call. Change the ITERS value to 50 so the example completes in a reasonable amount of time. define("ITERS", 50);
4.	Reload the conn_perf.php code. http://localhost/~phpweb/conn_perf.php Reload the script a few times. Persistent connections are faster because the PHP process holds an Oracle connection open even when the PHP script has completed.

Back to Topic

Use a Logon Trigger to Set the Date Format

This section shows how setting the default date format can be improved with a trigger. Perform the following steps:

1.	Review the code as follows that is contained in the logon_trig.sql file in the $HOME/public_html directory. drop user phpweb_trig cascade; create user phpweb_trig identified by phpweb_trig; grant connect, resource, create session to phpweb_trig; create or replace trigger my_set_date after logon on database begin if (user = 'PHPWEB_TRIG') then execute immediate 'alter session set nls_date_format = ''YYYY-MM-DD HH24:MI:SS'' '; end if; end my_set_date; / This script has already been run for you. It creates a logon trigger that sets Oracle's date format whenever the user PHPWEB_TRIG connects to the database.
2.	Verify the effect of the trigger. Open a terminal window and execute the following commands: sqlplus phpweb_trig/phpweb_trig select sysdate from dual; connect phpweb/phpweb select sysdate from dual;
3.	Review the code as follows that is contained in the logon_trig.php file in the $HOME/public_html directory. <?php define("ITERS", 500); require('helper.php'); // Explicitly set the date at logon $start = currTime(); for ($i = 0; $i < ITERS; $i++) { $c = oci_pconnect("phpweb", "phpweb", "//localhost/orcl", "AL32UTF8"); $s = oci_parse($c, "alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS '"); $r = oci_execute($s); } $t = elapsedTime($start); print "Run 1: Total time is: " . round($t, 3) . " seconds\n<br>"; // Let the logon trigger set the date $start = currTime(); for ($i = 0; $i < ITERS; $i++) { $c = oci_pconnect("phpweb_trig", "phpweb_trig", "//localhost/orcl", "AL32UTF8"); } $t = elapsedTime($start); print "Run 2: Total time is: " . round($t, 3) . " seconds\n<br>"; ?> The connection as PHPWEB has to explicitly set the date format after connecting. The connection as PHPWEB_TRIG has the date format set by the trigger.
4.	From your browser, enter the following URL to display the output: http://localhost/~phpweb/logon_trig.php The PHPWEB_TRIG user uses the date format set by the trigger. Because the connection is persistent, the trigger only fires when the database connection is created the first time the script runs.
5.	Reduce the number of iterations and add the following code in bold to the bottom of each connection loop in the logon_trig.php file. It verifies that the time formats are the same: <?php define("ITERS", 10); require('helper.php'); // Explicitly set the date at logon $start = currTime(); for ($i = 0; $i < ITERS; $i++) { $c = oci_pconnect("phpweb", "phpweb", "//localhost/orcl", "AL32UTF8"); $s = oci_parse($c, "alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS '"); $r = oci_execute($s); $s = oci_parse($c, "select sysdate as dt from dual"); oci_execute($s); oci_fetch_all($s, $r); echo $r['DT'][0], "<br>\n"; } $t = elapsedTime($start); print "Run 1: Total time is: " . round($t, 3) . " seconds\n<br>"; // Let the logon trigger set the date $start = currTime(); for ($i = 0; $i < ITERS; $i++) { $c = oci_pconnect("phpweb_trig", "phpweb_trig", "//localhost/orcl", "AL32UTF8"); $s = oci_parse($c, "select sysdate as dt from dual"); oci_execute($s); oci_fetch_all($s, $r); echo $r['DT'][0], "<br>\n"; } $t = elapsedTime($start); print "Run 2: Total time is: " . round($t, 3) . " seconds\n<br>"; ?>
6.	Reload the following URL to display the output: http://localhost/~phpweb/logon_trig.php ...

Back to Topic

Improve Query Performance

This section demonstrates some ways to improve query performance. Perform the following steps:

1.	First, create a table with a large number of rows. Review the following fetch_prefetch.sql script. set echo on drop table bigtab; create table bigtab (mycol varchar2(20)); begin for i in 1..20000 loop insert into bigtab (mycol) values (dbms_random.string('A',20)); end loop; end; / show errors exit From your sqlplus session, run the following: connect phpweb/phpweb @fetch_prefetch
2.	Review the code as follows that is contained in the fetch_prefetch.php file in the $HOME/public_html directory. <?php require('helper.php'); function do_prefetch($c, $pf) { $stid = oci_parse($c, "select mycol from bigtab"); oci_execute($stid); oci_set_prefetch($stid, $pf); oci_fetch_all($stid, $res); return $res; } $c = oci_connect("phpweb", "phpweb", "//localhost/orcl"); $pf_a = array(1, 10, 500, 2000); // Prefetch values to test foreach ($pf_a as $pf_num) { $start = currTime(); $r = do_prefetch($c, $pf_num); $t = elapsedTime($start); print "Prefetch $pf_num - Elapsed time is: " . round($t, 3) . " seconds<br>\n"; } ?>
3.	Load the following URL to display the output: http://localhost/~phpweb/fetch_prefetch.php Reload a few times to see the average times. The default prefetch size can be set in PHP's initialization file, php.ini. Prior to PHP 5.3, the default prefetch size was 10 rows. In 5.3, it will be 100 rows. You should choose a suitable default value for your application, and use oci_set_prefetch() for specific queries that need a different value.
4.	This section shows the oci_bind_array_by_name() function that allows a PHP array to be retrieved from, or passed to, a PL/SQL procedure. Review the following fetch_bulk.sql script. set echo on create or replace package fetchperfpkg as type arrtype is table of varchar2(20) index by pls_integer; procedure selbulk(p1 out arrtype); end fetchperfpkg; / create or replace package body fetchperfpkg as procedure selbulk(p1 out arrtype) is begin select mycol bulk collect into p1 from bigtab; end selbulk; end fetchperfpkg; / show errors exit This script creates a PL/SQL package that fetches from BIGTAB using a PL/SQL BULK COLLECT statement, and returns the results in a PL/SQL array. From your sqlplus session, run the following: sqlplus phpweb/phpweb @fetch_bulk
5.	Review the code as follows that is contained in the fetch_bulk.php file in the $HOME/public_html directory. <?php require('helper.php'); function do_sel_bulk($c) { $s = oci_parse($c, "begin fetchperfpkg.selbulk(:a1); end;"); oci_bind_array_by_name($s, ":a1", $res, 20000, 20, SQLT_CHR); oci_execute($s); return($res); } $c = oci_connect("phpweb", "phpweb", "//localhost/orcl"); $start = currTime(); $r = do_sel_bulk($c); $t = elapsedTime($start); print "Bulk collect - Elapsed time is: " . round($t, 3) . " seconds\n<br>"; ?> This code calls the PL/SQL package and binds a PHP variable to hold the returned data array. No OCI8 fetch call is needed. This example doesn't print the returned results. If you want to see them, add " var_dump($res);" before the function return statement. The output shows the random 20-character data strings created by fetch_prefetch.sql, which you ran earlier.
6.	Load the following URL to display the output: http://localhost/~phpweb/fetch_bulk.php Reload a few times to see the average times.
7.	Change the fetch_bulk.sql package code by adding a WHERE clause to reduce the number of rows fetched. set echo on create or replace package fetchperfpkg as type arrtype is table of varchar2(20) index by pls_integer; procedure selbulk(p1 out arrtype); end fetchperfpkg; / create or replace package body fetchperfpkg as procedure selbulk(p1 out arrtype) is begin select mycol bulk collect into p1 from bigtab where rownum < 100; end selbulk; end fetchperfpkg; / show errors exit From your sqlplus session, run the following: sqlplus phpweb/phpweb @fetch_bulk
8.	Reload the following URL to display the output: http://localhost/~phpweb/fetch_bulk.php Experiment with different row sizes to see the effect and compare the results with the previous fetch_prefetch.php script. Depending on your data, types, size and business requirements, testing will show which fetch method is faster for your application.

Back to Topic

Insert with BULK FORALL

The PL/SQL BULK FORALL statement is an efficient way to insert data. Perform the following steps:

This section reviews how BULK FORALL insert compares with a PHP transactional loop insert. Review the following code in the insert_perf.sql file.

set echo on
                              


drop table ptab;
                              


create table ptab(pdata varchar2(20));
                              


create or replace package inspkg as
                              


  type arrtype is table of varchar2(20) index by pls_integer;
                              


  procedure insforall(p1 in arrtype);
                              


end inspkg;
                              


/
                              


create or replace package body inspkg as
                              


  procedure insforall(p1 in arrtype) is
                              


  begin
                              


    forall i in indices of p1
                              


      insert into ptab values (p1(i));
                              


  end insforall;
                              


end inspkg;
                              


/
                              


exit

From your sqlplus session, run the following:

sqlplus phpweb/phpweb
@insert_perf

Review the code as follows that is contained in the insert_perf.php file in the $HOME/public_html directory.

<?php
                               


define('NUM_ITEMS', 50);  // number of rows to insert
                               


require('helper.php');
                               


function do_ins_bind_trans($c, $a)
                              


{
                              


  $s = oci_parse($c, 'insert into ptab (pdata) values (:bv)');
                              


  oci_bind_by_name($s, ':bv', $v, 20, SQLT_CHR);
                              


  foreach ($a as $v) {
                              


    $r = oci_execute($s, OCI_DEFAULT);
                              


  }
                              


  oci_commit($c);
                              


}
                               


function do_ins_forall($c, $a)
                              


{
                              


  $s = oci_parse($c, "begin inspkg.insforall(:c1); end;");
                              


  oci_bind_array_by_name($s, ":c1", $a, count($a), -1, SQLT_CHR);
                              


  oci_execute($s);
                              


}
                               


$c = oci_connect("hrweb", "hrweb", "//localhost/orcl");
                               


// Bind & one commit
                              


$a = do_buildarray(NUM_ITEMS);
                              


$start = currTime();
                              


do_ins_bind_trans($c, $a);
                              


$t = elapsedTime($start);
                              


echo "Insert with binds and one commit: " . round($t,3)." seconds<br>\n";
                              


do_delete($c);
                               


// FORALL
                              


$a = do_buildarray(NUM_ITEMS);
                              


$start = currTime();
                              


do_ins_forall($c, $a);
                              


$t = elapsedTime($start);
                              


echo "Insert BULK FORALL: " . round($t,3) . " seconds<br>\n";
                              


do_delete($c);
                               


?>

This do_buildarray() and do_delete() functions are in helper.php. Function do_ins_forall() loops over each value to insert, and commits when all rows are inserted. In contrast, function do_ins_bind_trans() calls the PL/SQL procedure insforall(). The OCI8 call:

oci_bind_array_by_name($s, ":c1", $a, count($a), -1, SQLT_CHR);

binds a PHP array to the PL/SQL procedure parameter. The count($a) tells OCI8 to bind all elements in the array $a. The -1 tells OCI8 to work out the length of each data item.

Load the following URL to display the output:

                               
http://localhost/~phpweb/insert_perf.php

This section has shown some ways to evaluate performance of OCI8 functions. Different techniques will be useful depending on your application. Don't forget to tune your SQL and Database. Tune the slowest parts of your system first. A slow query could, for example, easily outweigh any benefits of improving connection speed.

Back to Topic

Using Database Resident Connection Pooling

Database Resident Connection Pooling is a new feature of Oracle Database 11g. For PHP, it allows web applications to scale the number of connections as site usage grows. It allows multiple Apache processes on multiple machines to share a small pool of database server processes. Without DRCP, a standard PHP connection must start and terminate a server processe. A non-DRCP persistent connection holds database server resources even when idle. This tutorial shows how DRCP can be used by new or existing applications without writing or changing any application logic. Perform the following steps:

1.	Check that php has oci8.connection_class set. Open a terminal window and execute the following command: php -r 'echo ini_get("oci8.connection_class"), "\n";' The connection class tells the database server pool that connections are related. Session information (such as the default date format) might be retained between connection calls, giving performance benefits. Session information will be discarded if a pooled server is later reused by a different application with its own connection class name.
2.	Review the code as follows that is contained in the query_pooled.php file in the $HOME/public_html directory. <?php $c = oci_pconnect("phpweb", "phpweb", "//localhost/orcl :pooled"); $s = oci_parse($c, 'select * from employees'); oci_execute($s); oci_fetch_all($s, $res); var_dump($res); ?> Compare this code to the code in the query_nonpooled.php file in the $HOME/public_html directory. <?php $c = oci_pconnect("phpweb", "phpweb", "//localhost/orcl"); $s = oci_parse($c, 'select * from employees'); oci_execute($s); oci_fetch_all($s, $res); var_dump($res); ?> The only difference is the " :pooled" in the Easy Connect connection string in query_pooled.php.
3.	To run the scripts, the Apache Benchmark tool is used. This command repeatedly loads a web page, measuring its performance. From a terminal window, execute the following: ab -c 150 -t 30 http://localhost/~phpweb/query_pooled.php The above command sends Apache 150 concurrent requests for the script, repeatedly for 30 seconds.
4.	Now look at the number of database connections open. Open another terminal window, execute the following: sqlplus phpweb/phpweb select username, program from v$session where username = 'PHPWEB'; The default DRCP pool MAXSIZE is 40. You see up to 40 connections with Lxxx names, depending on how many Apache processes handled the 'ab' requests. You may also need to execute the query while 'ab' is running to see the pooled servers working. Oracle manages the DRCP pool, shrinking it after a specified timeout.
5.	Now, you will run the same command except run the non-pooled script to compare the difference. From a terminal window, execute the following: ab -c 150 -t 30 http://localhost/~phpweb/query_nonpooled.php
6.	Now look at the number of database connections open. Open another terminal window, execute the following: sqlplus phpweb/phpweb select username, program from v$session where username = 'PHPWEB'; Many more rows than previously are returned. The rows with httpd@localhost (TNS V1-V3) correspond to a running Apache process holding a database connection open. For PHP, Apache runs in a multi-process mode, spawning child processes each of which can handle one PHP script. Depending how Apache allocated these processes to handle the "ab" requests, you may see a varying number of rows in V$SESSION. Compare the number of requests completed in each run. You might want to run each script a few times to warm up the caches. Performance of the scripts is roughly similar. For the small works loads used in these two files, the tiny overhead of the handoff of pooled servers might make query_pooled.php a little slower than query_nonpooled.php. But the non-pooled script causes every single Apache process to open a separate connection to the database. For larger sites, or where memory is limited, the overall benefits of DRCP are significant.

Back to Topic List

Using Firefox Debugging Tools

Firebug and YSlow are addons to the Firefox browser for debugging and analyzing web pages. In this section of the tutorial, you examine how Firebug and YSlow can be used to debug your application and enhance its performance. Perform the following tasks:

	Inspect and Fix HTML and CSS
	Inspect the HTML
	Examine Performance

Back to Topic List

Inspect and Fix HTML and CSS

You can easily inspect and fix your HTML and CSS using Firebug. Perform the following steps:

1.	Review the code as follows that is contained in the firebug_1.php file in the $HOME/public_html directory. <!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="style1.css"> <link rel="stylesheet" type="text/css" href="style2.css"> <link rel="stylesheet" type="text/css" href="style3.css"> <link rel="stylesheet" type="text/css" href="style4.css"> <link rel="stylesheet" type="text/css" href="style5.css"> <title>Any Co. Report</title> </head> <body> <h1>Any Co. Report <h1> <!-- deliberate typo --> <?php $c = oci_connect("phpweb", "phpweb", "//localhost/orcl"); if (!$c) { $m = oci_error(); echo $m['message']; exit; } $query = 'select * from employees'; $s = oci_parse($c, $query); oci_execute($s); echo "<table border='1'>".PHP_EOL; while ($row = oci_fetch_array($s, OCI_ASSOC+OCI_RETURN_NULLS)) { echo "<tr>".PHP_EOL; foreach ($row as $item) { echo " <td>".($item?htmlentities($item):" ")."</td>".PHP_EOL; } echo "</tr>".PHP_EOL; } echo "</table>".PHP_EOL; oci_close($c); ?> <div class="footer"> <div class="date"><?php echo date('Y-m-d H:i:s'); ?></div> <div class="company">Any Co.</div> </div> </body> </html> Notice that the <h1> tag is not closed correctly.
2.	From your browser, enter the following URL to display the output: http://localhost/~phpweb/firebug_1.php
3.	To open Firebug, select Tools > Firebug > Open Firebug. Or you can select the "Bug" icon in the lower status bar.
4.	Firebug has a row of tabs. Above them are toggle buttons, varying with the current tab. Select the html tab.
5.	Expand the <body> tree. The browser has added a missing closing tab </h1>, but left the incorrect <h1>. Select the second <h1> line and click Edit.
6.	The HTML code is displayed. Delete the extra <h1> tag. Click Edit again. The page is automatically updated. This feature is useful for resolving problems without having to constantly edit source files and reload pages. The change is not permanent. If you reload the file, the problem re-occurs. The source file will be fixed later in the lab.
7.	Click Edit again to dismiss the edit window. Select the <body> tag. On the right side of the Firebug window, there is the CSS style for the body tag. Notice that it says the filename is style1.css. Hover over the background color number to see the color.
8.	Click the background color #CCCCFF and change it to #CCCC92.
9.	If you make a mistake, you can click Refresh in your browser to reload the page and CSS.
10.	To permanently fix the mistake in the original files, edit firebug_1.php and change the closing tag to </h1>. Optionally, edit style.css and change the background color.
11.	Reload the firebug_1.php page in your browser. Review the difference in your code as a result of the change you made.

Back to Topic

Inspect the HTML

You can inspect the HTML tags to highlight the corresponding area on the page. Perform the following steps:

From the Firebug window, select the HTML tab. Then click Inspect (above HTML) to select it.

Expand <body> and hover over the <h1> text. Notice that the element in the main output window is highlighted, letting you see exactly which HTML generated which display.

Expand <table border="1"> and hover over the <tbody> tag. Notice that the table is now highlighted in the main output window.

Back to Topic

Examine Performance

You can examine the page performance and identify ways to improve performance using Firebug and YSlow.

Note: If you reload scripts, you may need to clear the browser cache to get the exact results shown here. Select Tools > Clear Private Data to do this.

Perform the following steps:

1.	From the Firebug window, click the Net tab. Make sure the All tab is selected above it. Note that the download times for all files used to create the web page are shown.
2.	The file style3.css is in red because the file did not exist. Expand GET style3.css in the tree. The Headers tab shows the request for style3.css. The Response tab shows the web server sent a standard error page, with error 404. You may need to click Load Response on the response tab to see the error text.
3.	Edit the firebug_1.php file and remove the following line: <link rel="stylesheet" type="text/css" href="style3.css">
4.	Reload the firebug_1.php page in your browser and review the Net > All area again.
5.	Expand the tree for style1.css . Notice that there is a Cache tab displayed which indicates that the file was read from the browser cache.
6.	Empty the browser cache and reload the firebug_1.php page in your browser and expand the tree for style1.css again. Notice that there is no longer a Cache tab because the reload forced all the files to be re-read from the web server.
7.	Select the YSlow tab and click Performance (above it). Using static analysis, this gives a report card for various metrics about the web page.
8.	Click the small triangle next to Make fewer HTTP requests. Note that if you have an internet connection, you can click the Make Fewer Requests link. This opens a webpage with some general help text. Although you received an A grade, you can still improve the number of requests. This page has 4 external stylesheets. You can combine them into one stylesheet to improve performance.
9.	From your terminal window, copy the firebug_1.php file to firebug_2.php by executing the following command: cp firebug_1.php firebug_2.php
10.	Edit the firebug_2.php file and replace all stylesheet links with the following line: <link rel="stylesheet" type="text/css" href="style_all.css">
11.	Load firebug_2.php in your browser. Select the YSlow tab and click Performance. Notice that you now get an A Performance grade. YSlow gives a grade for the Expires header. This header allows caches to know whether the content needs to be reloaded from the web site. In a dynamic report such as the one we have been using, the content would always need to be reloaded; the YSlow result can be ignored.
12.	Select the Net tab and click All. Notice that the request time has been reduced.

Back to Topic

Incorporating AJAX into your Page

This section shows the basic technique of updating a section of a page without reloading the whole content. Perform the following tasks:

	Use a Basic XmlHttpRequest
	Extend to a Query
	Use JSON

Back to Topic List

Use a Basic XmlHttpRequest

You can use a XmlHttpRequest to update a section of a page without reloading the whole page content. Perform the following steps:

1.	Review the code as follows that is contained in the ajax_id.php file in the $HOME/public_html directory. This file simply echos the parameter passed in. <?php if (!isset($_GET['id'])) { $id = 'No id passed'; } else { $id = $_GET['id']; } echo "Id was: ", htmlentities($id); ?>
2.	From your browser, enter the following URL to display the output: http://localhost/~phpweb/ajax_id.php?id=185
3.	Review the code as follows that is contained in the ajax_id.html file in the $HOME/public_html directory. This file contains a JavaScript function, makeRequest(). <html> <head> <script type="text/javascript"> function makeRequest(id) { httpRequest = new XMLHttpRequest(); httpRequest.open('GET', 'http://localhost/~phpweb/ajax_id.php?id=' + id); httpRequest.onreadystatechange = function() { if (httpRequest.readyState == 4) { // The request is complete alert(httpRequest.responseText); // Display the result } } httpRequest.send(null); } </script> </head> <body onload="makeRequest(185)"> <h1>Employee Lookup</h2> <div id="descriptionNode">This page finds an employee</div> </body> </html>
4.	From your browser, enter the following URL to display the output: http://localhost/~phpweb/ajax_id.html Click OK to dismiss the alert window. Note: if you use a Internet Explorer, you will need to edit ajax_id.html and change the XMLHttpRequest() call to ActiveXObject("Msxml2.XMLHTTP") or ActiveXObject("Microsoft.XMLHTTP"). When the HTML page is loaded, the makeRequest() javascript function is called. It prepares an XMLHttpRequest request to call ajax_id.php. A callback function onreadystatechange is set. Finally the request is sent to the webserver asynchronously. When the callback function is notified that the web server request has returned, the output from ajax_query.php is displayed by the alert() function.
5.	Edit ajax_id.html and change 185 to 186.
6.	Reload it in the browser. The new value is displayed. Click OK to dimiss the alert window.
7.	Close the alert window. Open Firebug. Select Net, then click All. You can see that both the HTML and PHP files were used to construct the web page. Clear the browser cache or Shift-reload the page if necessary. In Firebug, expand the GET ajax-query=186 tree. Click Params.
8.	A list of the parameters passed in the AJAX call is displayed. Click Response.
9.	A list of what was returned is displayed. You may need to click Load Response to see the output. Close Firebug.

Back to Topic

Extend to a Query

You can also invoke a query within the php file and the resulting values are sent back to the page. Perform the following steps:

1.	Review the code as follows that is contained in the ajax_query.php file in the $HOME/public_html directory. This file accepts an employee id as a parameter and returns the name of the matching employee in a table. <?php if (!isset($_GET['id'])) { echo 'No id passed'; } else { $id = $_GET['id']; $c = oci_connect("phpweb", "phpweb", "//localhost/orcl"); $query = 'select first_name, last_name from employees where employee_id = :id'; $s = oci_parse($c, $query); oci_bind_by_name($s, ":id", $id); oci_execute($s); echo "<table border='1'>".PHP_EOL; while ($row = oci_fetch_array($s, OCI_ASSOC+OCI_RETURN_NULLS)) { echo "<tr>".PHP_EOL; foreach ($row as $item) { echo " <td>".($item?htmlentities($item):" ")."</td>".PHP_EOL; } echo "</tr>".PHP_EOL; } echo "</table>".PHP_EOL; } ?>
2.	From your browser, enter the following URL to display the output: http://localhost/~phpweb/ajax_query.php?id=185
3.	Change the number of the id to 186 as follows and press Enter. http://localhost/~phpweb/ajax_query.php?id=186
4.	Review the code as follows that is contained in the ajax_query.html file in the $HOME/public_html directory. This file differs from ajax_id.html. Now the Javascript function makeRequest() is only called when the form button is clicked. The value the user enters is passed to makeRequest(), and there used in the call to ajax_query.php. <html> <head> <script type="text/javascript"> function makeRequest(id) { httpRequest = new XMLHttpRequest(); httpRequest.open('GET', 'http://localhost/~phpweb/ajax_query.php?id=' + id); httpRequest.onreadystatechange = function() { if (httpRequest.readyState == 4) { // The request is complete alert(httpRequest.responseText); // Display the result } } httpRequest.send(null); } </script> </head> <body> <h1>Employee Lookup</h2> <div id="descriptionNode">This page finds an employee</div> <form name="EmpForm" method="post"> Enter Employee ID: <input type="text" value="185" name="id"> <input type="button" value="Submit" onclick="makeRequest(EmpForm.id.value);"> </form> </body> </html>
5.	From your browser, enter the following URL to display the output. Accept the default number of 185 and click Submit. http://localhost/~phpweb/ajax_query.html
6.	The alert window is displayed. Click OK.
7.	In Firebug, select the Script tab.
8.	You want to set a couple of breakpoints. Click to the left of the line number for the httpRequest.open line. Also click in the margin to the left of the line number on the alert() line.
9.	Make sure 185 is entered for the Employee ID and click Submit.
10.	The debugger stops at the first breakpoint. Click the blue arrow several times to step through the JavaScript until the alert window is shown.
11.	The alert window appears. Click OK to close the alert window.
12.	Click Submit again.
13.	At the first breakpoint, you want to change the value of the id. Right-click the red "185" in the Watch window and select Edit Variable from the list.
14.	Change the value to "186" (with quotes) and press enter.
15.	If you right click the variable and choose Refresh, the value of id is now set to "186". Click the blue arrow a couple of times until you receive the alert window.
16.	Notice that the name of employee 186 is returned. Click OK. Close Firebug by clicking the Firebug icon in the toolbar.

Back to Topic

Using JSON

You can build an array using JSON string format. Perform the following steps:

1.	Review the code as follows that is contained in the ajax_json.php file in the $HOME/public_html directory. This file is similar to ajax_query.php but instead of outputing an HTML table, it builds an array $res of the row. The line $res[] = $row extends the array with each new $row. In this example, there is only every one row of data returned by the query, so the $res array has one element. The json_encode($res) call converts the array to the JSON string format. <?php if (!isset($_GET['id'])) { echo = 'No id passed'; } else { $id = $_GET['id']; $c = oci_connect("phpweb", "phpweb", "//localhost/orcl"); $query = 'select first_name, last_name from employees where employee_id = :id'; $s = oci_parse($c, $query); oci_bind_by_name($s, ":id", $id); oci_execute($s); while ($row = oci_fetch_array($s, OCI_ASSOC+OCI_RETURN_NULLS)) { $res[] = $row; } var_dump($res); echo json_encode($res); } ?> JSON is a text format often used as a lightweight protocol for transferring data between Javascript in the browser and a server-side script. PHP's inbuilt json_encode() and json_decode() functions can be used to convert PHP data to and from JSON.
2.	From your browser, enter the following URL to display the output: http://localhost/~phpweb/ajax_json.php?id=185 The first line is PHP's internal array structure formatted for display by var_dump(). See the similarities with the JSON string on the next line.
3.	Edit ajax_json.php and comment out the var_dump($res) statement. Reload the script to check only the JSON string is displayed.
4.	Review the code as follows that is contained in the ajax_json.html file in the $HOME/public_html directory. The makeRequest() has changed from ajax_query.html. An eval converts the JSON string into a Javascript object. Beware of security issues with using eval() in this way. A JSON parser would be safer for a production system. The resulting array myArray can be used directly in Javascript. In this case, the loop concatenates the first and last names into a single string. <html> <head> <script type="text/javascript"> function makeRequest(id) { httpRequest = new XMLHttpRequest(); httpRequest.open('GET', 'http://localhost/~phpweb/ajax_json.php?id=' + id); httpRequest.onreadystatechange = function() { if (httpRequest.readyState == 4) { // The request is complete var JSONtext = httpRequest.responseText; alert(JSONtext); var myArray = eval('(' + JSONtext + ')'); // beware security issues var txt = ""; for (var i = 0; i < myArray.length; ++i) { txt = txt + myArray[i]["FIRST_NAME"] + ' ' + myArray[i]["LAST_NAME"]; } alert(txt); } } httpRequest.send(null); } </script> </head> <body> <h1>Employee Lookup</h2> <div id="descriptionNode">This page finds an employee</div> <form name="EmpForm" method="post"> Enter Employee ID: <input type="text" value="185" name="id"> <input type="button" value="Submit" onclick="makeRequest(EmpForm.id.value);"> </form> <div id="outputNode">Name Goes Here</div> </body> </html>
5.	From your browser, enter the following URL to display the output and click Submit. http://localhost/~phpweb/ajax_json.html
6.	Two alerts occur, one with the JSON string, and the other with the constructed string text. Click OK. Click OK again. If you only get one alert, and the Firebug icon changes to a red error, check that you commented out the var_dump() in the PHP file. Use the Clear button in the Console tab of Firebug to clear the error.
7.	The JavaScript loop protects against multiple rows being returned by the query. Edit the ajax_json.php file to simulate this situation. Add the line that is in bold below. This will create two array entries for each row. <?php if (!isset($_GET['id'])) { $res = 'No id passed'; } else { $id = $_GET['id']; $c = oci_connect("phpweb", "phpweb", "//localhost/orcl"); $query = 'select first_name, last_name from employees where employee_id = :id'; $s = oci_parse($c, $query); oci_bind_by_name($s, ":id", $id); oci_execute($s); while ($row = oci_fetch_array($s, OCI_ASSOC+OCI_RETURN_NULLS)) { $res[] = $row; $res[] = $row; } var_dump($res); echo json_encode($res); } ?>
8.	Switch to your browser and click Submit. View the output of the alerts. You don't need to reload the HTML file because only the PHP code has changed. Click OK. Click OK again.
9.	Remove the additional line you created in the ajax_json.php file.
10.	Modify the ajax_json.html file. Remove the last alert() and replace it with the line in bold below. <html> <head> <script type="text/javascript"> function makeRequest(id) { httpRequest = new XMLHttpRequest(); httpRequest.open('GET', 'http://localhost/~phpweb/ajax_json.php?id=' + id); httpRequest.onreadystatechange = function() { if (httpRequest.readyState == 4) { // The request is complete var JSONtext = httpRequest.responseText; alert(JSONtext); var myArray = eval('(' + JSONtext + ')'); // beware security issues var txt = ""; for (var i = 0; i < myArray.length; ++i) { txt = txt + myArray[i]["FIRST_NAME"] + ' ' + myArray[i]["LAST_NAME"]; } document.getElementById("outputNode").innerHTML = txt; } } httpRequest.send(null); } </script> </head> <body > <h1>Employee Lookup</h2> <div id="descriptionNode">This page finds an employee</div> <form name="EmpForm" method="post"> Enter Employee ID: <input type="text" value="185" name="id"> <input type="button" value="Submit" onclick="makeRequest(EmpForm.id.value);"> </form> <div id="outputNode">Name Goes Here</div> </body> </html> The word outputNode matches the div name at the bottom of the HTML file.
11.	Switch to your browser and reload the ajax_json.html page. click Submit.
12.	The first alert is displayed. Click OK.
13.	The second alert is no longer displayed. Instead, the text "Name goes here" is replaced with the retrieved person's name, without the rest of the page being reloaded.
14.	You can view the change in Firebug. Select the html tab and expand the <body> tree.
15.	Change the value in the Employee ID field to 186 and click Submit.
16.	The first alert is displayed. Click OK.
17.	Firebug shows the div being updated.

Back to Topic

Using a JavaScript Library

A number of sophisticated JavaScript libraries make building complex front ends for PHP web applications easy. This section of the tutorial uses the third-party Dojo toolkit. Perform the following steps:

1.	Review the code as follows that is contained in the dojo.html file in the $HOME/public_html directory. <html> <head> <script type="text/javascript" src="http://localhost/dojo/dojo/dojo.js"></script> <script type="text/javascript"> function makeRequest(id) { dojo.xhrGet({ url: "http://localhost/~phpweb/ajax_json.php?id=" + id, handleAs: "json", load: function(data,ioargs) { dojo.byId("outputNode").innerHTML = data[0]["FIRST_NAME"] + ' ' + data[0]["LAST_NAME"]; } }); }; </script> </head> <body> <h1>Employee Lookup</h2> <div id="descriptionNode">This page finds an employee</div> <form name="EmpForm" method="post"> Enter Employee ID: <input type="text" value="185" name="id"> <input type="button" value="Submit" onclick="makeRequest(EmpForm.id.value);"> </form> <div id="outputNode">Name Goes Here</div> </body> </html> The top script tag loads the Dojo framework. It uses Dojo to call the same PHP script we used previously, ajax_json.php. The handleAs argument "json" indicates the returned values will be treated as JSON notation and converted by Dojo to a Javascript Array object. When data comes back, the "load" function will be called. This populates the outputNode content of the HTML page with the returned data. For brevity, looping over the data array is omitted, since only one person's name will be returned.
2.	From your browser, enter the following URL to display the output and click Submit. http://localhost/~phpweb/dojo.html
3.	The employee's name is displayed.
4.	You can change the way in which the employee's name appears. You may want to takea backup before you modify dojo.html by executing the following command in a terminal window: cp dojo.html dojo.html.bak Edit dojo.html and change the following code from: dojo.byId("outputNode").innerHTML = data[0]["FIRST_NAME"] + ' ' + data[0]["LAST_NAME"]; to dojo.fadeOut({ node: outputNode, onEnd: function(){ outputNode.innerHTML = data[0]["FIRST_NAME"] + ' ' + data[0]["LAST_NAME"]; dojo.fadeIn({node: outputNode, duration: 500}).play(); } }).play(); Make sure you have the brackets and braces matched.
5.	Reload the dojo.html page in your browser and click Submit. The static placeholder text will fade out and fade in the employee's name.
6.	You can also fetch the data as text instead of a JSON object. Edit dojo.html and change the AJAX request to call ajax_query.php (which returns an HTML table). Change the "url:" line to: url: "http://localhost/~phpweb/ajax_ query.php?id=" + id,
7.	Also change the handleAs: type from JSON to text.
8.	Reload the dojo.html page in your browser. Before clicking Submit, open Firebug and select the Script tab and set a breakpoint (click in the margin left of the line number) on the "onEnd: function..." line.
9.	Click Submit. Notice that the variable "data" is set to "<table border='1'>...</table>\n" which is text. Click the blue arrow to step through the debugger.
10.	Run the script to completion. Because the Javascript code incorrectly tried to dereference "data" as an array, the output is not what you want.
11.	You need to change the load function to insert the string directly into outputNode. Modify the dojo.html file and change the code from: load: function(data,ioargs){ dojo.fadeOut({ node: outputNode, onEnd: function(){ outputNode.innerHTML = data[0]["FIRST_NAME"] + ' ' + data[0]["LAST_NAME"]; dojo.fadeIn({node: outputNode, duration: 500}).play(); } }).play(); to load: function(data,ioargs) { dojo.byId("outputNode").innerHTML = data; } Make sure you have the brackets and braces matched. The bottom right of Firebug will display the number of syntax errors in the JavaScript code.
12.	Reload the dojo.html page. Delete the breakpoint in the script by clicking the red breakpoint circle.
13.	Now click Submit. Notice that the output is now correct. Javascript libraries, in conjunction with asynchronous requests to web servers to retreive or insert data, allow highly functional web applications to be developed.

Back to Topic List

Using a PHP Framework Database Component

PHP Frameworks like Symfony, Cake and Zend Framework (to name just a few), bring structure and provide an overall architecture to PHP projects. They, along with component libraries like eZ Components, also provide a rich experience of prebuilt components, for example for using web services or authentication.

ZFW can be used in a framework model-view-controller style, or invididual modules can be used. This section looks at the database component of the Zend Framework (ZFW).

Perform the following tasks:

	Create a Zend_Db Query
	Change Adapters
	Access the Underlying Driver
	Inserting Data and Handling Exceptions
	Conditionally Building a Query

Back to Topic List

Create a Zend_Db Query

You can use Zend to execute a database query whose results will be presented on the page. Perform the following steps:

1.	Review the code as follows that is contained in the zfwdb.php file in the $HOME/public_html directory. This file simply displays information about one employee. <?php require_once 'Zend/Db.php'; $db = Zend_Db::factory('Oracle', array( 'username' => 'phpweb', 'password' => 'phpweb', 'dbname' => 'localhost/orcl' )); $result = $db->fetchAll("select * from employees where employee_id = 185"); echo "<pre>"; // make var_dump output formatted. var_dump($result); ?> It includes Zend/Db.php from the webserver's document root directory. This declares the Zend_DB class. It then creates an instance of the Zend_Db Adapter class. This defers actual connection until the first use, allowing you to write applications that can initialize the connection in a common initialization block, but not having the database connection physically opened unless a DB request is actually made. This is just one of the ways to open connections in Zend_Db. The fetchAll() method returns all results from a query. Use an appropriate WHERE clause if only a partial subset of rows is required. Note that Zend_Db doesn't support persistent connections, since not all databases support them. This makes Oracle 11g DRCP more attractive, because its pool of available connection servers makes PHP non-persistent connection faster.
2.	From your browser, enter the following URL to display the output. When the query executes, the employee details are returned. http://localhost/~phpweb/zfwdb.php
3.	There are a variety of query modes that can be used. Edit the zfwdb.php file and add the following line before the fetchAll() method. Add the statement in bold below. <?php require_once 'Zend/Db.php'; $db = Zend_Db::factory('Oracle', array( 'username' => 'phpweb', 'password' => 'phpweb', 'dbname' => 'localhost/orcl' )); $db->setFetchMode(Zend_Db::FETCH_NUM); $result = $db->fetchAll("select * from employees where employee_id = 185"); echo "<pre>"; // make var_dump output formatted. var_dump($result); ?>
4.	Reload the zfwdb.php file to see the different format.
5.	To pass an array of bind names and values to fetchAll(), edit the zfwdb.php file as followed in bold: <?php require_once 'Zend/Db.php'; $db = Zend_Db::factory('Oracle', array( 'username' => 'phpweb', 'password' => 'phpweb', 'dbname' => 'localhost/orcl' )); $bind = array(':id' => 186 ); $result = $db->fetchAll("select * from employees where employee_id = :id", $bind); echo "<pre>"; // make var_dump output formatted. var_dump($result); ?>
6.	Reload the zfwdb.php file to verify that the correct user value has been returned.

Back to Topic

Change Adapters

The previous example used the 'Oracle' adapter. Internally, this directs Zend_Db to use the OCI8 extension as the underlying database driver. The adapter can be changed to use the PDO_OCI extension without needing to change the query code. PDO_OCI is a database access extension similar to OCI8, but totally independent of it. Perform the following steps:

Modify the zfwdb.php file to change the driver name to PDO_OCI. Add a host attribute and change the db name. The PDO_OCI extension uses different connection notation to the OCI8 extension. Change the lines below that are in bold:

<?php
                               


require_once 'Zend/Db.php';
                               


$db = Zend_Db::factory('
                              
PDO_OCI', array(
                              


                           'username' => 'phpweb',
                              


                           'password' => 'phpweb',
                            
                              
'host'     => 'localhost',
                                


                           'dbname'   => 'orcl'
                              
                              


                      ));
                               


$bind = array(':id' => 186 );
                              


$result = $db->fetchAll("select * from employees where employee_id = :id", $bind);
                               


echo "<pre>";  // make var_dump output formatted.
                              


var_dump($result);
                               


?>

Reload the zfwdb.php file in your browser.

The existing code to query the database returned the same results as before. This shows that applications can be written that are portable across different database drivers. Note: Oracle recommends using the OCI8 extension.

Back to Topic

Access the Underlying Driver

The simplification inherent in an abstration layer like Zend_Db can be limiting in some cases. Perform the following steps:

Review the zfwdb_2.php file. This uses the Oracle adapter (i.e. it internally calls the OCI8 extension). It gets the OCI8 connection resource $c = $db->getConnection();

<?php
                               


require_once 'Zend/Db.php';
                               


$db = Zend_Db::factory('Oracle', array(
                              


                           'username' => 'phpweb',
                              


                           'password' => 'phpweb',
                              


                           'dbname'   => 'localhost/orcl'
                              


                      ));
                               


// Get the underlying OCI8 connection
                              


$c = $db->getConnection();
                               


// Use OCI8 extension functions directly
                              


$s = oci_parse($c, "select * from employees where employee_id = 186");
                              


oci_execute($s);
                              


oci_fetch_all($s, $r);
                               


echo "<pre>";  // make var_dump output formatted.
                              


var_dump($r);
                               


?>

Subsequent code uses OCI8 functions directly, bypassing the Zend_Db component. Such use should be restricted to cases where performance is critical, or the abstraction functionality is limted and Oracle's advanced features not supported.

Load the zfwdb_2.php file in your browser.

The output is the query results. Breaking out to the underlying driver could potentially cause the Zend_Db class to become inconsistent. It also reduces the portability of your applications.

Back to Topic

Inserting Data and Handling Exceptions

Zend_Db allows you to insert and query data, as well as handle exceptions. Perform the following steps:

1.	In SQLPlus create a new table. Open a terminal window and execute the following commands: sqlplus phpweb/phpweb create table ztab (my_name varchar2(40), my_id number, my_date date);*
2.	Review the zfw_ins_1.php file. The row of data that will be inserted is encapsulated in a PHP array. <?php echo "<pre>"; // make output formatted. require_once 'Zend/Db.php'; $db = Zend_Db::factory('Oracle', array( 'username' => 'phpweb', 'password' => 'phpweb', 'dbname' => 'localhost/orcl' )); $data = array( 'MY_NAME' => 'Fred', 'MY_ID' => 123, 'MY_DATE' => '01-JAN-2010' ); $db->insert('ZTAB', $data); // Query the new row back $result = $db->fetchAll("select * from ztab"); var_dump($result); ?>
3.	Load the zfw_ins_1.php file in your browser. The row is inserted and queried back.
4.	Modify the zfw_ins_1.php file. Change the date to 01-01-2010 and reload the zfw_ins_1.php file in your browser. This causes an exception and a Zend_Db backtrace is generated.
5.	Modify the zfw_ins_1.php file. Add a try/catch block around the insert statement in bold below. <?php echo "<pre>"; // make output formatted. require_once 'Zend/Db.php'; $db = Zend_Db::factory('Oracle', array( 'username' => 'phpweb', 'password' => 'phpweb', 'dbname' => 'localhost/orcl' )); $data = array( 'MY_NAME' => 'Fred', 'MY_ID' => 123, 'MY_DATE' => '01-01-2010' ); try { $db->insert('ZTAB', $data); } catch (Zend_Exception $e) { echo $e->getMessage(); exit; } // Query the new row back $result = $db->fetchAll("select * from ztab"); var_dump($result); ?>
6.	Reload the zfw_ins_1.php file in your browser. Now just the error message is displayed.
7.	Modify the zfw_ins_1.php file. Change the column name to lowercase in bold. <?php echo "<pre>"; // make output formatted. require_once 'Zend/Db.php'; $db = Zend_Db::factory('Oracle', array( 'username' => 'phpweb', 'password' => 'phpweb', 'dbname' => 'localhost/orcl' )); $data = array( ' my_name' => 'Fred', 'MY_ID' => 123, 'MY_DATE' => '01-01-2010' ); try { $db->insert('ZTAB', $data); } catch (Zend_Exception $e) { echo $e->getMessage(); exit; } // Query the new row back $result = $db->fetchAll("select * from ztab"); var_dump($result); ?>
8.	Reload the zfw_ins_1.php file in your browser. The error message is displayed. An ORA-904 is generated. With Zend_Db, column names are in uppercase. Zend_Db doesn't support case folding for the Oracle adapter.

Back to Topic

Conditionally Building a Query

The Zend_Db_Select class can be used to build up a query based on conditional logic. Perform the following steps:

Review the zfwdb_3.php file. The row of data that will be inserted is encapsulated in a PHP array.

<?php
                               


require_once 'Zend/Db.php';
                               


$db = Zend_Db::factory('Oracle', array(
                              


                           'username' => 'phpweb',
                              


                           'password' => 'phpweb',
                              


                           'dbname'   => 'localhost/orcl'
                              


                      ));
                               


$select = new Zend_Db_Select($db);
                               


$select->from("EMPLOYEES");
                              


$select->where('employee_id = 185');
                               


$stmt = $db->query($select);
                              


$result = $stmt->fetchAll();
                               


echo "<pre>";
                              


var_dump($result);
                               


?>

Each component of the query can be added separately

 $select->from("EMPLOYEES");
 $select->where('employee_id = 185');

You could even chain the method together:

 $select->from("EMPLOYEES")->where('employee_id = 185');

To select just one column, the FROM condition would be

 $select->from("EMPLOYEES", "LAST_NAME")

To select two columns, the FROM condition would be:

 $select->from("EMPLOYEES", array("LAST_NAME", "FIRST_NAME"))

Load the zfwdb_3.php file in your browser.

Edit zfwdb_3.php and experiment with different clauses.

This section has introduced just part of the functionality provided by Zend_Db, which itself is just part of ZFW. When Zend_Db is combined with the other components and with the model-view-controller (MVC) framework, applications can be rapidly built.

Before implementing a project with a framework or DB abstraction layer you should consider its maturity, support and performance characteristics, and make your own determination of the best solution for your business needs.

Back to Topic

Summary

In this tutorial, you learned how to:

	Measure and improve OCI8 performance
	Use Database Resident Connection Pooling
	Use Firefox debugging tools
	Incoporate AJAX into your page
	Utilize a JavaScript library
	Use a PHP framework database component

Back to Topic List

Move your mouse over this icon to hide all screenshots.

	Popular Downloads
Untitled Document Berkeley DB Enterprise Manager Database EE and XE Developer VMs Enterprise Pack for Eclipse Java JDeveloper and ADF Oracle Linux and Oracle VM MySQL NetBeans IDE NoSQL Database Solaris SQL Developer VirtualBox WebLogic Server

E-mail this page

Printer View

Oracle Cloud

Java

Customer and Events

Communities

Services and Store

Log In to My Oracle Support
Training and Certification
Become a Partner
Find a Partner Solution
Purchase from the Oracle Store
Contact and Chat
Global Contacts
Oracle Support
Phone: 800-633-0738

Products and Services

Solutions

Downloads

Store

Support

Training

Partners

About

Oracle Technology Network