Diff | Subversion & Trac

Download diff

	2		3
11	* When it is more or less finished, I'll change license.	11	* When it is more or less finished, I'll change license.
12	*/	12	*/
13	/**	13	/**
		14	* @addtogroup IgnitedRecord
		15	* @{
		16	*/
		17	/**
		18	* A SQL query builder / executor.
14	*	19	*
		20	* @version Alpha
		21	* @author Martin Wernstahl <m4rw3r@gmail.com>
		22	* @par Copyright
		23	* Copyright (c) 2008, Martin Wernstahl <m4rw3r@gmail.com>
15	*/	24	*/
16	class IgnitedQuery{	25	class IgnitedQuery{
		26
		27	// =============================
		28	// = ========== DATA ========= =
		29	// =============================
		30
17	var $select = '*';	31	var $select = '*';
18	var $distinct = false;	32	var $distinct = false;
19	var $from = array();	33	var $from = array();
...		...
22	var $order_by = array();	36	var $order_by = array();
23	var $offset = false;	37	var $offset = false;
24	var $limit = false;	38	var $limit = false;
		39	/**
		40	* The alias of this subquery (if this is one).
		41	*
		42	* @access private
		43	*
		44	* If set to false, the alias cannot be set
		45	* (that setting is used for subqueries which shouldn't have aliases)
		46	*/
25	var $as = null;	47	var $as = null;
26	// flags	48	/**
		49	* The data used by insert() and update().
		50	*
		51	* @access private
		52	*/
		53	var $set_data = array();
		54
		55	// =============================
		56	// = ========= FLAGS ========= =
		57	// =============================
		58
		59	/**
		60	* The parent node of the subquery (if this is one).
		61	*
		62	* If not null or false
		63	*/
27	var $parent = null;	64	var $parent = null;
		65	/**
		66	* Determines if only the WHERE part of the query should be retuned by _build_get_query().
		67	*/
28	var $only_where = false;	68	var $only_where = false;
		69
		70	// --------------------------------------------------------------------
		71
29	/**	72	/**
30	* Constructor	73	* Constructor
31	*/	74	*/
32	function IgnitedQuery(&$parent = null){	75	function IgnitedQuery(&$parent = null){
33	$this->reset();
34	$this->parent = $parent;	76	$this->parent = $parent;
35	}	77	}
36	/*function __call($method,$args){	78	/*function __call($method,$args){
...		...
39	return call_user_func_array(array($CI->db, $method),$args);	81	return call_user_func_array(array($CI->db, $method),$args);
40	show_error("IgnitedQuery: Method $method is not found.");	82	show_error("IgnitedQuery: Method $method is not found.");
41	}*/	83	}*/
		84
		85	// --------------------------------------------------------------------
		86
42	/**	87	/**
43	* Resets the state of this object.	88	* Resets the state of this object.
		89	*
		90	* @access public
44	*/	91	*/
45	function reset(){	92	function reset(){
46	$this->parent = null;	93	$this->parent = null;
...		...
54	$this->limit = false;	101	$this->limit = false;
55	$this->as = null;	102	$this->as = null;
56	$this->only_where = false;	103	$this->only_where = false;
		104	$this->set_data = array();
57	}	105	}
		106
		107	// =============================
		108	// = ========= SELECT ======== =
		109	// =============================
		110
58	/**	111	/**
59	* Specifies the SELECT part of the query.	112	* Specifies the SELECT part of the query.
60	*	113	*
		114	* @access public
61	* @param $cols The column(s) to be selected	115	* @param $cols The column(s) to be selected
62	* If set to false, a new IgnitedQuery is returned which acts like a subquery	116	* If set to false, a new IgnitedQuery is returned which acts like a subquery
63	*	117	*
64	* @return $this or a new IgnitedQuery	118	* @return $this or a new IgnitedQuery
65	*/	119	*/
66	function &select($cols = false){	120	function &select($cols = false, $protect_identifiers = true){
67	if($cols == false){	121	if($cols === false){
68	$obj = new IgnitedQuery($this);	122	$obj = new IgnitedQuery($this);
69	$this->select($obj);	123	$this->select($obj);
70	return $obj;	124	return $obj;
71	}	125	}
		126
		127	// reset select if it is the default "*"
72	if(is_string($this->select)){	128	if(is_string($this->select)){
73	$this->select = array();	129	$this->select = array();
74	}	130	}
		131
75	if(is_array($cols)){	132	if(is_array($cols)){
76	array_walk($cols,array($this,'_protect_identifiers_walk'));	133	// many columns in array
77	$this->select = $this->select + $cols;	134	if($protect_identifiers)
		135	array_walk($cols,array($this,'_protect_identifiers_walk'));
		136
		137	$this->select = array_merge($this->select,$cols);
78	}	138	}
79	elseif(is_object($cols) && is_a($cols,'IgnitedQuery')){	139	elseif(is_object($cols) && is_a($cols,'IgnitedQuery')){
		140	// subquery
80	$cols->parent =& $this;	141	$cols->parent =& $this;
81	$this->select[] =& $cols;	142	$this->select[] =& $cols;
82	}	143	}
83	else{	144	else{
84	$this->select[] =& $this->_protect_identifiers($cols);	145	if($protect_identifiers)
		146	$cols = $this->_protect_identifiers($cols);
		147
		148	$this->select[] = $cols;
85	}	149	}
		150
86	return $this;	151	return $this;
87	}	152	}
		153
		154	// --------------------------------------------------------------------
		155
88	/**	156	/**
89	* Specifies a MAX select function.	157	* Specifies a MAX select function.
90	*	158	*
		159	* @access public
91	* @param $col The column to be selected	160	* @param $col The column to be selected
92	* If set to false, a new IgnitedQuery is returned which acts like a subquery	161	* If set to false, a new IgnitedQuery is returned which acts like a subquery
93	* @param $alias The alias of the query, if empty, alias will be $col	162	* @param $alias The alias of the query, if empty, alias will be $col
94	*	163	*
95	* @return $this or a new IgnitedQuery	164	* @return $this or a new IgnitedQuery
96	*/	165	*/
97	function &select_max($col = false,$alias = ''){	166	function &select_max($col = false,$alias = '', $protect_identifiers = true){
98	return $this->_select_func($col,$alias,'MAX');	167	return $this->_select_func($col, $alias, 'MAX', $protect_identifiers);
99	}	168	}
		169
		170	// --------------------------------------------------------------------
		171
100	/**	172	/**
101	* Specifies a MIN select function.	173	* Specifies a MIN select function.
102	*	174	*
		175	* @access public
103	* @param $col The column to be selected	176	* @param $col The column to be selected
104	* If set to false, a new IgnitedQuery is returned which acts like a subquery	177	* If set to false, a new IgnitedQuery is returned which acts like a subquery
105	* @param $alias The alias of the query, if empty, alias will be $col	178	* @param $alias The alias of the query, if empty, alias will be $col
106	*	179	*
107	* @return $this or a new IgnitedQuery	180	* @return $this or a new IgnitedQuery
108	*/	181	*/
109	function &select_min($col = false,$alias = ''){	182	function &select_min($col = false,$alias = '', $protect_identifiers = true){
110	return $this->_select_func($col,$alias,'MIN');	183	return $this->_select_func($col, $alias, 'MIN', $protect_identifiers);
111	}	184	}
112	/**	185	/**
113	* Specifies an AVG select function.	186	* Specifies an AVG select function.
114	*	187	*
		188	* @access public
115	* @param $col The column to be selected	189	* @param $col The column to be selected
116	* If set to false, a new IgnitedQuery is returned which acts like a subquery	190	* If set to false, a new IgnitedQuery is returned which acts like a subquery
117	* @param $alias The alias of the query, if empty, alias will be $col	191	* @param $alias The alias of the query, if empty, alias will be $col
118	*	192	*
119	* @return $this or a new IgnitedQuery	193	* @return $this or a new IgnitedQuery
120	*/	194	*/
121	function &select_avg($col = false,$alias = ''){	195	function &select_avg($col = false,$alias = '', $protect_identifiers = true){
122	return $this->_select_func($col,$alias,'AVG');	196	return $this->_select_func($col, $alias, 'AVG', $protect_identifiers);
123	}	197	}
		198
		199	// --------------------------------------------------------------------
		200
124	/**	201	/**
125	* Specifies a SUM select function.	202	* Specifies a SUM select function.
126	*	203	*
		204	* @access public
127	* @param $col The column to be selected	205	* @param $col The column to be selected
128	* If set to false, a new IgnitedQuery is returned which acts like a subquery	206	* If set to false, a new IgnitedQuery is returned which acts like a subquery
129	* @param $alias The alias of the query, if empty, alias will be $col	207	* @param $alias The alias of the query, if empty, alias will be $col
130	*	208	*
131	* @return $this or a new IgnitedQuery	209	* @return $this or a new IgnitedQuery
132	*/	210	*/
133	function &select_sum($col = false,$alias = ''){	211	function &select_sum($col = false,$alias = '', $protect_identifiers = true){
134	return $this->_select_func($col,$alias,'SUM');	212	return $this->_select_func($col, $alias, 'SUM', $protect_identifiers);
135	}	213	}
		214
		215	// --------------------------------------------------------------------
		216
136	/**	217	/**
		218	* Specifies a COUNT select function.
		219	*
		220	* @access public
		221	* @param $col The column to be selected
		222	* If set to false, a new IgnitedQuery is returned which acts like a subquery
		223	* @param $alias The alias of the query, if empty, alias will be $col
		224	*
		225	* @return $this or a new IgnitedQuery
		226	*/
		227	function &select_count($col = false, $alias = '', $protect_identifiers = true){
		228	return $this->_select_func($col, $alias, 'COUNT', $protect_identifiers);
		229	}
		230
		231	// --------------------------------------------------------------------
		232
		233	/**
137	* Specifies a select function (eg. MIN, MAX, AVG).	234	* Specifies a select function (eg. MIN, MAX, AVG).
138	*	235	*
		236	* @access private
139	* @param $col The column to be selected	237	* @param $col The column to be selected
140	* If set to false, a new IgnitedQuery is returned which acts like a subquery	238	* If set to false, a new IgnitedQuery is returned which acts like a subquery
141	* @param $alias The alias of the query, if empty, alias will be $col	239	* @param $alias The alias of the query, if empty, alias will be $col
142	* @param $func The function to use	240	* @param $func The function to use
143	*	241	*
144	* @return $this or a new IgnitedQuery	242	* @return $this or a new IgnitedQuery
		243	*
		244	* @todo Check the subquery for SELECT FUNC(foo) AS bar
145	*/	245	*/
146	function &_select_func($col, $alias, $func){	246	function &_select_func($col, $alias, $func, $protect_identifiers){
147	if(is_string($this->select)){	247	if(is_string($this->select)){
148	$this->select = array();	248	$this->select = array();
149	}	249	}
		250
150	$this->select[] = $func;	251	$this->select[] = $func;
		252
151	if($col == false){	253	if($col == false){
152	$obj = new IgnitedQuery($this);	254	$obj = new IgnitedQuery($this);
		255	$obj->as = false;
153	$this->select($obj);	256	$this->select($obj);
154	return $obj;	257	return $obj;
155	}	258	}
156	$this->select[] = '('.$this->_protect_identifiers($col).') AS '.	259
157	($alias != '' ? $this->_protect_identifiers($alias) : $this->_protect_identifiers($col));	260	if($protect_identifiers)
		261	$col = $this->_protect_identifiers($col);
		262
		263	$this->select[] = '('.$col.') AS '.
		264	($alias != '' ? $this->_protect_identifiers($alias) :
		265	$this->_protect_identifiers($col));
		266
158	return $this;	267	return $this;
159	}	268	}
		269
		270	// --------------------------------------------------------------------
		271
160	/**	272	/**
161	* Sets the flag for the use of the DISTINCT modifier.	273	* Sets the flag for the use of the DISTINCT modifier.
162	*	274	*
...		...
168	$this->distinct = $val;	280	$this->distinct = $val;
169	return $this;	281	return $this;
170	}	282	}
		283
		284	// =============================
		285	// = ========== FROM ========= =
		286	// =============================
		287
171	/**	288	/**
172	* Specifies the FROM part of the query.	289	* Specifies the FROM part of the query.
173	*	290	*
		291	* @access public
174	* @param $tables The table(s) to be selected	292	* @param $tables The table(s) to be selected
175	* If set to false, a new IgnitedQuery is returned which acts like a subquery	293	* If set to false, a new IgnitedQuery is returned which acts like a subquery
176	*	294	*
177	* @return $this or a new IgnitedQuery	295	* @return $this or a new IgnitedQuery
178	*/	296	*/
179	function &from($tables = false){	297	function &from($tables = false){
180	if($tables == false){	298	if($tables === false){
181	$obj = new IgnitedQuery($this);	299	$obj = new IgnitedQuery($this);
182	$this->from($obj);	300	$this->from($obj);
183	return $obj;	301	return $obj;
184	}	302	}
		303
185	if(is_array($tables)){	304	if(is_array($tables)){
		305	// list of tables, or table => alias
186	array_walk($tables,array($this,'_protect_identifiers_walk'));	306	array_walk($tables,array($this,'_protect_identifiers_walk'));
187	$this->from = $this->from + $tables;	307	$this->from = array_merge($this->from, $tables);
188	}	308	}
189	elseif(is_object($tables) && is_a($tables,'IgnitedQuery')){	309	elseif(is_object($tables) && is_a($tables,'IgnitedQuery')){
		310	// subquery
190	$tables->parent =& $this;	311	$tables->parent =& $this;
191	$this->from[] =& $tables;	312	$this->from[] =& $tables;
192	}	313	}
193	else{	314	else{
194	$this->from[] = $this->_protect_identifiers($tables);	315	$this->from[] = $this->_protect_identifiers($tables);
195	}	316	}
		317
196	return $this;	318	return $this;
197	}	319	}
		320
		321	// =============================
		322	// = ========= WHERE ========= =
		323	// =============================
		324
198	/**	325	/**
199	* Specifies the WHERE part of the query.	326	* Specifies the WHERE part of the query.
200	*	327	*
		328	* @access public
201	* @param $where The where data	329	* @param $where The where data
202	* If set to false, a new IgnitedQuery is returned which acts like a subquery	330	* If set to false, a new IgnitedQuery is returned which acts like a subquery
203	* @param $value The value to match with	331	* @param $value The value to match with
...		...
206	* @return $this or a new IgnitedQuery	334	* @return $this or a new IgnitedQuery
207	*/	335	*/
208	function &where($where = false,$value = null,$escape = true){	336	function &where($where = false,$value = null,$escape = true){
209	if($where == false){	337	if($where === false){
210	$obj = new IgnitedQuery($this);	338	$obj = new IgnitedQuery($this);
211	$obj->only_where = true;	339	$obj->only_where = true;
212	$this->where($obj);	340	$this->where($obj);
213	return $obj;	341	return $obj;
214	}	342	}
		343
215	if(is_object($where) && is_a($where,'IgnitedQuery')){	344	if(is_object($where) && is_a($where,'IgnitedQuery')){
216	$where->parent =& $this;	345	$where->parent =& $this;
217	$where->only_where = true;	346	$where->only_where = true;
218	$this->where[] =& $where;	347	$this->where[] =& $where;
219	return $this;	348	return $this;
220	}	349	}
221	if(!is_array($where) && $escape)	350
		351	if( ! is_array($where) && $escape){
222	$where = array($where => $value);	352	$where = array($where => $value);
		353	}
		354
223	foreach((Array)$where as $k => $val){	355	foreach((Array)$where as $k => $val){
224	if(is_numeric($k))	356	// numeric tells us that we have a finished where statement
		357	if(is_numeric($k)){
225	$this->where[] = $escape ? $this->_protect_identifiers($val) : $val;	358	$this->where[] = $escape ? $this->_protect_identifiers($val) : $val;
		359	}
226	else{	360	else{
227	if ($this->_has_operator($k)){	361	// process key => value where
		362	if($this->_has_operator($k)){
228	$k = preg_replace("/([A-Za-z_0-9]+)/",	363	$k = preg_replace("/([A-Za-z_0-9]+)/",
229	$this->_protect_identifiers('$1'), $k);	364	$this->_protect_identifiers('$1'), $k);
230	}	365	}
231	else{	366	else{
232	$k = $this->_protect_identifiers($k);	367	$k = $this->_protect_identifiers($k);
233	}	368	}
		369
234	$this->where[] = array($k => $this->escape($val));	370	$this->where[] = array($k => $this->escape($val));
235	}	371	}
236	}	372	}
		373
237	return $this;	374	return $this;
238	}	375	}
		376
		377	// --------------------------------------------------------------------
		378
239	/**	379	/**
240	* Specifies an OR part in the WHERE part of the query.	380	* Specifies an OR part in the WHERE part of the query.
241	*	381	*
242	* Works like where()	382	* Works like where()
243	*	383	*
		384	* @access public
244	* @param $where The where data	385	* @param $where The where data
245	* If set to false, a new IgnitedQuery is returned which acts like a subquery	386	* If set to false, a new IgnitedQuery is returned which acts like a subquery
246	* @param $value The value to (NOT) match with	387	* @param $value The value to (NOT) match with
...		...
251	function &or_where($where = false,$value = null,$escape = true){	392	function &or_where($where = false,$value = null,$escape = true){
252	if(count($this->where))	393	if(count($this->where))
253	$this->where[] = 'OR';	394	$this->where[] = 'OR';
		395
254	return $this->where($where,$value,$escape);	396	return $this->where($where,$value,$escape);
255	}	397	}
		398
		399	// =============================
		400	// = ======= WHERE IN ======== =
		401	// =============================
		402
256	/**	403	/**
257	* Generates a WHERE IN in the WHERE part of the query.	404	* Generates a WHERE IN in the WHERE part of the query.
258	*	405	*
		406	* @access public
259	* @param $key The column to check	407	* @param $key The column to check
260	* @param $values The values to check against	408	* @param $values The values to check against
261	* If set to false, a new IgnitedQuery is returned which acts like a subquery	409	* If set to false, a new IgnitedQuery is returned which acts like a subquery
...		...
265	*/	413	*/
266	function &where_in($key,$values = false,$not = false){	414	function &where_in($key,$values = false,$not = false){
267	$this->where[] = $this->_protect_identifiers($key);	415	$this->where[] = $this->_protect_identifiers($key);
		416
268	if($not)	417	if($not)
269	$this->where[] = 'NOT';	418	$this->where[] = 'NOT';
		419
270	$this->where[] = 'IN';	420	$this->where[] = 'IN';
271	if($values == false){	421
		422	if($values === false){
272	$obj = new IgnitedQuery($this);	423	$obj = new IgnitedQuery($this);
273	$obj->as = false;	424	$obj->as = false;
274	$this->where[] = $obj;	425	$this->where[] = $obj;
275	return $obj;	426	return $obj;
276	}	427	}
		428
277	if(is_object($values) && is_a($values,'IgnitedQuery')){	429	if(is_object($values) && is_a($values,'IgnitedQuery')){
278	$values->parent =& $this;	430	$values->parent =& $this;
279	$values->as = false;	431	$values->as = false;
280	$this->where[] =& $values;	432	$this->where[] =& $values;
281	return $this;	433	return $this;
282	}	434	}
		435
283	$str = '';	436	$str = '';
		437
284	foreach((Array)$values as $key => $col){	438	foreach((Array)$values as $key => $col){
285	$str .= $key > 0 ? ', ' : '';	439	$str .= $key > 0 ? ', ' : '';
286	$str .= $this->escape($col);	440	$str .= $this->escape($col);
287	}	441	}
		442
288	$this->where[] = '('.$str.')';	443	$this->where[] = '('.$str.')';
		444
289	return $this;	445	return $this;
290	}	446	}
		447
		448	// --------------------------------------------------------------------
		449
291	/**	450	/**
292	* Specifies an OR WHERE IN in the WHERE part of the query.	451	* Specifies an OR WHERE IN in the WHERE part of the query.
293	*	452	*
294	* Works like where_in()	453	* Works like where_in()
295	*	454	*
		455	* @access public
296	* @param $key The column to check	456	* @param $key The column to check
297	* @param $values The values to check against	457	* @param $values The values to check against
298	* If set to false, a new IgnitedQuery is returned which acts like a subquery	458	* If set to false, a new IgnitedQuery is returned which acts like a subquery
...		...
302	function &or_where_in($key, $values = false){	462	function &or_where_in($key, $values = false){
303	if(count($this->where))	463	if(count($this->where))
304	$this->where[] = 'OR';	464	$this->where[] = 'OR';
		465
305	return $this->where_in($key,$values);	466	return $this->where_in($key,$values);
306	}	467	}
		468
		469	// --------------------------------------------------------------------
		470
307	/**	471	/**
308	* Specifies an WHERE NOT IN in the WHERE part of the query.	472	* Specifies an WHERE NOT IN in the WHERE part of the query.
309	*	473	*
		474	* @access public
310	* @param $key The column to check	475	* @param $key The column to check
311	* @param $values The values to check against	476	* @param $values The values to check against
312	* If set to false, a new IgnitedQuery is returned which acts like a subquery	477	* If set to false, a new IgnitedQuery is returned which acts like a subquery
...		...
316	function &where_not_in($key, $values = false){	481	function &where_not_in($key, $values = false){
317	return $this->where_in($key,$values,true);	482	return $this->where_in($key,$values,true);
318	}	483	}
		484
		485	// --------------------------------------------------------------------
		486
319	/**	487	/**
		488	* Specifies an OR WHERE NOT IN in the WHERE part of the query.
		489	*
		490	* Works like where_not_in()
		491	*
		492	* @access public
		493	* @param $key The column to check
		494	* @param $values The values to check against
		495	* If set to false, a new IgnitedQuery is returned which acts like a subquery
		496	*
		497	* @return $this or a new IgnitedQuery
		498	*/
		499	function &or_where_not_in($key, $values = false){
		500	if(count($this->where))
		501	$this->where[] = 'OR';
		502
		503	return $this->where_in($key,$values,true);
		504	}
		505
		506	// =============================
		507	// = ========== LIKE ========= =
		508	// =============================
		509
		510	/**
320	* Produces a LIKE clause in the WHERE art of the query.	511	* Produces a LIKE clause in the WHERE art of the query.
321	*	512	*
322	* @param $col	513	* @access public
		514	* @param $col The column to match
		515	* @param $match The match string
		516	* @param $side Which side the '%' sign should be on,
		517	* Accepts 'both', 'before' and 'after' (default: 'both')
		518	* @param $not If to add NOT to the statement (default: false)
323	*	519	*
324	* @todo Check if subqueries are suported	520	* @todo Check if subqueries are suported
325	*/	521	*/
326	function like($col, $match = '', $side = 'both', $not = false){	522	function &like($col, $match = '', $side = 'both', $not = false){
327	/* if(!is_array($col) && $match == ''){	523	/* if(!is_array($col) && $match == ''){
328	$this->where[] = $this->_protect_identifiers($col);	524	$this->where[] = $this->_protect_identifiers($col);
329	if($not)	525	if($not)
...		...
336	}*/	532	}*/
337	if(!is_array($col))	533	if(!is_array($col))
338	$col = array($col => $match);	534	$col = array($col => $match);
		535
339	foreach($col as $k => $v){	536	foreach($col as $k => $v){
340	$this->where[] = $this->_protect_identifiers($k);	537	$this->where[] = $this->_protect_identifiers($k);
		538
341	if($not)	539	if($not)
342	$this->where[] = 'NOT';	540	$this->where[] = 'NOT';
		541
343	$this->where[] = 'LIKE';	542	$this->where[] = 'LIKE';
344	$v = $this->escape($v);	543	$v = $this->escape($v);
		544
345	if($side == 'before')	545	if($side == 'before')
346	$this->where[] = '%'.$v;	546	$this->where[] = "%$v";
347	elseif($side == 'after')	547	elseif($side == 'after')
348	$this->where[] = $v.'%';	548	$this->where[] = "$v%";
349	else	549	else
350	$this->where[] = '%'$v'%';	550	$this->where[] = "%$v%";
351	}	551	}
		552
352	return $this;	553	return $this;
353	}	554	}
		555
		556	// --------------------------------------------------------------------
		557
		558	/**
		559	* Produces a NOT LIKE sql statement.
		560	*
		561	* @access public
		562	* @param $col The column to match
		563	* @param $match The match string
		564	* @param $side Which side the '%' sign should be on,
		565	* Accepts 'both', 'before' and 'after' (default: 'both')
		566	*
		567	* @return $this
		568	*/
