Subversion Repositories Applications.papyrus

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
848 florian 1
<?php
2
// CVS: $Id$
3
//
4
// Pager_Wrapper
5
// -------------
6
//
7
// Ready-to-use wrappers for paging the result of a query,
8
// when fetching the whole resultset is NOT an option.
9
// This is a performance- and memory-savvy method
10
// to use PEAR::Pager with a database.
11
// With this approach, the network load can be
12
// consistently smaller than with PEAR::DB_Pager.
13
//
14
// The following wrappers are provided: one for each PEAR
15
// db abstraction layer (DB, MDB and MDB2), one for
16
// PEAR::DB_DataObject, and one for the PHP Eclipse library
17
//
18
//
19
// SAMPLE USAGE
20
// ------------
21
//
22
// $query = 'SELECT this, that FROM mytable';
23
// require_once 'Pager_Wrapper.php'; //this file
24
// $pagerOptions = array(
25
//     'mode'    => 'Sliding',
26
//     'delta'   => 2,
27
//     'perPage' => 15,
28
// );
29
// $paged_data = Pager_Wrapper_MDB2($db, $query, $pagerOptions);
30
// //$paged_data['data'];  //paged data
31
// //$paged_data['links']; //xhtml links for page navigation
32
// //$paged_data['page_numbers']; //array('current', 'total');
33
//
34
 
35
/**
36
 * Helper method - Rewrite the query into a "SELECT COUNT(*)" query.
37
 * @param string $sql query
38
 * @return string rewritten query OR false if the query can't be rewritten
39
 * @access private
40
 */
41
function rewriteCountQuery($sql)
42
{
43
    if (preg_match('/^\s*SELECT\s+\bDISTINCT\b/is', $sql) || preg_match('/\s+GROUP\s+BY\s+/is', $sql)) {
44
        return false;
45
    }
46
    $open_parenthesis = '(?:\()';
47
    $close_parenthesis = '(?:\))';
48
    $subquery_in_select = $open_parenthesis.'.*\bFROM\b.*'.$close_parenthesis;
49
    $pattern = '/(?:.*'.$subquery_in_select.'.*)\bFROM\b\s+/Uims';
50
    if (preg_match($pattern, $sql)) {
51
        return false;
52
    }
53
    $subquery_with_limit_order = $open_parenthesis.'.*\b(LIMIT|ORDER)\b.*'.$close_parenthesis;
54
    $pattern = '/.*\bFROM\b.*(?:.*'.$subquery_with_limit_order.'.*).*/Uims';
55
    if (preg_match($pattern, $sql)) {
56
        return false;
57
    }
58
    $queryCount = preg_replace('/(?:.*)\bFROM\b\s+/Uims', 'SELECT COUNT(*) FROM ', $sql, 1);
59
    list($queryCount, ) = preg_split('/\s+ORDER\s+BY\s+/is', $queryCount);
60
    list($queryCount, ) = preg_split('/\bLIMIT\b/is', $queryCount);
61
    return trim($queryCount);
62
}
63
 
64
/**
65
 * @param object PEAR::DB instance
66
 * @param string db query
67
 * @param array  PEAR::Pager options
68
 * @param boolean Disable pagination (get all results)
69
 * @param integer fetch mode constant
70
 * @param mixed  parameters for query placeholders
71
 *        If you use placeholders for table names or column names, please
72
 *        count the # of items returned by the query and pass it as an option:
73
 *        $pager_options['totalItems'] = count_records('some query');
74
 * @return array with links and paged data
75
 */
76
function Pager_Wrapper_DB(&$db, $query, $pager_options = array(), $disabled = false, $fetchMode = DB_FETCHMODE_ASSOC, $dbparams = null)
77
{
78
   if (!array_key_exists('totalItems', $pager_options)) {
79
        //  be smart and try to guess the total number of records
80
        if ($countQuery = rewriteCountQuery($query)) {
81
            $totalItems = $db->getOne($countQuery, $dbparams);
82
            if (PEAR::isError($totalItems)) {
83
                return $totalItems;
84
            }
85
        } else {
86
            $res =& $db->query($query, $dbparams);
87
            if (PEAR::isError($res)) {
88
                return $res;
89
            }
90
            $totalItems = (int)$res->numRows();
91
            $res->free();
92
        }
93
        $pager_options['totalItems'] = $totalItems;
94
    }
95
    require_once 'Pager/Pager.php';
96
    $pager = Pager::factory($pager_options);
97
 
98
    $page = array();
99
    $page['totalItems'] = $pager_options['totalItems'];
100
    $page['links'] = $pager->links;
101
    $page['page_numbers'] = array(
102
        'current' => $pager->getCurrentPageID(),
103
        'total'   => $pager->numPages()
104
    );
105
    list($page['from'], $page['to']) = $pager->getOffsetByPageId();
106
 
107
    $res = ($disabled)
108
        ? $db->limitQuery($query, 0, $totalItems, $dbparams)
109
        : $db->limitQuery($query, $page['from']-1, $pager_options['perPage'], $dbparams);
110
 
111
    if (PEAR::isError($res)) {
112
        return $res;
113
    }
114
    $page['data'] = array();
115
    while ($res->fetchInto($row, $fetchMode)) {
116
       $page['data'][] = $row;
117
    }
118
    if ($disabled) {
119
        $page['links'] = '';
120
        $page['page_numbers'] = array(
121
            'current' => 1,
122
            'total'   => 1
123
        );
124
    }
125
    return $page;
126
}
127
 
