Using The Execute Method

The command execute() provides complete control over the reading and writing of the data in the database.

Reading Data

When used to read the data, the executed command returns a Query Result (or RecordSet), which can be accessed and used to read the records individually.

$sql = "select * from employees";
$result = $db->execute($sql);
/*
* $result is our query result
*/

Once the result set is available, there are numerous ways to retrieve the records in the result. One way to retrieve the data is to use the fetchRow() method, which reads the current record into an array, and advances the recordset pointer. This means that the loop will end once end-of-file has been reached.

$sql = "select * from employees";
$result = $db->execute($sql);
while ($r = $result->fetchRow())
{
     print_r($r);
     /*
     * prints:
     * array('emp_no' => 1000,
              'emp_name' => 'Joe Smith',
              'hire_date' => '2014-01-12'
              )
     * etc, until the end of file 
     */
} 

Once the end of file has been reached, a flag, $result→EOF is set, and can be directly queried.

Other ways of reading the recordset are:

CommandDescription
fetchInto() Fetches a recordset into an array
fetchObj() Returns the current row as an object for convenience

If the DBMS supports the functionality, the recordset can be paged and scrolled with the Move methods

CommandDescription
move() Move to the nth record of a recordset
moveNext() Moves the cursor to the next record of the recordset from the current position
moveFirst() Moves the cursor to the first record of the recordset
moveLast() Moves to the last record of a recordset

Writing Data

When used to INSERT/UPDATE/DELETE data, a simple boolean is returned that indicates the success or failure of the operation. In addition, some databases will return a value which indicates the number of records changed by the operation. This value can be retrieved with the affected_rows() method.

$sql = "UPDATE employees
           SET emp_name='Zaphod Beeblebrox'
         WHERE emp_no=10000";
 
$ok = $db->execute($sql);
 
print $db->affected_rows();
 
/*
* Returns: 1
*/

Limiting The Number Of Returned Rows

For large result sets, it is often necessary to limit the number of rows returned to a set number. This might be used, for example in paginated record sets. In order to do this, the ADOdb method selectLimit() is available, which provides this functionality.

When reading records, the execute() and selectLimit() methods can be used interchangeably.

selectLimit takes 2 parameters,

  1. The number of records to return
  2. Optionally, the starting offset. This value is 1 based, i.e. the first record in the table is numbered 1.
$sql = "select * from employees";
/*
* Retrieve 10 records, starting at offset 200
*/
$result = $db->selectLimit($sql,10,200);

Creating A Recordset Filter

A recordset filter pre-processes all the rows in a recordset after retrieval but before we use it. For example, we want to apply the PHP function ucwords to all the values in the recordset.

In order to apply the filter, we must include the extra file rsfilter.inc.php

include_once 'adodb/rsfilter.inc.php';
include_once 'adodb/adodb.inc.php';
 
/*
* ucwords() every element in the recordset
*/
function doUcwords(&$arr,$rs)
{
     foreach($arr as $k => $v) {
          $arr[$k] = ucwords($v);
     }
}
 
$db = newADOConnection('mysql');
$db->pConnect('server','user','pwd','db');
 
$rs = $db->Execute('select ... from table');
$rs = rsFilter($rs,'doUcwords');

The rsFilter() method takes 2 parameters, the recordset (passed by reference), and the name of the filter function. It returns the processed recordset scrolled to the first record.