354	function not_like($col, $match = '', $side = 'both'){	569	function not_like($col, $match = '', $side = 'both'){
355	return $this->like($col, $match, $side, true);	570	return $this->like($col, $match, $side, true);
356	}	571	}
		572
		573	// --------------------------------------------------------------------
		574
		575	/**
		576	* Produces an OR LIKE sql statement.
		577	*
		578	* @access public
		579	* @param $col The column to match
		580	* @param $match The match string
		581	* @param $side Which side the '%' sign should be on,
		582	* Accepts 'both', 'before' and 'after' (default: 'both')
		583	* @param $not If to add NOT to the statement (default: false)
		584	*
		585	* @return $this
		586	*/
357	function or_like($col, $match = '', $side = 'both', $not = false){	587	function or_like($col, $match = '', $side = 'both', $not = false){
358	$this->where[] = 'OR';	588	$this->where[] = 'OR';
359	return $this->like($col, $match, $side, $not);	589	return $this->like($col, $match, $side, $not);
360	}	590	}
361	function or_not_like($col, $match = '', $side = 'both'){	591
362	return $this->or_like($col, $match, $side, true);	592	// --------------------------------------------------------------------
363	}	593
364	/**	594	/**
365	* Specifies an OR WHERE NOT IN in the WHERE part of the query.	595	* Produces an OR NOT LIKE sql statement.
366	*	596	*
367	* Works like where_not_in()	597	* @access public
		598	* @param $col The column to match
		599	* @param $match The match string
		600	* @param $side Which side the '%' sign should be on,
		601	* Accepts 'both', 'before' and 'after' (default: 'both')
368	*	602	*
369	* @param $key The column to check	603	* @return $this
370	* @param $values The values to check against
371	* If set to false, a new IgnitedQuery is returned which acts like a subquery
372	*
373	* @return $this or a new IgnitedQuery
374	*/	604	*/
375	function &or_where_not_in($key, $values = false){	605	function or_not_like($col, $match = '', $side = 'both'){
376	if(count($this->where))	606	return $this->or_like($col, $match, $side, true);
377	$this->where[] = 'OR';
378	return $this->where_in($key,$values,true);
379	}	607	}
		608
		609	// =============================
		610	// = ===== SQL MODIFIERS ===== =
		611	// =============================
		612