128
/**
129
 * @param object PEAR::MDB instance
130
 * @param string db query
131
 * @param array  PEAR::Pager options
132
 * @param boolean Disable pagination (get all results)
133
 * @param integer fetch mode constant
134
 * @return array with links and paged data
135
 */
136
function Pager_Wrapper_MDB(&$db, $query, $pager_options = array(), $disabled = false, $fetchMode = MDB_FETCHMODE_ASSOC)
137
{
138
    if (!array_key_exists('totalItems', $pager_options)) {
139
        //be smart and try to guess the total number of records
140
        if ($countQuery = rewriteCountQuery($query)) {
141
            $totalItems = $db->queryOne($countQuery);
142
            if (PEAR::isError($totalItems)) {
143
                return $totalItems;
144
            }
145
        } else {
146
            $res = $db->query($query);
147
            if (PEAR::isError($res)) {
148
                return $res;
149
            }
150
            $totalItems = (int)$db->numRows($res);
151
            $db->freeResult($res);
152
        }
153
        $pager_options['totalItems'] = $totalItems;
154
    }
155
    require_once 'Pager/Pager.php';
156
    $pager = Pager::factory($pager_options);
157
 
158
    $page = array();
159
    $page['totalItems'] = $pager_options['totalItems'];
160
    $page['links'] = $pager->links;
161
    $page['page_numbers'] = array(
162
        'current' => $pager->getCurrentPageID(),
163
        'total'   => $pager->numPages()
164
    );
165
    list($page['from'], $page['to']) = $pager->getOffsetByPageId();
166
 
167
    $res = ($disabled)
168
        ? $db->limitQuery($query, null, 0, $totalItems)
169
        : $db->limitQuery($query, null, $page['from']-1, $pager_options['perPage']);
170
 
171
    if (PEAR::isError($res)) {
172
        return $res;
173
    }
174
    $page['data'] = array();
175
    while ($row = $db->fetchInto($res, $fetchMode)) {
176
        $page['data'][] = $row;
177
    }
178
    if ($disabled) {
179
        $page['links'] = '';
180
        $page['page_numbers'] = array(
181
            'current' => 1,
182
            'total'   => 1
183
        );
184
    }
185
    return $page;
186
}
187
 
188
/**
189
 * @param object PEAR::MDB2 instance
190
 * @param string db query
191
 * @param array  PEAR::Pager options
192
 * @param boolean Disable pagination (get all results)
193
 * @param integer fetch mode constant
194
 * @return array with links and paged data
195
 */
196
function Pager_Wrapper_MDB2(&$db, $query, $pager_options = array(), $disabled = false, $fetchMode = MDB2_FETCHMODE_ASSOC)
197
{
198
    if (!array_key_exists('totalItems', $pager_options)) {
199
        //be smart and try to guess the total number of records
200
        if ($countQuery = rewriteCountQuery($query)) {
201
            $totalItems = $db->queryOne($countQuery);
202
            if (PEAR::isError($totalItems)) {
203
                return $totalItems;
204
            }
205
        } else {
206
            //GROUP BY => fetch the whole resultset and count the rows returned
207
            $res =& $db->queryCol($query);
208
            if (PEAR::isError($res)) {
209
                return $res;
210
            }
211
            $totalItems = count($res);
212
        }
213
        $pager_options['totalItems'] = $totalItems;
214
    }
215
    require_once 'Pager/Pager.php';
216
    $pager = Pager::factory($pager_options);
217
 
218
    $page = array();
219
    $page['links'] = $pager->links;
220
    $page['totalItems'] = $pager_options['totalItems'];
221
    $page['page_numbers'] = array(
222
        'current' => $pager->getCurrentPageID(),
223
        'total'   => $pager->numPages()
224
    );
225
    list($page['from'], $page['to']) = $pager->getOffsetByPageId();
226
    $page['limit'] = $page['to'] - $page['from'] +1;
227
    if (!$disabled) {
228
        $db->setLimit($pager_options['perPage'], $page['from']-1);
229
    }
230
    $page['data'] = $db->queryAll($query, null, $fetchMode);
231
    if (PEAR::isError($page['data'])) {
232
        return $page['data'];
233
    }
234
    if ($disabled) {
235
        $page['links'] = '';
236
        $page['page_numbers'] = array(
237
            'current' => 1,
238
            'total'   => 1
239
        );
240
    }
241
    return $page;
242
}
243
 
