1: <?php  
  2: 
  3: /**
  4:  * Database access controller class
  5:  *
  6:  * @package objSQL
  7:  * @version 3.0.1
  8:  * @author MT Jordan <mtjo62@gmail.com>
  9:  * @copyright 2013
 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.1';
 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:         // 04/21/13 - added where clause to fix last page bug
212:         $query_where = ( !trim( $where ) ) ? '' : $where; 
213:         
214:         $result = $this->obj_select( $table, '*', $query_where ); 
215:         $last_page = ceil( $result->obj_num_rows()/$query_limit );
216:         $result->obj_free_result();
217:         
218:         $query_stmt = new $this->obj_statement( $this->obj_helper_paging( $table, $cols, $where, $order_by, $query_limit, $query_offset ), $this );
219: 
220:         return array( $query_stmt->obj_query_execute(), $last_page );
221:     }
222:     
223:     /**
224:      * Returns prepared statement instance
225:      *
226:      * @access public
227:      * @param  str   $query
228:      * @param  array $param_vars
229:      * @return mixed
230:      */
231:     public function obj_prepare_statement( $query, $param_vars=false )
232:     {
233:         return new $this->obj_prepare( $this, $query, $param_vars );
234:     }
235:     
236:     /**
237:      * Executes non-prepared query and returns resultset object/resource
238:      *
239:      * @access public
240:      * @param  str $query
241:      * @return mixed
242:      */
243:     public function obj_query( $query )
244:     {
245:         $query_stmt = new $this->obj_statement( $query, $this );
246: 
247:         return $query_stmt->obj_query_execute();
248:     }
249:     
250:     /**
251:      * Executes select query and returns resultset object/resource
252:      *
253:      * @access public
254:      * @param  str $table
255:      * @param  str $cols
256:      * @param  str $where
257:      * @param  str $order_by
258:      * @param  str $sort_order
259:      * @return mixed
260:      */
261:     public function obj_select( $table, $cols=false, $where=false, $order_by=false, $sort_order=false )
262:     {
263:         $query_stmt = new $this->obj_statement( $this->obj_helper_select( $table, $cols, $where, $order_by, $sort_order ), $this );
264:         
265:          return $query_stmt->obj_query_execute();
266:     }
267: 
268:     /**
269:      * Begins transaction     
270:      * 
271:      * @access public
272:      * @return mixed
273:      */
274:     public function obj_transaction()
275:     {
276:         return new $this->obj_transaction( $this->obj_connection );
277:     }
278: 
279:     /**
280:      * executes update query and returns resultset object/resource
281:      *
282:      * @access public
283:      * @param  str   $table
284:      * @param  array $data_array
285:      * @param  str   $where
286:      * @return mixed
287:      */
288:     public function obj_update( $table, $data_array, $where=false )
289:     {
290:         //$data_array MUST be a key value pair array
291:         $query_stmt = new $this->obj_statement( $this->obj_helper_update( $table, $data_array, $where ), $this );
292:         
293:         return $query_stmt->obj_query_execute();
294:     }
295:     
296:     /**********************************************
297:      * Private helper methods
298:      *********************************************/
299:     
300:     /**
301:      * Sets database connection instance/vars and loads database drivers
302:      *
303:      * @access private
304:      * @param  mixed $datasource
305:      * @return mixed 
306:      */
307:     private function obj_helper_datasource( $datasource )
308:     {
309:         $obj_datasource = ( is_array( $datasource ) ) ? $datasource : explode( ',', $datasource );
310:         
311:         if ( in_array( strtolower( $obj_datasource[0] ), $this->obj_db_array ) )
312:         {
313:             $this->obj_db_type = strtolower( $obj_datasource[0] );
314:             $this->obj_statement = "{$this->obj_db_type}_statement";
315:             $this->obj_prepare = "{$this->obj_db_type}_prepare";
316:             $this->obj_transaction = "{$this->obj_db_type}_transaction";
317:             
318:             require_once "drivers/{$this->obj_db_type}.php";
319:             
320:             $obj_connection = "{$this->obj_db_type}_connection";
321:             $this->obj_instance = new $obj_connection( $obj_datasource );
322:             
323:             if ( $this->obj_instance )
324:                 return $this->obj_connection = $this->obj_instance->obj_db_connection();
325:             else
326:                 return false;
327:         }
328:         else
329:         {
330:             trigger_error( "Unsupported database type: {$obj_datasource[0]}", E_USER_WARNING ); 
331:             
332:             return false;
333:         }    
334:     }
335:     
336:     /**
337:      * Returns delete query SQL statement
338:      *
339:      * @access private
340:      * @param str $table
341:      * @param str $where
342:      * @return mixed
343:      */
344:     private function obj_helper_delete( $table, $where=false )
345:     {
346:         $query_where = ( !trim( $where ) ) ? '' : "WHERE $where"; 
347:         
348:         return "DELETE FROM $table $query_where";
349:     }
350:     
351:     /**
352:      * Returns insert query SQL statement
353:      *
354:      * @access private
355:      * @param  str   $table
356:      * @param  array $data_array
357:      * @return str
358:      */
359:     private function obj_helper_insert( $table, $data_array )
360:     {
361:         //$data_array MUST be a key value pair array - value can be a one dim array or comma delimited string
362:         $array_keys = array_keys( $data_array );
363:         $array_vals = array_values( $data_array );
364:         
365:         //if array_vals[$i] is a string, convert to array
366:         for ( $i = 0; $i < count( $array_vals ); $i++ )
367:         {
368:             if ( !is_array( $array_vals[$i] ) )
369:             {
370:                 $temp_array = explode( ',', $array_vals[$i] );
371:                 $array_vals[$i] = $temp_array;
372:             }
373:         }
374:         
375:         for ( $i = 0; $i < count( $array_vals[0] ); $i++ )
376:         {
377:             $query_insert[] = '(';
378:             
379:             for ( $j = 0; $j < count( $array_vals ); $j++ )
380:             {
381:                 if ( is_string( $array_vals[$j][$i] ) && !is_numeric( $array_vals[$j][$i] ) )
382:                     $query_insert[] = "'{$array_vals[$j][$i]}'";  
383:                 else    
384:                     $query_insert[] = $array_vals[$j][$i];
385:             }
386:             
387:             $query_insert[] = ')';
388:         }
389: 
390:         $query_cols = implode( ',', $array_keys ); 
391:         $query_vals = str_replace( array( ',),(,', ',)', '(,' ), array( '),(', ')', '('), implode( ',', $query_insert ) );
392:                 
393:         return "INSERT INTO $table ($query_cols) VALUES $query_vals";
394:     }
395:     
396:     /**
397:      * Returns select paging query SQL statement
398:      *
399:      * @access private
400:      * @param  str $table
401:      * @param  int $limit 
402:      * @param  int $offset 
403:      * @param  str $cols
404:      * @param  str $where
405:      * @param  str $order_by
406:      * @return str
407:      */
408:     private function obj_helper_paging( $table, $cols=false, $where=false, $order_by=false, $limit=1, $offset=1 )
409:     {
410:         $query_cols  = ( !trim( $cols ) ) ? '*' : $cols;
411:         $query_where = ( !trim( $where ) ) ? '' : "WHERE $where"; 
412:         $query_order = ( !trim( $order_by ) ) ? '' : "ORDER BY $order_by";
413:         $query_offset = ( ( $offset - 1 ) * $limit );
414:         
415:         if ( $this->obj_db_type === 'sqlsrv' )
416:         {
417:             // check for SQL Server 2012+
418:             if ( explode( '.',  $this->obj_instance->obj_db_info()[0] ) < 11 )
419:             {
420:                 $query_sql = "WITH temp_table AS ( 
421:                               SELECT ROW_NUMBER() OVER ( $query_order ) AS RowNumber, $query_cols 
422:                               FROM $table $query_where ) 
423:                               SELECT * 
424:                               FROM temp_table 
425:                               WHERE RowNumber > $query_offset AND RowNumber <= ( $limit + $query_offset )";
426:             }
427:             else
428:             {
429:                 // added to support SQL Server 2012 - throws cursor exception using row_number method
430:                 $query_sql = "SELECT $query_cols FROM $table $query_where $query_order
431:                               OFFSET $query_offset ROWS
432:                               FETCH NEXT $limit ROWS ONLY"; 
433:             }                             
434:         }
435:         else
436:         {
437:             if ( $this->obj_db_type === 'pgsql' )
438:                 $limit_str = "LIMIT $limit OFFSET $query_offset";
439:             else
440:                 $limit_str = "LIMIT $query_offset,$limit"; 
441:                 
442:             $query_sql = "SELECT $query_cols FROM $table $query_where $query_order $limit_str";
443:         }
444:         
445:         return $query_sql;
446:     }
447:     
448:     /**
449:      * Returns select query SQL statement
450:      *
451:      * @access private
452:      * @param  str $table
453:      * @param  str $cols
454:      * @param  str $where
455:      * @param  str $order_by
456:      * @param  str $sort_order
457:      * @return str
458:      */
459:     private function obj_helper_select( $table, $cols=false, $where=false, $order_by=false, $sort_order=false )
460:     {
461:         $query_cols  = ( !trim( $cols ) ) ? '*' : $cols;
462:         $query_where = ( !trim( $where ) ) ? '' : "WHERE $where"; 
463:         $query_order = ( !trim( $order_by ) ) ? '' : "ORDER BY $order_by";
464:         $query_sort  = ( !trim( $sort_order ) || strtolower( $sort_order ) != 'desc' || strtolower( $sort_order ) != 'asc' ) ? '' : $sort_order;
465:         
466:         return "SELECT $query_cols FROM $table $query_where $query_order $query_sort";
467:     }
468:     
469:     /**
470:      * Returns update query SQL statement
471:      *
472:      * @access private
473:      * @param  str   $table
474:      * @param  array $data_array
475:      * @param  str   $where
476:      * @return str
477:      */
478:     private function obj_helper_update( $table, $data_array, $where=false )
479:     {
480:         //$array MUST be a key value pair array
481:         $query_cols   = array_keys( $data_array );
482:         $query_vals   = array_values( $data_array );
483:         $query_update = '';
484: 
485:         for ( $i = 0; $i < count( $data_array ); $i++ )
486:         {
487:             if ( is_string( $query_vals[$i] ) && !is_numeric( $query_vals[$i] ) )
488:                 $query_update .= "{$query_cols[$i]}='{$query_vals[$i]}',";
489:             else
490:                 $query_update .= "{$query_cols[$i]}={$query_vals[$i]},";
491:         }
492: 
493:         $query_update = rtrim( $query_update, ',' );
494:         $query_where  = ( !trim( $where ) ) ? '' : "WHERE $where";
495:                 
496:         return "UPDATE $table SET $query_update $query_where";
497:     }
498:     
499:     
500:     /*****************************************************************************************************
501:      * Abstract protected methods - prevents calling these methods outside of the objSQL controller class
502:      *****************************************************************************************************/
503:     
504:     /**
505:      * Protected methods from extended connection classes
506:      *
507:      * @access protected
508:      */
509:     protected function obj_db_close() {}
510:     protected function obj_db_connection() {}
511:     protected function obj_db_error() {}
512:     protected function obj_db_escape_data( $str ) {}
513:     protected function obj_db_message() {}
514:     protected function obj_db_info() {}
515: }
516: 
517: abstract class obj_access 
518: {
519:     /**
520:      * Abstract protected methods from extended connection classes
521:      *
522:      * @access protected
523:      */
524:     abstract protected function obj_db_close();
525:     abstract protected function obj_db_connection();
526:     abstract protected function obj_db_error();
527:     abstract protected function obj_db_escape_data( $str );
528:     abstract protected function obj_db_message();
529:     abstract protected function obj_db_info();
530: }
531: 
532: ?>