Subversion-Projekte lars-tiefland.ci

Revision

Revision 2254 | Revision 2258 | Zur aktuellen Revision | Details | Vergleich mit vorheriger | Letzte Änderung | Log anzeigen | RSS feed

Revision Autor Zeilennr. Zeile
68 lars 1 
<?php
2 
/**
3 
 * CodeIgniter
4 
 *
5 
 * An open source application development framework for PHP
6 
 *
7 
 * This content is released under the MIT License (MIT)
8 
 *
2257 lars 9 
 * Copyright (c) 2014 - 2018, British Columbia Institute of Technology
68 lars 10 
 *
11 
 * Permission is hereby granted, free of charge, to any person obtaining a copy
12 
 * of this software and associated documentation files (the "Software"), to deal
13 
 * in the Software without restriction, including without limitation the rights
14 
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
15 
 * copies of the Software, and to permit persons to whom the Software is
16 
 * furnished to do so, subject to the following conditions:
17 
 *
18 
 * The above copyright notice and this permission notice shall be included in
19 
 * all copies or substantial portions of the Software.
20 
 *
21 
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
22 
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
23 
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
24 
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
25 
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
26 
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
27 
 * THE SOFTWARE.
28 
 *
29 
 * @package	CodeIgniter
30 
 * @author	EllisLab Dev Team
31 
 * @copyright	Copyright (c) 2008 - 2014, EllisLab, Inc. (https://ellislab.com/)
2257 lars 32 
 * @copyright	Copyright (c) 2014 - 2018, British Columbia Institute of Technology (http://bcit.ca/)
68 lars 33 
 * @license	http://opensource.org/licenses/MIT	MIT License
34 
 * @link	https://codeigniter.com
35 
 * @since	Version 1.0.0
36 
 * @filesource
37 
 */
38 
defined('BASEPATH') OR exit('No direct script access allowed');
39 
 
40 
/**
41 
 * Query Builder Class
42 
 *
43 
 * This is the platform-independent base Query Builder implementation class.
44 
 *
45 
 * @package		CodeIgniter
46 
 * @subpackage	Drivers
47 
 * @category	Database
48 
 * @author		EllisLab Dev Team
49 
 * @link		https://codeigniter.com/user_guide/database/
50 
 */
51 
 
