ILIAS  release_5-1 Revision 5.0.0-5477-g43f3e3fab5f
Extended.php
Go to the documentation of this file.
1<?php
2// +----------------------------------------------------------------------+
3// | PHP versions 4 and 5 |
4// +----------------------------------------------------------------------+
5// | Copyright (c) 1998-2006 Manuel Lemos, Tomas V.V.Cox, |
6// | Stig. S. Bakken, Lukas Smith |
7// | All rights reserved. |
8// +----------------------------------------------------------------------+
9// | MDB2 is a merge of PEAR DB and Metabases that provides a unified DB |
10// | API as well as database abstraction for PHP applications. |
11// | This LICENSE is in the BSD license style. |
12// | |
13// | Redistribution and use in source and binary forms, with or without |
14// | modification, are permitted provided that the following conditions |
15// | are met: |
16// | |
17// | Redistributions of source code must retain the above copyright |
18// | notice, this list of conditions and the following disclaimer. |
19// | |
20// | Redistributions in binary form must reproduce the above copyright |
21// | notice, this list of conditions and the following disclaimer in the |
22// | documentation and/or other materials provided with the distribution. |
23// | |
24// | Neither the name of Manuel Lemos, Tomas V.V.Cox, Stig. S. Bakken, |
25// | Lukas Smith nor the names of his contributors may be used to endorse |
26// | or promote products derived from this software without specific prior|
27// | written permission. |
28// | |
29// | THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS |
30// | "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT |
31// | LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS |
32// | FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE |
33// | REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, |
34// | INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, |
35// | BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS|
36// | OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED |
37// | AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT |
38// | LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY|
39// | WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE |
40// | POSSIBILITY OF SUCH DAMAGE. |
41// +----------------------------------------------------------------------+
42// | Author: Lukas Smith <smith@pooteeweet.org> |
43// +----------------------------------------------------------------------+
44//
45// $Id: Extended.php,v 1.58 2007/01/06 21:40:52 quipo Exp $
46
56define('MDB2_AUTOQUERY_INSERT', 1);
57define('MDB2_AUTOQUERY_UPDATE', 2);
58define('MDB2_AUTOQUERY_DELETE', 3);
59define('MDB2_AUTOQUERY_SELECT', 4);
60
69{
70 // {{{ autoPrepare()
71
92 function autoPrepare($table, $table_fields, $mode = MDB2_AUTOQUERY_INSERT,
93 $where = false, $types = null, $result_types = MDB2_PREPARE_MANIP)
94 {
95 $query = $this->buildManipSQL($table, $table_fields, $mode, $where);
96 if (PEAR::isError($query)) {
97 return $query;
98 }
99 $db =& $this->getDBInstance();
100 if (PEAR::isError($db)) {
101 return $db;
102 }
103 return $db->prepare($query, $types, $result_types);
104 }
105 // }}}
106
107 // {{{ autoExecute()
108
131 function &autoExecute($table, $fields_values, $mode = MDB2_AUTOQUERY_INSERT,
132 $where = false, $types = null, $result_class = true, $result_types = MDB2_PREPARE_MANIP)
133 {
134 $fields_values = (array)$fields_values;
135 if ($mode == MDB2_AUTOQUERY_SELECT) {
136 if (is_array($result_types)) {
137 $keys = array_keys($result_types);
138 } elseif (!empty($fields_values)) {
139 $keys = $fields_values;
140 } else {
141 $keys = array();
142 }
143 } else {
144 $keys = array_keys($fields_values);
145 }
146 $params = array_values($fields_values);
147 if (empty($params)) {
148 $query = $this->buildManipSQL($table, $keys, $mode, $where);
149
150 $db =& $this->getDBInstance();
151 if (PEAR::isError($db)) {
152 return $db;
153 }
154 if ($mode == MDB2_AUTOQUERY_SELECT) {
155 $result =& $db->query($query, $result_types, $result_class);
156 } else {
157 $result = $db->exec($query);
158 }
159 } else {
160 $stmt = $this->autoPrepare($table, $keys, $mode, $where, $types, $result_types);
161 if (PEAR::isError($stmt)) {
162 return $stmt;
163 }
164 $result =& $stmt->execute($params, $result_class);
165 $stmt->free();
166 }
167 return $result;
168 }
169 // }}}
170
171 // {{{ buildManipSQL()
172
194 function buildManipSQL($table, $table_fields, $mode, $where = false)
195 {
196 $db =& $this->getDBInstance();
197 if (PEAR::isError($db)) {
198 return $db;
199 }
200
201 if ($db->options['quote_identifier']) {
202 $table = $db->quoteIdentifier($table);
203 }
204
205 if (!empty($table_fields) && $db->options['quote_identifier']) {
206 foreach ($table_fields as $key => $field) {
207 $table_fields[$key] = $db->quoteIdentifier($field);
208 }
209 }
210
211 if ($where !== false && !is_null($where)) {
212 if (is_array($where)) {
213 $where = implode(' AND ', $where);
214 }
215 $where = ' WHERE '.$where;
216 }
217
218 switch ($mode) {
220 if (empty($table_fields)) {
221 return $db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null,
222 'Insert requires table fields', __FUNCTION__);
223 }
224 $cols = implode(', ', $table_fields);
225 $values = '?'.str_repeat(', ?', (count($table_fields) - 1));
226 return 'INSERT INTO '.$table.' ('.$cols.') VALUES ('.$values.')';
227 break;
229 if (empty($table_fields)) {
230 return $db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null,
231 'Update requires table fields', __FUNCTION__);
232 }
233 $set = implode(' = ?, ', $table_fields).' = ?';
234 $sql = 'UPDATE '.$table.' SET '.$set.$where;
235 return $sql;
236 break;
238 $sql = 'DELETE FROM '.$table.$where;
239 return $sql;
240 break;
242 $cols = !empty($table_fields) ? implode(', ', $table_fields) : '*';
243 $sql = 'SELECT '.$cols.' FROM '.$table.$where;
244 return $sql;
245 break;
246 }
247 return $db->raiseError(MDB2_ERROR_SYNTAX, null, null,
248 'Non existant mode', __FUNCTION__);
249 }
250 // }}}
251
252 // {{{ limitQuery()
253
267 function &limitQuery($query, $types, $limit, $offset = 0, $result_class = true,
268 $result_wrap_class = false)
269 {
270 $db =& $this->getDBInstance();
271 if (PEAR::isError($db)) {
272 return $db;
273 }
274
275 $result = $db->setLimit($limit, $offset);
276 if (PEAR::isError($result)) {
277 return $result;
278 }
279 $result =& $db->query($query, $types, $result_class, $result_wrap_class);
280 return $result;
281 }
282 // }}}
283
284 // {{{ execParam()
285
297 function execParam($query, $params = array(), $param_types = null)
298 {
299 $db =& $this->getDBInstance();
300 if (PEAR::isError($db)) {
301 return $db;
302 }
303
304 settype($params, 'array');
305 if (empty($params)) {
306 return $db->exec($query);
307 }
308
309 $stmt = $db->prepare($query, $param_types, MDB2_PREPARE_MANIP);
310 if (PEAR::isError($stmt)) {
311 return $stmt;
312 }
313
314 $result = $stmt->execute($params);
315 if (PEAR::isError($result)) {
316 return $result;
317 }
318
319 $stmt->free();
320 return $result;
321 }
322 // }}}
323
324 // {{{ getOne()
325
340 function getOne($query, $type = null, $params = array(),
341 $param_types = null, $colnum = 0)
342 {
343 $db =& $this->getDBInstance();
344 if (PEAR::isError($db)) {
345 return $db;
346 }
347
348 settype($params, 'array');
349 settype($type, 'array');
350 if (empty($params)) {
351 return $db->queryOne($query, $type, $colnum);
352 }
353
354 $stmt = $db->prepare($query, $param_types, $type);
355 if (PEAR::isError($stmt)) {
356 return $stmt;
357 }
358
359 $result = $stmt->execute($params);
361 return $result;
362 }
363
364 $one = $result->fetchOne($colnum);
365 $stmt->free();
366 $result->free();
367 return $one;
368 }
369 // }}}
370
371 // {{{ getRow()
372
387 function getRow($query, $types = null, $params = array(),
388 $param_types = null, $fetchmode = MDB2_FETCHMODE_DEFAULT)
389 {
390 $db =& $this->getDBInstance();
391 if (PEAR::isError($db)) {
392 return $db;
393 }
394
395 settype($params, 'array');
396 if (empty($params)) {
397 return $db->queryRow($query, $types, $fetchmode);
398 }
399
400 $stmt = $db->prepare($query, $param_types, $types);
401 if (PEAR::isError($stmt)) {
402 return $stmt;
403 }
404
405 $result = $stmt->execute($params);
407 return $result;
408 }
409
410 $row = $result->fetchRow($fetchmode);
411 $stmt->free();
412 $result->free();
413 return $row;
414 }
415 // }}}
416
417 // {{{ getCol()
418
433 function getCol($query, $type = null, $params = array(),
434 $param_types = null, $colnum = 0)
435 {
436 $db =& $this->getDBInstance();
437 if (PEAR::isError($db)) {
438 return $db;
439 }
440
441 settype($params, 'array');
442 settype($type, 'array');
443 if (empty($params)) {
444 return $db->queryCol($query, $type, $colnum);
445 }
446
447 $stmt = $db->prepare($query, $param_types, $type);
448 if (PEAR::isError($stmt)) {
449 return $stmt;
450 }
451
452 $result = $stmt->execute($params);
454 return $result;
455 }
456
457 $col = $result->fetchCol($colnum);
458 $stmt->free();
459 $result->free();
460 return $col;
461 }
462 // }}}
463
464 // {{{ getAll()
465
488 function getAll($query, $types = null, $params = array(),
489 $param_types = null, $fetchmode = MDB2_FETCHMODE_DEFAULT,
490 $rekey = false, $force_array = false, $group = false)
491 {
492 $db =& $this->getDBInstance();
493 if (PEAR::isError($db)) {
494 return $db;
495 }
496
497 settype($params, 'array');
498 if (empty($params)) {
499 return $db->queryAll($query, $types, $fetchmode, $rekey, $force_array, $group);
500 }
501
502 $stmt = $db->prepare($query, $param_types, $types);
503 if (PEAR::isError($stmt)) {
504 return $stmt;
505 }
506
507 $result = $stmt->execute($params);
509 return $result;
510 }
511
512 $all = $result->fetchAll($fetchmode, $rekey, $force_array, $group);
513 $stmt->free();
514 $result->free();
515 return $all;
516 }
517 // }}}
518
519 // {{{ getAssoc()
520
594 function getAssoc($query, $types = null, $params = array(), $param_types = null,
595 $fetchmode = MDB2_FETCHMODE_DEFAULT, $force_array = false, $group = false)
596 {
597 $db =& $this->getDBInstance();
598 if (PEAR::isError($db)) {
599 return $db;
600 }
601
602 settype($params, 'array');
603 if (empty($params)) {
604 return $db->queryAll($query, $types, $fetchmode, true, $force_array, $group);
605 }
606
607 $stmt = $db->prepare($query, $param_types, $types);
608 if (PEAR::isError($stmt)) {
609 return $stmt;
610 }
611
612 $result = $stmt->execute($params);
614 return $result;
615 }
616
617 $all = $result->fetchAll($fetchmode, true, $force_array, $group);
618 $stmt->free();
619 $result->free();
620 return $all;
621 }
622 // }}}
623
624 // {{{ executeMultiple()
625
641 function executeMultiple(&$stmt, $params = null)
642 {
643 for ($i = 0, $j = count($params); $i < $j; $i++) {
644 $result = $stmt->execute($params[$i]);
645 if (PEAR::isError($result)) {
646 return $result;
647 }
648 }
649 return MDB2_OK;
650 }
651 // }}}
652
653 // {{{ getBeforeID()
654
667 function getBeforeID($table, $field = null, $ondemand = true, $quote = true)
668 {
669 $db =& $this->getDBInstance();
670 if (PEAR::isError($db)) {
671 return $db;
672 }
673
674 if ($db->supports('auto_increment') !== true) {
675 $seq = $table.(empty($field) ? '' : '_'.$field);
676 $id = $db->nextID($seq, $ondemand);
677 if (!$quote || PEAR::isError($id)) {
678 return $id;
679 }
680 return $db->quote($id, 'integer');
681 } elseif (!$quote) {
682 return null;
683 }
684 return 'NULL';
685 }
686 // }}}
687
688 // {{{ getAfterID()
689
700 function getAfterID($id, $table, $field = null)
701 {
702 $db =& $this->getDBInstance();
703 if (PEAR::isError($db)) {
704 return $db;
705 }
706
707 if ($db->supports('auto_increment') !== true) {
708 return $id;
709 }
710 return $db->lastInsertID($table, $field);
711 }
712 // }}}
713}
714?>
$result
const MDB2_AUTOQUERY_UPDATE
Definition: Extended.php:57
const MDB2_AUTOQUERY_SELECT
Definition: Extended.php:59
const MDB2_AUTOQUERY_INSERT
Used by autoPrepare()
Definition: Extended.php:56
const MDB2_AUTOQUERY_DELETE
Definition: Extended.php:58
const MDB2_FETCHMODE_DEFAULT
This is a special constant that tells MDB2 the user hasn't specified any particular get mode,...
Definition: MDB2.php:119
const MDB2_OK
The method mapErrorCode in each MDB2_dbtype implementation maps native error codes to one of these.
Definition: MDB2.php:67
const MDB2_ERROR_SYNTAX
Definition: MDB2.php:69
const MDB2_PREPARE_MANIP
These are just helper constants to more verbosely express parameters to prepare()
Definition: MDB2.php:109
const MDB2_ERROR_NEED_MORE_DATA
Definition: MDB2.php:87
execParam($query, $params=array(), $param_types=null)
Execute a parameterized DML statement.
Definition: Extended.php:297
getBeforeID($table, $field=null, $ondemand=true, $quote=true)
Returns the next free id of a sequence if the RDBMS does not support auto increment.
Definition: Extended.php:667
getAssoc($query, $types=null, $params=array(), $param_types=null, $fetchmode=MDB2_FETCHMODE_DEFAULT, $force_array=false, $group=false)
Fetch the entire result set of a query and return it as an associative array using the first column a...
Definition: Extended.php:594
getOne($query, $type=null, $params=array(), $param_types=null, $colnum=0)
Fetch the first column of the first row of data returned from a query.
Definition: Extended.php:340
& autoExecute($table, $fields_values, $mode=MDB2_AUTOQUERY_INSERT, $where=false, $types=null, $result_class=true, $result_types=MDB2_PREPARE_MANIP)
Generate an insert, update or delete query and call prepare() and execute() on it.
Definition: Extended.php:131
executeMultiple(&$stmt, $params=null)
This function does several execute() calls on the same statement handle.
Definition: Extended.php:641
& limitQuery($query, $types, $limit, $offset=0, $result_class=true, $result_wrap_class=false)
Generates a limited query.
Definition: Extended.php:267
buildManipSQL($table, $table_fields, $mode, $where=false)
Make automaticaly an sql query for prepare()
Definition: Extended.php:194
getRow($query, $types=null, $params=array(), $param_types=null, $fetchmode=MDB2_FETCHMODE_DEFAULT)
Fetch the first row of data returned from a query.
Definition: Extended.php:387
autoPrepare($table, $table_fields, $mode=MDB2_AUTOQUERY_INSERT, $where=false, $types=null, $result_types=MDB2_PREPARE_MANIP)
Generate an insert, update or delete query and call prepare() on it.
Definition: Extended.php:92
getAfterID($id, $table, $field=null)
Returns the autoincrement ID if supported or $id.
Definition: Extended.php:700
getCol($query, $type=null, $params=array(), $param_types=null, $colnum=0)
Fetch a single column from a result set and return it as an indexed array.
Definition: Extended.php:433
getAll($query, $types=null, $params=array(), $param_types=null, $fetchmode=MDB2_FETCHMODE_DEFAULT, $rekey=false, $force_array=false, $group=false)
Fetch all the rows returned from a query.
Definition: Extended.php:488
& getDBInstance()
Get the instance of MDB2 associated with the module instance.
Definition: MDB2.php:4206
isResultCommon($value)
Tell whether a value is a MDB2 result implementing the common interface.
Definition: MDB2.php:655
isError($data, $code=null)
Tell whether a value is a PEAR error.
Definition: PEAR.php:279
$params
Definition: example_049.php:96