Differences

This shows you the differences between two versions of the page.

--- v5:userguide:learn_abstraction:using_execute [2016/03/15 01:29] – created mnewnham
+++ v5:userguide:learn_abstraction:using_execute [2020/01/02 12:03] (current) – ↷ Links adapted because of a move operation dregad
@@ Line 1: / Line 1: @@
-<- v5:userguide:learn_abstraction:key_value_pairs|Key/Value Pairs ^ v5:userguide:learn_abstraction:start_lesson|Start Of Lesson ^ v5:userguide:learn_abstraction:record_fields|Record Fields ->
+<- 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 ====
+===== Reading Data =====
-When used to read the data, the executed command returns a **Query Result**, which can be accessed an used to read the records individually.
+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";
@@ Line 27: / Line 27: @@
               'hire_date' => '2014-01-12'
               )
-     * etc, until then end of file
+     * 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:
@@ Line 42: / Line 43: @@
 ^Command^Description|
 | [[v5:reference:recordset:move|move()]]                       | Move to the n<sup>th</sup> record of a recordset                                |
-| [[v5:reference:connection:movenext|moveNext()]]               | Moves the cursor to the next record of the recordset from the current position  |
+| [[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:connection:movelast|moveLast()]]               | Moves to the last record of a recordset                                         |
+| [[v5:reference:recordset:movelast|moveLast()]]               | Moves to the last record of a recordset                                         |
-==== Writing Data ====
+===== 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.
@@ Line 63: / Line 64: @@
 </code>
-==== See Also ====
+===== Limiting The Number Of Returned Rows =====
-[[v5:reference:connection:selectlimit|selectLimit]], which allows us to limit the number of rows shown.
+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.