3 * Base class for data access objects
5 class DataAccessObject {
8 * $da stores data access object
15 * @param $da instance of the DataAccess class
17 function DataAccessObject( & $da ) {
18 $this->table_name = 'CLASSNAME_MUST_BE_DEFINE_FOR_EACH_CLASS';
19 //Dynamic table_name guessing does not work (at least in php4)
20 //because classname are lowercase only :(
22 $s = get_class($this);
23 $this->table_name = '';
25 for($i = 1 ; $i <= $len ; ++$i) {
26 if ($i < $len && preg_match('`[A-Z]`', $s[$i - 1]) && preg_match('`[a-z]`', $s[$i])) {
27 $this->table_name .= '_';
29 $this->table_name .= strtolower($s[$i - 1]);
38 * @param $sql the query string
39 * @return mixed either false if error or object DataAccessResult
41 function &retrieve($sql,$params=array()) {
42 $result =& $this->da->fetch($sql,$params);
43 if ($error = $result->isError()) {
44 $trace = debug_backtrace();
45 $i = isset($trace[1]) ? 1 : 0;
46 trigger_error($error .' ==> '. $sql ." @@ ". $trace[$i]['file'] .' at line '. $trace[$i]['line']);
54 * For INSERT, UPDATE and DELETE queries
55 * @param $sql the query string
56 * @return boolean true if success
58 function update($sql,$params=array()) {
59 $result = $this->da->fetch($sql,$params);
60 if ($error = $result->isError()) {
61 $trace = debug_backtrace();
62 $i = isset($trace[1]) ? 1 : 0;
63 trigger_error($error .' ==> '. $sql ." @@ ". $trace[$i]['file'] .' at line '. $trace[$i]['line']);
71 * Prepare ranking of items.
73 * @see https://partners.xrce.xerox.com/plugins/docman/?group_id=120&action=show&id=95
75 * @param int $id The id of the item to rank. 0 if the item doesn't exist.
76 * @param int $parent_id The id of the element used to group items
77 * @param mixed $rank The rank asked for the items. Possible values are :
78 * '--' => do not change the rank
79 * 'beginning' => to put item before each others
80 * 'end' => to put item after each others
81 * 'up' => to put item before previous sibling
82 * 'down' => to put item after next sibling
83 * <int> => to put item at a specific position.
84 * Please note that for a new item ($id = 0) you must not use
85 * '--', 'up' or 'down' value
86 * @param string $primary_key the column name of the primary key. Default 'id'
87 * @param string $parent_key the column key used to groups items. Default 'parent_id'
88 * @param string $rank_key the column key used to rank items. Default 'rank'
89 * @return mixed false if there is no rank to update of the numerical
90 * value of the new rank of the item. If return 'null' it means
91 * that sth wrong happended.
93 function prepareRanking($id, $parent_id, $rank, $primary_key = 'id', $parent_key = 'parent_id', $rank_key = 'rank') {
96 // First, check if there is already some items
97 $sql = sprintf('SELECT NULL'.
98 ' FROM '. $this->table_name .
99 ' WHERE '. $parent_key .' = %d',
101 $dar = $this->retrieve($sql);
102 if($dar && !$dar->isError() && $dar->rowCount() == 0) {
103 // No items: nice, just set the first one to 0.
107 switch((string)$rank) {
109 $sql = sprintf('SELECT '. $rank_key .
110 ' FROM '. $this->table_name .
111 ' WHERE '. $primary_key .' = %d',
113 $dar = $this->retrieve($sql);
114 if($dar && !$dar->isError() && $dar->rowCount() == 1) {
115 $row = $dar->current();
116 $newRank = $row[$rank_key];
120 // Simple case: just pickup the most high rank in the table
121 // and add 1 to be laster than the first.
122 $sql = sprintf('SELECT MAX('. $rank_key .')+1 as '. $rank_key .
123 ' FROM '. $this->table_name .
124 ' WHERE '. $parent_key .' = %d',
126 $dar = $this->retrieve($sql);
127 if($dar && !$dar->isError() && $dar->rowCount() == 1) {
128 $row = $dar->current();
129 $newRank = $row[$rank_key];
135 // Those 2 cases are quite complex and are only mandatory if
136 // you want to 'Move up' or 'Move down' an item. If you can
137 // only select in a select box you can remove this part of
140 // The general idea here is: we want to move up (or down) an
141 // item but we only know it's id and the sens (up/down) of the
142 // slide. Our goal is to exchange the rank value of the item
143 // behind (in case of up) with the current one.
145 // This is done in 2 steps:
146 // * first fetch the item_id and the rank of the item we want
147 // to stole the place.
148 // * then exchange the 2 rank values.
150 if ($rank == 'down') {
158 // This SQL query aims to get the item_id and the rank of the item
159 // Just behind us (for 'up' case).
160 // In your implementation, USING(parent_id) should refer to the field
161 // that group all the items in one list.
162 $sql = sprintf('SELECT i1.'. $primary_key .' as id, i1.'. $rank_key .' as '. $rank_key .
163 ' FROM '. $this->table_name .' i1'.
164 ' INNER JOIN '. $this->table_name .' i2 USING('. $parent_key .')'.
165 ' WHERE i2.'. $primary_key .' = %d'.
166 ' AND i1.'. $parent_key .' = %d'.
167 ' AND i1.'. $rank_key .' %s i2.'. $rank_key .
168 ' ORDER BY i1.'. $rank_key .' %s'.
174 $dar = $this->retrieve($sql);
175 if ($dar && !$dar->isError() && $dar->rowCount() == 1) {
176 $row = $dar->current();
177 // This query exchange the two values.
178 // Warning: the order is very important, please check that
179 // your final query work as expected.
180 $sql = sprintf('UPDATE '. $this->table_name .' i1, '. $this->table_name .' i2'.
181 ' SET i1.'. $rank_key .' = i2.'. $rank_key .', i2.'. $rank_key .' = %d'.
182 ' WHERE i1.'. $primary_key .' = %d '.
183 ' AND i2.'. $primary_key .' = %d',
193 // This first part is quite simple: just pickup the lower rank
195 $sql = sprintf('SELECT MIN('. $rank_key .') as '. $rank_key .
196 ' FROM '. $this->table_name .
197 ' WHERE '. $parent_key .' = %d',
199 $dar = $this->retrieve($sql);
200 if($dar && !$dar->isError()) {
201 $row = $dar->current();
202 $rank = $row[$rank_key];
204 // Very important: no break here, because we have to update all
209 // Here $rank is a numerical value that represent the rank after
210 // one item (user selected 'After XXX' in select box).
211 // The idea is to move up all the ranks upper to this value and to
212 // return the current value as the new rank.
213 $sql = sprintf('UPDATE '. $this->table_name .
214 ' SET '. $rank_key .' = '. $rank_key .' + 1'.
215 ' WHERE '. $parent_key .' = %d'.
216 ' AND '. $rank_key .' >= %d',
218 $updated = $this->update($sql);
228 * Return the result of 'FOUND_ROWS()' SQL method for the last query.
230 function foundRows() {
231 $sql = "SELECT FOUND_ROWS() as nb";
232 $dar = $this->retrieve($sql);
233 if($dar && !$dar->isError()) {
234 $row = $dar->getRow();