52 
abstract class CI_DB_query_builder extends CI_DB_driver {
53 
 
54 
	/**
55 
	 * Return DELETE SQL flag
56 
	 *
57 
	 * @var	bool
58 
	 */
59 
	protected $return_delete_sql		= FALSE;
60 
 
61 
	/**
62 
	 * Reset DELETE data flag
63 
	 *
64 
	 * @var	bool
65 
	 */
66 
	protected $reset_delete_data		= FALSE;
67 
 
68 
	/**
69 
	 * QB SELECT data
70 
	 *
71 
	 * @var	array
72 
	 */
73 
	protected $qb_select			= array();
74 
 
75 
	/**
76 
	 * QB DISTINCT flag
77 
	 *
78 
	 * @var	bool
79 
	 */
80 
	protected $qb_distinct			= FALSE;
81 
 
82 
	/**
83 
	 * QB FROM data
84 
	 *
85 
	 * @var	array
86 
	 */
87 
	protected $qb_from			= array();
88 
 
89 
	/**
90 
	 * QB JOIN data
91 
	 *
92 
	 * @var	array
93 
	 */
94 
	protected $qb_join			= array();
95 
 
96 
	/**
97 
	 * QB WHERE data
98 
	 *
99 
	 * @var	array
100 
	 */
101 
	protected $qb_where			= array();
102 
 
103 
	/**
104 
	 * QB GROUP BY data
105 
	 *
106 
	 * @var	array
107 
	 */
108 
	protected $qb_groupby			= array();
109 
 
110 
	/**
111 
	 * QB HAVING data
112 
	 *
113 
	 * @var	array
114 
	 */
115 
	protected $qb_having			= array();
116 
 
117 
	/**
118 
	 * QB keys
119 
	 *
120 
	 * @var	array
121 
	 */
122 
	protected $qb_keys			= array();
123 
 
124 
	/**
125 
	 * QB LIMIT data
126 
	 *
127 
	 * @var	int
128 
	 */
129 
	protected $qb_limit			= FALSE;
130 
 
131 
	/**
132 
	 * QB OFFSET data
133 
	 *
134 
	 * @var	int
135 
	 */
136 
	protected $qb_offset			= FALSE;
137 
 
138 
	/**
139 
	 * QB ORDER BY data
140 
	 *
141 
	 * @var	array
142 
	 */
143 
	protected $qb_orderby			= array();
144 
 
145 
	/**
146 
	 * QB data sets
147 
	 *
148 
	 * @var	array
149 
	 */
150 
	protected $qb_set			= array();
151 
 
152 
	/**
2049 lars 153 
	 * QB data set for update_batch()
154 
	 *
155 
	 * @var	array
156 
	 */
157 
	protected $qb_set_ub			= array();
158 
 
159 
	/**
68 lars 160 
	 * QB aliased tables list
161 
	 *
162 
	 * @var	array
163 
	 */
164 
	protected $qb_aliased_tables		= array();
165 
 
166 
	/**
167 
	 * QB WHERE group started flag
168 
	 *
169 
	 * @var	bool
170 
	 */
171 
	protected $qb_where_group_started	= FALSE;
172 
 
173 
	/**
174 
	 * QB WHERE group count
175 
	 *
176 
	 * @var	int
177 
	 */
178 
	protected $qb_where_group_count		= 0;
179 
 
180 
	// Query Builder Caching variables
181 
 
182 
	/**
183 
	 * QB Caching flag
184 
	 *
185 
	 * @var	bool
186 
	 */
187 
	protected $qb_caching				= FALSE;
188 
 
189 
	/**
190 
	 * QB Cache exists list
191 
	 *
192 
	 * @var	array
193 
	 */
194 
	protected $qb_cache_exists			= array();
195 
 
196 
	/**
197 
	 * QB Cache SELECT data
198 
	 *
199 
	 * @var	array
200 
	 */
201 
	protected $qb_cache_select			= array();
202 
 
203 
	/**
204 
	 * QB Cache FROM data
205 
	 *
206 
	 * @var	array
207 
	 */
208 
	protected $qb_cache_from			= array();
209 
 
210 
	/**
211 
	 * QB Cache JOIN data
212 
	 *
213 
	 * @var	array
214 
	 */
215 
	protected $qb_cache_join			= array();
216 
 
217 
	/**
2107 lars 218 
	 * QB Cache aliased tables list
219 
	 *
220 
	 * @var	array
221 
	 */
222 
	protected $qb_cache_aliased_tables			= array();
223 
 
224 
	/**
68 lars 225 
	 * QB Cache WHERE data
226 
	 *
227 
	 * @var	array
228 
	 */
229 
	protected $qb_cache_where			= array();
230 
 
231 
	/**
232 
	 * QB Cache GROUP BY data
233 
	 *
234 
	 * @var	array
235 
	 */
236 
	protected $qb_cache_groupby			= array();
237 
 
238 
	/**
239 
	 * QB Cache HAVING data
240 
	 *
241 
	 * @var	array
242 
	 */
243 
	protected $qb_cache_having			= array();
244 
 
245 
	/**
246 
	 * QB Cache ORDER BY data
247 
	 *
248 
	 * @var	array
249 
	 */
250 
	protected $qb_cache_orderby			= array();
251 
 
252 
	/**
253 
	 * QB Cache data sets
254 
	 *
255 
	 * @var	array
256 
	 */
257 
	protected $qb_cache_set				= array();
258 
 
259 
	/**
260 
	 * QB No Escape data
261 
	 *
262 
	 * @var	array
263 
	 */
264 
	protected $qb_no_escape 			= array();
265 
 
266 
	/**
267 
	 * QB Cache No Escape data
268 
	 *
269 
	 * @var	array
270 
	 */
271 
	protected $qb_cache_no_escape			= array();
272 
 
273 
	// --------------------------------------------------------------------
274 
 
275 
	/**
276 
	 * Select
277 
	 *
278 
	 * Generates the SELECT portion of the query
279 
	 *
280 
	 * @param	string
281 
	 * @param	mixed
282 
	 * @return	CI_DB_query_builder
283 
	 */
284 
	public function select($select = '*', $escape = NULL)
285 
	{
286 
		if (is_string($select))
287 
		{
288 
			$select = explode(',', $select);
289 
		}
290 
 
291 
		// If the escape value was not set, we will base it on the global setting
292 
		is_bool($escape) OR $escape = $this->_protect_identifiers;
293 
 
294 
		foreach ($select as $val)
295 
		{
296 
			$val = trim($val);
297 
 
298 
			if ($val !== '')
299 
			{
300 
				$this->qb_select[] = $val;
301 
				$this->qb_no_escape[] = $escape;
302 
 
303 
				if ($this->qb_caching === TRUE)
304 
				{
305 
					$this->qb_cache_select[] = $val;
306 
					$this->qb_cache_exists[] = 'select';
307 
					$this->qb_cache_no_escape[] = $escape;
308 
				}
309 
			}
310 
		}
311 
 
312 
		return $this;
313 
	}
314 
 
315 
	// --------------------------------------------------------------------
316 
 
317 
	/**
318 
	 * Select Max
319 
	 *
320 
	 * Generates a SELECT MAX(field) portion of a query
321 
	 *
322 
	 * @param	string	the field
323 
	 * @param	string	an alias
324 
	 * @return	CI_DB_query_builder
325 
	 */
326 
	public function select_max($select = '', $alias = '')
327 
	{
328 
		return $this->_max_min_avg_sum($select, $alias, 'MAX');
329 
	}
330 
 
331 
	// --------------------------------------------------------------------
332 
 
333 
	/**
334 
	 * Select Min
335 
	 *
336 
	 * Generates a SELECT MIN(field) portion of a query
337 
	 *
338 
	 * @param	string	the field
339 
	 * @param	string	an alias
340 
	 * @return	CI_DB_query_builder
341 
	 */
342 
	public function select_min($select = '', $alias = '')
343 
	{
344 
		return $this->_max_min_avg_sum($select, $alias, 'MIN');
345 
	}
346 
 
347 
	// --------------------------------------------------------------------
348 
 
349 
	/**
350 
	 * Select Average
351 
	 *
352 
	 * Generates a SELECT AVG(field) portion of a query
353 
	 *
354 
	 * @param	string	the field
355 
	 * @param	string	an alias
356 
	 * @return	CI_DB_query_builder
357 
	 */
358 
	public function select_avg($select = '', $alias = '')
359 
	{
360 
		return $this->_max_min_avg_sum($select, $alias, 'AVG');
361 
	}
362 
 
363 
	// --------------------------------------------------------------------
364 
 
365 
	/**
366 
	 * Select Sum
367 
	 *
368 
	 * Generates a SELECT SUM(field) portion of a query
369 
	 *
370 
	 * @param	string	the field
371 
	 * @param	string	an alias
372 
	 * @return	CI_DB_query_builder
373 
	 */
374 
	public function select_sum($select = '', $alias = '')
375 
	{
376 
		return $this->_max_min_avg_sum($select, $alias, 'SUM');
377 
	}
378 
 
379 
	// --------------------------------------------------------------------
380 
 
381 
	/**
382 
	 * SELECT [MAX|MIN|AVG|SUM]()
383 
	 *
384 
	 * @used-by	select_max()
385 
	 * @used-by	select_min()
386 
	 * @used-by	select_avg()
387 
	 * @used-by	select_sum()
388 
	 *
389 
	 * @param	string	$select	Field name
390 
	 * @param	string	$alias
391 
	 * @param	string	$type
392 
	 * @return	CI_DB_query_builder
393 
	 */
394 
	protected function _max_min_avg_sum($select = '', $alias = '', $type = 'MAX')
395 
	{
396 
		if ( ! is_string($select) OR $select === '')
397 
		{
398 
			$this->display_error('db_invalid_query');
399 
		}
400 
 
401 
		$type = strtoupper($type);
402 
 
403 
		if ( ! in_array($type, array('MAX', 'MIN', 'AVG', 'SUM')))
404 
		{
405 
			show_error('Invalid function type: '.$type);
406 
		}
407 
 
408 
		if ($alias === '')
409 
		{
410 
			$alias = $this->_create_alias_from_table(trim($select));
411 
		}
412 
 
413 
		$sql = $type.'('.$this->protect_identifiers(trim($select)).') AS '.$this->escape_identifiers(trim($alias));
414 
 
415 
		$this->qb_select[] = $sql;
416 
		$this->qb_no_escape[] = NULL;
417 
 
418 
		if ($this->qb_caching === TRUE)
419 
		{
420 
			$this->qb_cache_select[] = $sql;
421 
			$this->qb_cache_exists[] = 'select';
422 
		}
423 
 
424 
		return $this;
425 
	}
426 
 
427 
	// --------------------------------------------------------------------
428 
 
429 
	/**
430 
	 * Determines the alias name based on the table
431 
	 *
432 
	 * @param	string	$item
433 
	 * @return	string
434 
	 */
435 
	protected function _create_alias_from_table($item)
436 
	{
437 
		if (strpos($item, '.') !== FALSE)
438 
		{
439 
			$item = explode('.', $item);
440 
			return end($item);
441 
		}
442 
 
443 
		return $item;
444 
	}
445 
 
446 
	// --------------------------------------------------------------------
447 
 
448 
	/**
449 
	 * DISTINCT
450 
	 *
451 
	 * Sets a flag which tells the query string compiler to add DISTINCT
452 
	 *
453 
	 * @param	bool	$val
454 
	 * @return	CI_DB_query_builder
455 
	 */
456 
	public function distinct($val = TRUE)
457 
	{
458 
		$this->qb_distinct = is_bool($val) ? $val : TRUE;
459 
		return $this;
460 
	}
461 
 
462 
	// --------------------------------------------------------------------
463 
 
464 
	/**
465 
	 * From
466 
	 *
467 
	 * Generates the FROM portion of the query
468 
	 *
469 
	 * @param	mixed	$from	can be a string or array
470 
	 * @return	CI_DB_query_builder
471 
	 */
472 
	public function from($from)
473 
	{
474 
		foreach ((array) $from as $val)
475 
		{
476 
			if (strpos($val, ',') !== FALSE)
477 
			{
478 
				foreach (explode(',', $val) as $v)
479 
				{
480 
					$v = trim($v);
481 
					$this->_track_aliases($v);
482 
 
483 
					$this->qb_from[] = $v = $this->protect_identifiers($v, TRUE, NULL, FALSE);
484 
 
485 
					if ($this->qb_caching === TRUE)
486 
					{
487 
						$this->qb_cache_from[] = $v;
488 
						$this->qb_cache_exists[] = 'from';
489 
					}
490 
				}
491 
			}
492 
			else
493 
			{
494 
				$val = trim($val);
495 
 
496 
				// Extract any aliases that might exist. We use this information
497 
				// in the protect_identifiers to know whether to add a table prefix
498 
				$this->_track_aliases($val);
499 
 
500 
				$this->qb_from[] = $val = $this->protect_identifiers($val, TRUE, NULL, FALSE);
501 
 
502 
				if ($this->qb_caching === TRUE)
503 
				{
504 
					$this->qb_cache_from[] = $val;
505 
					$this->qb_cache_exists[] = 'from';
506 
				}
507 
			}
508 
		}
509 
 
510 
		return $this;
511 
	}
512 
 
513 
	// --------------------------------------------------------------------
514 
 
515 
	/**
516 
	 * JOIN
517 
	 *
518 
	 * Generates the JOIN portion of the query
519 
	 *
520 
	 * @param	string
521 
	 * @param	string	the join condition
522 
	 * @param	string	the type of join
523 
	 * @param	string	whether not to try to escape identifiers
524 
	 * @return	CI_DB_query_builder
525 
	 */
526 
	public function join($table, $cond, $type = '', $escape = NULL)
527 
	{
528 
		if ($type !== '')
529 
		{
530 
			$type = strtoupper(trim($type));
531 
 
532 
			if ( ! in_array($type, array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER'), TRUE))
533 
			{
534 
				$type = '';
535 
			}
536 
			else
537 
			{
538 
				$type .= ' ';
539 
			}
540 
		}
541 
 
542 
		// Extract any aliases that might exist. We use this information
543 
		// in the protect_identifiers to know whether to add a table prefix
544 
		$this->_track_aliases($table);
545 
 
546 
		is_bool($escape) OR $escape = $this->_protect_identifiers;
547 
 
548 
		if ( ! $this->_has_operator($cond))
549 
		{
550 
			$cond = ' USING ('.($escape ? $this->escape_identifiers($cond) : $cond).')';
551 
		}
552 
		elseif ($escape === FALSE)
553 
		{
554 
			$cond = ' ON '.$cond;
555 
		}
556 
		else
557 
		{
558 
			// Split multiple conditions
559 
			if (preg_match_all('/\sAND\s|\sOR\s/i', $cond, $joints, PREG_OFFSET_CAPTURE))
560 
			{
561 
				$conditions = array();
562 
				$joints = $joints[0];
563 
				array_unshift($joints, array('', 0));
564 
 
565 
				for ($i = count($joints) - 1, $pos = strlen($cond); $i >= 0; $i--)
566 
				{
567 
					$joints[$i][1] += strlen($joints[$i][0]); // offset
568 
					$conditions[$i] = substr($cond, $joints[$i][1], $pos - $joints[$i][1]);
569 
					$pos = $joints[$i][1] - strlen($joints[$i][0]);
570 
					$joints[$i] = $joints[$i][0];
571 
				}
572 
			}
573 
			else
574 
			{
575 
				$conditions = array($cond);
576 
				$joints = array('');
577 
			}
578 
 
579 
			$cond = ' ON ';
580 
			for ($i = 0, $c = count($conditions); $i < $c; $i++)
581 
			{
582 
				$operator = $this->_get_operator($conditions[$i]);
583 
				$cond .= $joints[$i];
584 
				$cond .= preg_match("/(\(*)?([\[\]\w\.'-]+)".preg_quote($operator)."(.*)/i", $conditions[$i], $match)
585 
					? $match[1].$this->protect_identifiers($match[2]).$operator.$this->protect_identifiers($match[3])
586 
					: $conditions[$i];
587 
			}
588 
		}
589 
 
590 
		// Do we want to escape the table name?
591 
		if ($escape === TRUE)
592 
		{
593 
			$table = $this->protect_identifiers($table, TRUE, NULL, FALSE);
594 
		}
595 
 
596 
		// Assemble the JOIN statement
597 
		$this->qb_join[] = $join = $type.'JOIN '.$table.$cond;
598 
 
599 
		if ($this->qb_caching === TRUE)
600 
		{
601 
			$this->qb_cache_join[] = $join;
602 
			$this->qb_cache_exists[] = 'join';
603 
		}
604 
 
605 
		return $this;
606 
	}
607 
 
608 
	// --------------------------------------------------------------------
609 
 
610 
	/**
611 
	 * WHERE
612 
	 *
613 
	 * Generates the WHERE portion of the query.
614 
	 * Separates multiple calls with 'AND'.
615 
	 *
616 
	 * @param	mixed
617 
	 * @param	mixed
618 
	 * @param	bool
619 
	 * @return	CI_DB_query_builder
620 
	 */
621 
	public function where($key, $value = NULL, $escape = NULL)
622 
	{
623 
		return $this->_wh('qb_where', $key, $value, 'AND ', $escape);
624 
	}
625 
 
626 
	// --------------------------------------------------------------------
627 
 
628 
	/**
629 
	 * OR WHERE
630 
	 *
631 
	 * Generates the WHERE portion of the query.
632 
	 * Separates multiple calls with 'OR'.
633 
	 *
634 
	 * @param	mixed
635 
	 * @param	mixed
636 
	 * @param	bool
637 
	 * @return	CI_DB_query_builder
638 
	 */
639 
	public function or_where($key, $value = NULL, $escape = NULL)
640 
	{
641 
		return $this->_wh('qb_where', $key, $value, 'OR ', $escape);
642 
	}
643 
 
644 
	// --------------------------------------------------------------------
645 
 
646 
	/**
647 
	 * WHERE, HAVING
648 
	 *
649 
	 * @used-by	where()
650 
	 * @used-by	or_where()
651 
	 * @used-by	having()
652 
	 * @used-by	or_having()
653 
	 *
654 
	 * @param	string	$qb_key	'qb_where' or 'qb_having'
655 
	 * @param	mixed	$key
656 
	 * @param	mixed	$value
657 
	 * @param	string	$type
658 
	 * @param	bool	$escape
659 
	 * @return	CI_DB_query_builder
660 
	 */
661 
	protected function _wh($qb_key, $key, $value = NULL, $type = 'AND ', $escape = NULL)
662 
	{
663 
		$qb_cache_key = ($qb_key === 'qb_having') ? 'qb_cache_having' : 'qb_cache_where';
664 
 
665 
		if ( ! is_array($key))
666 
		{
667 
			$key = array($key => $value);
668 
		}
669 
 
670 
		// If the escape value was not set will base it on the global setting
671 
		is_bool($escape) OR $escape = $this->_protect_identifiers;
672 
 
673 
		foreach ($key as $k => $v)
674 
		{
675 
			$prefix = (count($this->$qb_key) === 0 && count($this->$qb_cache_key) === 0)
676 
				? $this->_group_get_type('')
677 
				: $this->_group_get_type($type);
678 
 
679 
			if ($v !== NULL)
680 
			{
681 
				if ($escape === TRUE)
682 
				{
2257 lars 683 
					$v = $this->escape($v);
68 lars 684 
				}
685 
 
686 
				if ( ! $this->_has_operator($k))
687 
				{
688 
					$k .= ' = ';
689 
				}
690 
			}
691 
			elseif ( ! $this->_has_operator($k))
692 
			{
693 
				// value appears not to have been set, assign the test to IS NULL
694 
				$k .= ' IS NULL';
695 
			}
1257 lars 696 
			elseif (preg_match('/\s*(!?=|<>|\sIS(?:\s+NOT)?\s)\s*$/i', $k, $match, PREG_OFFSET_CAPTURE))
68 lars 697 
			{
698 
				$k = substr($k, 0, $match[0][1]).($match[1][0] === '=' ? ' IS NULL' : ' IS NOT NULL');
699 
			}
700 
 
2257 lars 701 
			${$qb_key} = array('condition' => $prefix.$k, 'value' => $v, 'escape' => $escape);
702 
			$this->{$qb_key}[] = ${$qb_key};
68 lars 703 
			if ($this->qb_caching === TRUE)
704 
			{
2257 lars 705 
				$this->{$qb_cache_key}[] = ${$qb_key};
68 lars 706 
				$this->qb_cache_exists[] = substr($qb_key, 3);
707 
			}
708 
 
709 
		}
710 
 
711 
		return $this;
712 
	}
713 
 
714 
	// --------------------------------------------------------------------
715 
 
716 
	/**
717 
	 * WHERE IN
718 
	 *
719 
	 * Generates a WHERE field IN('item', 'item') SQL query,
720 
	 * joined with 'AND' if appropriate.
721 
	 *
722 
	 * @param	string	$key	The field to search
723 
	 * @param	array	$values	The values searched on
724 
	 * @param	bool	$escape
725 
	 * @return	CI_DB_query_builder
726 
	 */
727 
	public function where_in($key = NULL, $values = NULL, $escape = NULL)
728 
	{
729 
		return $this->_where_in($key, $values, FALSE, 'AND ', $escape);
730 
	}
731 
 
732 
	// --------------------------------------------------------------------
733 
 
734 
	/**
735 
	 * OR WHERE IN
736 
	 *
737 
	 * Generates a WHERE field IN('item', 'item') SQL query,
738 
	 * joined with 'OR' if appropriate.
739 
	 *
740 
	 * @param	string	$key	The field to search
741 
	 * @param	array	$values	The values searched on
742 
	 * @param	bool	$escape
743 
	 * @return	CI_DB_query_builder
744 
	 */
745 
	public function or_where_in($key = NULL, $values = NULL, $escape = NULL)
746 
	{
747 
		return $this->_where_in($key, $values, FALSE, 'OR ', $escape);
748 
	}
749 
 
750 
	// --------------------------------------------------------------------
751 
 
752 
	/**
753 
	 * WHERE NOT IN
754 
	 *
755 
	 * Generates a WHERE field NOT IN('item', 'item') SQL query,
756 
	 * joined with 'AND' if appropriate.
757 
	 *
758 
	 * @param	string	$key	The field to search
759 
	 * @param	array	$values	The values searched on
760 
	 * @param	bool	$escape
761 
	 * @return	CI_DB_query_builder
762 
	 */
763 
	public function where_not_in($key = NULL, $values = NULL, $escape = NULL)
764 
	{
765 
		return $this->_where_in($key, $values, TRUE, 'AND ', $escape);
766 
	}
767 
 
768 
	// --------------------------------------------------------------------
769 
 
770 
	/**
771 
	 * OR WHERE NOT IN
772 
	 *
773 
	 * Generates a WHERE field NOT IN('item', 'item') SQL query,
774 
	 * joined with 'OR' if appropriate.
775 
	 *
776 
	 * @param	string	$key	The field to search
777 
	 * @param	array	$values	The values searched on
778 
	 * @param	bool	$escape
779 
	 * @return	CI_DB_query_builder
780 
	 */
781 
	public function or_where_not_in($key = NULL, $values = NULL, $escape = NULL)
782 
	{
783 
		return $this->_where_in($key, $values, TRUE, 'OR ', $escape);
784 
	}
785 
 
786 
	// --------------------------------------------------------------------
787 
 
788 
	/**
789 
	 * Internal WHERE IN
790 
	 *
791 
	 * @used-by	where_in()
792 
	 * @used-by	or_where_in()
793 
	 * @used-by	where_not_in()
794 
	 * @used-by	or_where_not_in()
795 
	 *
796 
	 * @param	string	$key	The field to search
797 
	 * @param	array	$values	The values searched on
798 
	 * @param	bool	$not	If the statement would be IN or NOT IN
799 
	 * @param	string	$type
800 
	 * @param	bool	$escape
801 
	 * @return	CI_DB_query_builder
802 
	 */
803 
	protected function _where_in($key = NULL, $values = NULL, $not = FALSE, $type = 'AND ', $escape = NULL)
804 
	{
805 
		if ($key === NULL OR $values === NULL)
806 
		{
807 
			return $this;
808 
		}
809 
 
810 
		if ( ! is_array($values))
811 
		{
812 
			$values = array($values);
813 
		}
814 
 
815 
		is_bool($escape) OR $escape = $this->_protect_identifiers;
816 
 
817 
		$not = ($not) ? ' NOT' : '';
818 
 
819 
		if ($escape === TRUE)
820 
		{
821 
			$where_in = array();
822 
			foreach ($values as $value)
823 
			{
824 
				$where_in[] = $this->escape($value);
825 
			}
826 
		}
827 
		else
828 
		{
829 
			$where_in = array_values($values);
830 
		}
831 
 
832 
		$prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0)
833 
			? $this->_group_get_type('')
834 
			: $this->_group_get_type($type);
835 
 
836 
		$where_in = array(
837 
			'condition' => $prefix.$key.$not.' IN('.implode(', ', $where_in).')',
2257 lars 838 
			'value' => NULL,
68 lars 839 
			'escape' => $escape
840 
		);
841 
 
842 
		$this->qb_where[] = $where_in;
843 
		if ($this->qb_caching === TRUE)
844 
		{
845 
			$this->qb_cache_where[] = $where_in;
846 
			$this->qb_cache_exists[] = 'where';
847 
		}
848 
 
849 
		return $this;
850 
	}
851 
 
852 
	// --------------------------------------------------------------------
853 
 
854 
	/**
855 
	 * LIKE
856 
	 *
857 
	 * Generates a %LIKE% portion of the query.
858 
	 * Separates multiple calls with 'AND'.
859 
	 *
860 
	 * @param	mixed	$field
861 
	 * @param	string	$match
862 
	 * @param	string	$side
863 
	 * @param	bool	$escape
864 
	 * @return	CI_DB_query_builder
865 
	 */
866 
	public function like($field, $match = '', $side = 'both', $escape = NULL)
867 
	{
868 
		return $this->_like($field, $match, 'AND ', $side, '', $escape);
869 
	}
870 
 
871 
	// --------------------------------------------------------------------
872 
 
873 
	/**
874 
	 * NOT LIKE
875 
	 *
876 
	 * Generates a NOT LIKE portion of the query.
877 
	 * Separates multiple calls with 'AND'.
878 
	 *
879 
	 * @param	mixed	$field
880 
	 * @param	string	$match
881 
	 * @param	string	$side
882 
	 * @param	bool	$escape
883 
	 * @return	CI_DB_query_builder
884 
	 */
885 
	public function not_like($field, $match = '', $side = 'both', $escape = NULL)
886 
	{
887 
		return $this->_like($field, $match, 'AND ', $side, 'NOT', $escape);
888 
	}
889 
 
890 
	// --------------------------------------------------------------------
891 
 
892 
	/**
893 
	 * OR LIKE
894 
	 *
895 
	 * Generates a %LIKE% portion of the query.
896 
	 * Separates multiple calls with 'OR'.
897 
	 *
898 
	 * @param	mixed	$field
899 
	 * @param	string	$match
900 
	 * @param	string	$side
901 
	 * @param	bool	$escape
902 
	 * @return	CI_DB_query_builder
903 
	 */
904 
	public function or_like($field, $match = '', $side = 'both', $escape = NULL)
905 
	{
906 
		return $this->_like($field, $match, 'OR ', $side, '', $escape);
907 
	}
908 
 
909 
	// --------------------------------------------------------------------
910 
 
911 
	/**
912 
	 * OR NOT LIKE
913 
	 *
914 
	 * Generates a NOT LIKE portion of the query.
915 
	 * Separates multiple calls with 'OR'.
916 
	 *
917 
	 * @param	mixed	$field
918 
	 * @param	string	$match
919 
	 * @param	string	$side
920 
	 * @param	bool	$escape
921 
	 * @return	CI_DB_query_builder
922 
	 */
923 
	public function or_not_like($field, $match = '', $side = 'both', $escape = NULL)
924 
	{
925 
		return $this->_like($field, $match, 'OR ', $side, 'NOT', $escape);
926 
	}
927 
 
928 
	// --------------------------------------------------------------------
929 
 
930 
	/**
931 
	 * Internal LIKE
932 
	 *
933 
	 * @used-by	like()
934 
	 * @used-by	or_like()
935 
	 * @used-by	not_like()
936 
	 * @used-by	or_not_like()
937 
	 *
938 
	 * @param	mixed	$field
939 
	 * @param	string	$match
940 
	 * @param	string	$type
941 
	 * @param	string	$side
942 
	 * @param	string	$not
943 
	 * @param	bool	$escape
944 
	 * @return	CI_DB_query_builder
945 
	 */
946 
	protected function _like($field, $match = '', $type = 'AND ', $side = 'both', $not = '', $escape = NULL)
947 
	{
948 
		if ( ! is_array($field))
949 
		{
950 
			$field = array($field => $match);
951 
		}
952 
 
953 
		is_bool($escape) OR $escape = $this->_protect_identifiers;
954 
		// lowercase $side in case somebody writes e.g. 'BEFORE' instead of 'before' (doh)
955 
		$side = strtolower($side);
956 
 
957 
		foreach ($field as $k => $v)
958 
		{
959 
			$prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0)
960 
				? $this->_group_get_type('') : $this->_group_get_type($type);
961 
 
962 
			if ($escape === TRUE)
963 
			{
964 
				$v = $this->escape_like_str($v);
965 
			}
966 
 
2257 lars 967 
			switch ($side)
68 lars 968 
			{
2257 lars 969 
				case 'none':
970 
					$v = "'{$v}'";
971 
					break;
972 
				case 'before':
973 
					$v = "%'{$v}'";
974 
					break;
975 
				case 'after':
976 
					$v = "'{$v}%'";
977 
					break;
978 
				case 'both':
979 
				default:
980 
					$v = "'%{$v}%'";
981 
					break;
68 lars 982 
			}
983 
 
984 
			// some platforms require an escape sequence definition for LIKE wildcards
985 
			if ($escape === TRUE && $this->_like_escape_str !== '')
986 
			{
2257 lars 987 
				$v .= sprintf($this->_like_escape_str, $this->_like_escape_chr);
68 lars 988 
			}
989 
 
2257 lars 990 
			$qb_where = array('condition' => "{$prefix} {$k} {$not} LIKE", 'value' => $v, 'escape' => $escape);
991 
			$this->qb_where[] = $qb_where;
68 lars 992 
			if ($this->qb_caching === TRUE)
993 
			{
2257 lars 994 
				$this->qb_cache_where[] = $qb_where;
68 lars 995 
				$this->qb_cache_exists[] = 'where';
996 
			}
997 
		}
998 
 
999 
		return $this;
1000 
	}
1001 
 
1002 
	// --------------------------------------------------------------------
1003 
 
1004 
	/**
1005 
	 * Starts a query group.
1006 
	 *
1007 
	 * @param	string	$not	(Internal use only)
1008 
	 * @param	string	$type	(Internal use only)
1009 
	 * @return	CI_DB_query_builder
1010 
	 */
1011 
	public function group_start($not = '', $type = 'AND ')
1012 
	{
1013 
		$type = $this->_group_get_type($type);
1014 
 
1015 
		$this->qb_where_group_started = TRUE;
1016 
		$prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) ? '' : $type;
1017 
		$where = array(
1018 
			'condition' => $prefix.$not.str_repeat(' ', ++$this->qb_where_group_count).' (',
2257 lars 1019 
			'value' => NULL,
68 lars 1020 
			'escape' => FALSE
1021 
		);
1022 
 
1023 
		$this->qb_where[] = $where;
1024 
		if ($this->qb_caching)
1025 
		{
1026 
			$this->qb_cache_where[] = $where;
1027 
		}
1028 
 
1029 
		return $this;
1030 
	}