380	/**	613	/**
381	* Specifies the GROUP BY part of the query.	614	* Specifies the GROUP BY part of the query.
382	*	615	*
		616	* @access public
383	* @param $by The column(s) to group by	617	* @param $by The column(s) to group by
384	*	618	*
385	* @return $this	619	* @return $this
...		...
394	}	628	}
395	return $this;	629	return $this;
396	}	630	}
		631
		632	// --------------------------------------------------------------------
		633
397	/**	634	/**
398	* Specifies the ORDER BY part of the query.	635	* Specifies the ORDER BY part of the query.
399	*	636	*
		637	* @access public
400	* @param $by The column(s) to order by	638	* @param $by The column(s) to order by
401	*	639	*
402	* @return $this	640	* @return $this
...		...
411	}	649	}
412	return $this;	650	return $this;
413	}	651	}
		652
		653	// --------------------------------------------------------------------
		654
414	/**	655	/**
415	* Specifies if and what the LIMIT part of the query should be.	656	* Specifies if and what the LIMIT part of the query should be.
416	*	657	*
		658	* @access public
417	* @param $val The number	659	* @param $val The number
418	*	660	*
419	* @return $this	661	* @return $this
...		...
422	$this->limit = $val;	664	$this->limit = $val;
423	return $this;	665	return $this;
424	}	666	}
		667
		668	// --------------------------------------------------------------------
		669
425	/**	670	/**
426	* Specifies if and what the OFFSET part of the query should be.	671	* Specifies if and what the OFFSET part of the query should be.
427	*	672	*
		673	* @access public
428	* @param $val The number	674	* @param $val The number
429	*	675	*
430	* @return $this	676	* @return $this
...		...
433	$this->offset = $val;	679	$this->offset = $val;
434	return $this;	680	return $this;
435	}	681	}
		682
		683	// --------------------------------------------------------------------
		684
436	/**	685	/**
437	* Specifies an alias for this subquery.	686	* Specifies an alias for this subquery.
438	*	687	*
439	* @note This only applies if this object is a subquery	688	* @note This only applies if this object is a subquery
440	*	689	*
		690	* @access public
441	* @param $alias The alias	691	* @param $alias The alias
442	*	692	*
443	* @return $this	693	* @return $this
...		...
447	$this->as = $this->_protect_identifiers($alias);	697	$this->as = $this->_protect_identifiers($alias);
448	return $this;	698	return $this;
449	}	699	}
		700
		701	// =============================
		702	// = ========== SET ========== =
		703	// =============================
		704
450	/**	705	/**
		706	* This function saves the data to be INSERTed/UPDATEd.
		707	*
		708	* @access public
		709	* @param $key The name of the field to set, or an asociative array with field => value
		710	* @param $value The value matching t
		711	* @param $escape If to escape the values (default: true), subqueries are not escaped
		712	*
		713	* @return $this
		714	*/
		715	function set($key, $value = '', $escape = true){
		716	if( ! is_array($key)){
		717	$key = array($key => $value);
		718	}
		719
		720	foreach($key as $key => $value){
		721	if($escape === true && ! is_object($value)){
		722	$value = $this->escape($value);
		723	}
		724
		725	if(is_object($value) && is_a($value,'IgnitedQuery')){
		726	$value->parent =& $this; // subquery
		727	$value->as = false; // no aliases
		728	$value->limit(1); // set limit to 1, because only one row can be returned and assigned
		729	}
		730
		731	$this->set_data[$key] = $value;
		732	}
		733
		734	return $this;
		735	}
		736
		737	// =============================
		738	// = === END QUERY METHODS === =
		739	// =============================
		740
		741	/**
451	* Ends a subquery block, returning the parent query object.	742	* Ends a subquery block, returning the parent query object.
452	*	743	*
453	* If this is the root query, null is returned.	744	* If this is the root query, null is returned.
454	*	745	*
		746	* @access public
455	* @return The parent IgnitedQuery	747	* @return The parent IgnitedQuery
456	*/	748	*/
457	function &end(){	749	function &end(){
458	return $this->parent;	750	return $this->parent;
459	}	751	}
		752
		753	// --------------------------------------------------------------------
		754
