· 9 years ago · Jul 18, 2017, 11:38 PM
1<?php
2class MySQL
3{
4 // SET THESE VALUES TO MATCH YOUR DATA CONNECTION
5 private $db_host = DB_SERVER; // server name
6 private $db_user = DB_USER; // user name
7 private $db_pass = DB_PASSWORD; // password
8 private $db_dbname = DB_NAME; // database name
9 private $db_charset = ""; // optional character set (i.e. utf8)
10 private $db_pcon = false; // use persistent connection?
11
12 // constants for SQLValue function
13 const SQLVALUE_BIT = "bit";
14 const SQLVALUE_BOOLEAN = "boolean";
15 const SQLVALUE_DATE = "date";
16 const SQLVALUE_DATETIME = "datetime";
17 const SQLVALUE_NUMBER = "number";
18 const SQLVALUE_T_F = "t-f";
19 const SQLVALUE_TEXT = "text";
20 const SQLVALUE_TIME = "time";
21 const SQLVALUE_Y_N = "y-n";
22
23 // class-internal variables - do not change
24 private $active_row = -1; // current row
25 private $error_desc = ""; // last mysql error string
26 private $error_number = 0; // last mysql error number
27 private $in_transaction = false; // used for transactions
28 private $last_insert_id; // last id of record inserted
29 private $last_result; // last mysql query result
30 private $last_sql = ""; // last mysql query
31 private $mysql_link = 0; // mysql link resource
32 private $time_diff = 0; // holds the difference in time
33 private $time_start = 0; // start time for the timer
34
35 /**
36 * Determines if an error throws an exception
37 *
38 * @var boolean Set to true to throw error exceptions
39 */
40 public $ThrowExceptions = false;
41
42 /**
43 * Constructor: Opens the connection to the database
44 *
45 * @param boolean $connect (Optional) Auto-connect when object is created
46 * @param string $database (Optional) Database name
47 * @param string $server (Optional) Host address
48 * @param string $username (Optional) User name
49 * @param string $password (Optional) Password
50 * @param string $charset (Optional) Character set
51 */
52 public function __construct($connect = true, $database = null, $server = null,
53 $username = null, $password = null, $charset = null) {
54
55 if ($database !== null) $this->db_dbname = $database;
56 if ($server !== null) $this->db_host = $server;
57 if ($username !== null) $this->db_user = $username;
58 if ($password !== null) $this->db_pass = $password;
59 if ($charset !== null) $this->db_charset = $charset;
60
61 if (strlen($this->db_host) > 0 &&
62 strlen($this->db_user) > 0) {
63 if ($connect) $this->Open();
64 }
65 }
66
67 /**
68 * Destructor: Closes the connection to the database
69 *
70 */
71 public function __destruct() {
72 $this->Close();
73 }
74
75 /**
76 * Automatically does an INSERT or UPDATE depending if an existing record
77 * exists in a table
78 *
79 * @param string $tableName The name of the table
80 * @param array $valuesArray An associative array containing the column
81 * names as keys and values as data. The values
82 * must be SQL ready (i.e. quotes around
83 * strings, formatted dates, ect)
84 * @param array $whereArray An associative array containing the column
85 * names as keys and values as data. The values
86 * must be SQL ready (i.e. quotes around strings,
87 * formatted dates, ect).
88 * @return boolean Returns TRUE on success or FALSE on error
89 */
90 public function AutoInsertUpdate($tableName, $valuesArray, $whereArray) {
91 $this->ResetError();
92 $this->SelectRows($tableName, $whereArray);
93 if (! $this->Error()) {
94 if ($this->HasRecords()) {
95 return $this->UpdateRows($tableName, $valuesArray, $whereArray);
96 } else {
97 return $this->InsertRow($tableName, $valuesArray);
98 }
99 } else {
100 return false;
101 }
102 }
103
104 /**
105 * Returns true if the internal pointer is at the beginning of the records
106 *
107 * @return boolean TRUE if at the first row or FALSE if not
108 */
109 public function BeginningOfSeek() {
110 $this->ResetError();
111 if ($this->IsConnected()) {
112 if ($this->active_row < 1) {
113 return true;
114 } else {
115 return false;
116 }
117 } else {
118 $this->SetError("No connection");
119 return false;
120 }
121 }
122
123 /**
124 * [STATIC] Builds a comma delimited list of columns for use with SQL
125 *
126 * @param array $valuesArray An array containing the column names.
127 * @param boolean $addQuotes (Optional) TRUE to add quotes
128 * @param boolean $showAlias (Optional) TRUE to show column alias
129 * @return string Returns the SQL column list
130 */
131 static private function BuildSQLColumns($columns, $addQuotes = true, $showAlias = true) {
132 if ($addQuotes) {
133 $quote = "`";
134 } else {
135 $quote = "";
136 }
137 switch (gettype($columns)) {
138 case "array":
139 $sql = "";
140 foreach ($columns as $key => $value) {
141 // Build the columns
142 if (strlen($sql) == 0) {
143 $sql = $quote . $value . $quote;
144 } else {
145 $sql .= ", " . $quote . $value . $quote;
146 }
147 if ($showAlias && is_string($key) && (! empty($key))) {
148 $sql .= ' AS "' . $key . '"';
149 }
150 }
151 return $sql;
152 break;
153 case "string":
154 return $quote . $columns . $quote;
155 break;
156 default:
157 return false;
158 break;
159 }
160 }
161
162 /**
163 * [STATIC] Builds a SQL DELETE statement
164 *
165 * @param string $tableName The name of the table
166 * @param array $whereArray (Optional) An associative array containing the
167 * column names as keys and values as data. The
168 * values must be SQL ready (i.e. quotes around
169 * strings, formatted dates, ect). If not specified
170 * then all values in the table are deleted.
171 * @return string Returns the SQL DELETE statement
172 */
173 static public function BuildSQLDelete($tableName, $whereArray = null) {
174 $sql = "DELETE FROM `" . $tableName . "`";
175 if (! is_null($whereArray)) {
176 $sql .= self::BuildSQLWhereClause($whereArray);
177 }
178 return $sql;
179 }
180
181 /**
182 * [STATIC] Builds a SQL INSERT statement
183 *
184 * @param string $tableName The name of the table
185 * @param array $valuesArray An associative array containing the column
186 * names as keys and values as data. The values
187 * must be SQL ready (i.e. quotes around
188 * strings, formatted dates, ect)
189 * @return string Returns a SQL INSERT statement
190 */
191 static public function BuildSQLInsert($tableName, $valuesArray) {
192 $columns = self::BuildSQLColumns(array_keys($valuesArray));
193 $values = self::BuildSQLColumns($valuesArray, false, false);
194 $sql = "INSERT INTO `" . $tableName .
195 "` (" . $columns . ") VALUES (" . $values . ")";
196 return $sql;
197 }
198
199 /**
200 * Builds a simple SQL SELECT statement
201 *
202 * @param string $tableName The name of the table
203 * @param array $whereArray (Optional) An associative array containing the
204 * column names as keys and values as data. The
205 * values must be SQL ready (i.e. quotes around
206 * strings, formatted dates, ect)
207 * @param array/string $columns (Optional) The column or list of columns to select
208 * @param array/string $sortColumns (Optional) Column or list of columns to sort by
209 * @param boolean $sortAscending (Optional) TRUE for ascending; FALSE for descending
210 * This only works if $sortColumns are specified
211 * @param integer/string $limit (Optional) The limit of rows to return
212 * @return string Returns a SQL SELECT statement
213 */
214 static public function BuildSQLSelect($tableName, $whereArray = null, $columns = null,
215 $sortColumns = null, $sortAscending = true, $limit = null) {
216 if (! is_null($columns)) {
217 $sql = self::BuildSQLColumns($columns);
218 } else {
219 $sql = "*";
220 }
221 $sql = "SELECT " . $sql . " FROM `" . $tableName . "`";
222 if (is_array($whereArray)) {
223 $sql .= self::BuildSQLWhereClause($whereArray);
224 }
225 if (! is_null($sortColumns)) {
226 $sql .= " ORDER BY " .
227 self::BuildSQLColumns($sortColumns, true, false) .
228 " " . ($sortAscending ? "ASC" : "DESC");
229 }
230 if (! is_null($limit)) {
231 $sql .= " LIMIT " . $limit;
232 }
233 return $sql;
234 }
235
236 /**
237 * [STATIC] Builds a SQL UPDATE statement
238 *
239 * @param string $tableName The name of the table
240 * @param array $valuesArray An associative array containing the column
241 * names as keys and values as data. The values
242 * must be SQL ready (i.e. quotes around
243 * strings, formatted dates, ect)
244 * @param array $whereArray (Optional) An associative array containing the
245 * column names as keys and values as data. The
246 * values must be SQL ready (i.e. quotes around
247 * strings, formatted dates, ect). If not specified
248 * then all values in the table are updated.
249 * @return string Returns a SQL UPDATE statement
250 */
251 static public function BuildSQLUpdate($tableName, $valuesArray, $whereArray = null) {
252 $sql = "";
253 foreach ($valuesArray as $key => $value) {
254 if (strlen($sql) == 0) {
255 $sql = "`" . $key . "` = " . $value;
256 } else {
257 $sql .= ", `" . $key . "` = " . $value;
258 }
259 }
260 $sql = "UPDATE `" . $tableName . "` SET " . $sql;
261 if (is_array($whereArray)) {
262 $sql .= self::BuildSQLWhereClause($whereArray);
263 }
264 return $sql;
265 }
266
267 /**
268 * [STATIC] Builds a SQL WHERE clause from an array.
269 * If a key is specified, the key is used at the field name and the value
270 * as a comparison. If a key is not used, the value is used as the clause.
271 *
272 * @param array $whereArray An associative array containing the column
273 * names as keys and values as data. The values
274 * must be SQL ready (i.e. quotes around
275 * strings, formatted dates, ect)
276 * @return string Returns a string containing the SQL WHERE clause
277 */
278 static public function BuildSQLWhereClause($whereArray) {
279 $where = "";
280 foreach ($whereArray as $key => $value) {
281 if (strlen($where) == 0) {
282 if (is_string($key)) {
283 $where = " WHERE `" . $key . "` = " . $value;
284 } else {
285 $where = " WHERE " . $value;
286 }
287 } else {
288 if (is_string($key)) {
289 $where .= " AND `" . $key . "` = " . $value;
290 } else {
291 $where .= " AND " . $value;
292 }
293 }
294 }
295 return $where;
296 }
297
298 /**
299 * Close current MySQL connection
300 *
301 * @return object Returns TRUE on success or FALSE on error
302 */
303 public function Close() {
304 $this->ResetError();
305 $this->active_row = -1;
306 $success = $this->Release();
307 if ($success) {
308 $success = @mysql_close($this->mysql_link);
309 if (! $success) {
310 $this->SetError();
311 } else {
312 unset($this->last_sql);
313 unset($this->last_result);
314 unset($this->mysql_link);
315 }
316 }
317 return $success;
318 }
319
320 /**
321 * Deletes rows in a table based on a WHERE filter
322 * (can be just one or many rows based on the filter)
323 *
324 * @param string $tableName The name of the table
325 * @param array $whereArray (Optional) An associative array containing the
326 * column names as keys and values as data. The
327 * values must be SQL ready (i.e. quotes around
328 * strings, formatted dates, ect). If not specified
329 * then all values in the table are deleted.
330 * @return boolean Returns TRUE on success or FALSE on error
331 */
332 public function DeleteRows($tableName, $whereArray = null) {
333 $this->ResetError();
334 if (! $this->IsConnected()) {
335 $this->SetError("No connection");
336 return false;
337 } else {
338 $sql = self::BuildSQLDelete($tableName, $whereArray);
339 // Execute the UPDATE
340 if (! $this->Query($sql)) {
341 return false;
342 } else {
343 return true;
344 }
345 }
346 }
347
348 /**
349 * Returns true if the internal pointer is at the end of the records
350 *
351 * @return boolean TRUE if at the last row or FALSE if not
352 */
353 public function EndOfSeek() {
354 $this->ResetError();
355 if ($this->IsConnected()) {
356 if ($this->active_row >= ($this->RowCount())) {
357 return true;
358 } else {
359 return false;
360 }
361 } else {
362 $this->SetError("No connection");
363 return false;
364 }
365 }
366
367 /**
368 * Returns the last MySQL error as text
369 *
370 * @return string Error text from last known error
371 */
372 public function Error() {
373 $error = $this->error_desc;
374 if (empty($error)) {
375 if ($this->error_number <> 0) {
376 $error = "Unknown Error (#" . $this->error_number . ")";
377 } else {
378 $error = false;
379 }
380 } else {
381 if ($this->error_number > 0) {
382 $error .= " (#" . $this->error_number . ")";
383 }
384 }
385 return $error;
386 }
387
388 /**
389 * Returns the last MySQL error as a number
390 *
391 * @return integer Error number from last known error
392 */
393 public function ErrorNumber() {
394 if (strlen($this->error_desc) > 0)
395 {
396 if ($this->error_number <> 0)
397 {
398 return $this->error_number;
399 } else {
400 return -1;
401 }
402 } else {
403 return $this->error_number;
404 }
405 }
406
407 /**
408 * [STATIC] Converts any value of any datatype into boolean (true or false)
409 *
410 * @param mixed $value Value to analyze for TRUE or FALSE
411 * @return boolean Returns TRUE or FALSE
412 */
413 static public function GetBooleanValue($value) {
414 if (gettype($value) == "boolean") {
415 if ($value == true) {
416 return true;
417 } else {
418 return false;
419 }
420 } elseif (is_numeric($value)) {
421 if ($value > 0) {
422 return true;
423 } else {
424 return false;
425 }
426 } else {
427 $cleaned = strtoupper(trim($value));
428
429 if ($cleaned == "ON") {
430 return true;
431 } elseif ($cleaned == "SELECTED" || $cleaned == "CHECKED") {
432 return true;
433 } elseif ($cleaned == "YES" || $cleaned == "Y") {
434 return true;
435 } elseif ($cleaned == "TRUE" || $cleaned == "T") {
436 return true;
437 } else {
438 return false;
439 }
440 }
441 }
442
443 /**
444 * Returns the comments for fields in a table into an
445 * array or NULL if the table has not got any fields
446 *
447 * @param string $table Table name
448 * @return array An array that contains the column comments
449 */
450 public function GetColumnComments($table) {
451 $this->ResetError();
452 $records = mysql_query("SHOW FULL COLUMNS FROM " . $table);
453 if (! $records) {
454 $this->SetError();
455 return false;
456 } else {
457 // Get the column names
458 $columnNames = $this->GetColumnNames($table);
459 if ($this->Error()) {
460 return false;
461 } else {
462 $index = 0;
463 // Fetchs the array to be returned (column 8 is field comment):
464 while ($array_data = mysql_fetch_array($records)) {
465 $columns[$index] = $array_data[8];
466 $columns[$columnNames[$index++]] = $array_data[8];
467 }
468 return $columns;
469 }
470 }
471 }
472
473 /**
474 * This function returns the number of columns or returns FALSE on error
475 *
476 * @param string $table (Optional) If a table name is not specified, the
477 * column count is returned from the last query
478 * @return integer The total count of columns
479 */
480 public function GetColumnCount($table = "") {
481 $this->ResetError();
482 if (empty($table)) {
483 $result = mysql_num_fields($this->last_result);
484 if (! $result) $this->SetError();
485 } else {
486 $records = mysql_query("SELECT * FROM " . $table . " LIMIT 1");
487 if (! $records) {
488 $this->SetError();
489 $result = false;
490 } else {
491 $result = mysql_num_fields($records);
492 $success = @mysql_free_result($records);
493 if (! $success) {
494 $this->SetError();
495 $result = false;
496 }
497 }
498 }
499 return $result;
500 }
501
502 /**
503 * This function returns the data type for a specified column. If
504 * the column does not exists or no records exist, it returns FALSE
505 *
506 * @param string $column Column name or number (first column is 0)
507 * @param string $table (Optional) If a table name is not specified, the
508 * last returned records are used
509 * @return string MySQL data (field) type
510 */
511 public function GetColumnDataType($column, $table = "") {
512 $this->ResetError();
513 if (empty($table)) {
514 if ($this->RowCount() > 0) {
515 if (is_numeric($column)) {
516 return mysql_field_type($this->last_result, $column);
517 } else {
518 return mysql_field_type($this->last_result, $this->GetColumnID($column));
519 }
520 } else {
521 return false;
522 }
523 } else {
524 if (is_numeric($column)) $column = $this->GetColumnName($column, $table);
525 $result = mysql_query("SELECT " . $column . " FROM " . $table . " LIMIT 1");
526 if (mysql_num_fields($result) > 0) {
527 return mysql_field_type($result, 0);
528 } else {
529 $this->SetError("The specified column or table does not exist, or no data was returned", -1);
530 return false;
531 }
532 }
533 }
534
535 /**
536 * This function returns the position of a column
537 *
538 * @param string $column Column name
539 * @param string $table (Optional) If a table name is not specified, the
540 * last returned records are used.
541 * @return integer Column ID
542 */
543 public function GetColumnID($column, $table = "") {
544 $this->ResetError();
545 $columnNames = $this->GetColumnNames($table);
546 if (! $columnNames) {
547 return false;
548 } else {
549 $index = 0;
550 $found = false;
551 foreach ($columnNames as $columnName) {
552 if ($columnName == $column) {
553 $found = true;
554 break;
555 }
556 $index++;
557 }
558 if ($found) {
559 return $index;
560 } else {
561 $this->SetError("Column name not found", -1);
562 return false;
563 }
564 }
565 }
566
567 /**
568 * This function returns the field length or returns FALSE on error
569 *
570 * @param string $column Column name
571 * @param string $table (Optional) If a table name is not specified, the
572 * last returned records are used.
573 * @return integer Field length
574 */
575 public function GetColumnLength($column, $table = "") {
576 $this->ResetError();
577 if (empty($table)) {
578 if (is_numeric($column)) {
579 $columnID = $column;
580 } else {
581 $columnID = $this->GetColumnID($column);
582 }
583 if (! $columnID) {
584 return false;
585 } else {
586 $result = mysql_field_len($this->last_result, $columnID);
587 if (! $result) {
588 $this->SetError();
589 return false;
590 } else {
591 return $result;
592 }
593 }
594 } else {
595 $records = mysql_query("SELECT " . $column . " FROM " . $table . " LIMIT 1");
596 if (! $records) {
597 $this->SetError();
598 return false;
599 }
600 $result = mysql_field_len($records, 0);
601 if (! $result) {
602 $this->SetError();
603 return false;
604 } else {
605 return $result;
606 }
607 }
608 }
609
610 /**
611 * This function returns the name for a specified column number. If
612 * the index does not exists or no records exist, it returns FALSE
613 *
614 * @param string $columnID Column position (0 is the first column)
615 * @param string $table (Optional) If a table name is not specified, the
616 * last returned records are used.
617 * @return integer Field Length
618 */
619 public function GetColumnName($columnID, $table = "") {
620 $this->ResetError();
621 if (empty($table)) {
622 if ($this->RowCount() > 0) {
623 $result = mysql_field_name($this->last_result, $columnID);
624 if (! $result) $this->SetError();
625 } else {
626 $result = false;
627 }
628 } else {
629 $records = mysql_query("SELECT * FROM " . $table . " LIMIT 1");
630 if (! $records) {
631 $this->SetError();
632 $result = false;
633 } else {
634 if (mysql_num_fields($records) > 0) {
635 $result = mysql_field_name($records, $columnID);
636 if (! $result) $this->SetError();
637 } else {
638 $result = false;
639 }
640 }
641 }
642 return $result;
643 }
644
645 /**
646 * Returns the field names in a table or query in an array
647 *
648 * @param string $table (Optional) If a table name is not specified, the
649 * last returned records are used
650 * @return array An array that contains the column names
651 */
652 public function GetColumnNames($table = "") {
653 $this->ResetError();
654 if (empty($table)) {
655 $columnCount = mysql_num_fields($this->last_result);
656 if (! $columnCount) {
657 $this->SetError();
658 $columns = false;
659 } else {
660 for ($column = 0; $column < $columnCount; $column++) {
661 $columns[] = mysql_field_name($this->last_result, $column);
662 }
663 }
664 } else {
665 $result = mysql_query("SHOW COLUMNS FROM " . $table);
666 if (! $result) {
667 $this->SetError();
668 $columns = false;
669 } else {
670 while ($array_data = mysql_fetch_array($result)) {
671 $columns[] = $array_data[0];
672 }
673 }
674 }
675
676 // Returns the array
677 return $columns;
678 }
679
680 /**
681 * This function returns the last query as an HTML table
682 *
683 * @param boolean $showCount (Optional) TRUE if you want to show the row count,
684 * FALSE if you do not want to show the count
685 * @param string $styleTable (Optional) Style information for the table
686 * @param string $styleHeader (Optional) Style information for the header row
687 * @param string $styleData (Optional) Style information for the cells
688 * @return string HTML containing a table with all records listed
689 */
690 public function GetHTML($showCount = true, $styleTable = null, $styleHeader = null, $styleData = null) {
691 if ($styleTable === null) {
692 $tb = "border-collapse:collapse;empty-cells:show";
693 } else {
694 $tb = $styleTable;
695 }
696 if ($styleHeader === null) {
697 $th = "border-width:1px;border-style:solid;background-color:navy;color:white";
698 } else {
699 $th = $styleHeader;
700 }
701 if ($styleData === null) {
702 $td = "border-width:1px;border-style:solid";
703 } else {
704 $td = $styleData;
705 }
706
707 if ($this->last_result) {
708 if ($this->RowCount() > 0) {
709 $html = "";
710 if ($showCount) $html = "Record Count: " . $this->RowCount() . "<br />\n";
711 $html .= "<table style=\"$tb\" cellpadding=\"2\" cellspacing=\"2\">\n";
712 $this->MoveFirst();
713 $header = false;
714 while ($member = mysql_fetch_object($this->last_result)) {
715 if (!$header) {
716 $html .= "\t<tr>\n";
717 foreach ($member as $key => $value) {
718 $html .= "\t\t<td style=\"$th\"><strong>" . htmlspecialchars($key) . "</strong></td>\n";
719 }
720 $html .= "\t</tr>\n";
721 $header = true;
722 }
723 $html .= "\t<tr>\n";
724 foreach ($member as $key => $value) {
725 $html .= "\t\t<td style=\"$td\">" . htmlspecialchars($value) . "</td>\n";
726 }
727 $html .= "\t</tr>\n";
728 }
729 $this->MoveFirst();
730 $html .= "</table>";
731 } else {
732 $html = "No records were returned.";
733 }
734 } else {
735 $this->active_row = -1;
736 $html = false;
737 }
738 return $html;
739 }
740
741 /**
742 * Returns the last query as a JSON document
743 *
744 * @return string JSON containing all records listed
745 */
746 public function GetJSON() {
747 if ($this->last_result) {
748 if ($this->RowCount() > 0) {
749 for ($i = 0, $il = mysql_num_fields($this->last_result); $i < $il; $i++) {
750 $types[$i] = mysql_field_type($this->last_result, $i);
751 }
752 $json = '[';
753 $this->MoveFirst();
754 while ($member = mysql_fetch_object($this->last_result)) {
755 $json .= json_encode($member) . ",";
756 }
757 $json .= ']';
758 $json = str_replace("},]", "}]", $json);
759 } else {
760 $json = 'null';
761 }
762 } else {
763 $this->active_row = -1;
764 $json = 'null';
765 }
766 return $json;
767 }
768
769 /**
770 * Returns the last autonumber ID field from a previous INSERT query
771 *
772 * @return integer ID number from previous INSERT query
773 */
774 public function GetLastInsertID() {
775 return $this->last_insert_id;
776 }
777
778 /**
779 * Returns the last SQL statement executed
780 *
781 * @return string Current SQL query string
782 */
783 public function GetLastSQL() {
784 return $this->last_sql;
785 }
786
787 /**
788 * This function returns table names from the database
789 * into an array. If the database does not contains
790 * any tables, the returned value is FALSE
791 *
792 * @return array An array that contains the table names
793 */
794 public function GetTables() {
795 $this->ResetError();
796 // Query to get the tables in the current database:
797 $records = mysql_query("SHOW TABLES");
798 if (! $records) {
799 $this->SetError();
800 return FALSE;
801 } else {
802 while ($array_data = mysql_fetch_array($records)) {
803 $tables[] = $array_data[0];
804 }
805
806 // Returns the array or NULL
807 if (count($tables) > 0) {
808 return $tables;
809 } else {
810 return FALSE;
811 }
812 }
813 }
814
815 /**
816 * Returns the last query as an XML Document
817 *
818 * @return string XML containing all records listed
819 */
820 public function GetXML() {
821 // Create a new XML document
822 $doc = new DomDocument('1.0'); // ,'UTF-8');
823
824 // Create the root node
825 $root = $doc->createElement('root');
826 $root = $doc->appendChild($root);
827
828 // If there was a result set
829 if (is_resource($this->last_result)) {
830
831 // Show the row count and query
832 $root->setAttribute('rows',
833 ($this->RowCount() ? $this->RowCount() : 0));
834 $root->setAttribute('query', $this->last_sql);
835 $root->setAttribute('error', "");
836
837 // process one row at a time
838 $rowCount = 0;
839 while ($row = mysql_fetch_assoc($this->last_result)) {
840
841 // Keep the row count
842 $rowCount = $rowCount + 1;
843
844 // Add node for each row
845 $element = $doc->createElement('row');
846 $element = $root->appendChild($element);
847 $element->setAttribute('index', $rowCount);
848
849 // Add a child node for each field
850 foreach ($row as $fieldname => $fieldvalue) {
851 $child = $doc->createElement($fieldname);
852 $child = $element->appendChild($child);
853
854 // $fieldvalue = iconv("ISO-8859-1", "UTF-8", $fieldvalue);
855 $fieldvalue = htmlspecialchars($fieldvalue);
856 $value = $doc->createTextNode($fieldvalue);
857 $value = $child->appendChild($value);
858 } // foreach
859 } // while
860 } else {
861 // Process any errors
862 $root->setAttribute('rows', 0);
863 $root->setAttribute('query', $this->last_sql);
864 if ($this->Error()) {
865 $root->setAttribute('error', $this->Error());
866 } else {
867 $root->setAttribute('error', "No query has been executed.");
868 }
869 }
870
871 // Show the XML document
872 return $doc->saveXML();
873 }
874
875 /**
876 * Determines if a query contains any rows
877 *
878 * @param string $sql [Optional] If specified, the query is first executed
879 * Otherwise, the last query is used for comparison
880 * @return boolean TRUE if records exist, FALSE if not or query error
881 */
882 public function HasRecords($sql = "") {
883 if (strlen($sql) > 0) {
884 $this->Query($sql);
885 if ($this->Error()) return false;
886 }
887 if ($this->RowCount() > 0) {
888 return true;
889 } else {
890 return false;
891 }
892 }
893
894 /**
895 * Inserts a row into a table in the connected database
896 *
897 * @param string $tableName The name of the table
898 * @param array $valuesArray An associative array containing the column
899 * names as keys and values as data. The values
900 * must be SQL ready (i.e. quotes around
901 * strings, formatted dates, ect)
902 * @return integer Returns last insert ID on success or FALSE on failure
903 */
904 public function InsertRow($tableName, $valuesArray) {
905 $this->ResetError();
906 if (! $this->IsConnected()) {
907 $this->SetError("No connection");
908 return false;
909 } else {
910 // Execute the query
911 $sql = self::BuildSQLInsert($tableName, $valuesArray);
912 if (! $this->Query($sql)) {
913 return false;
914 } else {
915 return $this->GetLastInsertID();
916 }
917 }
918 }
919
920 /**
921 * Determines if a valid connection to the database exists
922 *
923 * @return boolean TRUE idf connectect or FALSE if not connected
924 */
925 public function IsConnected() {
926 if (gettype($this->mysql_link) == "resource") {
927 return true;
928 } else {
929 return false;
930 }
931 }
932
933 /**
934 * [STATIC] Determines if a value of any data type is a date PHP can convert
935 *
936 * @param date/string $value
937 * @return boolean Returns TRUE if value is date or FALSE if not date
938 */
939 static public function IsDate($value) {
940 $date = date('Y', strtotime($value));
941 if ($date == "1969" || $date == '') {
942 return false;
943 } else {
944 return true;
945 }
946 }
947
948 /**
949 * Stop executing (die/exit) and show last MySQL error message
950 *
951 */
952 public function Kill($message = "") {
953 if (strlen($message) > 0) {
954 exit($message);
955 } else {
956 exit($this->Error());
957 }
958 }
959
960 /**
961 * Seeks to the beginning of the records
962 *
963 * @return boolean Returns TRUE on success or FALSE on error
964 */
965 public function MoveFirst() {
966 $this->ResetError();
967 if (! $this->Seek(0)) {
968 $this->SetError();
969 return false;
970 } else {
971 $this->active_row = 0;
972 return true;
973 }
974 }
975
976 /**
977 * Seeks to the end of the records
978 *
979 * @return boolean Returns TRUE on success or FALSE on error
980 */
981 public function MoveLast() {
982 $this->ResetError();
983 $this->active_row = $this->RowCount() - 1;
984 if (! $this->Error()) {
985 if (! $this->Seek($this->active_row)) {
986 return false;
987 } else {
988 return true;
989 }
990 } else {
991 return false;
992 }
993 }
994
995 /**
996 * Connect to specified MySQL server
997 *
998 * @param string $database (Optional) Database name
999 * @param string $server (Optional) Host address
1000 * @param string $username (Optional) User name
1001 * @param string $password (Optional) Password
1002 * @param string $charset (Optional) Character set
1003 * @param boolean $pcon (Optional) Persistant connection
1004 * @return boolean Returns TRUE on success or FALSE on error
1005 */
1006 public function Open($database = null, $server = null, $username = null,
1007 $password = null, $charset = null, $pcon = false) {
1008 $this->ResetError();
1009
1010 // Use defaults?
1011 if ($database !== null) $this->db_dbname = $database;
1012 if ($server !== null) $this->db_host = $server;
1013 if ($username !== null) $this->db_user = $username;
1014 if ($password !== null) $this->db_pass = $password;
1015 if ($charset !== null) $this->db_charset = $charset;
1016 if (is_bool($pcon)) $this->db_pcon = $pcon;
1017
1018 $this->active_row = -1;
1019
1020 // Open persistent or normal connection
1021 if ($pcon) {
1022 $this->mysql_link = @mysql_pconnect(
1023 $this->db_host, $this->db_user, $this->db_pass);
1024 } else {
1025 $this->mysql_link = @mysql_connect (
1026 $this->db_host, $this->db_user, $this->db_pass);
1027 }
1028 // Connect to mysql server failed?
1029 if (! $this->IsConnected()) {
1030 $this->SetError();
1031 return false;
1032 } else {
1033 // Select a database (if specified)
1034 if (strlen($this->db_dbname) > 0) {
1035 if (strlen($this->db_charset) == 0) {
1036 if (! $this->SelectDatabase($this->db_dbname)) {
1037 return false;
1038 } else {
1039 return true;
1040 }
1041 } else {
1042 if (! $this->SelectDatabase(
1043 $this->db_dbname, $this->db_charset)) {
1044 return false;
1045 } else {
1046 return true;
1047 }
1048 }
1049 } else {
1050 return true;
1051 }
1052 }
1053 }
1054
1055 /**
1056 * Executes the given SQL query and returns the records
1057 *
1058 * @param string $sql The query string should not end with a semicolon
1059 * @return object PHP 'mysql result' resource object containing the records
1060 * on SELECT, SHOW, DESCRIBE or EXPLAIN queries and returns;
1061 * TRUE or FALSE for all others i.e. UPDATE, DELETE, DROP
1062 * AND FALSE on all errors (setting the local Error message)
1063 */
1064 public function Query($sql) {
1065 $this->ResetError();
1066 $this->last_sql = $sql;
1067 $this->last_result = @mysql_query($sql, $this->mysql_link);
1068 if(! $this->last_result) {
1069 $this->active_row = -1;
1070 $this->SetError();
1071 return false;
1072 } else {
1073 if (strpos(strtolower($sql), "insert") === 0) {
1074 $this->last_insert_id = mysql_insert_id();
1075 if ($this->last_insert_id === false) {
1076 $this->SetError();
1077 return false;
1078 } else {
1079 $numrows = 0;
1080 $this->active_row = -1;
1081 return $this->last_result;
1082 }
1083 } else if(strpos(strtolower($sql), "select") === 0) {
1084 $numrows = mysql_num_rows($this->last_result);
1085 if ($numrows > 0) {
1086 $this->active_row = 0;
1087 } else {
1088 $this->active_row = -1;
1089 }
1090 $this->last_insert_id = 0;
1091 return $this->last_result;
1092 } else {
1093 return $this->last_result;
1094 }
1095 }
1096 }
1097
1098 /**
1099 * Executes the given SQL query and returns a multi-dimensional array
1100 *
1101 * @param string $sql The query string should not end with a semicolon
1102 * @param integer $resultType (Optional) The type of array
1103 * Values can be: MYSQL_ASSOC, MYSQL_NUM, MYSQL_BOTH
1104 * @return array A multi-dimensional array containing all the data
1105 * returned from the query or FALSE on all errors
1106 */
1107 public function QueryArray($sql, $resultType = MYSQL_BOTH) {
1108 $this->Query($sql);
1109 if (! $this->Error()) {
1110 return $this->RecordsArray($resultType);
1111 } else {
1112 return false;
1113 }
1114 }
1115
1116 /**
1117 * Executes the given SQL query and returns only one (the first) row
1118 *
1119 * @param string $sql The query string should not end with a semicolon
1120 * @return object PHP resource object containing the first row or
1121 * FALSE if no row is returned from the query
1122 */
1123 public function QuerySingleRow($sql) {
1124 $this->Query($sql);
1125 if ($this->RowCount() > 0) {
1126 return $this->Row();
1127 } else {
1128 return false;
1129 }
1130 }
1131
1132 /**
1133 * Executes the given SQL query and returns the first row as an array
1134 *
1135 * @param string $sql The query string should not end with a semicolon
1136 * @param integer $resultType (Optional) The type of array
1137 * Values can be: MYSQL_ASSOC, MYSQL_NUM, MYSQL_BOTH
1138 * @return array An array containing the first row or FALSE if no row
1139 * is returned from the query
1140 */
1141 public function QuerySingleRowArray($sql, $resultType = MYSQL_BOTH) {
1142 $this->Query($sql);
1143 if ($this->RowCount() > 0) {
1144 return $this->RowArray(null, $resultType);
1145 } else {
1146 return false;
1147 }
1148 }
1149
1150 /**
1151 * Executes a query and returns a single value. If more than one row
1152 * is returned, only the first value in the first column is returned.
1153 *
1154 * @param string $sql The query string should not end with a semicolon
1155 * @return mixed The value returned or FALSE if no value
1156 */
1157 public function QuerySingleValue($sql) {
1158 $this->Query($sql);
1159 if ($this->RowCount() > 0 && $this->GetColumnCount() > 0) {
1160 $row = $this->RowArray(null, MYSQL_NUM);
1161 return $row[0];
1162 } else {
1163 return false;
1164 }
1165 }
1166
1167 /**
1168 * Executes the given SQL query, measures it, and saves the total duration
1169 * in microseconds
1170 *
1171 * @param string $sql The query string should not end with a semicolon
1172 * @return object PHP 'mysql result' resource object containing the records
1173 * on SELECT, SHOW, DESCRIBE or EXPLAIN queries and returns
1174 * TRUE or FALSE for all others i.e. UPDATE, DELETE, DROP
1175 */
1176 public function QueryTimed($sql) {
1177 $this->TimerStart();
1178 $result = $this->Query($sql);
1179 $this->TimerStop();
1180 return $result;
1181 }
1182
1183 /**
1184 * Returns the records from the last query
1185 *
1186 * @return object PHP 'mysql result' resource object containing the records
1187 * for the last query executed
1188 */
1189 public function Records() {
1190 return $this->last_result;
1191 }
1192
1193 /**
1194 * Returns all records from last query and returns contents as array
1195 * or FALSE on error
1196 *
1197 * @param integer $resultType (Optional) The type of array
1198 * Values can be: MYSQL_ASSOC, MYSQL_NUM, MYSQL_BOTH
1199 * @return Records in array form
1200 */
1201 public function RecordsArray($resultType = MYSQL_BOTH) {
1202 $this->ResetError();
1203 if ($this->last_result) {
1204 if (! mysql_data_seek($this->last_result, 0)) {
1205 $this->SetError();
1206 return false;
1207 } else {
1208 //while($member = mysql_fetch_object($this->last_result)){
1209 while ($member = mysql_fetch_array($this->last_result, $resultType)){
1210 $members[] = $member;
1211 }
1212 mysql_data_seek($this->last_result, 0);
1213 $this->active_row = 0;
1214 return $members;
1215 }
1216 } else {
1217 $this->active_row = -1;
1218 $this->SetError("No query results exist", -1);
1219 return false;
1220 }
1221 }
1222
1223 /**
1224 * Frees memory used by the query results and returns the function result
1225 *
1226 * @return boolean Returns TRUE on success or FALSE on failure
1227 */
1228 public function Release() {
1229 $this->ResetError();
1230 if (! $this->last_result) {
1231 $success = true;
1232 } else {
1233 $success = @mysql_free_result($this->last_result);
1234 if (! $success) $this->SetError();
1235 }
1236 return $success;
1237 }
1238
1239 /**
1240 * Clears the internal variables from any error information
1241 *
1242 */
1243 private function ResetError() {
1244 $this->error_desc = '';
1245 $this->error_number = 0;
1246 }
1247
1248 /**
1249 * Reads the current row and returns contents as a
1250 * PHP object or returns false on error
1251 *
1252 * @param integer $optional_row_number (Optional) Use to specify a row
1253 * @return object PHP object or FALSE on error
1254 */
1255 public function Row($optional_row_number = null) {
1256 $this->ResetError();
1257 if (! $this->last_result) {
1258 $this->SetError("No query results exist", -1);
1259 return false;
1260 } elseif ($optional_row_number === null) {
1261 if (($this->active_row) > $this->RowCount()) {
1262 $this->SetError("Cannot read past the end of the records", -1);
1263 return false;
1264 } else {
1265 $this->active_row++;
1266 }
1267 } else {
1268 if ($optional_row_number >= $this->RowCount()) {
1269 $this->SetError("Row number is greater than the total number of rows", -1);
1270 return false;
1271 } else {
1272 $this->active_row = $optional_row_number;
1273 $this->Seek($optional_row_number);
1274 }
1275 }
1276 $row = mysql_fetch_object($this->last_result);
1277 if (! $row) {
1278 $this->SetError();
1279 return false;
1280 } else {
1281 return $row;
1282 }
1283 }
1284
1285 /**
1286 * Reads the current row and returns contents as an
1287 * array or returns false on error
1288 *
1289 * @param integer $optional_row_number (Optional) Use to specify a row
1290 * @param integer $resultType (Optional) The type of array
1291 * Values can be: MYSQL_ASSOC, MYSQL_NUM, MYSQL_BOTH
1292 * @return array Array that corresponds to fetched row or FALSE if no rows
1293 */
1294 public function RowArray($optional_row_number = null, $resultType = MYSQL_BOTH) {
1295 $this->ResetError();
1296 if (! $this->last_result) {
1297 $this->SetError("No query results exist", -1);
1298 return false;
1299 } elseif ($optional_row_number === null) {
1300 if (($this->active_row) > $this->RowCount()) {
1301 $this->SetError("Cannot read past the end of the records", -1);
1302 return false;
1303 } else {
1304 $this->active_row++;
1305 }
1306 } else {
1307 if ($optional_row_number >= $this->RowCount()) {
1308 $this->SetError("Row number is greater than the total number of rows", -1);
1309 return false;
1310 } else {
1311 $this->active_row = $optional_row_number;
1312 $this->Seek($optional_row_number);
1313 }
1314 }
1315 $row = mysql_fetch_array($this->last_result, $resultType);
1316 if (! $row) {
1317 $this->SetError();
1318 return false;
1319 } else {
1320 return $row;
1321 }
1322 }
1323
1324 /**
1325 * Returns the last query row count
1326 *
1327 * @return integer Row count or FALSE on error
1328 */
1329 public function RowCount() {
1330 $this->ResetError();
1331 if (! $this->IsConnected()) {
1332 $this->SetError("No connection", -1);
1333 return false;
1334 } elseif (! $this->last_result) {
1335 $this->SetError("No query results exist", -1);
1336 return false;
1337 } else {
1338 $result = @mysql_num_rows($this->last_result);
1339 if (! $result) {
1340 $this->SetError();
1341 return false;
1342 } else {
1343 return $result;
1344 }
1345 }
1346 }
1347
1348 /**
1349 * Sets the internal database pointer to the
1350 * specified row number and returns the result
1351 *
1352 * @param integer $row_number Row number
1353 * @return object Fetched row as PHP object
1354 */
1355 public function Seek($row_number) {
1356 $this->ResetError();
1357 $row_count = $this->RowCount();
1358 if (! $row_count) {
1359 return false;
1360 } elseif ($row_number >= $row_count) {
1361 $this->SetError("Seek parameter is greater than the total number of rows", -1);
1362 return false;
1363 } else {
1364 $this->active_row = $row_number;
1365 $result = mysql_data_seek($this->last_result, $row_number);
1366 if (! $result) {
1367 $this->SetError();
1368 return false;
1369 } else {
1370 $record = mysql_fetch_row($this->last_result);
1371 if (! $record) {
1372 $this->SetError();
1373 return false;
1374 } else {
1375 // Go back to the record after grabbing it
1376 mysql_data_seek($this->last_result, $row_number);
1377 return $record;
1378 }
1379 }
1380 }
1381 }
1382
1383 /**
1384 * Returns the current cursor row location
1385 *
1386 * @return integer Current row number
1387 */
1388 public function SeekPosition() {
1389 return $this->active_row;
1390 }
1391
1392 /**
1393 * Selects a different database and character set
1394 *
1395 * @param string $database Database name
1396 * @param string $charset (Optional) Character set (i.e. utf8)
1397 * @return boolean Returns TRUE on success or FALSE on error
1398 */
1399 public function SelectDatabase($database, $charset = "") {
1400 $return_value = true;
1401 if (! $charset) $charset = $this->db_charset;
1402 $this->ResetError();
1403 if (! (mysql_select_db($database))) {
1404 $this->SetError();
1405 $return_value = false;
1406 } else {
1407 if ((strlen($charset) > 0)) {
1408 if (! (mysql_query("SET CHARACTER SET '{$charset}'", $this->mysql_link))) {
1409 $this->SetError();
1410 $return_value = false;
1411 }
1412 }
1413 }
1414 return $return_value;
1415 }
1416
1417 /**
1418 * Gets rows in a table based on a WHERE filter
1419 *
1420 * @param string $tableName The name of the table
1421 * @param array $whereArray (Optional) An associative array containing the
1422 * column names as keys and values as data. The
1423 * values must be SQL ready (i.e. quotes around
1424 * strings, formatted dates, ect)
1425 * @param array/string $columns (Optional) The column or list of columns to select
1426 * @param array/string $sortColumns (Optional) Column or list of columns to sort by
1427 * @param boolean $sortAscending (Optional) TRUE for ascending; FALSE for descending
1428 * This only works if $sortColumns are specified
1429 * @param integer/string $limit (Optional) The limit of rows to return
1430 * @return boolean Returns records on success or FALSE on error
1431 */
1432 public function SelectRows($tableName, $whereArray = null, $columns = null,
1433 $sortColumns = null, $sortAscending = true,
1434 $limit = null) {
1435 $this->ResetError();
1436 if (! $this->IsConnected()) {
1437 $this->SetError("No connection");
1438 return false;
1439 } else {
1440 $sql = self::BuildSQLSelect($tableName, $whereArray,
1441 $columns, $sortColumns, $sortAscending, $limit);
1442 // Execute the UPDATE
1443 if (! $this->Query($sql)) {
1444 return $this->last_result;
1445 } else {
1446 return true;
1447 }
1448 }
1449 }
1450
1451 /**
1452 * Retrieves all rows in a specified table
1453 *
1454 * @param string $tableName The name of the table
1455 * @return boolean Returns records on success or FALSE on error
1456 */
1457 public function SelectTable($tableName) {
1458 return $this->SelectRows($tableName);
1459 }
1460
1461 /**
1462 * Sets the local variables with the last error information
1463 *
1464 * @param string $errorMessage The error description
1465 * @param integer $errorNumber The error number
1466 */
1467 private function SetError($errorMessage = "", $errorNumber = 0) {
1468 try {
1469 if (strlen($errorMessage) > 0) {
1470 $this->error_desc = $errorMessage;
1471 } else {
1472 if ($this->IsConnected()) {
1473 $this->error_desc = mysql_error($this->mysql_link);
1474 } else {
1475 $this->error_desc = mysql_error();
1476 }
1477 }
1478 if ($errorNumber <> 0) {
1479 $this->error_number = $errorNumber;
1480 } else {
1481 if ($this->IsConnected()) {
1482 $this->error_number = @mysql_errno($this->mysql_link);
1483 } else {
1484 $this->error_number = @mysql_errno();
1485 }
1486 }
1487 } catch(Exception $e) {
1488 $this->error_desc = $e->getMessage();
1489 $this->error_number = -999;
1490 }
1491 if ($this->ThrowExceptions) {
1492 if (isset($this->error_desc) && $this->error_desc != NULL) {
1493 throw new Exception($this->error_desc . ' (' . __LINE__ . ')');
1494 }
1495 }
1496 }
1497
1498 /**
1499 * [STATIC] Converts a boolean into a formatted TRUE or FALSE value of choice
1500 *
1501 * @param mixed $value value to analyze for TRUE or FALSE
1502 * @param mixed $trueValue value to use if TRUE
1503 * @param mixed $falseValue value to use if FALSE
1504 * @param string $datatype Use SQLVALUE constants or the strings:
1505 * string, text, varchar, char, boolean, bool,
1506 * Y-N, T-F, bit, date, datetime, time, integer,
1507 * int, number, double, float
1508 * @return string SQL formatted value of the specified data type
1509 */
1510 static public function SQLBooleanValue($value, $trueValue, $falseValue, $datatype = self::SQLVALUE_TEXT) {
1511 if (self::GetBooleanValue($value)) {
1512 $return_value = self::SQLValue($trueValue, $datatype);
1513 } else {
1514 $return_value = self::SQLValue($falseValue, $datatype);
1515 }
1516 return $return_value;
1517 }
1518
1519 /**
1520 * [STATIC] Returns string suitable for SQL
1521 *
1522 * @param string $value
1523 * @return string SQL formatted value
1524 */
1525 static public function SQLFix($value) {
1526 return @addslashes($value);
1527 }
1528
1529 /**
1530 * [STATIC] Returns MySQL string as normal string
1531 *
1532 * @param string $value
1533 * @return string
1534 */
1535 static public function SQLUnfix($value) {
1536 return @stripslashes($value);
1537 }
1538
1539 /**
1540 * [STATIC] Formats any value into a string suitable for SQL statements
1541 * (NOTE: Also supports data types returned from the gettype function)
1542 *
1543 * @param mixed $value Any value of any type to be formatted to SQL
1544 * @param string $datatype Use SQLVALUE constants or the strings:
1545 * string, text, varchar, char, boolean, bool,
1546 * Y-N, T-F, bit, date, datetime, time, integer,
1547 * int, number, double, float
1548 * @return string
1549 */
1550 static public function SQLValue($value, $datatype = self::SQLVALUE_TEXT) {
1551 $return_value = "";
1552
1553 switch (strtolower(trim($datatype))) {
1554 case "text":
1555 case "string":
1556 case "varchar":
1557 case "char":
1558 if (strlen($value) == 0) {
1559 $return_value = "NULL";
1560 } else {
1561 if (get_magic_quotes_gpc()) {
1562 $value = stripslashes($value);
1563 }
1564 $return_value = "'" . str_replace("'", "''", $value) . "'";
1565 }
1566 break;
1567 case "number":
1568 case "integer":
1569 case "int":
1570 case "double":
1571 case "float":
1572 if (is_numeric($value)) {
1573 $return_value = $value;
1574 } else {
1575 $return_value = "NULL";
1576 }
1577 break;
1578 case "boolean": //boolean to use this with a bit field
1579 case "bool":
1580 case "bit":
1581 if (self::GetBooleanValue($value)) {
1582 $return_value = "1";
1583 } else {
1584 $return_value = "0";
1585 }
1586 break;
1587 case "y-n": //boolean to use this with a char(1) field
1588 if (self::GetBooleanValue($value)) {
1589 $return_value = "'Y'";
1590 } else {
1591 $return_value = "'N'";
1592 }
1593 break;
1594 case "t-f": //boolean to use this with a char(1) field
1595 if (self::GetBooleanValue($value)) {
1596 $return_value = "'T'";
1597 } else {
1598 $return_value = "'F'";
1599 }
1600 break;
1601 case "date":
1602 if (self::IsDate($value)) {
1603 $return_value = "'" . date('Y-m-d', strtotime($value)) . "'";
1604 } else {
1605 $return_value = "NULL";
1606 }
1607 break;
1608 case "datetime":
1609 if (self::IsDate($value)) {
1610 $return_value = "'" . date('Y-m-d H:i:s', strtotime($value)) . "'";
1611 } else {
1612 $return_value = "NULL";
1613 }
1614 break;
1615 case "time":
1616 if (self::IsDate($value)) {
1617 $return_value = "'" . date('H:i:s', strtotime($value)) . "'";
1618 } else {
1619 $return_value = "NULL";
1620 }
1621 break;
1622 default:
1623 exit("ERROR: Invalid data type specified in SQLValue method");
1624 }
1625 return $return_value;
1626 }
1627
1628 /**
1629 * Returns last measured duration (time between TimerStart and TimerStop)
1630 *
1631 * @param integer $decimals (Optional) The number of decimal places to show
1632 * @return Float Microseconds elapsed
1633 */
1634 public function TimerDuration($decimals = 4) {
1635 return number_format($this->time_diff, $decimals);
1636 }
1637
1638 /**
1639 * Starts time measurement (in microseconds)
1640 *
1641 */
1642 public function TimerStart() {
1643 $parts = explode(" ", microtime());
1644 $this->time_diff = 0;
1645 $this->time_start = $parts[1].substr($parts[0],1);
1646 }
1647
1648 /**
1649 * Stops time measurement (in microseconds)
1650 *
1651 */
1652 public function TimerStop() {
1653 $parts = explode(" ", microtime());
1654 $time_stop = $parts[1].substr($parts[0],1);
1655 $this->time_diff = ($time_stop - $this->time_start);
1656 $this->time_start = 0;
1657 }
1658
1659 /**
1660 * Starts a transaction
1661 *
1662 * @return boolean Returns TRUE on success or FALSE on error
1663 */
1664 public function TransactionBegin() {
1665 $this->ResetError();
1666 if (! $this->IsConnected()) {
1667 $this->SetError("No connection");
1668 return false;
1669 } else {
1670 if (! $this->in_transaction) {
1671 if (! mysql_query("START TRANSACTION", $this->mysql_link)) {
1672 $this->SetError();
1673 return false;
1674 } else {
1675 $this->in_transaction = true;
1676 return true;
1677 }
1678 } else {
1679 $this->SetError("Already in transaction", -1);
1680 return false;
1681 }
1682 }
1683 }
1684
1685 /**
1686 * Ends a transaction and commits the queries
1687 *
1688 * @return boolean Returns TRUE on success or FALSE on error
1689 */
1690 public function TransactionEnd() {
1691 $this->ResetError();
1692 if (! $this->IsConnected()) {
1693 $this->SetError("No connection");
1694 return false;
1695 } else {
1696 if ($this->in_transaction) {
1697 if (! mysql_query("COMMIT", $this->mysql_link)) {
1698 // $this->TransactionRollback();
1699 $this->SetError();
1700 return false;
1701 } else {
1702 $this->in_transaction = false;
1703 return true;
1704 }
1705 } else {
1706 $this->SetError("Not in a transaction", -1);
1707 return false;
1708 }
1709 }
1710 }
1711
1712 /**
1713 * Rolls the transaction back
1714 *
1715 * @return boolean Returns TRUE on success or FALSE on failure
1716 */
1717 public function TransactionRollback() {
1718 $this->ResetError();
1719 if (! $this->IsConnected()) {
1720 $this->SetError("No connection");
1721 return false;
1722 } else {
1723 if(! mysql_query("ROLLBACK", $this->mysql_link)) {
1724 $this->SetError("Could not rollback transaction");
1725 return false;
1726 } else {
1727 $this->in_transaction = false;
1728 return true;
1729 }
1730 }
1731 }
1732
1733 /**
1734 * Truncates a table removing all data
1735 *
1736 * @param string $tableName The name of the table
1737 * @return boolean Returns TRUE on success or FALSE on error
1738 */
1739 public function TruncateTable($tableName) {
1740 $this->ResetError();
1741 if (! $this->IsConnected()) {
1742 $this->SetError("No connection");
1743 return false;
1744 } else {
1745 $sql = "TRUNCATE TABLE `" . $tableName . "`";
1746 if (! $this->Query($sql)) {
1747 return false;
1748 } else {
1749 return true;
1750 }
1751 }
1752 }
1753
1754 /**
1755 * Updates rows in a table based on a WHERE filter
1756 * (can be just one or many rows based on the filter)
1757 *
1758 * @param string $tableName The name of the table
1759 * @param array $valuesArray An associative array containing the column
1760 * names as keys and values as data. The values
1761 * must be SQL ready (i.e. quotes around
1762 * strings, formatted dates, ect)
1763 * @param array $whereArray (Optional) An associative array containing the
1764 * column names as keys and values as data. The
1765 * values must be SQL ready (i.e. quotes around
1766 * strings, formatted dates, ect). If not specified
1767 * then all values in the table are updated.
1768 * @return boolean Returns TRUE on success or FALSE on error
1769 */
1770 public function UpdateRows($tableName, $valuesArray, $whereArray = null) {
1771 $this->ResetError();
1772 if (! $this->IsConnected()) {
1773 $this->SetError("No connection");
1774 return false;
1775 } else {
1776 $sql = self::BuildSQLUpdate($tableName, $valuesArray, $whereArray);
1777 // Execute the UPDATE
1778 if (! $this->Query($sql)) {
1779 return false;
1780 } else {
1781 return true;
1782 }
1783 }
1784 }
1785}
1786?>