Resin Documentationapp server |
quercus: pdo (portable database object)
The PDO tutorial explains using the new PHP 5 portable database object (PDO) interface.
This short article introduces PDO, the PHP 5 Portable Database Object interface. At the minimal level, PDO can be reduced to three statements:
PDO has several variations on those three statements, but you can always get by with the first three. Connecting to the Database: new PDOThe first step in working with databases is to specify which database
we're connecting to and create a new connection.
The $pdo = new PDO("java:comp/env/jdbc/resin"); Executing SQL statements: execOnce the application has connected with a database, it needs to
do something, and since relational databases require tables and some
data, the application will need to create them. In PDO, the
$pdo = new PDO("java:comp/env/jdbc/resin"); $pdo->exec(<<<END CREATE TABLE brooms ( id INTEGER PRIMARY KEY auto_increment, name VARCHAR(255) UNIQUE, price INTEGER ) END) or die("Can't create table 'brooms'"); $pdo->exec("INSERT INTO brooms (name, price)" . " VALUES ('cleansweep 5', 5)") or die("Can't insert data into table 'brooms'"); $pdo->exec("INSERT INTO brooms (name, price)" . " VALUES ('cleansweep 7', 15)") or die("Can't insert data into table 'brooms'"); Querying the database: queryOnce the database has data, we'll want to query the database to
see what we've stored. The $pdo = new PDO("java:comp/env/jdbc/resin"); echo "<table>\n" $sql = "SELECT name, price FROM brooms"; foreach ($pdo->query($sql) as $name => $price) { echo "<tr><td>$name<td>$price\n"; } echo "</table>\n" Moving forward: CRUDFor basic database access, the three calls we've
introduced are sufficient. If you're just starting with PDO, you may
want to stop right here, stick with Once you've tattooed the basic three PDO calls into your brain, it's time to start exploring the alternatives PDO provides. We'll introduce some of the main options using the CRUD framework. CRUD (Create, Read, Update, Delete) is just a simple acronym to organize the basics of any database or persistence application. It's a handy mnemonic when sketching out a prototype, helping to avoid the embarrassment of forgetting to let the user delete an object. And it's handy when learning a new persistence framework or database API to make sure you've covered the bases. The first step in any database application is to create the database
and to create the first entries in the database. In PDO, creating the
database table and creating entries can use the
same $pdo = new PDO("java:comp/env/jdbc/resin"); $pdo->exec(<<<END CREATE TABLE brooms ( id INTEGER PRIMARY KEY auto_increment, name VARCHAR(255), price INTEGER ) END); Although we could use the basic Prepared statements precompile the SQL for a database query and assign parameters for each call. Most importantly, this can create cleaner code. Prepared statements can also avoid some of the security problems associated with web applications. Because the parameter assignment are always values and never raw SQL, Quercus can properly escape the values automatically. Prepared statements can also improve efficiency by allowing the SQL to be parsed only once and then used multiple times. Prepared statements in PDO split SQL execution into three phases:
In this example, we'll add some more brooms to the database. This time, we'll loop across a PHP array to insert the values. $pdo = new PDO("java:comp/env/jdbc/resin"); $stmt = $pdo->prepare("INSERT INTO (name, price) VALUES (:name, :price)"); $stmt->bindParam(":name", $name); $stmt->bindParam(":price", $price); $brooms = array("nimbus 2000" => 100, "nimbus 2001" => 150); foreach ($brooms as $name => $price) { $stmt->execute() or die("Can't add $name to brooms"); } Because most database accesses are reads, most applications will
spend extra time creating useful queries, and optimizing and
caching for performance. PDO provides the basic
$pdo->query and foreachAs we described in the introduction, your application can get away
with using the basic PDO query pattern. The result of a PDO query
can work with the PHP $pdo = new PDO("java:comp/env/jdbc/resin"); $sql = "SELECT * FROM brooms"; foreach ($pdo->query($sql) as $row) { echo $row['name'] . ", " . $row['price'] . "<br>\n"; } $pdo->query and fetch(PDO::FETCH_ASSOC)In some cases, an application might need more control over
the row iteration than the The $pdo = new PDO("java:comp/env/jdbc/resin"); $stmt = $pdo->query("SELECT * FROM brooms") or die(); echo "<table>"; while (($row = $stmt->fetch(PDO::FETCH_ASSOC))) { echo "<tr><td>${row['name']}<td>${row['price']}\n"; } echo "</table>"; fetch(PDO::FETCH_OBJ)PDO's The PHP code for getting an object from a row is identical to the associative array but swapping PDO::FETCH_OBJ for PDO::FETCH_ASSOC. PDO provides many other fetch options as well, but we'll restrict ourselves to PDO::FETCH_OBJ. $pdo = new PDO("java:comp/env/jdbc/resin"); $stmt = $pdo->query("SELECT * FROM brooms") or die(); echo "<table>"; while (($row = $stmt->fetch(PDO::FETCH_OBJ))) { echo "<tr><td>$row->name<td>$row->price\n"; } echo "</table>"; Once an application has data, it will often need to update it.
In this case, we'll raise the prices of some of the brooms. As
with the creation, PDO's updates use the TransactionsIn this case, though, we'd like some extra reliability, so we'll add some transaction support. Transactions are a generalized lock in a database, allowing multiple statements to execute atomically. That means either all of the statements will update the database or none of them will. In many examples, the all-or-none property is vital to the integrity of a database. A bank transfer, for example, must deduct a balance from one account and give it to another. Withdrawing from one without adding to the other would be a disaster. In a less critical example, a bulletin board forum which updates a comment's hierarchy might need to change multiple columns at once to avoid corrupting the forum. Although the price of failure is less for a forum, it's still important for producing professional code. The transaction wraps the SQL statements
between a $stmt = $pdo->prepare("UPDATE brooms SET price=:price WHERE name=:broom"); $stmt->bindParam(":broom", $broom); $stmt->bindParam(":price", $price); $pdo->beginTransaction(); $brooms = array("nimbus 2000" => 120, "nimbus 2001" => 250); foreach ($brooms as $broom => $price) { $stmt->execute(); } $pdo->commit(); For completeness, and to clean up the example, we'll need to
delete the entries we've added. PDO uses the $pdo = new PDO("java:comp/env/jdbc/resin"); $stmt = $pdo->prepare("DELETE FROM brooms WHERE name=:name"); $stmt->bindParam(":name", $name); foreach (array('firebolt', 'nimbus 2000', 'nimbus 2001') as $name) { $stmt->execute(); }
|