460	/**	755	/**
		756	* Runs the SELECT query.
		757	*
		758	* Resets this object after the query has been assembled.
		759	*
		760	* @access public
		761	* @param $table A table which is forwarded to from()
		762	* @param $limit The limit
		763	* @param $offset The offset
		764	*
		765	* @return A DB_result object
		766	*/
		767	function &get($table = '', $limit = null, $offset = null){
		768	$this->limit($limit);
		769	$this->offset($offset);
		770
		771	if($table != '')
		772	$this->from($table);
		773
		774	$CI =& get_instance();
		775	return $CI->db->query($this->_build_get_query());
		776	}
		777
		778	// --------------------------------------------------------------------
		779
		780	/**
		781	* Runs the SELECT query, and specifies a where clause.
		782	*
		783	* Resets this object after the query has been assembled.
		784	*
		785	* @access public
		786	* @param $table A table which is forwarded to from()
		787	* @param $where Forwarded to where()
		788	* @param $limit The limit
		789	* @param $offset The offset
		790	*
		791	* @return A DB_result object
		792	*/
		793	function &get_where($table, $where, $limit = null, $offset = null){
		794	$this->where($where);
		795	$this->limit($limit);
		796	$this->offset($offset);
		797
		798	return $this->get($table);
		799	}
		800
		801	// --------------------------------------------------------------------
		802
		803	/**
		804	* Performs an INSERT query
		805	*
		806	* @access public
		807	* @param $table The table
		808	* @param $set The data to insert
		809	*/
		810	function insert($table = '', $set = null){
		811	if( ! is_null($set))
		812	$this->set($set)
		813
		814	if($table == ''){
		815	if( ! isset($this->from[0]))
		816	return false;
		817
		818	$table = $this->from[0];
		819	}
		820
		821	$data = array();
		822
		823	foreach($this->set_data as $field => $value){
		824	$data[$this->_protect_identifiers($field)] =
		825	}
		826
		827
		828	$sql = $this->_insert($this->_protect_identifiers($this->dbprefix.$table), array_keys($this->ar_set), array_values($this->ar_set));
		829
		830	$this->_reset_write();
		831	return $this->query($sql);
		832	}
		833
		834	// =============================
		835	// = ==== PRIVATE METHODS ==== =
		836	// =============================
		837
		838	/**
461	* Builds a SELECT query from the internal variables.	839	* Builds a SELECT query from the internal variables.
462	*	840	*
		841	* @access private
		842	* (called by parent queries)
463	* @return a SQL string, or false if no from() call was made.	843	* @return a SQL string, or false if no from() call was made.
		844	*
		845	* @todo Raise error on missing WHERE
464	*/	846	*/
465	function _build_get(){	847	function _build_get_query(){
466	if($this->only_where)	848	if($this->only_where)
467	return '('.$this->build_where($this->where).')';	849	return '('.$this->build_where($this->where).')';
		850
468	if(!count($this->from))	851	if(!count($this->from))
469	return false;	852	return false;
		853
470	$query = 'SELECT '. ($this->distinct ? 'DISTINCT ' : '');	854	$query = 'SELECT '. ($this->distinct ? 'DISTINCT ' : '');
		855
471	$query .= $this->_build_select($this->select);	856	$query .= $this->_build_select($this->select);
		857
472	$query .= "\nFROM ".$this->_build_from($this->from);	858	$query .= "\nFROM ".$this->_build_from($this->from);
		859
473	$query .= count($this->where) ?	860	$query .= count($this->where) ?
474	"\nWHERE ".$this->_build_where($this->where) : '';	861	"\nWHERE ".$this->_build_where($this->where) : '';
		862
475	$query .= count($this->group_by) ?	863	$query .= count($this->group_by) ?
476	"\nGROUP BY ".implode($this->group_by,', ') : '';	864	"\nGROUP BY ".implode($this->group_by,', ') : '';
		865
477	$query .= count($this->order_by) ?	866	$query .= count($this->order_by) ?
478	"\nORDER BY ".implode($this->order_by,', ') : '';	867	"\nORDER BY ".implode($this->order_by,', ') : '';
		868
479	if(is_numeric($this->limit)){	869	if(is_numeric($this->limit)){
		870	// use CI's database specific limit
480	$CI =& get_instance();	871	$CI =& get_instance();
481	$query = $CI->db->_limit($query,$this->limit,$this->offset);	872	$query = $CI->db->_limit($query,$this->limit,$this->offset);
482	}	873	}
		874
483	if(isset($this->parent)){	875	if(isset($this->parent)){
484	return '('.$query.')'.	876	// this is a subquery
		877	$query = '('.$query.')'.
485	(isset($this->as) && $this->as != false ?	878	(isset($this->as) && $this->as != false ?
486	' AS '.$this->as.' ' : '');	879	' AS '.$this->as.' ' : '');
487	}	880	}
		881
488	$this->reset();	882	$this->reset();
		883
489	return $query;	884	return $query;
490	}	885	}
		886
		887	// --------------------------------------------------------------------
		888