1031 
 
1032 
	// --------------------------------------------------------------------
1033 
 
1034 
	/**
1035 
	 * Starts a query group, but ORs the group
1036 
	 *
1037 
	 * @return	CI_DB_query_builder
1038 
	 */
1039 
	public function or_group_start()
1040 
	{
1041 
		return $this->group_start('', 'OR ');
1042 
	}
1043 
 
1044 
	// --------------------------------------------------------------------
1045 
 
1046 
	/**
1047 
	 * Starts a query group, but NOTs the group
1048 
	 *
1049 
	 * @return	CI_DB_query_builder
1050 
	 */
1051 
	public function not_group_start()
1052 
	{
1053 
		return $this->group_start('NOT ', 'AND ');
1054 
	}
1055 
 
1056 
	// --------------------------------------------------------------------
1057 
 
1058 
	/**
1059 
	 * Starts a query group, but OR NOTs the group
1060 
	 *
1061 
	 * @return	CI_DB_query_builder
1062 
	 */
1063 
	public function or_not_group_start()
1064 
	{
1065 
		return $this->group_start('NOT ', 'OR ');
1066 
	}
1067 
 
1068 
	// --------------------------------------------------------------------
1069 
 
1070 
	/**
1071 
	 * Ends a query group
1072 
	 *
1073 
	 * @return	CI_DB_query_builder
1074 
	 */
1075 
	public function group_end()
1076 
	{
1077 
		$this->qb_where_group_started = FALSE;
1078 
		$where = array(
1079 
			'condition' => str_repeat(' ', $this->qb_where_group_count--).')',
2257 lars 1080 
			'value' => NULL,
68 lars 1081 
			'escape' => FALSE
1082 
		);
1083 
 
1084 
		$this->qb_where[] = $where;
1085 
		if ($this->qb_caching)
1086 
		{
1087 
			$this->qb_cache_where[] = $where;
1088 
		}
1089 
 
1090 
		return $this;
1091 
	}
1092 
 
1093 
	// --------------------------------------------------------------------
1094 
 
1095 
	/**
1096 
	 * Group_get_type
1097 
	 *
1098 
	 * @used-by	group_start()
1099 
	 * @used-by	_like()
1100 
	 * @used-by	_wh()
1101 
	 * @used-by	_where_in()
1102 
	 *
1103 
	 * @param	string	$type
1104 
	 * @return	string
1105 
	 */
1106 
	protected function _group_get_type($type)
1107 
	{
1108 
		if ($this->qb_where_group_started)
1109 
		{
1110 
			$type = '';
1111 
			$this->qb_where_group_started = FALSE;
1112 
		}
1113 
 
1114 
		return $type;
1115 
	}
1116 
 
1117 
	// --------------------------------------------------------------------
1118 
 
1119 
	/**
1120 
	 * GROUP BY
1121 
	 *
1122 
	 * @param	string	$by
1123 
	 * @param	bool	$escape
1124 
	 * @return	CI_DB_query_builder
1125 
	 */
1126 
	public function group_by($by, $escape = NULL)
1127 
	{
1128 
		is_bool($escape) OR $escape = $this->_protect_identifiers;
1129 
 
1130 
		if (is_string($by))
1131 
		{
1132 
			$by = ($escape === TRUE)
1133 
				? explode(',', $by)
1134 
				: array($by);
1135 
		}
1136 
 
1137 
		foreach ($by as $val)
1138 
		{
1139 
			$val = trim($val);
1140 
 
1141 
			if ($val !== '')
1142 
			{
1143 
				$val = array('field' => $val, 'escape' => $escape);
1144 
 
1145 
				$this->qb_groupby[] = $val;
1146 
				if ($this->qb_caching === TRUE)
1147 
				{
1148 
					$this->qb_cache_groupby[] = $val;
1149 
					$this->qb_cache_exists[] = 'groupby';
1150 
				}
1151 
			}
1152 
		}
1153 
 
1154 
		return $this;
1155 
	}
1156 
 
1157 
	// --------------------------------------------------------------------
1158 
 
1159 
	/**
1160 
	 * HAVING
1161 
	 *
1162 
	 * Separates multiple calls with 'AND'.
1163 
	 *
1164 
	 * @param	string	$key
1165 
	 * @param	string	$value
1166 
	 * @param	bool	$escape
1167 
	 * @return	CI_DB_query_builder
1168 
	 */
1169 
	public function having($key, $value = NULL, $escape = NULL)
1170 
	{
1171 
		return $this->_wh('qb_having', $key, $value, 'AND ', $escape);
1172 
	}
1173 
 
1174 
	// --------------------------------------------------------------------
1175 
 
1176 
	/**
1177 
	 * OR HAVING
1178 
	 *
1179 
	 * Separates multiple calls with 'OR'.
1180 
	 *
1181 
	 * @param	string	$key
1182 
	 * @param	string	$value
1183 
	 * @param	bool	$escape
1184 
	 * @return	CI_DB_query_builder
1185 
	 */
1186 
	public function or_having($key, $value = NULL, $escape = NULL)
1187 
	{
1188 
		return $this->_wh('qb_having', $key, $value, 'OR ', $escape);
1189 
	}
1190 
 
1191 
	// --------------------------------------------------------------------
1192 
 
1193 
	/**
1194 
	 * ORDER BY
1195 
	 *
1196 
	 * @param	string	$orderby
1197 
	 * @param	string	$direction	ASC, DESC or RANDOM
1198 
	 * @param	bool	$escape
1199 
	 * @return	CI_DB_query_builder
1200 
	 */
1201 
	public function order_by($orderby, $direction = '', $escape = NULL)
1202 
	{
1203 
		$direction = strtoupper(trim($direction));
1204 
 
1205 
		if ($direction === 'RANDOM')
1206 
		{
1207 
			$direction = '';
1208 
 
1209 
			// Do we have a seed value?
1210 
			$orderby = ctype_digit((string) $orderby)
1211 
				? sprintf($this->_random_keyword[1], $orderby)
1212 
				: $this->_random_keyword[0];
1213 
		}
1214 
		elseif (empty($orderby))
1215 
		{
1216 
			return $this;
1217 
		}
1218 
		elseif ($direction !== '')
1219 
		{
1220 
			$direction = in_array($direction, array('ASC', 'DESC'), TRUE) ? ' '.$direction : '';
1221 
		}
1222 
 
1223 
		is_bool($escape) OR $escape = $this->_protect_identifiers;
1224 
 
1225 
		if ($escape === FALSE)
1226 
		{
1227 
			$qb_orderby[] = array('field' => $orderby, 'direction' => $direction, 'escape' => FALSE);
1228 
		}
1229 
		else
1230 
		{
1231 
			$qb_orderby = array();
1232 
			foreach (explode(',', $orderby) as $field)
1233 
			{
1234 
				$qb_orderby[] = ($direction === '' && preg_match('/\s+(ASC|DESC)$/i', rtrim($field), $match, PREG_OFFSET_CAPTURE))
1235 
					? array('field' => ltrim(substr($field, 0, $match[0][1])), 'direction' => ' '.$match[1][0], 'escape' => TRUE)
1236 
					: array('field' => trim($field), 'direction' => $direction, 'escape' => TRUE);
1237 
			}
1238 
		}
1239 
 
1240 
		$this->qb_orderby = array_merge($this->qb_orderby, $qb_orderby);
1241 
		if ($this->qb_caching === TRUE)
1242 
		{
1243 
			$this->qb_cache_orderby = array_merge($this->qb_cache_orderby, $qb_orderby);
1244 
			$this->qb_cache_exists[] = 'orderby';
1245 
		}
1246 
 
1247 
		return $this;
1248 
	}
1249 
 
1250 
	// --------------------------------------------------------------------
1251 
 
1252 
	/**
1253 
	 * LIMIT
1254 
	 *
1255 
	 * @param	int	$value	LIMIT value
1256 
	 * @param	int	$offset	OFFSET value
1257 
	 * @return	CI_DB_query_builder
1258 
	 */
1259 
	public function limit($value, $offset = 0)
1260 
	{
1261 
		is_null($value) OR $this->qb_limit = (int) $value;
1262 
		empty($offset) OR $this->qb_offset = (int) $offset;
1263 
 
1264 
		return $this;
1265 
	}
1266 
 
1267 
	// --------------------------------------------------------------------
1268 
 
1269 
	/**
1270 
	 * Sets the OFFSET value
1271 
	 *
1272 
	 * @param	int	$offset	OFFSET value
1273 
	 * @return	CI_DB_query_builder
1274 
	 */
1275 
	public function offset($offset)
1276 
	{
1277 
		empty($offset) OR $this->qb_offset = (int) $offset;
1278 
		return $this;
1279 
	}
1280 
 
1281 
	// --------------------------------------------------------------------
1282 
 
1283 
	/**
1284 
	 * LIMIT string
1285 
	 *
1286 
	 * Generates a platform-specific LIMIT clause.
1287 
	 *
1288 
	 * @param	string	$sql	SQL Query
1289 
	 * @return	string
1290 
	 */
1291 
	protected function _limit($sql)
1292 
	{
1257 lars 1293 
		return $sql.' LIMIT '.($this->qb_offset ? $this->qb_offset.', ' : '').(int) $this->qb_limit;
68 lars 1294 
	}
1295 
 
1296 
	// --------------------------------------------------------------------
1297 
 
1298 
	/**
1299 
	 * The "set" function.
1300 
	 *
1301 
	 * Allows key/value pairs to be set for inserting or updating
1302 
	 *
1303 
	 * @param	mixed
1304 
	 * @param	string
1305 
	 * @param	bool
1306 
	 * @return	CI_DB_query_builder
1307 
	 */
1308 
	public function set($key, $value = '', $escape = NULL)
1309 
	{
1310 
		$key = $this->_object_to_array($key);
1311 
 
1312 
		if ( ! is_array($key))
1313 
		{
1314 
			$key = array($key => $value);
1315 
		}
1316 
 
1317 
		is_bool($escape) OR $escape = $this->_protect_identifiers;
1318 
 
1319 
		foreach ($key as $k => $v)
1320 
		{
1321 
			$this->qb_set[$this->protect_identifiers($k, FALSE, $escape)] = ($escape)
1322 
				? $this->escape($v) : $v;
1323 
		}
1324 
 
1325 
		return $this;
1326 
	}
1327 
 
1328 
	// --------------------------------------------------------------------
1329 
 
1330 
	/**
1331 
	 * Get SELECT query string
1332 
	 *
1333 
	 * Compiles a SELECT query string and returns the sql.
1334 
	 *
1335 
	 * @param	string	the table name to select from (optional)
1336 
	 * @param	bool	TRUE: resets QB values; FALSE: leave QB values alone
1337 
	 * @return	string
1338 
	 */
1339 
	public function get_compiled_select($table = '', $reset = TRUE)
1340 
	{
1341 
		if ($table !== '')
1342 
		{
1343 
			$this->_track_aliases($table);
1344 
			$this->from($table);
1345 
		}
1346 
 
1347 
		$select = $this->_compile_select();
1348 
 
1349 
		if ($reset === TRUE)
1350 
		{
1351 
			$this->_reset_select();
1352 
		}
1353 
 
1354 
		return $select;
1355 
	}
1356 
 
1357 
	// --------------------------------------------------------------------
1358 
 
1359 
	/**
1360 
	 * Get
1361 
	 *
1362 
	 * Compiles the select statement based on the other functions called
1363 
	 * and runs the query
1364 
	 *
1365 
	 * @param	string	the table
1366 
	 * @param	string	the limit clause
1367 
	 * @param	string	the offset clause
1368 
	 * @return	CI_DB_result
1369 
	 */
1370 
	public function get($table = '', $limit = NULL, $offset = NULL)
1371 
	{
1372 
		if ($table !== '')
1373 
		{
1374 
			$this->_track_aliases($table);
1375 
			$this->from($table);
1376 
		}
1377 
 
1378 
		if ( ! empty($limit))
1379 
		{
1380 
			$this->limit($limit, $offset);
1381 
		}
1382 
 
1383 
		$result = $this->query($this->_compile_select());
1384 
		$this->_reset_select();
1385 
		return $result;
1386 
	}
1387 
 
1388 
	// --------------------------------------------------------------------
1389 
 
1390 
	/**
1391 
	 * "Count All Results" query
1392 
	 *
1393 
	 * Generates a platform-specific query string that counts all records
1394 
	 * returned by an Query Builder query.
1395 
	 *
1396 
	 * @param	string
1397 
	 * @param	bool	the reset clause
1398 
	 * @return	int
1399 
	 */
1400 
	public function count_all_results($table = '', $reset = TRUE)
1401 
	{
1402 
		if ($table !== '')
1403 
		{
1404 
			$this->_track_aliases($table);
1405 
			$this->from($table);
1406 
		}
1407 
 
1408 
		// ORDER BY usage is often problematic here (most notably
1409 
		// on Microsoft SQL Server) and ultimately unnecessary
1410 
		// for selecting COUNT(*) ...
2107 lars 1411 
		$qb_orderby       = $this->qb_orderby;
1412 
		$qb_cache_orderby = $this->qb_cache_orderby;
2257 lars 1413 
		$this->qb_orderby = $this->qb_cache_orderby = array();
68 lars 1414 
 
2107 lars 1415 
		$result = ($this->qb_distinct === TRUE OR ! empty($this->qb_groupby) OR ! empty($this->qb_cache_groupby) OR $this->qb_limit OR $this->qb_offset)
68 lars 1416 
			? $this->query($this->_count_string.$this->protect_identifiers('numrows')."\nFROM (\n".$this->_compile_select()."\n) CI_count_all_results")
1417 
			: $this->query($this->_compile_select($this->_count_string.$this->protect_identifiers('numrows')));
1418 
 
1419 
		if ($reset === TRUE)
1420 
		{
1421 
			$this->_reset_select();
1422 
		}
2107 lars 1423 
		else
68 lars 1424 
		{
2107 lars 1425 
			$this->qb_orderby       = $qb_orderby;
1426 
			$this->qb_cache_orderby = $qb_cache_orderby;
68 lars 1427 
		}
1428 
 
1429 
		if ($result->num_rows() === 0)
1430 
		{
1431 
			return 0;
1432 
		}
1433 
 
1434 
		$row = $result->row();
1435 
		return (int) $row->numrows;
1436 
	}
1437 
 
1438 
	// --------------------------------------------------------------------
1439 
 
1440 
	/**
2257 lars 1441 
	 * get_where()
68 lars 1442 
	 *
1443 
	 * Allows the where clause, limit and offset to be added directly
1444 
	 *
1445 
	 * @param	string	$table
1446 
	 * @param	string	$where
1447 
	 * @param	int	$limit
1448 
	 * @param	int	$offset
1449 
	 * @return	CI_DB_result
1450 
	 */
1451 
	public function get_where($table = '', $where = NULL, $limit = NULL, $offset = NULL)
1452 
	{
1453 
		if ($table !== '')
1454 
		{
1455 
			$this->from($table);
1456 
		}
1457 
 
1458 
		if ($where !== NULL)
1459 
		{
1460 
			$this->where($where);
1461 
		}
1462 
 
1463 
		if ( ! empty($limit))
1464 
		{
1465 
			$this->limit($limit, $offset);
1466 
		}
1467 
 
1468 
		$result = $this->query($this->_compile_select());
1469 
		$this->_reset_select();
1470 
		return $result;
1471 
	}
1472 
 
1473 
	// --------------------------------------------------------------------
1474 
 
1475 
	/**
1476 
	 * Insert_Batch
1477 
	 *
1478 
	 * Compiles batch insert strings and runs the queries
1479 
	 *
1480 
	 * @param	string	$table	Table to insert into
1481 
	 * @param	array	$set 	An associative array of insert values
1482 
	 * @param	bool	$escape	Whether to escape values and identifiers
1483 
	 * @return	int	Number of rows inserted or FALSE on failure
1484 
	 */
1485 
	public function insert_batch($table, $set = NULL, $escape = NULL, $batch_size = 100)
1486 
	{
1487 
		if ($set === NULL)
1488 
		{
1489 
			if (empty($this->qb_set))
1490 
			{
1491 
				return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE;
1492 
			}
1493 
		}
1494 
		else
1495 
		{
1496 
			if (empty($set))
1497 
			{
1498 
				return ($this->db_debug) ? $this->display_error('insert_batch() called with no data') : FALSE;
1499 
			}
1500 
 
1501 
			$this->set_insert_batch($set, '', $escape);
1502 
		}
1503 
 
1504 
		if (strlen($table) === 0)
1505 
		{
1506 
			if ( ! isset($this->qb_from[0]))
1507 
			{
1508 
				return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE;
1509 
			}
1510 
 
1511 
			$table = $this->qb_from[0];
1512 
		}
1513 
 
1514 
		// Batch this baby
1515 
		$affected_rows = 0;
1516 
		for ($i = 0, $total = count($this->qb_set); $i < $total; $i += $batch_size)
1517 
		{
1518 
			if ($this->query($this->_insert_batch($this->protect_identifiers($table, TRUE, $escape, FALSE), $this->qb_keys, array_slice($this->qb_set, $i, $batch_size))))
1519 
			{
1520 
				$affected_rows += $this->affected_rows();
1521 
			}
1522 
		}
1523 
 
1524 
		$this->_reset_write();
1525 
		return $affected_rows;
1526 
	}
1527 
 
1528 
	// --------------------------------------------------------------------
1529 
 
1530 
	/**
1531 
	 * Insert batch statement
1532 
	 *
1533 
	 * Generates a platform-specific insert string from the supplied data.
1534 
	 *
1535 
	 * @param	string	$table	Table name
1536 
	 * @param	array	$keys	INSERT keys
1537 
	 * @param	array	$values	INSERT values
1538 
	 * @return	string
1539 
	 */
1540 
	protected function _insert_batch($table, $keys, $values)
1541 
	{
1542 
		return 'INSERT INTO '.$table.' ('.implode(', ', $keys).') VALUES '.implode(', ', $values);
1543 
	}
1544 
 
1545 
	// --------------------------------------------------------------------
1546 
 
1547 
	/**
1548 
	 * The "set_insert_batch" function.  Allows key/value pairs to be set for batch inserts
1549 
	 *
1550 
	 * @param	mixed
1551 
	 * @param	string
1552 
	 * @param	bool
1553 
	 * @return	CI_DB_query_builder
1554 
	 */
1555 
	public function set_insert_batch($key, $value = '', $escape = NULL)
1556 
	{
1557 
		$key = $this->_object_to_array_batch($key);
1558 
 
1559 
		if ( ! is_array($key))
1560 
		{
1561 
			$key = array($key => $value);
1562 
		}
1563 
 
1564 
		is_bool($escape) OR $escape = $this->_protect_identifiers;
1565 
 
2049 lars 1566 
		$keys = array_keys($this->_object_to_array(reset($key)));
68 lars 1567 
		sort($keys);
1568 
 
1569 
		foreach ($key as $row)
1570 
		{
1571 
			$row = $this->_object_to_array($row);
1572 
			if (count(array_diff($keys, array_keys($row))) > 0 OR count(array_diff(array_keys($row), $keys)) > 0)
1573 
			{
1574 
				// batch function above returns an error on an empty array
1575 
				$this->qb_set[] = array();
1576 
				return;
1577 
			}
1578 
 
1579 
			ksort($row); // puts $row in the same order as our keys
1580 
 
1581 
			if ($escape !== FALSE)
1582 
			{
1583 
				$clean = array();
1584 
				foreach ($row as $value)
1585 
				{
1586 
					$clean[] = $this->escape($value);
1587 
				}
1588 
 
1589 
				$row = $clean;
1590 
			}
1591 
 
1592 
			$this->qb_set[] = '('.implode(',', $row).')';
1593 
		}
1594 
 
1595 
		foreach ($keys as $k)
1596 
		{
1597 
			$this->qb_keys[] = $this->protect_identifiers($k, FALSE, $escape);
1598 
		}
1599 
 
1600 
		return $this;
1601 
	}
1602 
 
1603 
	// --------------------------------------------------------------------
1604 
 
1605 
	/**
1606 
	 * Get INSERT query string
1607 
	 *
1608 
	 * Compiles an insert query and returns the sql
1609 
	 *
1610 
	 * @param	string	the table to insert into
1611 
	 * @param	bool	TRUE: reset QB values; FALSE: leave QB values alone
1612 
	 * @return	string
1613 
	 */
1614 
	public function get_compiled_insert($table = '', $reset = TRUE)
1615 
	{
1616 
		if ($this->_validate_insert($table) === FALSE)
1617 
		{
1618 
			return FALSE;
1619 
		}
1620 
 
1621 
		$sql = $this->_insert(
1622 
			$this->protect_identifiers(
1623 
				$this->qb_from[0], TRUE, NULL, FALSE
1624 
			),
1625 
			array_keys($this->qb_set),
1626 
			array_values($this->qb_set)
1627 
		);
1628 
 
1629 
		if ($reset === TRUE)
1630 
		{
1631 
			$this->_reset_write();
1632 
		}
1633 
 
1634 
		return $sql;
1635 
	}
1636 
 
1637 
	// --------------------------------------------------------------------
1638 
 
1639 
	/**
1640 
	 * Insert
1641 
	 *
1642 
	 * Compiles an insert string and runs the query
1643 
	 *
1644 
	 * @param	string	the table to insert data into
1645 
	 * @param	array	an associative array of insert values
1646 
	 * @param	bool	$escape	Whether to escape values and identifiers
1647 
	 * @return	bool	TRUE on success, FALSE on failure
1648 
	 */
1649 
	public function insert($table = '', $set = NULL, $escape = NULL)
1650 
	{
1651 
		if ($set !== NULL)
1652 
		{
1653 
			$this->set($set, '', $escape);
1654 
		}
1655 
 
1656 
		if ($this->_validate_insert($table) === FALSE)
1657 
		{
1658 
			return FALSE;
1659 
		}
1660 
 
1661 
		$sql = $this->_insert(
1662 
			$this->protect_identifiers(
1663 
				$this->qb_from[0], TRUE, $escape, FALSE
1664 
			),
1665 
			array_keys($this->qb_set),
1666 
			array_values($this->qb_set)
1667 
		);
1668 
 
1669 
		$this->_reset_write();
1670 
		return $this->query($sql);
1671 
	}
1672 
 
1673 
	// --------------------------------------------------------------------
1674 
 
1675 
	/**
1676 
	 * Validate Insert
1677 
	 *
1678 
	 * This method is used by both insert() and get_compiled_insert() to
1679 
	 * validate that the there data is actually being set and that table
1680 
	 * has been chosen to be inserted into.
1681 
	 *
1682 
	 * @param	string	the table to insert data into
1683 
	 * @return	string
1684 
	 */
1685 
	protected function _validate_insert($table = '')
1686 
	{
1687 
		if (count($this->qb_set) === 0)
1688 
		{
1689 
			return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE;
1690 
		}
1691 
 
1692 
		if ($table !== '')
1693 
		{
1694 
			$this->qb_from[0] = $table;
1695 
		}
1696 
		elseif ( ! isset($this->qb_from[0]))
1697 
		{
1698 
			return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE;
1699 
		}
1700 
 
1701 
		return TRUE;
1702 
	}
1703 
 
1704 
	// --------------------------------------------------------------------
1705 
 
1706 
	/**
1707 
	 * Replace
1708 
	 *
1709 
	 * Compiles an replace into string and runs the query
1710 
	 *
1711 
	 * @param	string	the table to replace data into
1712 
	 * @param	array	an associative array of insert values
1713 
	 * @return	bool	TRUE on success, FALSE on failure
1714 
	 */
1715 
	public function replace($table = '', $set = NULL)
1716 
	{
1717 
		if ($set !== NULL)
1718 
		{
1719 
			$this->set($set);
1720 
		}
1721 
 
1722 
		if (count($this->qb_set) === 0)
1723 
		{
1724 
			return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE;
1725 
		}
1726 
 
1727 
		if ($table === '')
1728 
		{
1729 
			if ( ! isset($this->qb_from[0]))
1730 
			{
1731 
				return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE;
1732 
			}
1733 
 
1734 
			$table = $this->qb_from[0];
1735 
		}
1736 
 
1737 
		$sql = $this->_replace($this->protect_identifiers($table, TRUE, NULL, FALSE), array_keys($this->qb_set), array_values($this->qb_set));
1738 
 
1739 
		$this->_reset_write();
1740 
		return $this->query($sql);
1741 
	}
1742 
 
1743 
	// --------------------------------------------------------------------
1744 
 
1745 
	/**
1746 
	 * Replace statement
1747 
	 *
1748 
	 * Generates a platform-specific replace string from the supplied data
1749 
	 *
1750 
	 * @param	string	the table name
1751 
	 * @param	array	the insert keys
1752 
	 * @param	array	the insert values
1753 
	 * @return	string
1754 
	 */
1755 
	protected function _replace($table, $keys, $values)
1756 
	{
1757 
		return 'REPLACE INTO '.$table.' ('.implode(', ', $keys).') VALUES ('.implode(', ', $values).')';
1758 
	}
1759 
 
1760 
	// --------------------------------------------------------------------
1761 
 
1762 
	/**
1763 
	 * FROM tables
1764 
	 *
1765 
	 * Groups tables in FROM clauses if needed, so there is no confusion
1766 
	 * about operator precedence.
1767 
	 *
1768 
	 * Note: This is only used (and overridden) by MySQL and CUBRID.
1769 
	 *
1770 
	 * @return	string
1771 
	 */
1772 
	protected function _from_tables()
1773 
	{
1774 
		return implode(', ', $this->qb_from);
1775 
	}
1776 
 
1777 
	// --------------------------------------------------------------------
1778 
 
1779 
	/**
1780 
	 * Get UPDATE query string
1781 
	 *
1782 
	 * Compiles an update query and returns the sql
1783 
	 *
1784 
	 * @param	string	the table to update
1785 
	 * @param	bool	TRUE: reset QB values; FALSE: leave QB values alone
1786 
	 * @return	string
1787 
	 */
1788 
	public function get_compiled_update($table = '', $reset = TRUE)
1789 
	{
1790 
		// Combine any cached components with the current statements
1791 
		$this->_merge_cache();
1792 
 
1793 
		if ($this->_validate_update($table) === FALSE)
1794 
		{
1795 
			return FALSE;
1796 
		}
1797 
 
1798 
		$sql = $this->_update($this->qb_from[0], $this->qb_set);
1799 
 
1800 
		if ($reset === TRUE)
1801 
		{
1802 
			$this->_reset_write();
1803 
		}
1804 
 
1805 
		return $sql;
1806 
	}
1807 
 
1808 
	// --------------------------------------------------------------------
1809 
 
1810 
	/**
1811 
	 * UPDATE
1812 
	 *
1813 
	 * Compiles an update string and runs the query.
1814 
	 *
1815 
	 * @param	string	$table
1816 
	 * @param	array	$set	An associative array of update values
1817 
	 * @param	mixed	$where
1818 
	 * @param	int	$limit
1819 
	 * @return	bool	TRUE on success, FALSE on failure
1820 
	 */
1821 
	public function update($table = '', $set = NULL, $where = NULL, $limit = NULL)
1822 
	{
1823 
		// Combine any cached components with the current statements
1824 
		$this->_merge_cache();
1825 
 
1826 
		if ($set !== NULL)
1827 
		{
1828 
			$this->set($set);
1829 
		}
1830 
 
1831 
		if ($this->_validate_update($table) === FALSE)
1832 
		{
1833 
			return FALSE;
1834 
		}
1835 
 
1836 
		if ($where !== NULL)
1837 
		{
1838 
			$this->where($where);
1839 
		}
1840 
 
1841 
		if ( ! empty($limit))
1842 
		{
1843 
			$this->limit($limit);
1844 
		}
1845 
 
1846 
		$sql = $this->_update($this->qb_from[0], $this->qb_set);
1847 
		$this->_reset_write();
1848 
		return $this->query($sql);
1849 
	}
1850 
 
1851 
	// --------------------------------------------------------------------
1852 
 
1853 
	/**
1854 
	 * Validate Update
1855 
	 *
1856 
	 * This method is used by both update() and get_compiled_update() to
1857 
	 * validate that data is actually being set and that a table has been
1858 
	 * chosen to be update.
1859 
	 *
1860 
	 * @param	string	the table to update data on
1861 
	 * @return	bool
1862 
	 */
1863 
	protected function _validate_update($table)
1864 
	{
1865 
		if (count($this->qb_set) === 0)
1866 
		{
1867 
			return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE;
1868 
		}
1869 
 
1870 
		if ($table !== '')
1871 
		{
1872 
			$this->qb_from = array($this->protect_identifiers($table, TRUE, NULL, FALSE));
1873 
		}
1874 
		elseif ( ! isset($this->qb_from[0]))
1875 
		{
1876 
			return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE;
1877 
		}
1878 
 
1879 
		return TRUE;
1880 
	}
1881 
 
1882 
	// --------------------------------------------------------------------
1883 
 
1884 
	/**
1885 
	 * Update_Batch
1886 
	 *
1887 
	 * Compiles an update string and runs the query
1888 
	 *
1889 
	 * @param	string	the table to retrieve the results from
1890 
	 * @param	array	an associative array of update values
1891 
	 * @param	string	the where key
1892 
	 * @return	int	number of rows affected or FALSE on failure
1893 
	 */
1894 
	public function update_batch($table, $set = NULL, $index = NULL, $batch_size = 100)
1895 
	{
1896 
		// Combine any cached components with the current statements
1897 
		$this->_merge_cache();
1898 
 
1899 
		if ($index === NULL)
1900 
		{
1901 
			return ($this->db_debug) ? $this->display_error('db_must_use_index') : FALSE;
1902 
		}
1903 
 
1904 
		if ($set === NULL)
1905 
		{
2049 lars 1906 
			if (empty($this->qb_set_ub))
68 lars 1907 
			{
1908 
				return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE;
1909 
			}
1910 
		}
1911 
		else
1912 
		{
1913 
			if (empty($set))
1914 
			{
1915 
				return ($this->db_debug) ? $this->display_error('update_batch() called with no data') : FALSE;
1916 
			}
1917 
 
1918 
			$this->set_update_batch($set, $index);
1919 
		}
1920 
 
1921 
		if (strlen($table) === 0)
1922 
		{
1923 
			if ( ! isset($this->qb_from[0]))
1924 
			{
1925 
				return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE;
1926 
			}
1927 
 
1928 
			$table = $this->qb_from[0];
1929 
		}
1930 
 
1931 
		// Batch this baby
1932 
		$affected_rows = 0;
2049 lars 1933 
		for ($i = 0, $total = count($this->qb_set_ub); $i < $total; $i += $batch_size)
68 lars 1934 
		{
2049 lars 1935 
			if ($this->query($this->_update_batch($this->protect_identifiers($table, TRUE, NULL, FALSE), array_slice($this->qb_set_ub, $i, $batch_size), $index)))
68 lars 1936 
			{
1937 
				$affected_rows += $this->affected_rows();
1938 
			}
1939 
 
1940 
			$this->qb_where = array();
1941 
		}
1942 
 
1943 
		$this->_reset_write();
1944 
		return $affected_rows;
1945 
	}
1946 
 
1947 
	// --------------------------------------------------------------------
1948 
 
1949 
	/**
1950 
	 * Update_Batch statement
1951 
	 *
1952 
	 * Generates a platform-specific batch update string from the supplied data
1953 
	 *
1954 
	 * @param	string	$table	Table name
1955 
	 * @param	array	$values	Update data
1956 
	 * @param	string	$index	WHERE key
1957 
	 * @return	string
1958 
	 */
1959 
	protected function _update_batch($table, $values, $index)
1960 
	{
1961 
		$ids = array();
1962 
		foreach ($values as $key => $val)
1963 
		{
2049 lars 1964 
			$ids[] = $val[$index]['value'];
68 lars 1965 
 
1966 
			foreach (array_keys($val) as $field)
1967 
			{
1968 
				if ($field !== $index)
1969 
				{
2049 lars 1970 
					$final[$val[$field]['field']][] = 'WHEN '.$val[$index]['field'].' = '.$val[$index]['value'].' THEN '.$val[$field]['value'];
68 lars 1971 
				}
1972 
			}
1973 
		}
1974 
 
1975 
		$cases = '';
1976 
		foreach ($final as $k => $v)
1977 
		{
1978 
			$cases .= $k." = CASE \n"
1979 
				.implode("\n", $v)."\n"
1980 
				.'ELSE '.$k.' END, ';
1981 
		}
1982 
 
2049 lars 1983 
		$this->where($val[$index]['field'].' IN('.implode(',', $ids).')', NULL, FALSE);
68 lars 1984 
 
1985 
		return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_wh('qb_where');
1986 
	}
1987 
 
1988 
	// --------------------------------------------------------------------
1989 
 
1990 
	/**
1991 
	 * The "set_update_batch" function.  Allows key/value pairs to be set for batch updating
1992 
	 *
1993 
	 * @param	array
1994 
	 * @param	string
1995 
	 * @param	bool
1996 
	 * @return	CI_DB_query_builder
1997 
	 */
1998 
	public function set_update_batch($key, $index = '', $escape = NULL)
1999 
	{
2000 
		$key = $this->_object_to_array_batch($key);
2001 
 
2002 
		if ( ! is_array($key))
2003 
		{
2004 
			// @todo error
2005 
		}
2006 
 
2007 
		is_bool($escape) OR $escape = $this->_protect_identifiers;
2008 
 
2009 
		foreach ($key as $k => $v)
2010 
		{
2011 
			$index_set = FALSE;
2012 
			$clean = array();
2013 
			foreach ($v as $k2 => $v2)
2014 
			{
2015 
				if ($k2 === $index)
2016 
				{
2017 
					$index_set = TRUE;
2018 
				}
2019 
 
2049 lars 2020 
				$clean[$k2] = array(
2021 
					'field'  => $this->protect_identifiers($k2, FALSE, $escape),
2022 
					'value'  => ($escape === FALSE ? $v2 : $this->escape($v2))
2023 
				);
68 lars 2024 
			}
2025 
 
2026 
			if ($index_set === FALSE)
2027 
			{
2028 
				return $this->display_error('db_batch_missing_index');
2029 
			}
2030 
 
2049 lars 2031 
			$this->qb_set_ub[] = $clean;
68 lars 2032 
		}
2033 
 
2034 
		return $this;
2035 
	}
2036 
 
2037 
	// --------------------------------------------------------------------
2038 
 
2039 
	/**
2040 
	 * Empty Table
2041 
	 *
2042 
	 * Compiles a delete string and runs "DELETE FROM table"
2043 
	 *
2044 
	 * @param	string	the table to empty
2045 
	 * @return	bool	TRUE on success, FALSE on failure
2046 
	 */
2047 
	public function empty_table($table = '')
2048 
	{
2049 
		if ($table === '')
2050 
		{
2051 
			if ( ! isset($this->qb_from[0]))
2052 
			{
2053 
				return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE;
2054 
			}
2055 
 
2056 
			$table = $this->qb_from[0];
2057 
		}
2058 
		else
2059 
		{
2060 
			$table = $this->protect_identifiers($table, TRUE, NULL, FALSE);
2061 
		}
2062 
 
2063 
		$sql = $this->_delete($table);
2064 
		$this->_reset_write();
2065 
		return $this->query($sql);
2066 
	}
2067 
 
2068 
	// --------------------------------------------------------------------
2069 
 
2070 
	/**
2071 
	 * Truncate
2072 
	 *
2073 
	 * Compiles a truncate string and runs the query
2074 
	 * If the database does not support the truncate() command
2075 
	 * This function maps to "DELETE FROM table"
2076 
	 *
2077 
	 * @param	string	the table to truncate
2078 
	 * @return	bool	TRUE on success, FALSE on failure
2079 
	 */
2080 
	public function truncate($table = '')
2081 
	{
2082 
		if ($table === '')
2083 
		{
2084 
			if ( ! isset($this->qb_from[0]))
2085 
			{
2086 
				return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE;
2087 
			}
2088 
 
2089 
			$table = $this->qb_from[0];
2090 
		}
2091 
		else
2092 
		{
2093 
			$table = $this->protect_identifiers($table, TRUE, NULL, FALSE);
2094 
		}
2095 
 
2096 
		$sql = $this->_truncate($table);
2097 
		$this->_reset_write();
2098 
		return $this->query($sql);
2099 
	}
2100 
 
2101 
	// --------------------------------------------------------------------
2102 
 
2103 
	/**
2104 
	 * Truncate statement
2105 
	 *
2106 
	 * Generates a platform-specific truncate string from the supplied data
2107 
	 *
2108 
	 * If the database does not support the truncate() command,
2109 
	 * then this method maps to 'DELETE FROM table'
2110 
	 *
2111 
	 * @param	string	the table name
2112 
	 * @return	string
2113 
	 */
2114 
	protected function _truncate($table)
2115 
	{
2116 
		return 'TRUNCATE '.$table;
2117 
	}
2118 
 
2119 
	// --------------------------------------------------------------------
2120 
 
2121 
	/**
2122 
	 * Get DELETE query string
2123 
	 *
2124 
	 * Compiles a delete query string and returns the sql
2125 
	 *
2126 
	 * @param	string	the table to delete from
2127 
	 * @param	bool	TRUE: reset QB values; FALSE: leave QB values alone
2128 
	 * @return	string
2129 
	 */
2130 
	public function get_compiled_delete($table = '', $reset = TRUE)
2131 
	{
2132 
		$this->return_delete_sql = TRUE;
2133 
		$sql = $this->delete($table, '', NULL, $reset);
2134 
		$this->return_delete_sql = FALSE;
2135 
		return $sql;
2136 
	}
2137 
 
2138 
	// --------------------------------------------------------------------
2139 
 
2140 
	/**
2141 
	 * Delete
2142 
	 *
2143 
	 * Compiles a delete string and runs the query
2144 
	 *
2145 
	 * @param	mixed	the table(s) to delete from. String or array
2146 
	 * @param	mixed	the where clause
2147 
	 * @param	mixed	the limit clause
2148 
	 * @param	bool
2149 
	 * @return	mixed
2150 
	 */
2151 
	public function delete($table = '', $where = '', $limit = NULL, $reset_data = TRUE)
2152 
	{
2153 
		// Combine any cached components with the current statements
2154 
		$this->_merge_cache();
2155 
 
2156 
		if ($table === '')
2157 
		{
2158 
			if ( ! isset($this->qb_from[0]))
2159 
			{
2160 
				return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE;
2161 
			}
2162 
 
2163 
			$table = $this->qb_from[0];
2164 
		}
2165 
		elseif (is_array($table))
2166 
		{
2167 
			empty($where) && $reset_data = FALSE;
2168 
 
2169 
			foreach ($table as $single_table)
2170 
			{
2171 
				$this->delete($single_table, $where, $limit, $reset_data);
2172 
			}
2173 
 
2174 
			return;
2175 
		}
2176 
		else
2177 
		{
2178 
			$table = $this->protect_identifiers($table, TRUE, NULL, FALSE);
2179 
		}
2180 
 
2181 
		if ($where !== '')
2182 
		{
2183 
			$this->where($where);
2184 
		}
2185 
 
2186 
		if ( ! empty($limit))
2187 
		{
2188 
			$this->limit($limit);
2189 
		}
2190 
 
2191 
		if (count($this->qb_where) === 0)
2192 
		{
2193 
			return ($this->db_debug) ? $this->display_error('db_del_must_use_where') : FALSE;
2194 
		}
2195 
 
2196 
		$sql = $this->_delete($table);
2197 
		if ($reset_data)
2198 
		{
2199 
			$this->_reset_write();
2200 
		}
2201 
 
2202 
		return ($this->return_delete_sql === TRUE) ? $sql : $this->query($sql);
2203 
	}
2204 
 
2205 
	// --------------------------------------------------------------------
2206 
 
2207 
	/**
2208 
	 * Delete statement
2209 
	 *
2210 
	 * Generates a platform-specific delete string from the supplied data
2211 
	 *
2212 
	 * @param	string	the table name
2213 
	 * @return	string
2214 
	 */
2215 
	protected function _delete($table)
2216 
	{
2217 
		return 'DELETE FROM '.$table.$this->_compile_wh('qb_where')
2254 lars 2218 
			.($this->qb_limit ? ' LIMIT '.$this->qb_limit : '');
68 lars 2219 
	}
2220 
 
2221 
	// --------------------------------------------------------------------
2222 
 
2223 
	/**
2224 
	 * DB Prefix
2225 
	 *
2226 
	 * Prepends a database prefix if one exists in configuration
2227 
	 *
2228 
	 * @param	string	the table
2229 
	 * @return	string
2230 
	 */
2231 
	public function dbprefix($table = '')
2232 
	{
2233 
		if ($table === '')
2234 
		{
2235 
			$this->display_error('db_table_name_required');
2236 
		}
2237 
 
2238 
		return $this->dbprefix.$table;
2239 
	}
2240 
 
2241 
	// --------------------------------------------------------------------
2242 
 
2243 
	/**
2244 
	 * Set DB Prefix
2245 
	 *
2246 
	 * Set's the DB Prefix to something new without needing to reconnect
2247 
	 *
2248 
	 * @param	string	the prefix
2249 
	 * @return	string
2250 
	 */
2251 
	public function set_dbprefix($prefix = '')
2252 
	{
2253 
		return $this->dbprefix = $prefix;
2254 
	}
2255 
 
2256 
	// --------------------------------------------------------------------
2257 
 
2258 
	/**
2259 
	 * Track Aliases
2260 
	 *
2261 
	 * Used to track SQL statements written with aliased tables.
2262 
	 *
2263 
	 * @param	string	The table to inspect
2264 
	 * @return	string
2265 
	 */
2266 
	protected function _track_aliases($table)
2267 
	{
2268 
		if (is_array($table))
2269 
		{
2270 
			foreach ($table as $t)
2271 
			{
2272 
				$this->_track_aliases($t);
2273 
			}
2274 
			return;
2275 
		}
2276 
 
2277 
		// Does the string contain a comma?  If so, we need to separate
2278 
		// the string into discreet statements
2279 
		if (strpos($table, ',') !== FALSE)
2280 
		{
2281 
			return $this->_track_aliases(explode(',', $table));
2282 
		}
2283 
 
2284 
		// if a table alias is used we can recognize it by a space
2285 
		if (strpos($table, ' ') !== FALSE)
2286 
		{
2287 
			// if the alias is written with the AS keyword, remove it
2288 
			$table = preg_replace('/\s+AS\s+/i', ' ', $table);
2289 
 
2290 
			// Grab the alias
2291 
			$table = trim(strrchr($table, ' '));
2292 
 
2293 
			// Store the alias, if it doesn't already exist
2107 lars 2294 
			if ( ! in_array($table, $this->qb_aliased_tables, TRUE))
68 lars 2295 
			{
2296 
				$this->qb_aliased_tables[] = $table;
2107 lars 2297 
				if ($this->qb_caching === TRUE && ! in_array($table, $this->qb_cache_aliased_tables, TRUE))
2298 
				{
2299 
					$this->qb_cache_aliased_tables[] = $table;
2300 
					$this->qb_cache_exists[] = 'aliased_tables';
2301 
				}
68 lars 2302 
			}
2303 
		}
2304 
	}
2305 
 
2306 
	// --------------------------------------------------------------------
2307 
 
2308 
	/**
2309 
	 * Compile the SELECT statement
2310 
	 *
2311 
	 * Generates a query string based on which functions were used.
2312 
	 * Should not be called directly.
2313 
	 *
2314 
	 * @param	bool	$select_override
2315 
	 * @return	string
2316 
	 */
2317 
	protected function _compile_select($select_override = FALSE)
2318 
	{
2319 
		// Combine any cached components with the current statements
2320 
		$this->_merge_cache();
2321 
 
2322 
		// Write the "select" portion of the query
2323 
		if ($select_override !== FALSE)
2324 
		{
2325 
			$sql = $select_override;
2326 
		}
2327 
		else
2328 
		{
2329 
			$sql = ( ! $this->qb_distinct) ? 'SELECT ' : 'SELECT DISTINCT ';
2330 
 
2331 
			if (count($this->qb_select) === 0)
2332 
			{
2333 
				$sql .= '*';
2334 
			}
2335 
			else
2336 
			{
2337 
				// Cycle through the "select" portion of the query and prep each column name.
2338 
				// The reason we protect identifiers here rather than in the select() function
2339 
				// is because until the user calls the from() function we don't know if there are aliases
2340 
				foreach ($this->qb_select as $key => $val)
2341 
				{
2342 
					$no_escape = isset($this->qb_no_escape[$key]) ? $this->qb_no_escape[$key] : NULL;
2343 
					$this->qb_select[$key] = $this->protect_identifiers($val, FALSE, $no_escape);
2344 
				}
2345 
 
2346 
				$sql .= implode(', ', $this->qb_select);
2347 
			}
2348 
		}
2349 
 
2350 
		// Write the "FROM" portion of the query
2351 
		if (count($this->qb_from) > 0)
2352 
		{
2353 
			$sql .= "\nFROM ".$this->_from_tables();
2354 
		}
2355 
 
2356 
		// Write the "JOIN" portion of the query
2357 
		if (count($this->qb_join) > 0)
2358 
		{
2359 
			$sql .= "\n".implode("\n", $this->qb_join);
2360 
		}
2361 
 
2362 
		$sql .= $this->_compile_wh('qb_where')
2363 
			.$this->_compile_group_by()
2364 
			.$this->_compile_wh('qb_having')
2365 
			.$this->_compile_order_by(); // ORDER BY
2366 
 
2367 
		// LIMIT
2254 lars 2368 
		if ($this->qb_limit OR $this->qb_offset)
68 lars 2369 
		{
2370 
			return $this->_limit($sql."\n");
2371 
		}
2372 
 
2373 
		return $sql;
2374 
	}
2375 
 
2376 
	// --------------------------------------------------------------------
2377 
 
2378 
	/**
2379 
	 * Compile WHERE, HAVING statements
2380 
	 *
2381 
	 * Escapes identifiers in WHERE and HAVING statements at execution time.
2382 
	 *
2383 
	 * Required so that aliases are tracked properly, regardless of whether
2384 
	 * where(), or_where(), having(), or_having are called prior to from(),
2385 
	 * join() and dbprefix is added only if needed.
2386 
	 *
2387 
	 * @param	string	$qb_key	'qb_where' or 'qb_having'
2388 
	 * @return	string	SQL statement
2389 
	 */
2390 
	protected function _compile_wh($qb_key)
2391 
	{
2392 
		if (count($this->$qb_key) > 0)
2393 
		{
2394 
			for ($i = 0, $c = count($this->$qb_key); $i < $c; $i++)
2395 
			{
2396 
				// Is this condition already compiled?
2397 
				if (is_string($this->{$qb_key}[$i]))
2398 
				{
2399 
					continue;
2400 
				}
2401 
				elseif ($this->{$qb_key}[$i]['escape'] === FALSE)
2402 
				{
2257 lars 2403 
					$this->{$qb_key}[$i] = $this->{$qb_key}[$i]['condition'].(isset($this->{$qb_key}[$i]['value']) ? ' '.$this->{$qb_key}[$i]['value'] : '');
68 lars 2404 
					continue;
2405 
				}
2406 
 
2407 
				// Split multiple conditions
2408 
				$conditions = preg_split(
2409 
					'/((?:^|\s+)AND\s+|(?:^|\s+)OR\s+)/i',
2410 
					$this->{$qb_key}[$i]['condition'],
2411 
					-1,
2412 
					PREG_SPLIT_DELIM_CAPTURE | PREG_SPLIT_NO_EMPTY
2413 
				);
2414 
 
2415 
				for ($ci = 0, $cc = count($conditions); $ci < $cc; $ci++)
2416 
				{
2417 
					if (($op = $this->_get_operator($conditions[$ci])) === FALSE
2418 
						OR ! preg_match('/^(\(?)(.*)('.preg_quote($op, '/').')\s*(.*(?<!\)))?(\)?)$/i', $conditions[$ci], $matches))
2419 
					{
2420 
						continue;
2421 
					}
2422 
 
2423 
					// $matches = array(
2424 
					//	0 => '(test <= foo)',	/* the whole thing */
2425 
					//	1 => '(',		/* optional */
2426 
					//	2 => 'test',		/* the field name */
2427 
					//	3 => ' <= ',		/* $op */
2428 
					//	4 => 'foo',		/* optional, if $op is e.g. 'IS NULL' */
2429 
					//	5 => ')'		/* optional */
2430 
					// );
2431 
 
2432 
					if ( ! empty($matches[4]))
2433 
					{
2434 
						$this->_is_literal($matches[4]) OR $matches[4] = $this->protect_identifiers(trim($matches[4]));
2435 
						$matches[4] = ' '.$matches[4];
2436 
					}
2437 
 
2438 
					$conditions[$ci] = $matches[1].$this->protect_identifiers(trim($matches[2]))
2439 
						.' '.trim($matches[3]).$matches[4].$matches[5];
2440 
				}
2441 
 
2257 lars 2442 
				$this->{$qb_key}[$i] = implode('', $conditions).(isset($this->{$qb_key}[$i]['value']) ? ' '.$this->{$qb_key}[$i]['value'] : '');
68 lars 2443 
			}
2444 
 
2445 
			return ($qb_key === 'qb_having' ? "\nHAVING " : "\nWHERE ")
2446 
				.implode("\n", $this->$qb_key);
2447 
		}
2448 
 
2449 
		return '';
2450 
	}
2451 
 
2452 
	// --------------------------------------------------------------------
2453 
 
2454 
	/**
2455 
	 * Compile GROUP BY
2456 
	 *
2457 
	 * Escapes identifiers in GROUP BY statements at execution time.
2458 
	 *
2107 lars 2459 
	 * Required so that aliases are tracked properly, regardless of whether
68 lars 2460 
	 * group_by() is called prior to from(), join() and dbprefix is added
2461 
	 * only if needed.
2462 
	 *
2463 
	 * @return	string	SQL statement
2464 
	 */
