The command execute() provides complete control over the reading and writing of the data in the database.
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:
Command | Description |
---|---|
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
Command | Description |
---|---|
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 |
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 */
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,
$sql = "select * from employees"; /* * Retrieve 10 records, starting at offset 200 */ $result = $db->selectLimit($sql,10,200);
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.