244
/**
245
 * @param object PEAR::DataObject instance
246
 * @param array  PEAR::Pager options
247
 * @param boolean Disable pagination (get all results)
248
 * @return array with links and paged data
249
 * @author Massimiliano Arione <garak@studenti.it>
250
 */
251
function Pager_Wrapper_DBDO(&$db, $pager_options = array(), $disabled = false)
252
{
253
    if (!array_key_exists('totalItems', $pager_options)) {
254
        $totalItems = $db->count();
255
        $pager_options['totalItems'] = $totalItems;
256
    }
257
    require_once 'Pager/Pager.php';
258
    $pager = Pager::factory($pager_options);
259
 
260
    $page = array();
261
    $page['links'] = $pager->links;
262
    $page['totalItems'] = $pager_options['totalItems'];
263
    $page['page_numbers'] = array(
264
        'current' => $pager->getCurrentPageID(),
265
        'total'   => $pager->numPages()
266
    );
267
    list($page['from'], $page['to']) = $pager->getOffsetByPageId();
268
    $page['limit'] = $page['to'] - $page['from'] + 1;
269
    if (!$disabled) {
270
        $db->limit($page['from'] - 1, $pager_options['perPage']);
271
    }
272
    $db->find();
273
    while ($db->fetch()) {
274
        $db->getLinks();
275
        $page['data'][] = $db->toArray('%s', true);
276
    }
277
    return $page;
278
}
279
 
280
/**
281
 * @param object PHP Eclipse instance
282
 * @param string db query
283
 * @param array  PEAR::Pager options
284
 * @param boolean Disable pagination (get all results)
285
 * @return array with links and paged data
286
 * @author Matte Edens <matte@arubanetworks.com>
287
 * @see http://sourceforge.net/projects/eclipselib/
288
 */
289
function Pager_Wrapper_Eclipse(&$db, $query, $pager_options = array(), $disabled = false)
290
{
291
    if (!$disabled) {
292
        require_once(ECLIPSE_ROOT . 'PagedQuery.php');
293
        $query =& new PagedQuery($db->query($query), $pager_options['perPage']);
294
        $totalrows = $query->getRowCount();
295
        $numpages  = $query->getPageCount();
296
        $whichpage = isset($_GET[$pager_options['urlVar']]) ? (int)$_GET[$pager_options['urlVar']] - 1 : 0;
297
        if ($whichpage >= $numpages) {
298
            $whichpage = $numpages - 1;
299
        }
300
        $result = $query->getPage($whichpage);
301
    } else {
302
        $result    = $db->query($query);
303
        $totalrows = $result->getRowCount();
304
        $numpages  = 1;
305
    }
306
    if (!$result->isSuccess()) {
307
        return PEAR::raiseError($result->getErrorMessage());
308
    }
309
    if (!array_key_exists('totalItems', $pager_options)) {
310
        $pager_options['totalItems'] = $totalrows;
311
    }
312
 
313
    $page = array();
314
    require_once(ECLIPSE_ROOT . 'QueryIterator.php');
315
    for ($it =& new QueryIterator($result); $it->isValid(); $it->next()) {
316
        $page['data'][] =& $it->getCurrent();
317
    }
318
    require_once 'Pager/Pager.php';
319
    $pager = Pager::factory($pager_options);
320
 
321
    $page['links']        = $pager->links;
322
    $page['totalItems']   = $pager_options['totalItems'];
323
    $page['page_numbers'] = array(
324
        'current' => $pager->getCurrentPageID(),
325
        'total'   => $numpages
326
    );
327
	$page['perPageSelectBox'] = $pager->getperpageselectbox();
328
    list($page['from'], $page['to']) = $pager->getOffsetByPageId();
329
    $page['limit'] = $page['to'] - $page['from'] +1;
330
    if ($disabled) {
331
        $page['links'] = '';
332
        $page['page_numbers'] = array(
333
            'current' => 1,
334
            'total'   => 1
335
        );
336
    }
337
    return $page;
338
}
339
?>