1: <?php /* vim: set expandtab : */
2: /**
3: * The statement class belonging to the AMysql_Abstract class, where mysql
4: * queries are built and handled.
5: * Most methods here are chainable, and many common AMysql_Abstract methods
6: * return a new instance of this class.
7: * A simple use example:
8: *
9: * $amysql = new AMysql($conn);
10: * try {
11: * $stmt = $amysql->query('SELECT * FROM cms_content');
12: * while ($row = $stmt->fetchAssoc()) {
13: * ...
14: * }
15: * }
16: * catch (AMysql_Exception $e) {
17: * echo $e->getDetails();
18: * }
19: *
20: * Visit https://github.com/amcsi/amysql
21: * @author Szerémi Attila
22: * @license MIT License; http://www.opensource.org/licenses/mit-license.php
23: **/
24: class AMysql_Statement implements IteratorAggregate, Countable
25: {
26: public $amysql;
27: public $error;
28: public $errno;
29: public $result;
30: public $results = array ();
31: public $query;
32: public $affectedRows;
33: public $throwExceptions;
34: public $lastException = null;
35: public $insertId;
36:
37: /**
38: * Whether the time all the queries take should be recorded.
39: *
40: * @deprecated Queries are now always being profiled
41: *
42: * @var boolean
43: */
44: public $profileQueries;
45:
46: public $link;
47: public $isMysqli;
48:
49: protected $_fetchMode;
50: protected $_fetchModeExtraArgs = array ();
51:
52: public $beforeSql = '';
53: public $prepared = '';
54: public $binds = array();
55:
56: // whether this statement has been executed yet.
57: protected $_executed = false;
58:
59: protected $_replacements;
60:
61: /**
62: * The time in took in seconds with microseconds to perform the query.
63: * It is automatically filled only when $profileQueries is set to true.
64: *
65: * @var float
66: */
67: public $queryTime;
68:
69: const CODE_QUERY_NOT_SUCCESSFUL = 120000;
70:
71: /**
72: * __construct
73: *
74: * @param AMysql_Abstract $amysql
75: * @access public
76: */
77: public function __construct(AMysql_Abstract $amysql)
78: {
79: $amysql->lastStatement = $this;
80: $this->amysql = $amysql;
81: $this->isMysqli = $amysql->isMysqli;
82: $this->throwExceptions = $this->amysql->throwExceptions;
83: $this->setFetchMode($amysql->getFetchMode());
84: }
85:
86: /**
87: * Fetches the mysql link. If it is not set, try to take it
88: * from the amysql object. Of still not set, try to connect and
89: * take it.
90: *
91: * @access public
92: * @return resource|Mysqli $link The mysql resource or Mysqli object
93: */
94: public function getLink()
95: {
96: if (!$this->link) {
97: $amysql = $this->amysql;
98: $link = $amysql->link;
99: if (!$link) {
100: $amysql->connect();
101: $link = $amysql->link;
102: }
103: $this->link = $link;
104: }
105: return $this->link;
106: }
107:
108: /**
109: * Checks whether mysqli is being used (as opposed to mysql)
110: *
111: * @access public
112: * @return boolean
113: */
114: public function isMysqli()
115: {
116: if (!isset($this->isMysqli)) {
117: $link = $this->getLink();
118: $this->isMysqli = $link instanceof Mysqli;
119: }
120: return $this->isMysqli;
121: }
122:
123: /**
124: * Fetches the iterator for iterating through the results of the statement
125: * with the set fetch mode (default is assoc).
126: * This method is automatically called due to this class being an
127: * IteratorAggregate, so all you need to do is foreach your $statement
128: * object.
129: *
130: * @access public
131: * @return AMysql_Iterator
132: */
133: public function getIterator()
134: {
135: return new AMysql_Iterator($this);
136: }
137:
138: /**
139: * Sets the fetch mode for fetch() and fetchAll()
140: * Any extra parameters are passed on to the handler for that fetch type.
141: * For example you can pass the class name and parameters for fetchObject
142: * here.
143: *
144: * @param mixed $fetchMode The fetch mode. Use the AMysql_Abstract
145: * constants.
146: * @access public
147: * @return AMysql_Statement (chainable)
148: */
149: public function setFetchMode($fetchMode/* [, extras [, extras...]]*/)
150: {
151: static $fetchModes = array (
152: AMysql_Abstract::FETCH_ASSOC, AMysql_Abstract::FETCH_OBJECT,
153: AMysql_Abstract::FETCH_ARRAY, AMysql_Abstract::FETCH_ROW
154: );
155: $args = func_get_args();
156: $extraArgs = array_slice($args, 1);
157: if (in_array($fetchMode, $fetchModes)) {
158: $this->_fetchMode = $fetchMode;
159: $this->_fetchModeExtraArgs = $extraArgs;
160: } else {
161: throw new Exception("Unknown fetch mode: `$fetchMode`");
162: }
163: return $this;
164: }
165:
166: /**
167: * Executes a prepared statement, optionally accepting binds for replacing
168: * placeholders.
169: *
170: * @param mixed $binds (Optional) The binds for the placeholders. This
171: * library supports names and unnames placeholders.
172: * To use unnamed placeholders, use question marks
173: * (?) as placeholders. A bind's key should be the
174: * index of the question mark. If $binds is not
175: * an array, it is casted to one.
176: * To use named placeholders, the placeholders
177: * must start with a non-alphanumeric, non-128+
178: * character. If the key starts with an
179: * alphanumeric or 128+ character, the placeholder
180: * that is searched to be replaced will be the
181: * key prepended by a colon (:). Here are examples
182: * for what keys will replace what placeholders
183: * for the value:
184: *
185: * :key: => :key:
186: * :key => :key
187: * key => :key
188: * key: => :key:
189: * !key => !key
190: * élet => :élet
191: *
192: * All values are escaped and are automatically
193: * surrounded by apostrophes if needed. Do NOT
194: * add apostrophes around the string values as
195: * encapsulating for a mysql string.
196: * @see AMysql_Abstract::escape()
197: *
198: * @return AMysql_Statement (chainable)
199: */
200: public function execute($binds = array ())
201: {
202: if (1 <= func_num_args()) {
203: $this->binds = is_array($binds) ? $binds : array ($binds);
204: }
205: $sql = $this->getSql();
206: if ($this->amysql->autoPing) {
207: $this->amysql->autoPing();
208: }
209: try {
210: $result = $this->_query($sql);
211: } catch (AMysql_Exception $e) {
212: /**
213: * If the error is "2006 mysql server has gone away" and
214: * autoReconnect is set, try to reconnect and execute the
215: * query again before giving up.
216: */
217: if (AMysql_Exception::CODE_SERVER_GONE_AWAY == $e->getCode()) {
218: $this->link = null; // clear the db link cache.
219:
220: if ($this->amysql->getAutoReconnect()) {
221: try {
222: $this->amysql->pingReconnect();
223: $result = $this->_query($sql);
224: return $this;
225: } catch (AMysql_Exception $e2) {
226: // failed to reconnect
227: }
228: }
229: }
230: $this->handleException($e);
231: }
232: return $this;
233: }
234:
235: /**
236: * The sql string built up by the different preparing methods (prepare,
237: * select, insert etc.) is returned, having the placeholders being
238: * replaced by their binded values. You can debug what the final SQL
239: * string would be by calling this method.
240: *
241: * @param string $prepared (Optional) Use this prepared string
242: * instead of the one set.
243: **/
244: public function getSql($prepared = null)
245: {
246: if (!$prepared) {
247: $prepared = $this->prepared;
248: }
249: return $this->beforeSql . $this->quoteInto($prepared, $this->binds);
250: }
251:
252: /**
253: * Like {@link AMysql_Statement::getSql()}, but you must give the prepared
254: * statement, the binds, and {@link AMysql_Statement::beforeSql} is ignored.
255: *
256: * @see AMysql_Statement::getSql()
257: *
258: * @param string $prepared
259: * @param mixed $binds $binds are automatically type casted
260: * to an array.
261: * @return string
262: */
263: public function quoteInto($prepared, $binds)
264: {
265: $sql = $prepared;
266: if (!is_array($binds)) {
267: $binds = is_array($binds) ? $binds : array ($binds);
268: }
269: if (!$binds) {
270: return $sql;
271: }
272: if (array_key_exists(0, $binds)) {
273: $parts = explode('?', $sql);
274: $sql = '';
275: if (count($parts) - 1 == count($binds)) {
276: foreach ($binds as &$bind) {
277: $sql .= array_shift($parts);
278: $sql .= $this->amysql->escape($bind);
279: };
280: $sql .= array_shift($parts);
281: } elseif (count($parts) - 1 < count($binds)) {
282: $msg = "More binds than question marks!\n";
283: $msg .= "Prepared query: `$prepared`\n";
284: $msg .= sprintf("Binds: %s\n", print_r($binds, true));
285: throw new RuntimeException($msg);
286: } else {
287: $msg = "Fewer binds than question marks!\n";
288: $msg .= "Prepared query: `$prepared`\n";
289: $msg .= sprintf("Binds: %s\n", print_r($binds, true));
290: throw new RuntimeException($msg);
291: }
292: } else {
293: $keysQuoted = array ();
294: $replacements = array ();
295: foreach ($binds as $key => &$bind) {
296: if (127 < ord($key[0]) || preg_match('/^\w$/', $key[0])) {
297: $key = ':' . $key;
298: }
299: $keyQuoted = preg_quote($key, '/');
300: $keysQuoted[] = $keyQuoted;
301: $replacements[$key] = $this->amysql->escape($bind);
302: }
303: $keysOr = join('|', $keysQuoted);
304: # Anything that is one of the keys followed by a non-word ascii
305: # character. This prevents :someKeyWithLongerName from being
306: # treated as :someKey, if both those keys actually existed.
307: $pattern =
308: "/($keysOr)(?![\w\x80-\xff])/m";
309: $this->_replacements = $replacements;
310:
311: $sql = preg_replace_callback(
312: $pattern,
313: array($this, '_replaceCallback'),
314: $sql
315: );
316: }
317: return $sql;
318: }
319:
320: /**
321: * Replaced a named placeholder with its replacement escaped via
322: * {@link AMysql_Abstract::escape()}
323: *
324: * @param array $match
325: * @access protected
326: * @return string The replacement
327: */
328: protected function _replaceCallback($match)
329: {
330: $key = $match[0];
331: $replacement = array_key_exists($key, $this->_replacements) ?
332: $this->_replacements[$key] :
333: $key;
334: return $replacement;
335: }
336:
337: /**
338: * Prepares an SQL string for binding and execution. Use of this
339: * method is not recommended externally. Use the AMysql class's
340: * prepare method instead which returns a new AMysql_Statement instance.
341: *
342: * @param string $sql The SQL string to prepare.
343: * @return AMysql_Statement (chainable)
344: */
345: public function prepare($sql)
346: {
347: $this->beforeSql = '';
348: $this->prepared = $sql;
349: $this->binds = array();
350: return $this;
351: }
352:
353: /**
354: * Prepares a statement and executes it with the given binds.
355: *
356: * @see AMysql_Statement::prepare()
357: * @see AMysql_Statement::execute()
358: *
359: * @param string $sql The SQL string to prepare.
360: * @param array $binds @see $this->execute()
361: * @access public
362: * @return AMysql_Statement (chainable)
363: */
364: public function query($sql, $binds = array ())
365: {
366: $this->prepare($sql);
367: $result = $this->execute($binds);
368: return $this;
369: }
370:
371: /**
372: * Performs the actual query on the database with the given SQL string,
373: * making no further modifications on it.
374: *
375: * @param string $sql The SQL string.
376: * @access protected
377: * @throws AMysql_Exception on error
378: * @return AMysql_Statement (chainable)
379: */
380: protected function _query($sql)
381: {
382: $link = $this->getLink();
383: $isMysqli = $this->isMysqli();
384: $this->query = $sql;
385: $success = false;
386: $stmt = null;
387: try {
388: set_error_handler(array($this, 'errorHandlerCallback'), E_WARNING);
389: if ($this->_executed) {
390: throw new LogicException(
391: "This statement has already been executed.\nQuery: $sql"
392: );
393: }
394: if ($isMysqli) {
395: $startTime = microtime(true);
396:
397: $stmt = $link->prepare($sql);
398: if ($stmt) {
399: $success = $stmt->execute();
400: }
401:
402: $duration = microtime(true) - $startTime;
403: $this->queryTime = $duration;
404: } else {
405: $startTime = microtime(true);
406:
407: $result = mysql_query($sql, $link);
408:
409: $duration = microtime(true) - $startTime;
410: $this->queryTime = $duration;
411: }
412: if ($isMysqli) {
413: $result = $stmt ? $stmt->get_result() : false;
414: if (!$result && $success) {
415: /**
416: * In mysqli, result_metadata will return a falsy value
417: * even for successful SELECT queries, so for compatibility
418: * let's set the result to true if it isn't an object
419: * (is false), but the query was successful.
420: */
421: $result = true;
422: }
423: }
424: $this->amysql->addQuery($sql, $this->queryTime);
425: if (false !== $result) {
426: if ($isMysqli) {
427: $this->affectedRows = $stmt->affected_rows;
428: $this->insertId = $stmt->insert_id;
429: } else {
430: $this->affectedRows = mysql_affected_rows($link);
431: $this->insertId = mysql_insert_id($link);
432: }
433: $this->result = $result;
434: $this->results[] = $result;
435: $this->amysql->affectedRows = $this->affectedRows;
436: $this->amysql->insertId = $this->insertId;
437: $this->_executed = true;
438: } else {
439: throw new RuntimeException(
440: "Query was not successful.",
441: self::CODE_QUERY_NOT_SUCCESSFUL
442: );
443: }
444: restore_error_handler();
445: } catch (Exception $e) {
446: restore_error_handler();
447: if ($e instanceof ErrorException) {
448: $this->reportErrorException($e);
449: } elseif ($e instanceof RuntimeException) {
450: if (self::CODE_QUERY_NOT_SUCCESSFUL == $e->getCode()) {
451: // continue on reporting the error
452: } else {
453: // unexpected RuntimeException should be thrown.
454: throw $e;
455: }
456: } else {
457: // throw the unexpected exception
458: throw $e;
459: }
460: $error = $isMysqli ? $link->error : mysql_error($link);
461: $errno = $isMysqli ? $link->errno : mysql_errno($link);
462: throw new AMysql_Exception($error, $errno, $this->query, $e);
463: }
464: return $this;
465: }
466:
467: /**
468: * Frees the mysql result resource.
469: *
470: * @return AMysql_Statement (chainable)
471: **/
472: public function freeResults()
473: {
474: foreach ($this->results as $result) {
475: if (is_resource($result)) {
476: $this->isMysqli() ? $result->free() : mysql_free_result($result);
477: }
478: }
479: return $this;
480: }
481:
482: /**
483: * Returns all the results with each row in the format of that specified
484: * by the fetch mode.
485: *
486: * @see AMysql_Statement::setFetchMode()
487: *
488: * @return array
489: **/
490: public function fetchAll()
491: {
492: $result = $this->result;
493: $ret = array ();
494: if (AMysql_Abstract::FETCH_ASSOC == $this->_fetchMode) {
495: $methodName = 'fetchAssoc';
496: } elseif (AMysql_Abstract::FETCH_OBJECT == $this->_fetchMode) {
497: $methodName = 'fetchObject';
498: } elseif (AMysql_Abstract::FETCH_ARRAY == $this->_fetchMode) {
499: $methodName = 'fetchArray';
500: } elseif (AMysql_Abstract::FETCH_ROW == $this->_fetchMode) {
501: $methodName = 'fetchRow';
502: } else {
503: throw new Exception("Unknown fetch mode: `$this->_fetchMode`");
504: }
505: $ret = array();
506: $numRows = $this->numRows();
507: if (0 === $numRows) {
508: return array ();
509: } elseif (false === $numRows) {
510: return false;
511: }
512: $extraArgs = $this->_fetchModeExtraArgs;
513: $method = array ($this, $methodName);
514: $result instanceof Mysqli_Result ?
515: $result->data_seek(0) :
516: mysql_data_seek($result, 0);
517: while (
518: ($row = call_user_func_array($method, $extraArgs)) && isset($row)
519: ) {
520: $ret[] = $row;
521: }
522: return $ret;
523: }
524:
525: /**
526: * Returns one row in the format specified by the fetch mode.
527: *
528: * @see AMysql_Statement::setFetchMode()
529: *
530: * @return mixed Usually array. Can also be FALSE if there are
531: * no more rows. If AMysql_Abstract::FETCH_OBJECT
532: * is the fetch mode, then an object would be
533: * returned.
534: */
535: public function fetch()
536: {
537: if ('assoc' == $this->_fetchMode) {
538: return $this->fetchAssoc();
539: } elseif ('object' == $this->_fetchMode) {
540: $extraArgs = $this->_fetchModeExtraArgs;
541: $method = array ($this, 'fetchObject');
542: return call_user_func_array($method, $extraArgs);
543: } elseif ('row' == $this->_fetchMode) {
544: return $this->fetchRow();
545: } elseif (AMysql_Abstract::FETCH_ARRAY == $this->_fetchMode) {
546: return $this->fetchArray();
547: } else {
548: throw new RuntimeException("Unknown fetch mode: `$this->_fetchMode`");
549: }
550: }
551:
552: /**
553: * Fetches one row with column names as the keys.
554: *
555: * @return array|FALSE
556: */
557: public function fetchAssoc()
558: {
559: $result = $this->result;
560: return $this->isMysqli() ? $result->fetch_assoc() : mysql_fetch_assoc($result);
561: }
562:
563: /**
564: * Fetches all rows and returns them as an array of associative arrays. The
565: * outer array is numerically indexed by default, but can be indexed by
566: * a field value.
567: *
568: * @param integer|string|boolean $keyColumn (Optional) If a string, the cell
569: * of the given field will be the key for
570: * its row, so the result will not be an array
571: * numerically indexed from 0 in order. This
572: * value can also be an integer, specifying
573: * the index of the field with the key.
574: * @access public
575: * @return <Associative result array>[]
576: */
577: public function fetchAllAssoc($keyColumn = false)
578: {
579: $result = $this->result;
580: $ret = array();
581: $numRows = $this->numRows();
582: if (0 === $numRows) {
583: return array ();
584: } elseif (false === $numRows) {
585: return false;
586: }
587: $result instanceof Mysqli_Result ? $result->data_seek(0) : mysql_data_seek($result, 0);
588: $keyColumnGiven = is_string($keyColumn) || is_int($keyColumn);
589: if (!$keyColumnGiven) {
590: while (false !== ($row = $this->fetchAssoc()) && isset($row)) {
591: $ret[] = $row;
592: }
593: } else {
594: $row = $this->fetchAssoc();
595: /**
596: * Since we are using associative keys here, if we gave the key as an
597: * int, we have to find out the associative version of the key.
598: **/
599: if (is_int($keyColumn)) {
600: $cnt = count($keyColumn);
601: reset($row);
602: for ($i = 0; $i < $cnt; $i++) {
603: next($row);
604: }
605: $keyColumn = key($row);
606: reset($row);
607: }
608: $ret[$row[$keyColumn]] = $row;
609: while ($row = $this->fetchAssoc()) {
610: $ret[$row[$keyColumn]] = $row;
611: }
612: }
613: return $ret;
614: }
615:
616: /**
617: * Fetches the next row with column names as numeric indices.
618: * Returns FALSE if there are no more rows.
619: *
620: * @return array|FALSE
621: */
622: public function fetchRow()
623: {
624: $result = $this->result;
625: return $this->isMysqli() ?
626: $result->fetch_row() :
627: mysql_fetch_row($result);
628: }
629:
630: /**
631: * Alias of {@link AMysql_Statement::fetchRow()}
632: *
633: * @see AMysql_Statement::fetchRow()
634: *
635: * @return array|FALSE
636: */
637: public function fetchNum()
638: {
639: return $this->fetchRow();
640: }
641:
642: /**
643: * Fetches the next row with column names and their indexes as keys.
644: *
645: * @return array|FALSE
646: */
647: public function fetchArray()
648: {
649: $result = $this->result;
650: $isMysqli = $this->isMysqli();
651: return $isMysqli ?
652: $result->fetch_array(MYSQLI_BOTH) :
653: mysql_fetch_array($result, MYSQL_BOTH);
654: }
655:
656: /**
657: * Returns the result of the given row and field. A warning is issued
658: * if the result on the given row and column does not exist.
659: *
660: * @param int $row (Optional) The row number.
661: * @param int|string $field (Optional) The field number or name.
662: * @return string|int|FALSE
663: */
664: public function result($row = 0, $field = 0)
665: {
666: $result = $this->result;
667: if ($this->isMysqli()) {
668: if ($result->num_rows <= $row) {
669: // mysql_result compatibility, sort of...
670: trigger_error("Unable to jump to row $row", E_WARNING);
671: return false;
672: }
673: /**
674: * @todo optimize
675: **/
676: $result->data_seek($row);
677: $array = $result->fetch_array(MYSQLI_BOTH);
678: if (!array_key_exists($field, $array)) {
679: // mysql_result compatibility, sort of...
680: trigger_error("Unable to access field `$field` of row $row", E_WARNING);
681: return false;
682: }
683: $ret = $array[$field];
684: $result->data_seek(0);
685: return $ret;
686: }
687: return mysql_result($result, $row, $field);
688: }
689:
690: /**
691: * Returns the result of the given row and field, or the given value
692: * if the row doesn't exist
693: *
694: *
695: * @param mixed $default The value to return if the field is not found.
696: * @param int $row (Optional) The row number.
697: * @param int $field (Optional) The field.
698: * @return mixed
699: */
700: public function resultDefault($default, $row = 0, $field = 0)
701: {
702: $result = $this->result;
703: return $row < $this->numRows() ? $this->result($row, $field) :
704: $default;
705: }
706:
707: /**
708: * Returns the result of the given row and field, or null if the
709: * row doesn't exist
710: *
711: *
712: * @param int $row (Optional) The row number.
713: * @param int $field (Optional) The field.
714: * @return mixed
715: */
716:
717: public function resultNull($row = 0, $field = 0)
718: {
719: return $this->resultDefault(null, $row, $field);
720: }
721:
722: /**
723: * Returns the result of the given row and field as an integer.
724: * 0, if that result doesn't exist.
725: *
726: * @param int $row (Optional) The row number.
727: * @param int $field (Optional) The field.
728: * @return int
729: */
730: public function resultInt($row = 0, $field = 0)
731: {
732: return (int) $this->resultNull($row, $field);
733: }
734:
735: /**
736: * Returns an array of scalar values, where the keys are the values
737: * of the key column specified, and the values are the values of the
738: * value column specified.
739: *
740: * @param mixed $keyColumn (Optional) column number or string for
741: * the keys.
742: * Default: 0.
743: * @param mixed $valueColumn (Optional) column number or string for
744: * the values.
745: * Default: 1.
746: * @access public
747: * @return array
748: */
749: public function fetchPairs($keyColumn = 0, $valueColumn = 1)
750: {
751: $ret = array ();
752: while ($row = $this->fetchArray()) {
753: $key = $row[$keyColumn];
754: $ret[$key] = $row[$valueColumn];
755: }
756: return $ret;
757: }
758:
759: /**
760: * Alias of {@link AMysql_Statement::fetchPairs()}
761: *
762: */
763: public function pairUp($keyColumn = 0, $valueColumn = 1)
764: {
765: return $this->fetchPairs($keyColumn, $valueColumn);
766: }
767:
768: /**
769: * Returns all values of a specified column as an array.
770: *
771: * @param mixed $column (Optional) column number or string for
772: * the values.
773: * Default: 0.
774: * @access public
775: * @return array
776: */
777: public function fetchAllColumn($column = 0)
778: {
779: $ret = array ();
780: $numRows = $this->numRows();
781: if (!$numRows) {
782: return $ret;
783: }
784: $this->isMysqli() ? $result->data_seek(0) : mysql_data_seek($result, 0);
785: while ($row = $this->fetchArray()) {
786: $ret[] = $row[$column];
787: }
788: return $ret;
789: }
790:
791: /**
792: * Fetches all rows and returns them as an array of columns containing an
793: * array of values. Works simalarly to fetchAllAssoc(), but with the
794: * resulting array transposed.
795: *
796: * e.g.
797: * [
798: * 'id' => ['1', '2'],
799: * 'val' => ['val1', 'val2']
800: * ]
801: *
802: * @param string|int $keyColumn When building an array of arrays
803: * (list of values for that column)
804: * if this value is given, the indexes
805: * of the inner array will be equal to
806: * the value of the column in the row
807: * equivalent of data. Typically you
808: * want to choose the primary key.
809: * e.g. if $keyColumn is 'id', the
810: * example changes to:
811: * [
812: * 'id' => ['1' => '1', '2' => '2'],
813: * 'val' => [
814: * '1' => 'val1',
815: * '2' => 'val2'
816: * ]
817: * ]
818: *
819: * @access public
820: * @return array
821: */
822: public function fetchAllColumns($keyColumn = false)
823: {
824: $ret = array ();
825: $numRows = $this->numRows();
826: $keyColumnGiven = is_string($keyColumn) || is_int($keyColumn);
827: if (!$numRows) {
828: // ok
829: } elseif (!$keyColumnGiven) {
830: /**
831: * If $keyColumn isn't given i.e. the resulting array indexes
832: * don't matter, let's build the returning array here to
833: * dodge unnecessary overhead.
834: **/
835: $result = $this->result;
836: $result instanceof Mysqli_Result ?
837: $result->data_seek(0) :
838: mysql_data_seek($result, 0);
839: $firstRow = $this->fetchAssoc();
840: foreach ($firstRow as $colName => $val) {
841: $ret[$colName] = array ($val);
842: }
843: while ($row = $this->fetchAssoc()) {
844: foreach ($row as $colName => $val) {
845: $ret[$colName][] = $val;
846: }
847: }
848: return $ret;
849: } else {
850: /**
851: * Otherwise if $keyColumn is given, we have no other choice but to use
852: * $this->fetchAllAssoc($keyColumn) and transpose it.
853: **/
854: $ret = AMysql_Abstract::transpose(
855: $this->fetchAllAssoc($keyColumn)
856: );
857: }
858: return $ret;
859: }
860:
861: /**
862: * Fetches the next row as an object.
863: *
864: * @param string $className (Optional) The class to use. Default is stdClass.
865: * @param array $params (Optional) The params to pass to the object.
866: *
867: * @return object
868: */
869: public function fetchObject(
870: $className = 'stdClass',
871: array $params = array()
872: ) {
873: $result = $this->result;
874: $isMysqli = $this->isMysqli();
875: if ($params) {
876: return $isMysqli ?
877: $result->fetch_object($className, $params) :
878: mysql_fetch_object($result, $className, $params)
879: ;
880: } else {
881: return $isMysqli ?
882: $result->fetch_object($className) :
883: mysql_fetch_object($result, $className)
884: ;
885: }
886: }
887:
888: /**
889: * Returns the number of affected rows.
890: *
891: * @return int
892: */
893: public function affectedRows()
894: {
895: return $this->affectedRows;
896: }
897:
898: /**
899: * Returns the number of rows selected, or FALSE on failure.
900: *
901: * @access public
902: * @return int|FALSE
903: */
904: public function numRows()
905: {
906: if ($this->isMysqli()) {
907: return $this->result instanceof Mysqli_Result ? $this->result->num_rows : false;
908: }
909: return mysql_num_rows($this->result);
910: }
911:
912: /**
913: * Throws an AMysqlException for the last mysql error.
914: * For internal use (if even used).
915: *
916: * @throws AMysql_Exception
917: */
918: public function throwException()
919: {
920: throw new AMysql_Exception($this->error, $this->errno, $this->query);
921: }
922:
923: /**
924: * @deprecated
925: */
926: public function escapeIdentifierSimple($columnName)
927: {
928: return AMysql_Abstract::escapeIdentifierSimple($columnName);
929: }
930:
931: /**
932: * Escapes an identifier and puts it between identifier quotes.
933: *
934: * @param string $identifier The identifier
935: * @param string $qc The quote character. Default: `
936: *
937: * @return string The escaped identifier.
938: **/
939: public function escapeIdentifier($columnName, $as = null)
940: {
941: return $this->amysql->escapeIdentifier($columnName, $as);
942: }
943:
944: /**
945: * Appends a string to the prepared string.
946: *
947: * @param string $sql The string to append.
948: * @return AMysql_Statement (chainable)
949: **/
950: public function appendPrepare($sql)
951: {
952: $this->prepared .= $sql;
953: return $this;
954: }
955:
956: /**
957: * Binds a value to the sql string.
958: *
959: * @param mixed $key If an integer, then the given index
960: * question mark will be replaced.
961: * If a string, then then, if it starts
962: * with an alphanumberic or 128+ ascii
963: * character, then a colon plus the string
964: * given will be replaced, otherwise the
965: * given string literally will be replaced.
966: * Example: if the string is
967: * foo
968: * then :foo will be replaced.
969: * if the string is
970: * !foo
971: * then !foo will be replaced
972: * if the string is
973: * :foo:
974: * then :foo: will be replaced.
975: * Note: don't worry about keys that have a
976: * common beginning. If foo and fool are set,
977: * :fool will not be replaced with the value
978: * given for foo.
979: *
980: * @param mixed $val Bind this value for replacing the mark
981: * defined by $key. The value is escaped
982: * depeding on its type, apostrophes included,
983: * so do not add apostrophes in your
984: * prepared sqls.
985: *
986: * @return AMysql_Statement (chainable)
987: **/
988: public function bindValue($key, $val)
989: {
990: if (is_numeric($key) && $this->amysql->pdoIndexedBinding) {
991: $key--;
992: }
993: $this->binds[$key] = $val;
994: return $this;
995: }
996:
997: /**
998: * The same as $this->bindValue(), except that $val is binded by
999: * reference, meaning its value is extracted on execute.
1000: *
1001: * @see AMysql_Statement::bindValue()
1002: * @param mixed $key @see AMysql_Statement::bindValue()
1003: * @param mixed &$val Like $val in
1004: * {@link AMysql_Statement::bindValue()}, but by
1005: * reference.
1006: *
1007: * @return AMysql_Statement (chainable)
1008: */
1009: public function bindParam($key, &$val)
1010: {
1011: if (is_numeric($key) && $this->amysql->pdoIndexedBinding) {
1012: $key--;
1013: }
1014: $this->binds[$key] =& $val;
1015: return $this;
1016: }
1017:
1018: /**
1019: * Sets the binds binding question marks or named binds to values.
1020: *
1021: * @param array $binds The binds.
1022: * @access public
1023: * @return AMysql_Statement (chainable)
1024: */
1025: public function setBinds(array $binds)
1026: {
1027: $this->binds = $binds;
1028: return $this;
1029: }
1030:
1031: /**
1032: * Merges an array of binds with the ones already set.
1033: * Only use for named parameters!
1034: *
1035: * @param array $binds The binds.
1036: * @access public
1037: * @return AMysql_Statement (chainable)
1038: */
1039: public function addBinds(array $binds)
1040: {
1041: $this->binds = array_merge($this->binds, $binds);
1042: return $this;
1043: }
1044:
1045: /**
1046: * Builds a columns list with values as a string. Can be an
1047: * array of column-value pairs (2D for one row), can be an
1048: * array of array of key-value pairs (3D for multiple rows),
1049: * or can be an array with column names as the keys, each value
1050: * being an array of values (3D for multiple rows).
1051: *
1052: * e.g. (`col1`, `col2`) VALUES ('col1val1', 'col1val2'),
1053: * ('col2val1', 'col2val2')
1054: *
1055: * @param array $data @see $this->insertReplace()
1056: * @return string
1057: */
1058: public function buildColumnsValues(array $data)
1059: {
1060: $i = 0;
1061: if (empty($data[0])) {
1062: // keys are column names
1063: foreach ($data as $columnName => $values) {
1064: $cols[] = $this->amysql->escapeColumn($columnName);
1065: if (!is_array($values)) {
1066: // single piece of data here.
1067: $values = array($values);
1068: }
1069: foreach ($values as $key => $value) {
1070: if (!isset($vals[$key])) {
1071: $vals[$key] = array ();
1072: }
1073: $vals[$key][] = $this->amysql->escape($value);
1074: }
1075: }
1076: } else {
1077: // keys are indexes
1078:
1079: // the column names should be found in the first index's keys
1080: $akeys = array_keys($data[0]);
1081: $cols = array ();
1082: foreach ($akeys as $col) {
1083: $cols[] = $this->amysql->escapeColumn($col);
1084: }
1085:
1086: foreach ($data as $row) {
1087: $vals[$i] = array ();
1088: $row2 = array();
1089: foreach ($akeys as $key) {
1090: $row2[$key] = null;
1091: }
1092: foreach ($row as $columnName => $value) {
1093: $row2[$columnName] = $this->amysql->escape($value);
1094:
1095: }
1096: $vals[$i] = $row2;
1097: $i++;
1098: }
1099: }
1100: $columnsString = join(', ', $cols);
1101: $rowValueStrings = array();
1102: foreach ($vals as $rowValues) {
1103: $rowValueStrings[] = join(', ', $rowValues);
1104: }
1105: $valuesString = join('), (', $rowValueStrings);
1106: $columnsValuesString = "($columnsString) VALUES ($valuesString)";
1107: return $columnsValuesString;
1108: }
1109:
1110: /**
1111: * Puts together a string that is to be placed after a SET statement.
1112: * i.e. column1 = 'value', int_col = 3
1113: *
1114: * @param array $data Keys are column names, values are the values unescaped
1115: * @return string
1116: */
1117: public function buildSet(array $data)
1118: {
1119: $sets = array ();
1120: foreach ($data as $columnName => $value) {
1121: $columnName = $this->amysql->escapeColumn($columnName);
1122: $sets[] = "$columnName = " . $this->amysql->escape($value);
1123: }
1124: $setsString = join(', ', $sets);
1125: return $setsString;
1126: }
1127:
1128: /**
1129: * Prepares a mysql UPDATE unexecuted. By execution, have the placeholders
1130: * of the WHERE statement binded.
1131: * It is rather recommended to use AMysql_Abstract::update() instead, which
1132: * lets you also bind the values in one call and it returns the success
1133: * of the query.
1134: *
1135: * @param string $tableName The table name.
1136: * @param array $data The array of data changes. A
1137: * one-dimensional array
1138: * with keys as column names and values
1139: * as their values.
1140: * @param string $where An SQL substring of the WHERE clause.
1141: *
1142: * @return AMysql_Statement (chainable)
1143: **/
1144: public function update($tableName, array $data, $where)
1145: {
1146: if (!$data) {
1147: return false;
1148: }
1149: $setsString = $this->buildSet($data);
1150:
1151: /**
1152: * This must be solved by beforeSql, otherwise bind substrings
1153: * could cause problems within the SET string.
1154: **/
1155: $tableSafe = $this->amysql->escapeTable($tableName);
1156: $beforeSql = "UPDATE $tableSafe SET $setsString WHERE ";
1157: $this->prepare($where);
1158: $this->beforeSql = $beforeSql;
1159:
1160: return $this;
1161: }
1162:
1163: /**
1164: * Prepares a mysql INSERT or REPLACE unexecuted. After this, you should just
1165: * call $this->execute().
1166: * It is rather recommended to use AMysql_Abstract::insert() instead, which
1167: * returns the last inserted id already.
1168: *
1169: * @param string $type "$type INTO..." (INSERT, INSERT IGNORE, REPLACE) etc.
1170: * @param string $tableName The table name.
1171: * @param array $data A one or two-dimensional array.
1172: * 1D:
1173: * an associative array of keys as column names and values
1174: * as their values. This inserts one row.
1175: * 2D numeric:
1176: * A numeric array where each value is an associative array
1177: * with column-value pairs. Each outer, numeric value represents
1178: * a row of data.
1179: * 2D associative:
1180: * An associative array where the keys are the columns, the
1181: * values are numerical arrays, where each value represents the
1182: * value for the new row of that key.
1183: *
1184: * @return AMysql_Statement (chainable)
1185: **/
1186: public function insertReplace($type, $tableName, array $data)
1187: {
1188: $cols = array ();
1189: $vals = array();
1190: if (!$data) {
1191: return false;
1192: }
1193: $tableSafe = $this->amysql->escapeTable($tableName);
1194: $columnsValues = $this->buildColumnsValues($data);
1195: $sql = "$type INTO $tableSafe $columnsValues";
1196: $this->prepare($sql);
1197: return $this;
1198: }
1199:
1200: /**
1201: * Performs an INSERT.
1202: *
1203: * @see $this->insertReplace
1204: *
1205: * @param string $tableName
1206: * @param array $data
1207: * @access public
1208: * @return AMysql_Statement (chainable)
1209: */
1210: public function insert($tableName, array $data)
1211: {
1212: return $this->insertReplace('INSERT', $tableName, $data);
1213: }
1214:
1215: /**
1216: * Performs a REPLACE.
1217: *
1218: * @see $this->insertReplace
1219: *
1220: * @param string $tableName
1221: * @param array $data
1222: * @access public
1223: * @return AMysql_Statement (chainable)
1224: */
1225: public function replace($tableName, array $data)
1226: {
1227: return $this->insertReplace('REPLACE', $tableName, $data);
1228: }
1229:
1230: /**
1231: * Prepares a mysql DELETE unexecuted. By execution, have the placeholders
1232: * of the WHERE statement binded.
1233: * It is rather recommended to use AMysql_Abstract::delete() instead, which
1234: * lets you also bind the values in one call and it returns the success
1235: * of the query.
1236: *
1237: * @param string $tableName The table name.
1238: * @param string $where An SQL substring of the WHERE clause.
1239: *
1240: * @see AMysql_Abstract::delete()
1241: *
1242: * @return AMysql_Statement (chainable)
1243: **/
1244: public function delete($tableName, $where)
1245: {
1246: $tableSafe = $this->amysql->escapeTable($tableName);
1247: $sql = "DELETE FROM $tableSafe";
1248: if ($where) {
1249: $sql .= ' WHERE ' . $where;
1250: }
1251: $this->prepare($sql);
1252: return $this;
1253: }
1254:
1255: /**
1256: * Returns the last insert id
1257: *
1258: * @return integer|FALSE
1259: **/
1260: public function insertId()
1261: {
1262: $ret = $this->isMysqli() ? $result->insert_id() : mysql_insert_id($this->getLink());
1263: return $ret;
1264: }
1265:
1266: /**
1267: * Free the results.
1268: **/
1269: public function __destruct()
1270: {
1271: $this->freeResults();
1272: }
1273:
1274: public function __set($name, $value)
1275: {
1276: switch($name) {
1277: case 'fetchMode':
1278: $this->setFetchMode($value);
1279: break;
1280: default:
1281: throw new OutOfBoundsException(
1282: "Invalid member: `$name` (target value was `$value`)"
1283: );
1284: }
1285: }
1286:
1287: /**
1288: * Returns the number of rows in the result.
1289: *
1290: * @throws LogicException If the query was not a read query.
1291: * @access public
1292: * @return int
1293: */
1294: public function count()
1295: {
1296: if (!is_resource($this->result) && !is_object($this->result)) {
1297: $msg = "No SELECT result. ".
1298: "Last query: " . $this->query;
1299: throw new LogicException($msg);
1300: }
1301: $count = $this->numRows();
1302: return $count;
1303: }
1304:
1305: /**
1306: * Handle an ErrorException thrown with the help of
1307: * $this->errorHandlerCallback. For now, just suppress the
1308: * error.
1309: * For internal use.
1310: *
1311: * @param ErrorException $ex
1312: * @access public
1313: * @return void
1314: */
1315: public function reportErrorException(ErrorException $ex)
1316: {
1317: }
1318:
1319: /**
1320: * Changes warnings into exceptions.
1321: * For internal use.
1322: * Mainly for handling warnings generated by mysql functions/methods.
1323: *
1324: * @param int $errno
1325: * @param string $errstr
1326: * @param string $errfile
1327: * @param int $errline
1328: * @param array $errcontext
1329: * @access public
1330: * @throws ErrorException
1331: * @return void
1332: */
1333: public function errorHandlerCallback(
1334: $errno,
1335: $errstr,
1336: $errfile = null,
1337: $errline = null,
1338: $errcontext = null
1339: ) {
1340: throw new ErrorException($errstr, $errno, 1, $errfile, $errline);
1341: }
1342:
1343: /**
1344: * handleError
1345: *
1346: * @param string $msg
1347: * @param int $code
1348: * @param string $query
1349: * @access protected
1350: * @throws AMysql_Exception
1351: * @return void
1352: */
1353: protected function handleError($msg, $code, $query)
1354: {
1355: $this->error = $msg;
1356: $this->errno = $code;
1357: return $this->amysql->handleError($msg, $code, $query);
1358: }
1359:
1360: /**
1361: * handleException
1362: *
1363: * @param AMysql_Exception
1364: * @access protected
1365: * @throws AMysql_Exception
1366: * @return void
1367: */
1368: protected function handleException(AMysql_Exception $ex)
1369: {
1370: return $this->amysql->handleException($ex);
1371: }
1372: }
1373: