PageRenderTime 74ms CodeModel.GetById 29ms RepoModel.GetById 0ms app.codeStats 1ms

/mysql.class.php

https://bitbucket.org/DieNrSieben/sdp
PHP | 1786 lines | 1208 code | 82 blank | 496 comment | 289 complexity | 645f0ea4a53e5ad0d39c8365d083f17a MD5 | raw file

Large files files are truncated, but you can click here to view the full file

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

Large files files are truncated, but you can click here to view the full file