491	/**	889	/**
492	* Runs the query.
493	*/
494	function &get($table = '', $limit = null, $offset = null){
495	$this->limit($limit);
496	$this->offset($offset);
497	if($table != '')
498	$this->from($table);
499	$CI =& get_instance();
500	return $CI->db->query($this->_build_get());
501	}
502	/**
503	*
504	*/
505	function &get_where($table, $where, $limit = null, $offset = null){
506	$this->where($where);
507	$this->limit($limit);
508	$this->offset($offset);
509	return $this->get($table);
510	}
511	/**
512	* Builds the SELECT part of the query.	890	* Builds the SELECT part of the query.
513	*	891	*
		892	* @access private
514	* @param $select The select data	893	* @param $select The select data
515	*	894	*
516	* @return A string	895	* @return A string
517	*/	896	*/
518	function _build_select($select){	897	function _build_select($select){
519	if(is_object($select)){	898	if(is_object($select)){
520	return $select->_build_get();	899	return $select->_build_get_query();
521	}	900	}
522	elseif(is_array($select)){	901	elseif(is_array($select)){
		902
523	$str = '';	903	$str = '';
524	$i = 0;	904	$i = 0;
		905
525	foreach($select as $key => $col){	906	foreach($select as $key => $col){
		907	// add commas if needed
526	$str .= $i++ > 0 ? ', ' : '';	908	$str .= $i++ > 0 ? ', ' : '';
527	if(in_array($col,array('MIN','MAX','AVG','SUM'))){	909
528	$str .= "$col";	910	// these are db functions, just echo them
		911	if(in_array($col,array('MIN','MAX','AVG','SUM','COUNT'))){
		912	$str .= $col;
529	$i = 0;	913	$i = 0;
530	continue;	914	continue;
531	}	915	}
		916
532	if(!is_numeric($key))	917	if(!is_numeric($key))
533	$str .= $this->_build_select($key)." AS $col";	918	$str .= $this->_build_select($key)." AS $col";
534	else	919	else
...		...
536	}	921	}
537	return $str;	922	return $str;
538	}	923	}
539	else	924
540	return $select;	925	return $select;
541	}	926	}
		927
		928	// --------------------------------------------------------------------
		929
