1: <?php
2:
3: /**
4: * MySQL database server access classes
5: *
6: * @package objSQL
7: * @version 3.3.0
8: * @author MT Jordan <mtjo62@gmail.com>
9: * @copyright 2013
10: * @license zlib/libpng
11: * @link http://objsql.sourceforge.net
12: */
13:
14:
15: /*************************************************************************************************************
16: * Begin database connection/utility class
17: ************************************************************************************************************/
18:
19:
20: class mysql_connection extends obj_access
21: {
22: /**********************************************
23: * Internal variables
24: *********************************************/
25:
26: /**
27: * Database connection object
28: *
29: * @access private
30: * @var mixed
31: */
32: private $obj_connection = false;
33:
34: /**
35: * Database connection information
36: *
37: * @access private
38: * @var array
39: */
40: private $obj_datasource;
41:
42: /**********************************************
43: * Class methods
44: *********************************************/
45:
46: /**
47: * Constructor
48: *
49: * @access public
50: * @param array $datasource
51: */
52: public function __construct( $datasource )
53: {
54: $this->obj_datasource = $datasource;
55: }
56:
57: /**
58: * Returns database connection object
59: *
60: * @access protected
61: * @return mixed
62: */
63: protected function obj_db_connection()
64: {
65: if ( ( array_key_exists( 5, $this->obj_datasource ) ) && is_numeric( trim( $this->obj_datasource[5] ) ) )
66: $db_connection = new mysqli( $this->obj_datasource[1], $this->obj_datasource[2], $this->obj_datasource[3], $this->obj_datasource[4], $this->obj_datasource[5] );
67: else
68: $db_connection = new mysqli( $this->obj_datasource[1], $this->obj_datasource[2], $this->obj_datasource[3], $this->obj_datasource[4] );
69:
70: return ( is_object( $db_connection ) ) ? $this->obj_connection = $db_connection : false;
71: }
72:
73: /**
74: * Closes connection to database server
75: *
76: * @access protected
77: * @return bool
78: */
79: protected function obj_db_close()
80: {
81: return $this->obj_connection->close();
82: }
83:
84: /**
85: * Returns error flag for current connection instance
86: *
87: * @access protected
88: * @return bool
89: */
90: protected function obj_db_error()
91: {
92: return ( $this->obj_connection->errno ) ? true : false;
93: }
94:
95: /**
96: * Escapes string data for database insertion
97: *
98: * @access protected
99: * @param mixed $data
100: * @return mixed
101: */
102: protected function obj_db_escape_data( $data )
103: {
104: return $this->obj_connection->real_escape_string( $data );
105: }
106:
107: /**
108: * Returns error message for current connection instance
109: *
110: * @access protected
111: * @return mixed
112: */
113: protected function obj_db_message()
114: {
115: return ( $this->obj_db_error() ) ? $this->obj_connection->error : null;
116: }
117:
118: /**
119: * Returns database server information
120: *
121: * @access protected
122: * @return array
123: */
124: protected function obj_db_info()
125: {
126: return array( $this->obj_connection->server_version, $this->obj_connection->character_set_name(), $this->obj_datasource[4] );
127: }
128:
129: /**
130: * Returns row count for named table with arguments
131: * Returns -1 if undetermined or failure
132: *
133: * @access protected
134: * @param str $table
135: * @param str $cols
136: * @param str $where
137: * @return int
138: */
139: protected function obj_db_rowcount( $table, $cols=false, $where=false )
140: {
141: $query_cols = ( !trim( $cols ) ) ? '*' : $cols;
142: $query_where = ( !trim( $where ) ) ? '' : "WHERE $where";
143:
144: $query_stmt = $this->obj_connection->query( "SELECT COUNT($query_cols) FROM $table $query_where" );
145:
146: if ( !$query_stmt || !is_object( $query_stmt ) )
147: return -1;
148:
149: while ( $num_rows = $query_stmt->fetch_row() )
150: $rowcount = ( $num_rows[0] >= 0 ) ? $num_rows[0] : -1;
151:
152: $query_stmt->free();
153:
154: return $rowcount;
155: }
156: }
157:
158:
159: /*************************************************************************************************************
160: * End database connection class
161: ************************************************************************************************************/
162:
163:
164: /*************************************************************************************************************
165: * Begin database statement class
166: ************************************************************************************************************/
167:
168:
169: class mysql_statement
170: {
171: /**********************************************
172: * Internal variables
173: *********************************************/
174:
175: /**
176: * Database connection object
177: *
178: * @access private
179: * @var mixed
180: */
181: private $obj_connection;
182:
183: /**
184: * Query string
185: *
186: * @access private
187: * @var str
188: */
189: private $obj_query;
190:
191: /**********************************************
192: * Class methods
193: *********************************************/
194:
195: /**
196: * Constructor
197: *
198: * @access public
199: * @param str $query
200: * @param mixed $connection
201: */
202: function __construct( $query, $connection )
203: {
204: $this->obj_connection = $connection->obj_connection;
205: $this->obj_query = $query;
206: }
207:
208: /**
209: * Executes general query and returns resultset object
210: *
211: * @access public
212: * @return mixed
213: */
214: public function obj_query_execute()
215: {
216: $query_stmt = $this->obj_connection->query( $this->obj_query );
217:
218: return ( $query_stmt || is_object( $query_stmt ) ) ? new mysql_resultset( $query_stmt, $this->obj_connection ) : false;
219: }
220: }
221:
222:
223: /*************************************************************************************************************
224: * End database statement class
225: ************************************************************************************************************/
226:
227:
228: /*************************************************************************************************************
229: * Begin database prepared statement class
230: ************************************************************************************************************/
231:
232:
233: class mysql_prepare
234: {
235: /**********************************************
236: * Internal variables
237: *********************************************/
238:
239: /**
240: * Database connection object
241: *
242: * @access private
243: * @var mixed
244: */
245: private $obj_connection;
246:
247: /**
248: * Prepared statement parameter values
249: *
250: * @access private
251: * @var array
252: */
253: private $obj_parameter = array();
254:
255: /**
256: * Prepared statement parameter types
257: *
258: * @access private
259: * @var array
260: */
261: private $obj_parameter_type = array();
262:
263: /**
264: * Prepared statement instance
265: *
266: * @access public
267: * @var bool
268: */
269: private $obj_prepare_instance;
270:
271: /**
272: * Prepared statement parameter types
273: *
274: * @access private
275: * @var array
276: */
277: private $obj_query_type;
278:
279: /**********************************************
280: * Class methods
281: *********************************************/
282:
283: /**
284: * Constructor
285: *
286: * @access public
287: * @param str $query
288: * @param mixed $connection
289: */
290: function __construct( $query, $connection )
291: {
292: $this->obj_connection = $connection->obj_connection;
293: $this->obj_prepare_init( $query );
294: }
295:
296: /**
297: * Sets parameters and parameter types for prepared statement
298: *
299: * @access public
300: * @param mixed $param
301: */
302: public function obj_bind( $param )
303: {
304: $this->obj_parameter_type[] = $this->obj_param_type( $param );
305: $this->obj_parameter[] = $param;
306: }
307:
308: /**
309: * Destroys prepared statement object
310: *
311: * @access public
312: * @return bool
313: */
314: public function obj_close_statement()
315: {
316: return $this->obj_prepare_instance->close();
317: }
318:
319: /**
320: * Binds parameters, executes prepared statement and returns resultset object
321: *
322: * @access public
323: * @return mixed
324: */
325: public function obj_execute()
326: {
327: call_user_func_array( 'mysqli_stmt_bind_param', array_merge( array( $this->obj_prepare_instance, implode( '', $this->obj_parameter_type ) ), $this->obj_param_values() ) );
328: $query_stmt = $this->obj_prepare_instance->execute();
329: $query_result = $this->obj_prepare_instance->get_result();
330:
331: if ( $query_stmt )
332: {
333: if ( !$query_result )
334: return new mysql_resultset( $query_stmt, $this->obj_connection );
335: else
336: return new mysql_resultset( $query_result, $this->obj_connection );
337: }
338: else
339: return false;
340: }
341:
342: /**
343: * Frees resultset memory from prepared statement object and resets binding parameters
344: *
345: * @access public
346: * @return bool
347: */
348: public function obj_free_statement()
349: {
350: $this->obj_parameter = array();
351: $this->obj_parameter_type = array();
352:
353: return $this->obj_prepare_instance->reset();
354: }
355:
356: /**
357: * Returns parameter datatype
358: *
359: * @access private
360: * @param mixed $param
361: * @return mixed
362: */
363: private function obj_param_type( $param )
364: {
365: $param = trim( $param );
366:
367: if ( is_numeric( $param ) )
368: {
369: if ( substr_count( $param, '.' ) )
370: return 'd';
371: else
372: return 'i';
373: }
374: elseif ( is_string( $param ) )
375: return 's';
376: else
377: return 'b';
378: }
379:
380: /**
381: * Returns by reference bound parameter values
382: *
383: * @access private
384: * @return array
385: */
386: private function obj_param_values()
387: {
388: $param = array();
389: $value = null;
390:
391: foreach( $this->obj_parameter as $key => $value )
392: $param[$key] = &$this->obj_parameter[$key];
393:
394: return $param;
395: }
396:
397: /**
398: * Returns prepared statement instance
399: *
400: * @access private
401: * @param str $query
402: * @return bool
403: */
404: private function obj_prepare_init( $query )
405: {
406: return $this->obj_prepare_instance = $this->obj_connection->prepare( $query );
407: }
408: }
409:
410:
411: /*************************************************************************************************************
412: * End database prepared statement class
413: ************************************************************************************************************/
414:
415:
416: /*************************************************************************************************************
417: * Begin database resultset class
418: ************************************************************************************************************/
419:
420:
421: class mysql_resultset
422: {
423: /**********************************************
424: * Internal variables
425: *********************************************/
426:
427: /**
428: * Database connection object
429: *
430: * @access private
431: * @var mixed
432: */
433: private $obj_connection;
434:
435: /**
436: * Query record
437: *
438: * @access private
439: * @var array
440: */
441: private $obj_record = array();
442:
443: /**
444: * Query resultset object
445: *
446: * @access private
447: * @var mixed
448: */
449: private $obj_result = false;
450:
451: /**********************************************
452: * Class methods
453: *********************************************/
454: /**
455: * Constructor
456: *
457: * @access public
458: * @param mixed $result
459: * @param mixed $connection
460: */
461: public function __construct( $result, $connection )
462: {
463: $this->obj_connection = $connection;
464: $this->obj_result = $result;
465: }
466:
467: /**
468: * Returns number of affected rows from insert/delete/update query
469: * Returns -1 if undetermined or failure
470: *
471: * @access public
472: * @return int
473: */
474: public function obj_affected_rows()
475: {
476: $affected_rows = $this->obj_connection->affected_rows;
477:
478: return ( $affected_rows !== null && $affected_rows >= 0 ) ? $affected_rows : -1;
479: }
480:
481: /**
482: * Returns resultset object as associative array
483: *
484: * @access public
485: * @return mixed
486: */
487: public function obj_fetch_assoc()
488: {
489: $result = $this->obj_result->fetch_assoc();
490:
491: return ( is_array( $result ) && $result !== null ) ? $this->obj_record = $result : null;
492: }
493:
494: /**
495: * Returns resultset object as numeric array
496: *
497: * @access public
498: * @return mixed
499: */
500: public function obj_fetch_num()
501: {
502: $result = $this->obj_result->fetch_row();
503:
504: return ( is_array( $result ) && $result !== null ) ? $this->obj_record = $result : null;
505: }
506:
507: /**
508: * Returns resultset object as object
509: *
510: * @access public
511: * @return mixed
512: */
513:
514: public function obj_fetch_object()
515: {
516: $result = $this->obj_result->fetch_object();
517:
518: return ( is_object( $result ) && $result !== null ) ? $this->obj_record = $result : null;
519: }
520:
521: /**
522: * Returns resultset record
523: *
524: * @access public
525: * @param mixed $field
526: * @return mixed
527: */
528: public function obj_field( $field )
529: {
530: if ( $this->obj_result )
531: {
532: //get_magic_quotes deprecated in php 5.4 - added for backwards compatibility
533: return ( get_magic_quotes_runtime() ) ? stripslashes( $this->obj_record[$field] ) : $this->obj_record[$field];
534: }
535: }
536:
537: /**
538: * Frees resultset memory and destroys resultset object
539: *
540: * @access public
541: * @return bool
542: */
543: public function obj_free_result()
544: {
545: $this->obj_result->free();
546: $this->obj_record = array();
547:
548: return ( !is_object( $this->obj_result = null ) ) ? true : false;
549: }
550:
551: /**
552: * Returns number of fields from query
553: * Returns -1 if undetermined or failure
554: *
555: * @access public
556: * @return int
557: */
558: public function obj_num_fields()
559: {
560: $num_cols = $this->obj_result->field_count;
561:
562: return ( $num_cols >= 0 ) ? $num_cols : -1;
563: }
564:
565: /**
566: * Returns number rows from query
567: * Returns -1 if undetermined or failure
568: *
569: * @access public
570: * @return int
571: */
572: public function obj_num_rows()
573: {
574: $num_rows = $this->obj_result->num_rows;
575:
576: return ( $num_rows >= 0 ) ? $num_rows : -1;
577: }
578: }
579:
580:
581: /*************************************************************************************************************
582: * End database resultset class
583: ************************************************************************************************************/
584:
585:
586: /*************************************************************************************************************
587: * Begin database transaction class
588: ************************************************************************************************************/
589:
590:
591: class mysql_transaction
592: {
593: /**********************************************
594: * Internal variables
595: *********************************************/
596:
597: /**
598: * Database connection instance
599: *
600: * @access private
601: * @var mixed
602: */
603: private $obj_connection;
604:
605: /**********************************************
606: * Class methods
607: *********************************************/
608:
609: /**
610: * Constructor
611: *
612: * @access public
613: * @param mixed $connection
614: */
615: public function __construct( $connection )
616: {
617: $this->obj_connection = $connection;
618:
619: //turn off autocommit
620: $this->obj_connection->autocommit( false );
621: }
622:
623: /**
624: * Commits transaction for current transaction instance
625: *
626: * @access public
627: * @return bool
628: */
629: public function obj_commit()
630: {
631: return $this->obj_connection->commit();
632: }
633:
634: /**
635: * Rollbacks transaction for current transaction instance
636: *
637: * @access public
638: * @param str $savepoint
639: * @return bool
640: */
641: public function obj_rollback( $savepoint=false )
642: {
643: if ( !$savepoint )
644: $rollback = $this->obj_connection->rollback();
645: else
646: $rollback = $this->obj_connection->query( "ROLLBACK TO SAVEPOINT $savepoint" );
647:
648: return ( $rollback ) ? true : false;
649: }
650:
651: /**
652: * Creates transaction savepoint for current transaction instance
653: *
654: * @access public
655: * @param str $savepoint
656: * @return bool
657: */
658: public function obj_savepoint( $savepoint )
659: {
660: return ( $this->obj_connection->query( "SAVEPOINT $savepoint" ) ) ? true : false;
661: }
662: }
663:
664:
665: /*************************************************************************************************************
666: * End database transaction class
667: ************************************************************************************************************/
668:
669:
670: ?>