ADOdb

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
<- v5:userguide:learn_abstraction:key_value_pairs|Key/Value Pairs ^ v5:userguide:learn_abstraction:start_lesson|Start Of Lesson ^ v5:userguide:learn_abstraction:record_insertion|Inserting And Updating Records->
~~NOTOC~~

====== Using The Execute Method ======
The command [[v5:reference:connection:execute|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.
<code php>
$sql = "select * from employees";
$result = $db->execute($sql);
/*
* $result is our query result
*/
</code>
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 [[v5:reference:recordset:fetchrow|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.

<code php>
$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 
     */
} 
</code>
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|
| [[v5:reference:connection:fetchinto|fetchInto()]]         | Fetches  a recordset into an array                                                    |
| [[v5:reference:recordset:fetchobj|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|
| [[v5:reference:recordset:move|move()]]                       | Move to the n<sup>th</sup> record of a recordset                                |
| [[v5:reference:recordset:movenext|moveNext()]]               | Moves the cursor to the next record of the recordset from the current position  |
| [[v5:reference:recordset:movefirst|moveFirst()]]             | Moves the cursor to the first record of the recordset                           |
| [[v5:reference:recordset:movelast|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 [[v5:reference:connection:affected_rows|affected_rows()]] method.

<code php>
$sql = "UPDATE employees
           SET emp_name='Zaphod Beeblebrox'
         WHERE emp_no=10000";
         
$ok = $db->execute($sql);

print $db->affected_rows();

/*
* Returns: 1
*/
</code>

===== 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 [[v5:reference:connection:selectlimit|selectLimit()]] is available, which provides this functionality.

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

selectLimit takes 2 parameters,
  - The number of records to return
  - Optionally, the starting offset. This value is 1 based, i.e. the first record in the table is numbered 1.

<code php>
$sql = "select * from employees";
/*
* Retrieve 10 records, starting at offset 200
*/
$result = $db->selectLimit($sql,10,200);
</code>

===== 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**

<code 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');
</code>

The [[v5:reference:connection:rsfilter|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.