Subversion-Projekte lars-tiefland.ci

Revision

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