http://wiki4.caucho.com/index.php?title=Resin_4_built-in_fast_PHP_support_called_Quercus_:_covers_Java_integration,_JSON_support_and_more&feed=atom&action=historyResin 4 built-in fast PHP support called Quercus : covers Java integration, JSON support and more - Revision history2024-03-28T20:44:33ZRevision history for this page on the wikiMediaWiki 1.18.0http://wiki4.caucho.com/index.php?title=Resin_4_built-in_fast_PHP_support_called_Quercus_:_covers_Java_integration,_JSON_support_and_more&diff=669&oldid=prevRick at 00:00, 16 June 20122012-06-16T00:00:00Z<p></p>
<table class='diff diff-contentalign-left'>
<col class='diff-marker' />
<col class='diff-content' />
<col class='diff-marker' />
<col class='diff-content' />
<tr valign='top'>
<td colspan='2' style="background-color: white; color:black;">← Older revision</td>
<td colspan='2' style="background-color: white; color:black;">Revision as of 00:00, 16 June 2012</td>
</tr><tr><td colspan="2" class="diff-lineno">Line 1:</td>
<td colspan="2" class="diff-lineno">Line 1:</td></tr>
<tr><td colspan="2"> </td><td class='diff-marker'>+</td><td style="background: #cfc; color:black; font-size: smaller;"><div><ins style="color: red; font-weight: bold; text-decoration: none;">{{Cookbook}}  {{Development}}</ins></div></td></tr>
<tr><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>=Quercus Tutorials=</div></td><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>=Quercus Tutorials=</div></td></tr>
<tr><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>=Quercus: Hello, World installation=</div></td><td class='diff-marker'> </td><td style="background: #eee; color:black; font-size: smaller;"><div>=Quercus: Hello, World installation=</div></td></tr>
</table>Rickhttp://wiki4.caucho.com/index.php?title=Resin_4_built-in_fast_PHP_support_called_Quercus_:_covers_Java_integration,_JSON_support_and_more&diff=657&oldid=prevRick: Created page with "=Quercus Tutorials= =Quercus: Hello, World installation= <p>The Hello, World tutorial covers the basic steps to start Resin and run a trivial php script in Quercus...."2012-06-16T00:00:00Z<p>Created page with "=Quercus Tutorials= =Quercus: Hello, World installation= <p>The Hello, World tutorial covers the basic steps to start Resin and run a trivial php script in Quercus...."</p>
<p><b>New page</b></p><div>=Quercus Tutorials=<br />
=Quercus: Hello, World installation=<br />
<p>The Hello, World tutorial covers the basic steps to<br />
start Resin and run a trivial php script in Quercus.</p><br />
<br />
==Files in this tutorial==<br />
<table><br />
<tr><br />
<th>File</th><br />
<th>Description</th><br />
</tr><br />
<tr><td><code>WEB-INF/resin-web.xml</code><br />
</td><td>web.xml configuration<br />
</td></tr><tr><td><code>hello.php</code><br />
</td><td>The PHP Hello, World<br />
</td></tr></table><br />
<br />
==Introduction==<br />
<br />
<p>This short article is intended for PHP programers who are<br />
interested in trying Caucho's PHP implementation. The download and<br />
unzipping instructions are intended for windows users. However, the<br />
implementation will work just as well in UNIX.</p><br />
<br />
<br />
==Part I: Installing Resin==<br />
<br />
===Downloading and unzipping Resin===<br />
<br />
<br />
<ol><br />
<li>Navigate to http://www.caucho.com/download</li><br />
<li>Download the latest Resin binary for your operating system.</li><br />
<li>For purposes of this demo, I have downloaded the Windows .zip binary.<br />
The non-pro version is open-source.</li><br />
<li>Save the file to your desktop.</li><br />
<li>Double click the binary and unzip the file.</li><br />
<li>For purposes of this demo, I have unzipped the contents to C:\resin-3.2.0</li><br />
</ol><br />
<br />
<br />
===Running Resin for the first time===<br />
<br />
<br />
<ol><br />
<li>Browse to the directory into which you unzipped the resin download (ie: C:\resin-3.2.0)</li><br />
<li>Double-click resin.exe. At this point the resin web server should start.</li><br />
<li>Open your favorite browser and type in the following URL: http://localhost:8080</li><br />
<li>You will now see Resin's Default Home Page</li><br />
</ol><br />
<br />
<br />
==Part II: Configuring Resin to Serve PHP Pages==<br />
<br />
<ol><br />
<li>Cut and paste the "resin-web.xml" into your favorite text editor.</li><br />
<li>Save the file into C:\resin-3.2.0\webapps\ROOT\WEB-INF (NB: if you use<br />
Notepad, then be careful to either save as "all files" or just put the<br />
file name in double quotes. IE: "resin-web.xml").</li><br />
<li>Cut and paste the "hello-world.php" into your text editor.</li><br />
<li>Save the file into C:\resin-3.2.0\webapps\ROOT</li><br />
<li>Open your favorite browser and type in the<br />
following URL: http://localhost:8080/hello-world.php.</li><br />
</ol><br />
<br />
====WEB-INF/resin-web.xml====<br />
<pre><br />
<web-app xmlns="http://caucho.com/ns/resin"><br />
<servlet-mapping url-pattern="*.php"<br />
servlet-class="com.caucho.quercus.servlet.QuercusServlet"><br />
</servlet-mapping><br />
</web-app><br />
</pre><br />
<br />
====hello-world.php====<br />
<pre><br />
<?php<br />
echo "Hello World";<br />
?><br />
</pre><br />
<br />
<br />
==Conclusion==<br />
<br />
<p>If all went well with the above steps, you have installed<br />
Resin correctly. Congratulations!</p><br />
<br />
<p>You can now create your own PHP pages and store them in the same<br />
directory as the hello-world.php file.</p><br />
<br />
=Quercus: Java and PHP integration=<br />
<p>Adding PHP functions with a Java module.</p><br />
<br />
==Files in this tutorial==<br />
<table><br />
<tr><br />
<th>File</th><br />
<th>Description</th><br />
</tr><br />
<tr><br />
<td><code>hello.php</code></td><br />
<td>The PHP Hello, World</td><br />
</tr><br />
<tr><br />
<td><code>WEB-INF/classes/example/HelloModule.java</code></td><br />
<td>The Java module definition</td><br />
</tr><br />
<tr><br />
<td><code>WEB-INF/resin-web.xml</code></td><br />
<td>web.xml configuration</td><br />
</tr><br />
<tr><br />
<td><code>WEB-INF/classes/META-INF/services/com.caucho.quercus.QuercusModule</code></td><br />
<td>Adding the module</td><br />
</tr><br />
</table><br />
<br />
==Introduction==<br />
<p><br />
This article shows how to use Quercus, Resin's PHP implementation,<br />
to create a module in Java callable from a PHP page.<br />
</p><br />
<!--<br />
[[PHP Hello World Module (jar version)]] explains how a module is meant to be used within your PHP code, so I won't repeat that information here. Instead, this article will focus on the steps necessary to create and use a module without having to "jar up" your files.<br />
--><br />
<br />
<p>For purposes of this article, I assume that you are working with<br />
Resin 3.2.0 with the home directory at C:\resin-3.2.0. I will call this<br />
directory <code>$webApp</code>.<br />
</p><br />
<br />
<br />
==Step 1: Create resin-web.xml and place it in $webApp/WEB-INF==<br />
<br />
====resin-web.xml====<br />
<pre><br />
<web-app xmlns="http://caucho.com/ns/resin"><br />
<servlet servlet-name="resin-php"<br />
servlet-class="com.caucho.quercus.servlet.QuercusServlet"/><br />
<br />
<servlet-mapping url-pattern="*.php" servlet-name="resin-php"/> <br />
</web-app><br />
</pre><br />
<br />
<br />
==Step 2: Create HelloModule.java and place it in $webApp/WEB-INF/classes/example==<br />
<br />
====HelloModule.java====<br />
<pre><br />
package example;<br />
<br />
import com.caucho.quercus.module.AbstractQuercusModule;<br />
<br />
public class HelloModule extends AbstractQuercusModule {<br />
/*<br />
** Notice the careful use of the naming<br />
** convention hello_test. This is done<br />
** in order to prevent name collisions<br />
** among different libraries.<br />
*/<br />
public String hello_test(String name)<br />
{<br />
return "Hello, " + name;<br />
}<br />
}<br />
</pre><br />
<br />
==Step 3: Create com.caucho.quercus.QuercusModule and place it in $webApp/WEB-INF/classes/META-INF/services==<br />
<br />
====com.caucho.quercus.QuercusModule====<br />
<pre><br />
example.HelloModule<br />
</pre><br />
<br />
==Step 4: Create hello.php and place it in webapps/ROOT==<br />
<br />
====hello.php====<br />
<pre><br />
<?php echo hello_test("World") ?><br />
</pre><br />
<br />
<p><br />
In your favorite browser, type:<br />
</p><br />
<br />
========<br />
<pre><br />
http://localhost:8080/hello.php<br />
</pre><br />
<br />
<p><br />
You should see:<br />
</p><br />
<br />
====results====<br />
<pre><br />
Hello, World<br />
</pre><br />
<br />
<br />
==Advanced Users==<br />
<br />
<p>The first argument of a Java function may be the <code>Env</code>.<br />
The <code>Env</code> provides access to Quercus resources like printing<br />
to the browser and retrieving a PHP ini value. For the API of<br />
<code>Env</code>, refer to the Quercus javadoc.</p><br />
<br />
========<br />
<pre><br />
<br />
package example;<br />
<br />
import com.caucho.quercus.env.Env;<br />
import com.caucho.quercus.module.AbstractQuercusModule;<br />
<br />
public class HelloModule extends AbstractQuercusModule {<br />
/*<br />
** Notice the careful use of the naming<br />
** convention hello_test. This is done<br />
** in order to prevent name collisions<br />
** among different libraries.<br />
**<br />
** @param env provides access to Quercus environment resources<br />
** @param name<br />
*/<br />
public String hello_test(Env env, String name)<br />
{<br />
env.println("inside HelloModule hello_test()");<br />
return "Hello, " + name;<br />
}<br />
}<br />
<br />
</pre><br />
<br />
<p>Now <code>hello_test</code> will print "inside HelloModule hello_test()"<br />
to the browser before returning.</p><br />
<br />
<br />
==Java Function Arguments/Return Marshaling==<br />
<br />
<p>Quercus does marshaling to and from Quercus Values and Java objects.<br />
If a Java function requires a String, Quercus will automatically convert<br />
the internal Quercus StringValue to a String. If a Java function returns<br />
an <code>int</code>, Quercus will create a Quercus LongValue for it.</p><br />
<br />
<p>For other Java Objects like <code>java.util.Date</code> that are returned<br />
to PHP, Quercus puts them into wrappers to expose their public methods and<br />
members to the PHP script. Java Collection, List, and Map instances have<br />
the additional ability to be used within a PHP script like any other PHP<br />
array.</p><br />
<br />
<p>For more information, see<br />
[../../doc/quercus-java-integration.xtp Java Interface].</p><br />
<br />
<br />
==Conclusion==<br />
<br />
<p>It is fairly straight forward to create your own modules callable from<br />
within a Quercus/PHP page. The above tutorial takes through the steps<br />
to create the simple hello world application (without needing<br />
to "jar-up" your files).</p><br />
<br />
<p>If you want to change your module in any way, all you have to do is<br />
resave the ".java" file in the classes\example directory, and Resin<br />
will recompile it for you.</p><br />
<br />
<p>You do not need to restart your web app or Resin. It's just that simple.</p><br />
<br />
=Quercus JSON=<br />
<p><br />
<b>JSON</b> (JavaScript Object Notation) is a popular text data<br />
exchange format with built-in support from Quercus since Resin 3.0.20.<br />
One of the common uses of JSON in a PHP environment is for the server<br />
to send JSON data to the user's browser. Because the JSON language is<br />
a subset of JavaScript, JSON-encoded text can be readily parsed on the<br />
user's browser using JavaScript's <code>eval()</code> function.<br />
</p><br />
<br />
==Files in this tutorial==<br />
<table><br />
<tr><br />
<th>File</th><br />
<th>Description</th><br />
</tr><br />
<tr><br />
<td><code>json.html</code></td><br />
<td>The JSON example page</td><br />
</tr><br />
<tr><br />
<td><code>json.php</code></td><br />
<td>The JSON PHP page</td><br />
</tr><br />
</table><br />
<br />
==Using JSON in Quercus==<br />
<br />
<p><br />
Quercus has built-in JSON support and JSON functionality is enabled the moment Quercus is started: no additional downloads are required. Quercus sports two PHP functions for working with JSON: <i>json_encode</i> and <i>json_decode</i>.<br />
</p><br />
========<br />
<pre><br />
<b>json_encode(mixed php_object)</b><br />
encodes any PHP array or object into JSON.<br />
<br />
<b>json_decode(string json_string [, bool is_assoc])</b><br />
decodes JSON into a PHP array or object.<br />
</pre><br />
<p><br />
<i>json_decode</i> may return either a PHP array or object depending on the circumstances:<br />
</p><br />
<ul><br />
<li><br />
If the text is that of a JSON [http://www.json.org array],<br />
decoding returns a non-associative PHP array.<br />
</li><li><br />
If the text is that of a JSON object and the second argument to <i>json_decode</i> is not specified or is <i>false</i>, decoding returns a standard PHP object.<br />
</li><br />
<li><br />
If the text is that of a JSON object and the second argument to <i>json_decode</i> is <i>true</i>, then decoding returns an associative PHP array.<br />
</li><br />
</ul><br />
<br />
==Examples==<br />
===json_encode===<br />
<br />
<p><br />
To encode an array into JSON:<br />
</p><br />
========<br />
<pre><br />
<?php<br />
$array = array("a"=>"Caucho", "b"=>"Resin", "c"=>"Quercus");<br />
$json = json_encode($array);<br />
?><br />
</pre><br />
<p><br />
The value of $json would be: <i>'{"a":"Caucho", "b":"Resin", "c":"Quercus"}'</i>.<br />
The JSON text may then be sent and used on the user's browser or to any client that can decode JSON.<br />
</p><br />
<br />
===json_decode===<br />
<br />
<p><br />
To decode JSON data into a standard PHP object in Quercus (using the above JSON text <i>$json</i> as an example):<br />
</p><br />
========<br />
<pre><br />
<?php<br />
$object = json_decode($json);<br />
?><br />
</pre><br />
<p><br />
<i>$object</i> would be a standard PHP object with three fields "a", "b", and "c" with values "Caucho", "Resin", and "Quercus" respectively.<br />
</p><br />
<br />
===Simple Web Example===<br />
<br />
<p><br />
Below is a simple example using JSON on the web.<br />
</p><br />
========<br />
<pre><br />
<script type="text/javascript"><br />
<br />
<?php<br />
$array = array("a"=>"Caucho", "b"=>"Resin", "c"=>"Quercus");<br />
$json = json_encode($array);<br />
echo "var data = $json;";<br />
?><br />
<br />
var decoded = eval("(" + data + ")");<br />
<br />
//Should output: "Quercus at work."<br />
document.write(decoded.c + " at work.");<br />
</script><br />
</pre><br />
<br />
===AJAX Example===<br />
<br />
<br />
<p> JSON data is more commonly sent to<br />
the browser via AJAX requests. Suppose there are two files defined<br />
below. The PHP script in <i>json.php</i> encodes an array into JSON.<br />
When the user's browser is directed to <i>json.html</i>, an AJAX<br />
request receives JSON data from <i>json.php</i>. Then the browser<br />
calls <code>eval()</code> on the JSON data to recover a JavaScript object.<br />
</p><br />
<br />
<s3 title="json.php:"><br />
========<br />
<pre><br />
<?php<br />
$array = array("a"=>"Caucho", "b"=>"Resin", "c"=>"Quercus");<br />
$json = json_encode($array);<br />
echo $json;<br />
?><br />
</pre><br />
<br />
====json.html:====<br />
<br />
========<br />
<pre><br />
<html><br />
<head><br />
<script type="text/javascript"><br />
<br />
var url = "data.php";<br />
function request() { <br />
if (window.XMLHttpRequest)<br />
http_request = new XMLHttpRequest();<br />
else<br />
http_request = new ActiveXObject("Microsoft.XMLHTTP");<br />
http_request.onreadystatechange = function() {<br />
handle_json(http_request)<br />
};<br />
http_request.open("GET", url, true);<br />
http_request.send(null);<br />
}<br />
<br />
function handle_json(http_request) {<br />
if (http_request.readyState == 4) {<br />
document.firstForm.json.value = http_request.responseText;<br />
var decoded = eval("(" + http_request.responseText + ")");<br />
document.firstForm.decoded.value = decoded.a + "'s " +<br />
decoded.b + " with " + decoded.c + " at work.";<br />
}<br />
}<br />
<br />
function clearForm() {<br />
document.firstForm.json.value = "";<br />
document.firstForm.decoded.value = "";<br />
}<br />
</script><br />
</head><br />
<body><br />
<form name="firstForm"><br />
<p>JSON:<br><textarea name="json" cols="50"></textarea></p><br />
<p>Decoded:<br><textarea name="decoded" cols="50"></textarea></p><br />
<input type="button" onclick="request()" value="AJAX Request"><br />
<input type="button" onclick="clearForm()" value="Clear"><br />
</form><br />
</body><br />
</html><br />
</pre><br />
<br />
</s2><br />
<br />
==External Links==<br />
<ul><br />
<li><br />
[http://www.json.org Official JSON Homepage]<br />
</li><br />
<li><br />
[http://en.wikipedia.org/wiki/JSON JSON Wikipedia entry]<br />
</li><br />
<li><br />
[http://wiki.caucho.com/Quercus:_JSON Caucho's JSON Wiki entry]<br />
</li><br />
<li><br />
<a href="http://www.aurore.net/projects/php-json">C Implementation from which<br />
Quercus' JSON syntax is based off of<br />
</a><br />
</li><br />
</ul><br />
<br />
=Quercus: PDO (portable database object)=<br />
<p>The PDO tutorial explains using the new PHP 5 portable<br />
database object (PDO) interface.</p><br />
<br />
==Files in this tutorial==<br />
<table><br />
<tr><br />
<th>File</th><br />
<th>Description</th><br />
</tr><br />
<tr><br />
<td><code>WEB-INF/resin-web.xml</code></td><br />
<td>resin-web.xml configuration</td><br />
</tr><br />
<tr><br />
<td><code>test.php</code></td><br />
<td>The PDO tutorial</td><br />
</tr><br />
</table><br />
<br />
==Introduction==<br />
<br />
<p>This short article introduces PDO, the PHP 5 Portable Database Object<br />
interface.</p><br />
<br />
<p>At the minimal level, PDO can be reduced to three statements:</p><br />
<ol><br />
<li>Creating a PDO object with <code>new PDO('''''url''''')</code></li><br />
<li>Querying a SQL statement with results with <code>$pdo->query('''''sql''''')</code></li><br />
<li>Executing a SQL statement with <code>$pdo->exec('''''sql''''')</code></li><br />
</ol><br />
<br />
<p>PDO has several variations on those three statements, but you can always<br />
get by with the first three.</p><br />
<br />
===Connecting to the Database: new PDO===<br />
<br />
<br />
<p>The first step in working with databases is to specify which database<br />
we're connecting to and create a new connection.<br />
The <code>new PDO('''''url''''')</code> call creates a new connection<br />
to a database. The '''''url''''' describes which database to use.<br />
Quercus recognizes the PHP standard urls like '''''mysql:dbname=test'''''<br />
and it also can use JNDI names directly<br />
like '''''java:comp/env/jdbc/resin'''''.<br />
</p><br />
<br />
====connecting to a database in JNDI====<br />
<pre><br />
$pdo = new PDO("java:comp/env/jdbc/resin");<br />
</pre><br />
<br />
<br />
===Executing SQL statements: exec===<br />
<br />
<br />
<p>Once the application has connected with a database, it needs to<br />
do something, and since relational databases require tables and some<br />
data, the application will need to create them. In PDO, the<br />
<code>$pdo->exec('''''sql''''')</code> method executes arbitrary<br />
SQL. We'll use <code>exec</code> to create the database and add some<br />
data.</p><br />
<br />
====creating the database====<br />
<pre><br />
$pdo = new PDO("java:comp/env/jdbc/resin");<br />
<br />
$pdo->exec(<<<END<br />
CREATE TABLE brooms (<br />
id INTEGER PRIMARY KEY auto_increment,<br />
name VARCHAR(255) UNIQUE,<br />
price INTEGER<br />
)<br />
END)<br />
or die("Can't create table 'brooms'");<br />
<br />
$pdo->exec("INSERT INTO brooms (name, price)"<br />
. " VALUES ('cleansweep 5', 5)")<br />
or die("Can't insert data into table 'brooms'");<br />
<br />
$pdo->exec("INSERT INTO brooms (name, price)"<br />
. " VALUES ('cleansweep 7', 15)")<br />
or die("Can't insert data into table 'brooms'");<br />
</pre><br />
<br />
<br />
===Querying the database: query===<br />
<br />
<br />
<p>Once the database has data, we'll want to query the database to<br />
see what we've stored. The <code>$pdo->query('''''sql''''')</code> method<br />
queries the database. For now, we'll use the <code>foreach</code> form<br />
of the query.</p><br />
<br />
====displaying the data====<br />
<pre><br />
$pdo = new PDO("java:comp/env/jdbc/resin");<br />
<br />
echo "<table>\n"<br />
<br />
$sql = "SELECT name, price FROM brooms";<br />
foreach ($pdo->query($sql) as $name => $price) {<br />
echo "<tr><td>$name<td>$price\n";<br />
}<br />
<br />
echo "</table>\n"<br />
</pre><br />
<br />
===Moving forward: CRUD===<br />
<br />
<br />
<p>For basic database access, the three calls we've<br />
introduced are sufficient. If you're just starting with PDO, you may<br />
want to stop right here, stick with <code>new PDO('''''url''''')</code>,<br />
<code>$pdo->exec('''''sql''''')</code>,<br />
and <code>$pdo->query('''''sql''''')</code> with the foreach pattern until<br />
you can write PDO code without checking the tutorial. Go ahead, add some<br />
foo.php with some sample tables and get learning!</p><br />
<br />
<p>Once you've tattooed the basic three PDO calls into your brain, it's time<br />
to start exploring the alternatives PDO provides. We'll introduce some<br />
of the main options using the<br />
[http://en.wikipedia.org/wiki/CRUD_%28acronym%29 CRUD]<br />
framework.</p><br />
<br />
<p>CRUD (<b>C</b>reate, <b>R</b>ead, <b>U</b>pdate, <b>D</b>elete)<br />
is just a simple acronym to organize the basics of any database or<br />
persistence application. It's a handy mnemonic when sketching out<br />
a prototype, helping to avoid the embarrassment of forgetting to let the user<br />
delete an object. And it's handy when learning a new persistence<br />
framework or database API to make sure you've covered the bases.</p><br />
<br />
<br />
<br />
==Create==<br />
<br />
<p>The first step in any database application is to create the database<br />
and to create the first entries in the database. In PDO, creating the<br />
database table and creating entries can use the<br />
same <code>$pdo->exec('''''sql''''')</code> call as we<br />
introduced above. From a relation database perspective,<br />
creating a table is very different from adding a new item, but at the PDO<br />
level, they're similar. We'll use the <code>exec</code> call to create<br />
the database as above, and then introduce the<br />
<code>prepare('''''sql''''')</code> call for prepared statements to add<br />
items.</p><br />
<br />
====creating the database====<br />
<pre><br />
$pdo = new PDO("java:comp/env/jdbc/resin");<br />
<br />
$pdo->exec(<<<END<br />
CREATE TABLE brooms (<br />
id INTEGER PRIMARY KEY auto_increment,<br />
name VARCHAR(255),<br />
price INTEGER<br />
)<br />
END);<br />
</pre><br />
<br />
<p>Although we could use the basic <code>exec</code> method to add the<br />
data, this time we'll introduce prepared statements and<br />
the <code>$pdo->prepare('''''sql''''')</code> method.</p><br />
<br />
<p>Prepared statements precompile the SQL for a database query and assign<br />
parameters for each call. Most importantly, this can create cleaner code.<br />
Prepared statements can also avoid some of the security problems associated<br />
with web applications. Because the parameter assignment are always values<br />
and never raw SQL, Quercus can properly escape the values automatically.<br />
Prepared statements can also improve efficiency by allowing the SQL to<br />
be parsed only once and then used multiple times.</p><br />
<br />
<p>Prepared statements in PDO split SQL execution into three phases:<br />
<code>prepare</code>, <code>bindParam</code> and <code>execute</code>.<br />
<code>$pdo->prepare('''''sql''''')</code> parses the SQL and<br />
returns a <code>PDOStatement</code> object. <code>$stmt->bindParam(...)</code><br />
assigns the parameters to PHP variables. And <code>$stmt->execute()</code><br />
actually executes the statement.</p><br />
<br />
<p>In this example, we'll add some more brooms to the database. This time,<br />
we'll loop across a PHP array to insert the values.</p><br />
<br />
====inserting with prepare====<br />
<pre><br />
$pdo = new PDO("java:comp/env/jdbc/resin");<br />
<br />
$stmt = $pdo->prepare("INSERT INTO (name, price) VALUES (:name, :price)");<br />
<br />
$stmt->bindParam(":name", $name);<br />
$stmt->bindParam(":price", $price);<br />
<br />
$brooms = array("nimbus 2000" => 100,<br />
"nimbus 2001" => 150);<br />
<br />
foreach ($brooms as $name => $price) {<br />
$stmt->execute() or die("Can't add $name to brooms");<br />
}<br />
</pre><br />
<br />
<br />
==Read==<br />
<br />
<p>Because most database accesses are reads, most applications will<br />
spend extra time creating useful queries, and optimizing and<br />
caching for performance. PDO provides the basic<br />
<code>$pdo->query('''''sql''''')</code>, but it also supports many ways<br />
of extracting data.</p><br />
<br />
===$pdo->query and foreach===<br />
<br />
<br />
<p>As we described in the introduction, your application can get away<br />
with using the basic PDO query pattern. The result of a PDO query<br />
can work with the PHP <code>foreach</code> statement to iterate<br />
through the rows of the result. Each result will be an associative<br />
array of the result values.</p><br />
<br />
========<br />
<pre><br />
$pdo = new PDO("java:comp/env/jdbc/resin");<br />
<br />
$sql = "SELECT * FROM brooms";<br />
<br />
foreach ($pdo->query($sql) as $row) {<br />
echo $row['name'] . ", " . $row['price'] . "<br>\n";<br />
}<br />
</pre><br />
<br />
<br />
===$pdo->query and fetch(PDO::FETCH_ASSOC)===<br />
<br />
<br />
<p>In some cases, an application might need more control over<br />
the row iteration than the <code>foreach</code> pattern provides.<br />
For example, an application might want to avoid creating a<br />
&lt;table> if the database has no data.<br />
When more control is needed, PDO lets you split out the<br />
iteration from retrieving the row, using <code>$stmt->fetch(...)</code>.</p><br />
<br />
<p>The <code>$stmt->fetch(...)</code> call will also let the application<br />
choose the type of object it wants to deal with. For now, we'll stick<br />
with an associative array.</p><br />
<br />
====PDO::FETCH_ASSOC====<br />
<pre><br />
$pdo = new PDO("java:comp/env/jdbc/resin");<br />
<br />
$stmt = $pdo->query("SELECT * FROM brooms") or die();<br />
<br />
echo "<table>";<br />
while (($row = $stmt->fetch(PDO::FETCH_ASSOC))) {<br />
echo "<tr><td>${row['name']}<td>${row['price']}\n";<br />
}<br />
echo "</table>";<br />
</pre><br />
<br />
<br />
===fetch(PDO::FETCH_OBJ)===<br />
<br />
<br />
<p>PDO's <code>fetch</code> method provides many options for extracting<br />
data from a row other than a full associative list. In particular, you<br />
can return a PHP 5 object representing the row. In many cases, the<br />
object will be more efficient than an array in PHP programs.<br />
Since PHP automatically copies arrays but does not automatically copy<br />
PHP 5 objects, you can avoid stressing out the garbage collector by<br />
using objects over arrays.</p><br />
<br />
<p>The PHP code for getting an object from a row is identical to the<br />
associative array but swapping PDO::FETCH_OBJ for PDO::FETCH_ASSOC.<br />
PDO provides many other fetch options as well, but we'll restrict<br />
ourselves to PDO::FETCH_OBJ.</p><br />
<br />
====PDO::FETCH_OBJ====<br />
<pre><br />
$pdo = new PDO("java:comp/env/jdbc/resin");<br />
<br />
$stmt = $pdo->query("SELECT * FROM brooms") or die();<br />
<br />
echo "<table>";<br />
while (($row = $stmt->fetch(PDO::FETCH_OBJ))) {<br />
echo "<tr><td>$row->name<td>$row->price\n";<br />
}<br />
echo "</table>";<br />
</pre><br />
<br />
<br />
<br />
==Update==<br />
<br />
<p>Once an application has data, it will often need to update it.<br />
In this case, we'll raise the prices of some of the brooms. As<br />
with the creation, PDO's updates use the <code>exec</code> and<br />
<code>prepare</code> methods.</p><br />
<br />
===Transactions===<br />
<br />
<br />
<p>In this case, though, we'd like some extra reliability, so we'll<br />
add some transaction support. Transactions are a generalized lock<br />
in a database, allowing multiple statements to execute atomically. That<br />
means either all of the statements will update the<br />
database or none of them will.</p><br />
<br />
<p>In many examples, the all-or-none property is vital to the integrity<br />
of a database. A bank transfer, for example, must deduct a balance<br />
from one account and give it to another. Withdrawing from one without<br />
adding to the other would be a disaster. In a less critical example,<br />
a bulletin board forum which updates a comment's hierarchy might need<br />
to change multiple columns at once to avoid corrupting the forum.<br />
Although the price of failure is less for a forum, it's still important<br />
for producing professional code.</p><br />
<br />
<p>The transaction wraps the SQL statements<br />
between a <code>beginTransaction()</code> call<br />
and a <code>commit()</code> call. All the SQL statements in between<br />
will be executed as one indivisible block (atomic) or the commit will fail.<br />
</p><br />
<br />
========<br />
<pre><br />
$stmt = $pdo->prepare("UPDATE brooms SET price=:price WHERE name=:broom");<br />
<br />
$stmt->bindParam(":broom", $broom);<br />
$stmt->bindParam(":price", $price);<br />
<br />
$pdo->beginTransaction();<br />
<br />
$brooms = array("nimbus 2000" => 120,<br />
"nimbus 2001" => 250);<br />
<br />
foreach ($brooms as $broom => $price) {<br />
$stmt->execute();<br />
}<br />
<br />
$pdo->commit();<br />
</pre><br />
<br />
<br />
<br />
==Delete==<br />
<br />
<p>For completeness, and to clean up the example, we'll need to<br />
delete the entries we've added. PDO uses the <code>exec</code><br />
and <code>prepare</code> methods for DELETE just as for<br />
INSERT. We'll use the prepared statement method since we'll be deleting<br />
several items.</p><br />
<br />
========<br />
<pre><br />
$pdo = new PDO("java:comp/env/jdbc/resin");<br />
<br />
$stmt = $pdo->prepare("DELETE FROM brooms WHERE name=:name");<br />
$stmt->bindParam(":name", $name);<br />
<br />
foreach (array('firebolt', 'nimbus 2000', 'nimbus 2001') as $name) {<br />
$stmt->execute();<br />
}<br />
</pre></div>Rick