1: <?php  
  2: 
  3: /**
  4:  * Database access controller class
  5:  *
  6:  * @package objSQL
  7:  * @version 3.0.0
  8:  * @author MT Jordan <mtjo62@gmail.com>
  9:  * @copyright 2012
 10:  * @license zlib/libpng
 11:  * @link http://objsql.sourceforge.net 
 12:  */
 13: 
 14: class objSQL extends obj_access
 15: {
 16:     /**********************************************
 17:      * Internal private variables
 18:      *********************************************/
 19: 
 20:     /**
 21:      * Database connection object
 22:      *
 23:      * @access public
 24:      * @var    mixed
 25:      */
 26:     public $obj_connection = false;
 27:     
 28:     /**
 29:      * Database type array contains all supported database drivers
 30:      *
 31:      * @access private
 32:      * @var    array
 33:      */
 34:     private $obj_db_array = array( 'mysql',
 35:                                    'pgsql',
 36:                                    'sqlite3',
 37:                                    'sqlsrv' );
 38: 
 39:     /**
 40:      * Database type as set in datasource argument.
 41:      *
 42:      * @access private
 43:      * @var    str
 44:      */
 45:     private $obj_db_type;  
 46:                                  
 47:     /**
 48:      * Database connection class instance
 49:      *
 50:      * @access private
 51:      * @var    mixed
 52:      */
 53:     private $obj_instance;
 54:     
 55:     /**
 56:      * Database prepared statement string
 57:      *
 58:      * @access private
 59:      * @var    str
 60:      */
 61:     private $obj_prepare;
 62:     
 63:     /**
 64:      * Database prepared statement string
 65:      *
 66:      * @access private
 67:      * @var    str
 68:      */
 69:     private $obj_statement;
 70:     
 71:     /**
 72:      * Database transaction string
 73:      *
 74:      * @access private
 75:      * @var    str
 76:      */
 77:     private $obj_transaction;
 78:     
 79:     /**
 80:      * objSQL version
 81:      *
 82:      * @access private
 83:      * @var    str
 84:      */
 85:     private $obj_version = '3.0.0';
 86:     
 87:     /**********************************************
 88:      * Public class methods
 89:      *********************************************/
 90: 
 91:     /**
 92:      * Constructor
 93:      *
 94:      * @access public
 95:      * @param  mixed $datasource
 96:      */
 97:     public function __construct( $datasource )
 98:     {
 99:         $this->obj_helper_datasource( $datasource );
100:     }
101:     
102:     /**
103:      * Closes current database connection
104:      *
105:      * @access public
106:      * @return bool
107:      */
108:     public function obj_close()
109:     {
110:         return $this->obj_instance->obj_db_close();
111:     }
112:     
113:     /**
114:      * Executes delete query and returns resultset object/resource
115:      *
116:      * @access public
117:      * @param str $table
118:      * @param str $where
119:      * @return mixed
120:      */
121:     public function obj_delete( $table, $where=false )
122:     {
123:         $query_stmt = new $this->obj_statement( $this->obj_helper_delete( $table, $where ), $this );
124:         
125:         return $query_stmt->obj_query_execute();
126:     }
127:     
128:     /**
129:      * Returns error flag for current connection instance - true/false
130:      *
131:      * @access public
132:      * @return bool
133:      */
134:     public function obj_error()
135:     {
136:         return $this->obj_instance->obj_db_error();
137:     }
138:     
139:     /**
140:      * Returns error message for current connection instance
141:      *
142:      * @access public
143:      * @return str
144:      */
145:     public function obj_error_message()
146:     {
147:         return $this->obj_instance->obj_db_message();
148:     }
149:     
150:     /**
151:      * Returns escaped string data for database insertion
152:      *
153:      * @access public
154:      * @param  str $data
155:      * @return str
156:      */
157:     public function obj_escape( $data )
158:     {
159:         return $this->obj_instance->obj_db_escape_data( $data );
160:     }
161:     
162:     /**
163:      * Returns associative array with general database and script information
164:      *
165:      * @access public
166:      * @return array
167:      */
168:     public function obj_info()
169:     {
170:         return array( 'OBJSQL_VERSION'   => $this->obj_version,
171:                       'DATABASE_NAME'    => $this->obj_instance->obj_db_info()[2],
172:                       'DATABASE_TYPE'    => $this->obj_db_type,
173:                       'DATABASE_VERSION' => $this->obj_instance->obj_db_info()[0],
174:                       'DATABASE_CHARSET' => $this->obj_instance->obj_db_info()[1],
175:                       'PHP_VERSION'      => phpversion() );
176:     }
177:     
178:     /**
179:      * executes insert query  and returns resultset object/resource
180:      *
181:      * @access public
182:      * @param  str   $table
183:      * @param  array $data_array
184:      * @return mixed
185:      */
186:     public function obj_insert( $table, $data_array )
187:     {
188:         //$data_array MUST be a key value pair array - value can be a one dim array or comma delimited string
189:         $query_stmt = new $this->obj_statement( $this->obj_helper_insert( $table, $data_array ), $this );
190: 
191:         return $query_stmt->obj_query_execute();
192:     }
193:     
194:     /**
195:      * Executes select paging query and returns resultset object/resource and number of pages via $limit
196:      *
197:      * @access public
198:      * @param  str $table
199:      * @param  str $cols
200:      * @param  str $where
201:      * @param  str $order_by
202:      * @param  int $limit
203:      * @param  int $offset
204:      * @return array
205:      */
206:     public function obj_paging( $table, $cols=false, $where=false, $order_by=false, $limit=1, $offset=1 )
207:     {
208:         //make sure $limit & $offset are unsigned ints > 0
209:         $query_offset = ( is_numeric( $offset ) && $offset > 0 ) ? ( int )$offset : 1;
210:         $query_limit  = ( is_numeric( $limit ) && $limit > 0 ) ? ( int )$limit : 1;
211:         
212:         $result = $this->obj_select( $table ); 
213:         $last_page = ceil( $result->obj_num_rows()/$query_limit );
214:         $result->obj_free_result();
215:         
216:         $query_stmt = new $this->obj_statement( $this->obj_helper_paging( $table, $cols, $where, $order_by, $query_limit, $query_offset ), $this );
217: 
218:         return array( $query_stmt->obj_query_execute(), $last_page );
219:     }
220:     
221:     /**
222:      * Returns prepared statement instance
223:      *
224:      * @access public
225:      * @param  str   $query
226:      * @param  array $param_vars
227:      * @return mixed
228:      */
229:     public function obj_prepare_statement( $query, $param_vars=false )
230:     {
231:         return new $this->obj_prepare( $this, $query, $param_vars );
232:     }
233:     
234:     /**
235:      * Executes non-prepared query and returns resultset object/resource
236:      *
237:      * @access public
238:      * @param  str $query
239:      * @return mixed
240:      */
241:     public function obj_query( $query )
242:     {
243:         $query_stmt = new $this->obj_statement( $query, $this );
244: 
245:         return $query_stmt->obj_query_execute();
246:     }
247:     
248:     /**
249:      * Executes select query and returns resultset object/resource
250:      *
251:      * @access public
252:      * @param  str $table
253:      * @param  str $cols
254:      * @param  str $where
255:      * @param  str $order_by
256:      * @param  str $sort_order
257:      * @return mixed
258:      */
259:     public function obj_select( $table, $cols=false, $where=false, $order_by=false, $sort_order=false )
260:     {
261:         $query_stmt = new $this->obj_statement( $this->obj_helper_select( $table, $cols, $where, $order_by, $sort_order ), $this );
262:         
263:          return $query_stmt->obj_query_execute();
264:     }
265: 
266:     /**
267:      * Begins transaction     
268:      * 
269:      * @access public
270:      * @return mixed
271:      */
272:     public function obj_transaction()
273:     {
274:         return new $this->obj_transaction( $this->obj_connection );
275:     }
276: 
277:     /**
278:      * executes update query and returns resultset object/resource
279:      *
280:      * @access public
281:      * @param  str   $table
282:      * @param  array $data_array
283:      * @param  str   $where
284:      * @return mixed
285:      */
286:     public function obj_update( $table, $data_array, $where=false )
287:     {
288:         //$data_array MUST be a key value pair array
289:         $query_stmt = new $this->obj_statement( $this->obj_helper_update( $table, $data_array, $where ), $this );
290:         
291:         return $query_stmt->obj_query_execute();
292:     }
293:     
294:     /**********************************************
295:      * Private helper methods
296:      *********************************************/
297:     
298:     /**
299:      * Sets database connection instance/vars and loads database drivers
300:      *
301:      * @access private
302:      * @param  mixed $datasource
303:      * @return mixed 
304:      */
305:     private function obj_helper_datasource( $datasource )
306:     {
307:         $obj_datasource = ( is_array( $datasource ) ) ? $datasource : explode( ',', $datasource );
308:         
309:         if ( in_array( strtolower( $obj_datasource[0] ), $this->obj_db_array ) )
310:         {
311:             $this->obj_db_type = strtolower( $obj_datasource[0] );
312:             $this->obj_statement = "{$this->obj_db_type}_statement";
313:             $this->obj_prepare = "{$this->obj_db_type}_prepare";
314:             $this->obj_transaction = "{$this->obj_db_type}_transaction";
315:             
316:             require_once "drivers/{$this->obj_db_type}.php";
317:             
318:             $obj_connection = "{$this->obj_db_type}_connection";
319:             $this->obj_instance = new $obj_connection( $obj_datasource );
320:             
321:             if ( $this->obj_instance )
322:                 return $this->obj_connection = $this->obj_instance->obj_db_connection();
323:             else
324:                 return false;
325:         }
326:         else
327:         {
328:             trigger_error( "Unsupported database type: {$obj_datasource[0]}", E_USER_WARNING ); 
329:             
330:             return false;
331:         }    
332:     }
333:     
334:     /**
335:      * Returns delete query SQL statement
336:      *
337:      * @access private
338:      * @param str $table
339:      * @param str $where
340:      * @return mixed
341:      */
342:     private function obj_helper_delete( $table, $where=false )
343:     {
344:         $query_where = ( !trim( $where ) ) ? '' : "WHERE $where"; 
345:         
346:         return "DELETE FROM $table $query_where";
347:     }
348:     
349:     /**
350:      * Returns insert query SQL statement
351:      *
352:      * @access private
353:      * @param  str   $table
354:      * @param  array $data_array
355:      * @return str
356:      */
357:     private function obj_helper_insert( $table, $data_array )
358:     {
359:         //$data_array MUST be a key value pair array - value can be a one dim array or comma delimited string
360:         $array_keys = array_keys( $data_array );
361:         $array_vals = array_values( $data_array );
362:         
363:         //if array_vals[$i] is a string, convert to array
364:         for ( $i = 0; $i < count( $array_vals ); $i++ )
365:         {
366:             if ( !is_array( $array_vals[$i] ) )
367:             {
368:                 $temp_array = explode( ',', $array_vals[$i] );
369:                 $array_vals[$i] = $temp_array;
370:             }
371:         }
372:         
373:         for ( $i = 0; $i < count( $array_vals[0] ); $i++ )
374:         {
375:             $query_insert[] = '(';
376:             
377:             for ( $j = 0; $j < count( $array_vals ); $j++ )
378:             {
379:                 if ( is_string( $array_vals[$j][$i] ) && !is_numeric( $array_vals[$j][$i] ) )
380:                     $query_insert[] = "'{$array_vals[$j][$i]}'";  
381:                 else    
382:                     $query_insert[] = $array_vals[$j][$i];
383:             }
384:             
385:             $query_insert[] = ')';
386:         }
387: 
388:         $query_cols = implode( ',', $array_keys ); 
389:         $query_vals = str_replace( array( ',),(,', ',)', '(,' ), array( '),(', ')', '('), implode( ',', $query_insert ) );
390:                 
391:         return "INSERT INTO $table ($query_cols) VALUES $query_vals";
392:     }
393:     
394:     /**
395:      * Returns select paging query SQL statement
396:      *
397:      * @access private
398:      * @param  str $table
399:      * @param  int $limit 
400:      * @param  int $offset 
401:      * @param  str $cols
402:      * @param  str $where
403:      * @param  str $order_by
404:      * @return str
405:      */
406:     private function obj_helper_paging( $table, $cols=false, $where=false, $order_by=false, $limit=1, $offset=1 )
407:     {
408:         $query_cols  = ( !trim( $cols ) ) ? '*' : $cols;
409:         $query_where = ( !trim( $where ) ) ? '' : "WHERE $where"; 
410:         $query_order = ( !trim( $order_by ) ) ? '' : "ORDER BY $order_by";
411:         $query_offset = ( ( $offset - 1 ) * $limit );
412:         
413:         if ( $this->obj_db_type === 'sqlsrv' )
414:         {
415:             $query_sql = "WITH temp_table AS ( 
416:                           SELECT ROW_NUMBER() OVER ( $query_order ) AS RowNumber, $query_cols 
417:                           FROM $table $query_where ) 
418:                           SELECT * 
419:                           FROM temp_table 
420:                           WHERE RowNumber > $query_offset AND RowNumber <= ( $limit + $query_offset )";
421:         }
422:         else
423:         {
424:             if ( $this->obj_db_type === 'pgsql' )
425:                 $limit_str = "LIMIT $limit OFFSET $query_offset";
426:             else
427:                 $limit_str = "LIMIT $query_offset,$limit"; 
428:                 
429:             $query_sql = "SELECT $query_cols FROM $table $query_where $query_order $limit_str";
430:         }
431:         
432:         return $query_sql;
433:     }
434:     
435:     /**
436:      * Returns select query SQL statement
437:      *
438:      * @access private
439:      * @param  str $table
440:      * @param  str $cols
441:      * @param  str $where
442:      * @param  str $order_by
443:      * @param  str $sort_order
444:      * @return str
445:      */
446:     private function obj_helper_select( $table, $cols=false, $where=false, $order_by=false, $sort_order=false )
447:     {
448:         $query_cols  = ( !trim( $cols ) ) ? '*' : $cols;
449:         $query_where = ( !trim( $where ) ) ? '' : "WHERE $where"; 
450:         $query_order = ( !trim( $order_by ) ) ? '' : "ORDER BY $order_by";
451:         $query_sort  = ( !trim( $sort_order ) || strtolower( $sort_order ) != 'desc' || strtolower( $sort_order ) != 'asc' ) ? '' : $sort_order;
452:         
453:         return "SELECT $query_cols FROM $table $query_where $query_order $query_sort";
454:     }
455:     
456:     /**
457:      * Returns update query SQL statement
458:      *
459:      * @access private
460:      * @param  str   $table
461:      * @param  array $data_array
462:      * @param  str   $where
463:      * @return str
464:      */
465:     private function obj_helper_update( $table, $data_array, $where=false )
466:     {
467:         //$array MUST be a key value pair array
468:         $query_cols   = array_keys( $data_array );
469:         $query_vals   = array_values( $data_array );
470:         $query_update = '';
471: 
472:         for ( $i = 0; $i < count( $data_array ); $i++ )
473:         {
474:             if ( is_string( $query_vals[$i] ) && !is_numeric( $query_vals[$i] ) )
475:                 $query_update .= "{$query_cols[$i]}='{$query_vals[$i]}',";
476:             else
477:                 $query_update .= "{$query_cols[$i]}={$query_vals[$i]},";
478:         }
479: 
480:         $query_update = rtrim( $query_update, ',' );
481:         $query_where  = ( !trim( $where ) ) ? '' : "WHERE $where";
482:                 
483:         return "UPDATE $table SET $query_update $query_where";
484:     }
485:     
486:     
487:     /*****************************************************************************************************
488:      * Abstract protected methods - prevents calling these methods outside of the objSQL controller class
489:      *****************************************************************************************************/
490:     
491:     /**
492:      * Protected methods from extended connection classes
493:      *
494:      * @access protected
495:      */
496:     protected function obj_db_close() {}
497:     protected function obj_db_connection() {}
498:     protected function obj_db_error() {}
499:     protected function obj_db_escape_data( $str ) {}
500:     protected function obj_db_message() {}
501:     protected function obj_db_info() {}
502: }
503: 
504: abstract class obj_access 
505: {
506:     /**
507:      * Abstract protected methods from extended connection classes
508:      *
509:      * @access protected
510:      */
511:     abstract protected function obj_db_close();
512:     abstract protected function obj_db_connection();
513:     abstract protected function obj_db_error();
514:     abstract protected function obj_db_escape_data( $str );
515:     abstract protected function obj_db_message();
516:     abstract protected function obj_db_info();
517: }
518: 
519: ?>