542	/**	930	/**
543	* Builds the FROM part of the query.	931	* Builds the FROM part of the query.
544	*	932	*
		933	* @access private
545	* @param $from The from data	934	* @param $from The from data
546	*	935	*
547	* @return A string	936	* @return A string
548	*/	937	*/
549	function _build_from($from){	938	function _build_from($from){
550	if(is_object($from)){	939	if(is_object($from)){
551	return $from->_build_get();	940	return $from->_build_get_query();
552	}	941	}
553	elseif(is_array($from)){	942	elseif(is_array($from)){
		943
554	$str = '';	944	$str = '';
555	$i = 0;	945	$i = 0;
		946
556	foreach($from as $key => $col){	947	foreach($from as $key => $col){
		948	// add commas if needed
557	$str .= $i++ > 0 ? ', ' : '';	949	$str .= $i++ > 0 ? ', ' : '';
		950
		951	// not numeric tells us that we have "tablename => alias"
558	if(!is_numeric($key))	952	if(!is_numeric($key))
559	$str .= $this->_build_from($key)." AS $col";	953	$str .= $this->_build_from($key)." AS $col";
560	else	954	else
...		...
562	}	956	}
563	return $str;	957	return $str;
564	}	958	}
565	else	959
566	return $from;	960	return $from;
567	}	961	}
		962
		963	// --------------------------------------------------------------------
		964
