CHAPTER 6 – Executing PEAR DB Queries

There are four ways of running queries with PEAR DB. All are performed by calling different methods in the connection object: query(), limitQuery(), prepare()/execute(), or simpleQuery(). An explanation of each follows.

query($query, $params = array()) This is the default way of calling queries if you don't need to limit the number of results. If the result contains one or more rows, query() returns a result object; otherwise, it returns a Boolean indicating success. Here is an example that returns results: <?php require_once 'DB.php'; PEAR::setErrorHandling(PEAR_ERROR_DIE, "%s n"); $dbh = DB::connect("mysql://test@localhost/world"); $result = $dbh->query("SELECT Name FROM City WHERE " . "CountryCode = 'NOR'"); while ($result->fetchInto($row)) { print "$row[0] n"; } This example uses the "world" database referenced in the previous MySQL section. Here, the query() method returns a DB_result object. DB_result's fetchInto() method retrieves a row of results and stores it in the $row array. When the last row has been read, fetchInto() returns null. Continue reading for more details about fetchInto() and the other fetch methods. The query() method also accepts an additional parameter for passing input parameters to the query: <?php require_once 'DB.php'; PEAR::setErrorHandling(PEAR_ERROR_DIE, "%s n"); $dbh = DB::connect("mysql://test@localhost/world"); $code = 'NOR'; $result = $dbh->query("SELECT Name FROM City WHERE CountryCode = ?", $code); while ($result->fetchInto($row)) { print "$row[0] n"; } This example does exactly the same thing as the previous one, except it uses prepare/execute or bind if the database supports it. The other advantage of passing input parameters like this is that you need not worry about quoting. DB automatically quotes your parameters for you as necessary.

limitQuery($query, $from, $count, $params = array()) This method is almost identical to query(), except that it takes a "from" and "count" parameter that limits the result set to a specific offset range. Here's an example:

<?php require_once 'DB.php'; $from = isset($_GET['from']) ? (int)$_GET['from'] : 0; $show = isset($_GET['show']) ? (int)$_GET['show'] : 0; $from = $from ? $from : 0; $show = $show ? $show : 10; PEAR::setErrorHandling(PEAR_ERROR_DIE, "%s n"); $dbh = DB::connect("mysql://test@localhost/world"); $result = $dbh->limitQuery("SELECT Name, Population FROM City ". "ORDER BY Population", $from, $show); while ($result->fetchInto($row)) { print "$row[0] ($row[1]) n"; } The limitQuery() method ensures that the first result is at offset $from (starting at 0), and no more than $show results are returned. prepare($query) and execute($sth, $data = array()) T h e last way of running queries is to use the prepare() and execute() methods. The prepare() method will parse the query and extract input parameter placeholders. If the back-end database supports either input parameter bind- ing or the prepare/execute paradigm, the appropriate native calls are done to prepare the query for execution. Next, the execute() takes a prepared query along with input parameters, sends the parameters to the database, executes the query, and returns either a Boolean or a DB_result object, just like the other querying methods. You may call execute() many times for each prepared query. By using prepare/execute (for example) in a loop with many INSERT queries, you may save yourself from a lot of query parsing overhead, because the database has already parsed the query and just needs to execute it with new data. You can use prepare() and execute() regardless of whether the back-end database supports this feature. DB emulates as necessary by building and executing a new query for each execute() call. Here is an example that updates the world database numbers with offi- cial numbers for Norway as of January 1, 2004: <?php require_once 'DB.php'; $changes = array( array(154351, "Trondheim", "NOR"), array(521886, "Oslo", "NOR"), array(112405, "Stavanger", "NOR"), array(237430, "Bergen", "NOR"), array(103313, "BÊrum", "NOR"), ); PEAR::setErrorHandling(PEAR_ERROR_DIE, "%s n"); $dbh = DB::connect("mysql://test@localhost/world"); $sth = $dbh->prepare("UPDATE City SET Population = ? " . "WHERE Name = ? AND CountryCode = ?"); foreach ($changes as $data) { $dbh->execute($sth, $data); printf("%s: %d row(s) changed n", $data[1], $dbh->affectedRows()); } Here, the query is prepared once, and $sth contains a reference (integer or resource, depending on the driver) to the prepared query. Then the prepared query is executed once for each UPDATE statement. This example also demonstrates the affectedRows() call, which returns the number of rows with different content after the execute() call.

Post Comment