Writing MySQL-Based Programs - MySQL - page 74

• select_one executes a query and returns the first row as an array (or nil if the
result is empty):
row = dbh . select_one ( "SELECT id, name, cats FROM profile WHERE id = 3" )
• select_all executes a query and returns an array of DBI::Row objects, one per row
of the result set. The array is empty if the result is empty:
rows = dbh . select_all ( "SELECT id, name, cats FROM profile" )
The select_all method is useful when you need to process a result set more than
once because Ruby DBI provides no way to “rewind” a result set. By fetching the
entire result set as an array of row objects, you can iterate through its elements
multiple times. If you need to run through the rows only once, you can apply an
iterator directly to select_all :
dbh . select_all ( "SELECT id, name, cats FROM profile" ) . each do | row |
printf "id: %s, name: %s, cats: %s \n " ,
row [ "id" ] , row [ "name" ] , row [ "cats" ]
end
PHP
PDO has two connection-object methods to execute SQL statements: exec() for state‐
ments that do not return a result set and query() for those that do. If you have PDO
exceptions enabled, both methods raise an exception if statement execution fails. (An‐
other approach couples the prepare() and execute() methods; see Recipe 2.5 .)
To execute statements such as INSERT or UPDATE that don't return rows, use exec() . It
returns a count to indicate how many rows were changed:
$count = $dbh -> exec ( "UPDATE profile SET cats = cats+1 WHERE name = 'Sybil'" );
printf ( "Number of rows updated: %d \n " , $count );
For statements such as SELECT that return a result set, the query() method returns a
statement handle. Generally, you use this object to call a row-fetching method in a loop,
and count the rows if you need to know how many there are:
$sth = $dbh -> query ( "SELECT id, name, cats FROM profile" );
$count = 0 ;
while ( $row = $sth -> fetch ( PDO :: FETCH_NUM ))
{
printf ( "id: %s, name: %s, cats: %s \n " , $row [ 0 ], $row [ 1 ], $row [ 2 ]);
$count ++ ;
}
printf ( "Number of rows returned: %d \n " , $count );
To determine the number of columns in the result set, call the statement handle colum
nCount() method.