568	/**	965	/**
569	* Builds the WHERE part of the query.	966	* Builds the WHERE part of the query.
570	*	967	*
		968	* @access private
571	* @param $select The where data	969	* @param $select The where data
572	*	970	*
573	* @return A string	971	* @return A string
574	*/	972	*/
575	function _build_where($where){	973	function _build_where($where){
576	if(is_object($where)){	974	if(is_object($where)){
577	return $where->_build_get();	975	return $where->_build_get_query();
578	}	976	}
579	elseif(is_array($where)){	977	elseif(is_array($where)){
		978
580	$str = '';	979	$str = '';
581	$i = 0;	980	$i = 0;
		981
582	foreach($where as $key => $col){	982	foreach($where as $key => $col){
		983	// these are modifiers that should be preserved
583	if(in_array($col,array('OR','NOT','IN','LIKE'))){	984	if(in_array($col,array('OR','NOT','IN','LIKE'))){
584	$str .= " $col ";	985	$str .= " $col ";
585	$i = 0;	986	$i = 0;
586	continue;	987	continue;
587	}	988	}
		989
		990	// add AND:s if needed
588	$str .= $i++ > 0 ? " AND " : '';	991	$str .= $i++ > 0 ? " AND " : '';
		992
		993	// not numeric tells us that we have a column name
589	if(!is_numeric($key)){	994	if(!is_numeric($key)){
590	if(!$this->_has_operator($key)){	995
		996	// do we need to add an equal sign?
		997	if( ! $this->_has_operator($key)){
591	if(is_null($col))	998	if(is_null($col))
592	$str .= "$key IS NULL";	999	$str .= "$key IS NULL";
593	else	1000	else
...		...
597	$str .= "$key ".$this->_build_where($col);	1004	$str .= "$key ".$this->_build_where($col);
598	}	1005	}
599	}	1006	}
600	else	1007	else{
601	$str .= $this->_build_where($col);	1008	$str .= $this->_build_where($col);
602	}	1009	}
		1010	} // endforeach
603	return $str;	1011	return $str;
604	}	1012	}
605	else	1013
606	return $where;	1014	return $where;
607	}	1015	}
		1016
		1017	// --------------------------------------------------------------------
		1018
608	/**	1019	/**
		1020	* Determines if a string has an operator.
		1021	*
609	* From CI_active_rec.	1022	* From CI_active_rec.
		1023	*
		1024	* @access private
		1025	* @param $str The string to be filtered
		1026	*
		1027	* @return string
610	*/	1028	*/
611	function _has_operator($str){	1029	function _has_operator($str){
612	$str = trim($str);	1030	$str = trim($str);
613	if (!preg_match("/(\s\|<\|>\|!\|=\|is null\|is not null)/i", $str)){	1031	if ( ! preg_match("/(\s\|<\|>\|!\|=\|is null\|is not null)/i", $str)){
614	return FALSE;	1032	return FALSE;
615	}	1033	}
616	return true;	1034	return true;
617	}	1035	}
		1036
		1037	// --------------------------------------------------------------------
		1038
618	function escape($str){	1039	function escape($str){
619	$CI =& get_instance();	1040	$CI =& get_instance();
620	return $CI->db->escape($str);	1041	return $CI->db->escape($str);
621	}	1042	}
		1043
		1044	// --------------------------------------------------------------------
		1045
622	function _protect_identifiers($str){	1046	function _protect_identifiers($str){
623	$CI =& get_instance();	1047	$CI =& get_instance();
624	return $CI->db->_protect_identifiers($str);	1048	return $CI->db->_protect_identifiers($str);
625	}	1049	}
		1050
		1051	// --------------------------------------------------------------------
		1052
626	function _protect_identifiers_walk(&$str){	1053	function _protect_identifiers_walk(&$str){
627	$CI =& get_instance();	1054	$CI =& get_instance();
628	$str = $CI->db->_protect_identifiers($str);	1055	$str = $CI->db->_protect_identifiers($str);
629	}	1056	}
630	}	1057	}
631		1058
		1059	/**
		1060	* @}
		1061	*/
		1062
632	/* End of file ignitedquery.php */	1063	/* End of file ignitedquery.php */

IgnitedRecord

root/trunk/application/libraries/ignitedquery.php