2465 
	protected function _compile_group_by()
2466 
	{
2467 
		if (count($this->qb_groupby) > 0)
2468 
		{
2469 
			for ($i = 0, $c = count($this->qb_groupby); $i < $c; $i++)
2470 
			{
2471 
				// Is it already compiled?
2472 
				if (is_string($this->qb_groupby[$i]))
2473 
				{
2474 
					continue;
2475 
				}
2476 
 
2477 
				$this->qb_groupby[$i] = ($this->qb_groupby[$i]['escape'] === FALSE OR $this->_is_literal($this->qb_groupby[$i]['field']))
2478 
					? $this->qb_groupby[$i]['field']
2479 
					: $this->protect_identifiers($this->qb_groupby[$i]['field']);
2480 
			}
2481 
 
2482 
			return "\nGROUP BY ".implode(', ', $this->qb_groupby);
2483 
		}
2484 
 
2485 
		return '';
2486 
	}
2487 
 
2488 
	// --------------------------------------------------------------------
2489 
 
2490 
	/**
2491 
	 * Compile ORDER BY
2492 
	 *
2493 
	 * Escapes identifiers in ORDER BY statements at execution time.
2494 
	 *
2107 lars 2495 
	 * Required so that aliases are tracked properly, regardless of whether
68 lars 2496 
	 * order_by() is called prior to from(), join() and dbprefix is added
2497 
	 * only if needed.
2498 
	 *
2499 
	 * @return	string	SQL statement
2500 
	 */
2501 
	protected function _compile_order_by()
2502 
	{
2107 lars 2503 
		if (empty($this->qb_orderby))
68 lars 2504 
		{
2107 lars 2505 
			return '';
2506 
		}
2507 
 
2508 
		for ($i = 0, $c = count($this->qb_orderby); $i < $c; $i++)
2509 
		{
2510 
			if (is_string($this->qb_orderby[$i]))
68 lars 2511 
			{
2107 lars 2512 
				continue;
2513 
			}
68 lars 2514 
 
2107 lars 2515 
			if ($this->qb_orderby[$i]['escape'] !== FALSE && ! $this->_is_literal($this->qb_orderby[$i]['field']))
2516 
			{
2517 
				$this->qb_orderby[$i]['field'] = $this->protect_identifiers($this->qb_orderby[$i]['field']);
68 lars 2518 
			}
2519 
 
2107 lars 2520 
			$this->qb_orderby[$i] = $this->qb_orderby[$i]['field'].$this->qb_orderby[$i]['direction'];
68 lars 2521 
		}
2522 
 
2107 lars 2523 
		return "\nORDER BY ".implode(', ', $this->qb_orderby);
68 lars 2524 
	}
2525 
 
2526 
	// --------------------------------------------------------------------
2527 
 
2528 
	/**
2529 
	 * Object to Array
2530 
	 *
2531 
	 * Takes an object as input and converts the class variables to array key/vals
2532 
	 *
2533 
	 * @param	object
2534 
	 * @return	array
2535 
	 */
2536 
	protected function _object_to_array($object)
2537 
	{
2538 
		if ( ! is_object($object))
2539 
		{
2540 
			return $object;
2541 
		}
2542 
 
2543 
		$array = array();
2544 
		foreach (get_object_vars($object) as $key => $val)
2545 
		{
2546 
			// There are some built in keys we need to ignore for this conversion
2547 
			if ( ! is_object($val) && ! is_array($val) && $key !== '_parent_name')
2548 
			{
2549 
				$array[$key] = $val;
2550 
			}
2551 
		}
2552 
 
2553 
		return $array;
2554 
	}
2555 
 
2556 
	// --------------------------------------------------------------------
2557 
 
2558 
	/**
2559 
	 * Object to Array
2560 
	 *
2561 
	 * Takes an object as input and converts the class variables to array key/vals
2562 
	 *
2563 
	 * @param	object
2564 
	 * @return	array
2565 
	 */
2566 
	protected function _object_to_array_batch($object)
2567 
	{
2568 
		if ( ! is_object($object))
2569 
		{
2570 
			return $object;
2571 
		}
2572 
 
2573 
		$array = array();
2574 
		$out = get_object_vars($object);
2575 
		$fields = array_keys($out);
2576 
 
2577 
		foreach ($fields as $val)
2578 
		{
2579 
			// There are some built in keys we need to ignore for this conversion
2580 
			if ($val !== '_parent_name')
2581 
			{
2582 
				$i = 0;
2583 
				foreach ($out[$val] as $data)
2584 
				{
2585 
					$array[$i++][$val] = $data;
2586 
				}
2587 
			}
2588 
		}
2589 
 
2590 
		return $array;
2591 
	}
2592 
 
2593 
	// --------------------------------------------------------------------
2594 
 
2595 
	/**
2596 
	 * Start Cache
2597 
	 *
2598 
	 * Starts QB caching
2599 
	 *
2600 
	 * @return	CI_DB_query_builder
2601 
	 */
2602 
	public function start_cache()
2603 
	{
2604 
		$this->qb_caching = TRUE;
2605 
		return $this;
2606 
	}
2607 
 
2608 
	// --------------------------------------------------------------------
2609 
 
2610 
	/**
2611 
	 * Stop Cache
2612 
	 *
2613 
	 * Stops QB caching
2614 
	 *
2615 
	 * @return	CI_DB_query_builder
2616 
	 */
2617 
	public function stop_cache()
2618 
	{
2619 
		$this->qb_caching = FALSE;
2620 
		return $this;
2621 
	}
2622 
 
2623 
	// --------------------------------------------------------------------
2624 
 
2625 
	/**
2626 
	 * Flush Cache
2627 
	 *
2628 
	 * Empties the QB cache
2629 
	 *
2630 
	 * @return	CI_DB_query_builder
2631 
	 */
2632 
	public function flush_cache()
2633 
	{
2634 
		$this->_reset_run(array(
2635 
			'qb_cache_select'		=> array(),
2636 
			'qb_cache_from'			=> array(),
2637 
			'qb_cache_join'			=> array(),
2638 
			'qb_cache_where'		=> array(),
2639 
			'qb_cache_groupby'		=> array(),
2640 
			'qb_cache_having'		=> array(),
2641 
			'qb_cache_orderby'		=> array(),
2642 
			'qb_cache_set'			=> array(),
2643 
			'qb_cache_exists'		=> array(),
2107 lars 2644 
			'qb_cache_no_escape'	=> array(),
2645 
			'qb_cache_aliased_tables'	=> array()
68 lars 2646 
		));
2647 
 
2648 
		return $this;
2649 
	}
2650 
 
2651 
	// --------------------------------------------------------------------
2652 
 
2653 
	/**
2654 
	 * Merge Cache
2655 
	 *
2656 
	 * When called, this function merges any cached QB arrays with
2657 
	 * locally called ones.
2658 
	 *
2659 
	 * @return	void
2660 
	 */
2661 
	protected function _merge_cache()
2662 
	{
2663 
		if (count($this->qb_cache_exists) === 0)
2664 
		{
2665 
			return;
2666 
		}
2667 
		elseif (in_array('select', $this->qb_cache_exists, TRUE))
2668 
		{
2669 
			$qb_no_escape = $this->qb_cache_no_escape;
2670 
		}
2671 
 
2672 
		foreach (array_unique($this->qb_cache_exists) as $val) // select, from, etc.
2673 
		{
2674 
			$qb_variable	= 'qb_'.$val;
2675 
			$qb_cache_var	= 'qb_cache_'.$val;
2676 
			$qb_new 	= $this->$qb_cache_var;
2677 
 
2678 
			for ($i = 0, $c = count($this->$qb_variable); $i < $c; $i++)
2679 
			{
2680 
				if ( ! in_array($this->{$qb_variable}[$i], $qb_new, TRUE))
2681 
				{
2682 
					$qb_new[] = $this->{$qb_variable}[$i];
2683 
					if ($val === 'select')
2684 
					{
2685 
						$qb_no_escape[] = $this->qb_no_escape[$i];
2686 
					}
2687 
				}
2688 
			}
2689 
 
2690 
			$this->$qb_variable = $qb_new;
2691 
			if ($val === 'select')
2692 
			{
2693 
				$this->qb_no_escape = $qb_no_escape;
2694 
			}
2695 
		}
2696 
	}
2697 
 
2698 
	// --------------------------------------------------------------------
2699 
 
2700 
	/**
2701 
	 * Is literal
2702 
	 *
2703 
	 * Determines if a string represents a literal value or a field name
2704 
	 *
2705 
	 * @param	string	$str
2706 
	 * @return	bool
2707 
	 */
2708 
	protected function _is_literal($str)
2709 
	{
2710 
		$str = trim($str);
2711 
 
2712 
		if (empty($str) OR ctype_digit($str) OR (string) (float) $str === $str OR in_array(strtoupper($str), array('TRUE', 'FALSE'), TRUE))
2713 
		{
2714 
			return TRUE;
2715 
		}
2716 
 
2717 
		static $_str;
2718 
 
2719 
		if (empty($_str))
2720 
		{
2721 
			$_str = ($this->_escape_char !== '"')
2722 
				? array('"', "'") : array("'");
2723 
		}
2724 
 
2725 
		return in_array($str[0], $_str, TRUE);
2726 
	}
2727 
 
2728 
	// --------------------------------------------------------------------
2729 
 
2730 
	/**
2731 
	 * Reset Query Builder values.
2732 
	 *
2733 
	 * Publicly-visible method to reset the QB values.
2734 
	 *
2735 
	 * @return	CI_DB_query_builder
2736 
	 */
2737 
	public function reset_query()
2738 
	{
2739 
		$this->_reset_select();
2740 
		$this->_reset_write();
2741 
		return $this;
2742 
	}
2743 
 
2744 
	// --------------------------------------------------------------------
2745 
 
2746 
	/**
2747 
	 * Resets the query builder values.  Called by the get() function
2748 
	 *
2749 
	 * @param	array	An array of fields to reset
2750 
	 * @return	void
2751 
	 */
2752 
	protected function _reset_run($qb_reset_items)
2753 
	{
2754 
		foreach ($qb_reset_items as $item => $default_value)
2755 
		{
2756 
			$this->$item = $default_value;
2757 
		}
2758 
	}
2759 
 
2760 
	// --------------------------------------------------------------------
2761 
 
2762 
	/**
2763 
	 * Resets the query builder values.  Called by the get() function
2764 
	 *
2765 
	 * @return	void
2766 
	 */
2767 
	protected function _reset_select()
2768 
	{
2769 
		$this->_reset_run(array(
2770 
			'qb_select'		=> array(),
2771 
			'qb_from'		=> array(),
2772 
			'qb_join'		=> array(),
2773 
			'qb_where'		=> array(),
2774 
			'qb_groupby'		=> array(),
2775 
			'qb_having'		=> array(),
2776 
			'qb_orderby'		=> array(),
2777 
			'qb_aliased_tables'	=> array(),
2778 
			'qb_no_escape'		=> array(),
2779 
			'qb_distinct'		=> FALSE,
2780 
			'qb_limit'		=> FALSE,
2781 
			'qb_offset'		=> FALSE
2782 
		));
2783 
	}
2784 
 
2785 
	// --------------------------------------------------------------------
2786 
 
2787 
	/**
2788 
	 * Resets the query builder "write" values.
2789 
	 *
2790 
	 * Called by the insert() update() insert_batch() update_batch() and delete() functions
2791 
	 *
2792 
	 * @return	void
2793 
	 */
2794 
	protected function _reset_write()
2795 
	{
2796 
		$this->_reset_run(array(
2797 
			'qb_set'	=> array(),
2049 lars 2798 
			'qb_set_ub'	=> array(),
68 lars 2799 
			'qb_from'	=> array(),
2800 
			'qb_join'	=> array(),
2801 
			'qb_where'	=> array(),
2802 
			'qb_orderby'	=> array(),
2803 
			'qb_keys'	=> array(),
2804 
			'qb_limit'	=> FALSE
2805 
		));
2806 
	}
2807 
 
2808 
}