PageRenderTime 84ms CodeModel.GetById 21ms RepoModel.GetById 0ms app.codeStats 1ms

/application/classes/class_db_core.php

https://github.com/jaywilliams/ultralite2
PHP | 742 lines | 387 code | 128 blank | 227 comment | 64 complexity | 90e90c715c403bc8098a74683ad78119 MD5 | raw file
  1. <?php
  2. /**
  3. * Author: Justin Vincent (justin@visunet.ie)
  4. * Web...: http://php.justinvincent.com
  5. * Name..: ezSQL_mysql
  6. * Desc..: mySQL component (part of ezSQL databse abstraction library)
  7. *
  8. */
  9. /**
  10. * @author Justin Vincent <justin@visunet.ie>, Modifications by Nabeel Shahzad <nabeel@nsslive.net>
  11. * @link www.nsslive.net
  12. */
  13. define('EZSQL_VERSION','2.03');
  14. define('OBJECT','OBJECT', true);
  15. define('ARRAY_A','ARRAY_A', true);
  16. define('ARRAY_N','ARRAY_N', true);
  17. define('EZSQL_CORE_ERROR','ezSQLcore can not be used by itself (it is designed for use by database specific modules).');
  18. /**********************************************************************
  19. * Core class containg common functions to manipulate query result
  20. * sets once returned
  21. */
  22. class DB_Core
  23. {
  24. public $trace = false; // same as $debug_all
  25. public $debug_all = false; // same as $trace
  26. public $debug_called = false;
  27. public $vardump_called = false;
  28. public $show_errors = true;
  29. public $num_queries = 0;
  30. public $last_query = null;
  31. public $error = null;
  32. public $errno = null;
  33. public $col_info = null;
  34. public $captured_errors = array();
  35. public $cache_dir = false;
  36. public $cache_queries = false;
  37. public $cache_inserts = false;
  38. public $use_disk_cache = false;
  39. public $cache_timeout = 24; // hours
  40. public $insert_id;
  41. public $dsn = false;
  42. public $dbuser = false;
  43. public $dbpassword = false;
  44. public $dbname = false;
  45. public $dbhost = false;
  46. public $result;
  47. public $get_col_info = false;
  48. // == TJH == default now needed for echo of debug function
  49. public $debug_echo_is_on = true;
  50. /**
  51. * Clear any previous errors
  52. */
  53. public function clear_errors()
  54. {
  55. $this->error = '';
  56. $this->errno = 0;
  57. }
  58. /**
  59. * Save an error that occurs in our log
  60. *
  61. * @param string $err_str This is the error string
  62. * @param int $err_no This is the error number
  63. * @return bool True
  64. *
  65. */
  66. public function register_error($err_str, $err_no=-1)
  67. {
  68. // Keep track of last error
  69. $this->error = $err_str;
  70. $this->errno = $err_no;
  71. // Capture all errors to an error array no matter what happens
  72. $this->captured_errors[] = array(
  73. 'error' => $err_str,
  74. 'errno' => $err_no,
  75. 'query' => $this->last_query);
  76. //show output if enabled
  77. //$this->show_errors ? trigger_error($this->error . '(' . $this->last_query . ')', E_USER_WARNING) : null;
  78. }
  79. /**
  80. * Get the error log from all the query
  81. *
  82. * @return array Queries and their error/errno values
  83. *
  84. */
  85. public function get_all_errors()
  86. {
  87. return $this->captured_errors;
  88. }
  89. /**
  90. * Returns the error string from the previous query
  91. *
  92. * @return string Error string
  93. *
  94. */
  95. public function error()
  96. {
  97. return $this->error;
  98. }
  99. /**
  100. * Returns the error code from the previous query
  101. *
  102. * @return mixed Error code
  103. *
  104. */
  105. public function errno()
  106. {
  107. return $this->errno;
  108. }
  109. /**
  110. * Show all errors by default
  111. *
  112. * @return bool true
  113. *
  114. */
  115. public function show_errors()
  116. {
  117. $this->show_errors = true;
  118. return true;
  119. }
  120. /**
  121. * Hide any errors from showing by default.
  122. * Can also access the property as $this->show_errors=false
  123. *
  124. * @return bool true
  125. *
  126. */
  127. public function hide_errors()
  128. {
  129. $this->show_errors = false;
  130. return true;
  131. }
  132. /**
  133. * Remove the results from the last query
  134. *
  135. * @return bool Returns true
  136. *
  137. */
  138. public function flush()
  139. {
  140. // Get rid of these
  141. $this->last_result = null;
  142. $this->col_info = null;
  143. $this->last_query = null;
  144. $this->from_disk_cache = false;
  145. return true;
  146. }
  147. /**
  148. * Get a single column/variable
  149. *
  150. * @param string $query SQL query
  151. * @param int $x Column offset (default 0, returns first column)
  152. * @param int $y Row offset (default 0, first row returned)
  153. * @return mixed Returns the value of the variable
  154. *
  155. */
  156. public function get_var($query=null,$x=0,$y=0)
  157. {
  158. // Log how the function was called
  159. $this->func_call = "\$db->get_var(\"$query\",$x,$y)";
  160. // If there is a query then perform it if not then use cached results..
  161. if ( $query )
  162. {
  163. $this->query($query);
  164. }
  165. // Extract var out of cached results based x,y vals
  166. if ( $this->last_result[$y] )
  167. {
  168. $values = array_values(get_object_vars($this->last_result[$y]));
  169. }
  170. // If there is a value return it else return null
  171. return (isset($values[$x]) && $values[$x]!=='')?$values[$x]:null;
  172. }
  173. /**
  174. * Return one row from the DB query (use if your doing LIMIT 1)
  175. * or are expecting/want only one row returned
  176. *
  177. * @param string $query The SQL Query
  178. * @param type $output OBJECT (fastest, default), ARRAY_A, ARRAY_N
  179. * @param string $y Row offset (0 for first, 1 for 2nd, etc)
  180. * @return type Returns type as defined in $output
  181. *
  182. */
  183. public function get_row($query=null,$output=OBJECT,$y=0)
  184. {
  185. // Log how the function was called
  186. $this->func_call = "\$db->get_row(\"$query\",$output,$y)";
  187. // If there is a query then perform it if not then use cached results..
  188. if ( $query )
  189. {
  190. $this->query($query);
  191. }
  192. // If the output is an object then return object using the row offset..
  193. if ( $output == OBJECT )
  194. {
  195. return $this->last_result[$y]?$this->last_result[$y]:null;
  196. }
  197. // If the output is an associative array then return row as such..
  198. elseif ( $output == ARRAY_A )
  199. {
  200. return $this->last_result[$y]?get_object_vars($this->last_result[$y]):null;
  201. }
  202. // If the output is an numerical array then return row as such..
  203. elseif ( $output == ARRAY_N )
  204. {
  205. return $this->last_result[$y]?array_values(get_object_vars($this->last_result[$y])):null;
  206. }
  207. // If invalid output type was specified..
  208. else
  209. {
  210. $this->print_error(" \$db->get_row(string query, output type, int offset) -- Output type must be one of: OBJECT, ARRAY_A, ARRAY_N");
  211. }
  212. }
  213. /**
  214. * Build a SELECT query, specifying the table, fields and extra conditions
  215. *
  216. * @param string $table Table to SELECT from
  217. * @param mixed $fields Array of fields to SELECT for, or comma delimited string of fields
  218. * @param string $cond Extra conditions (include the WHERE, LIMIT, etc)
  219. * @param int $limit Number of results to limit
  220. * @param type $type OBJECT, ARRAY_A, ARRAY_n
  221. * @return type Array of results
  222. *
  223. */
  224. public function quick_select($table, $fields, $cond='', $type=OBJECT)
  225. {
  226. if($table == '') return false;
  227. $sql = 'SELECT ';
  228. if(is_array($fields))
  229. {
  230. $sql.= implode(',', $fields);
  231. }
  232. else
  233. {
  234. $sql .= $fields;
  235. }
  236. $sql .= ' FROM '.$table;
  237. if($cond != '')
  238. $sql .= ' '.$cond;
  239. return $this->get_results($sql, $type);
  240. }
  241. /**
  242. * Build a UPDATE SQL Query. All values except for
  243. * numeric and NOW() will be put in quotes
  244. *
  245. * Values are NOT escaped
  246. *
  247. * @param string $table Table to build update on
  248. * @param array $fields Associative array, with [column]=value
  249. * @param string $cond Extra conditions, without WHERE
  250. * @return bool Results
  251. *
  252. */
  253. public function quick_update($table, $fields, $cond='')
  254. {
  255. if($table == '') return false;
  256. $sql = 'UPDATE '.$table.' SET ';
  257. if(is_array($fields))
  258. {
  259. foreach($fields as $key=>$value)
  260. {
  261. $sql.= "$key=";
  262. if(is_numeric($value) || $value == $this->sysdate())
  263. $sql.=$value.',';
  264. else
  265. $sql.="'$value',";
  266. }
  267. $sql = substr($sql, 0, strlen($sql)-1);
  268. }
  269. else
  270. {
  271. $sql .= $fields;
  272. }
  273. if($cond != '')
  274. $sql .= ' WHERE '.$cond;
  275. return $this->query($sql);
  276. }
  277. /**
  278. * Build a quick INSERT query. For simplistic INSERTs only,
  279. * all values except numeric and NOW() are put in quotes
  280. *
  281. * Values are NOT escaped
  282. *
  283. * @param string $table Table to insert into
  284. * @param array $fields Associative array [column] = value
  285. * @param string $flags Extra INSERT flags to add
  286. * @return bool Results
  287. *
  288. */
  289. public function quick_insert($table, $fields, $flags= '')
  290. {
  291. if($table == '') return false;
  292. //if(!is_array($fields) == false) return false;
  293. $sql = 'INSERT '. $flags .' INTO '.$table.' ';
  294. $cols = $col_values = '';
  295. if(is_array($fields))
  296. {
  297. foreach($fields as $key=>$value)
  298. {
  299. // build both strings
  300. $cols .= $key.',';
  301. // Quotes or none based on value
  302. if(is_numeric($value) || $value == $this->sysdate())
  303. $col_values .= "$value,";
  304. else
  305. {
  306. $col_values .= "'$value',";
  307. }
  308. }
  309. $cols = substr($cols, 0, strlen($cols)-1);
  310. $col_values = substr($col_values, 0, strlen($col_values)-1);
  311. $sql .= '('.$cols.') VALUES ('.$col_values.')';
  312. }
  313. return $this->query($sql);
  314. }
  315. /**
  316. * Get the value of one column from a query
  317. *
  318. * @param string $query The SQL query
  319. * @param string $x Column to return
  320. * @return array Return's the results of that one column
  321. *
  322. */
  323. public function get_col($query=null,$x=0)
  324. {
  325. // If there is a query then perform it if not then use cached results..
  326. if ( $query )
  327. {
  328. $this->query($query);
  329. }
  330. // Extract the column values
  331. for ( $i=0; $i < count($this->last_result); $i++ )
  332. {
  333. $new_array[$i] = $this->get_var(null,$x,$i);
  334. }
  335. return $new_array;
  336. }
  337. /**
  338. * Returns the query as a set of results. Default returns OBJECT,
  339. * that is much faster than translating to ARRAY_A or ARRAY_N
  340. *
  341. * @param string $query SQL query
  342. * @param define $output OBJECT, ARRAY_A (associative array), ARRAY_N (numeric indexed). OBJECT is default and fastest
  343. * @return object Array of results, each array value being what $output is defined as
  344. *
  345. */
  346. public function get_results($query=null, $output = OBJECT)
  347. {
  348. // Log how the function was called
  349. $this->func_call = "\$db->get_results(\"$query\", $output)";
  350. // If there is a query then perform it if not then use cached results..
  351. if ( $query )
  352. {
  353. $this->query($query);
  354. }
  355. // Send back array of objects. Each row is an object
  356. if ( $output == OBJECT )
  357. {
  358. return $this->last_result;
  359. }
  360. elseif ( $output == ARRAY_A || $output == ARRAY_N )
  361. {
  362. if ( $this->last_result )
  363. {
  364. $i=0;
  365. foreach( $this->last_result as $row )
  366. {
  367. $new_array[$i] = get_object_vars($row);
  368. if ( $output == ARRAY_N )
  369. {
  370. $new_array[$i] = array_values($new_array[$i]);
  371. }
  372. $i++;
  373. }
  374. return $new_array;
  375. }
  376. else
  377. {
  378. return null;
  379. }
  380. }
  381. }
  382. /**
  383. * Get metadata regarding a column, about a column in the last query
  384. *
  385. * @param string $info_type Column information type to get
  386. * @param int $col_offset Column number, -1 returns all columns
  387. * @return array Column information
  388. *
  389. */
  390. public function get_col_info($info_type='name',$col_offset=-1)
  391. {
  392. if ($this->col_info )
  393. {
  394. if ( $col_offset == -1 )
  395. {
  396. $i=0;
  397. foreach($this->col_info as $col )
  398. {
  399. $new_array[$i] = $col->{$info_type};
  400. $i++;
  401. }
  402. return $new_array;
  403. }
  404. else
  405. {
  406. return $this->col_info[$col_offset]->{$info_type};
  407. }
  408. }
  409. return false;
  410. }
  411. /**
  412. * Store a results in the cache for a certain query
  413. *
  414. * @param string $query SQL query to store
  415. * @param bool $is_insert If it's an INSERT or not
  416. * @return bool Success
  417. *
  418. */
  419. public function store_cache($query,$is_insert)
  420. {
  421. // The would be cache file for this query
  422. $cache_file = $this->cache_dir.'/'.md5($query);
  423. // disk caching of queries
  424. if ( $this->use_disk_cache && ( $this->cache_queries && ! $is_insert ) || ( $this->cache_inserts && $is_insert ))
  425. {
  426. if ( ! is_dir($this->cache_dir) )
  427. {
  428. $this->register_error("Could not open cache dir: $this->cache_dir");
  429. return false;
  430. }
  431. else
  432. {
  433. // Cache all result values
  434. $result_cache = array
  435. (
  436. 'col_info' => $this->col_info,
  437. 'last_result' => $this->last_result,
  438. 'num_rows' => $this->num_rows,
  439. 'return_value' => $this->num_rows,
  440. );
  441. error_log ( serialize($result_cache), 3, $cache_file);
  442. }
  443. }
  444. return true;
  445. }
  446. /**
  447. * Get the cached results for a query. This is called more internally
  448. *
  449. * @param string $query SQL query to return results for
  450. * @return mixed Returns the unserialized results
  451. *
  452. */
  453. public function get_cache($query)
  454. {
  455. // The would be cache file for this query
  456. $cache_file = $this->cache_dir.'/'.md5($query);
  457. // Try to get previously cached version
  458. if ( $this->use_disk_cache && file_exists($cache_file) )
  459. {
  460. // Only use this cache file if less than 'cache_timeout' (hours)
  461. if ( (time() - filemtime($cache_file)) > ($this->cache_timeout*3600) )
  462. {
  463. unlink($cache_file);
  464. }
  465. else
  466. {
  467. $result_cache = unserialize(file_get_contents($cache_file));
  468. $this->col_info = $result_cache['col_info'];
  469. $this->last_result = $result_cache['last_result'];
  470. $this->num_rows = $result_cache['num_rows'];
  471. $this->from_disk_cache = true;
  472. // If debug ALL queries
  473. $this->trace || $this->debug_all ? $this->debug() : null ;
  474. return $result_cache['return_value'];
  475. }
  476. }
  477. }
  478. /**
  479. * Show values of any variable type "nicely"
  480. *
  481. * @param mixed $mixed Variable to show
  482. * @param bool $return Return the results or show on screen
  483. * @return mixed This is the return value description
  484. *
  485. */
  486. public function vardump($mixed='', $return=false)
  487. {
  488. // Start outup buffering
  489. ob_start();
  490. echo "<p><table><tr><td bgcolor=ffffff><blockquote><font color=000090>";
  491. echo "<pre><font face=arial>";
  492. if ( ! $this->vardump_called )
  493. {
  494. echo "<font color=800080><b>ezSQL</b> (v".EZSQL_VERSION.") <b>Variable Dump..</b></font>\n\n";
  495. }
  496. $var_type = gettype ($mixed);
  497. print_r(($mixed?$mixed:"<font color=red>No Value / False</font>"));
  498. echo "\n\n<b>Type:</b> " . ucfirst($var_type) . "\n";
  499. echo "<b>Last Query</b> [$this->num_queries]<b>:</b> ".($this->last_query?$this->last_query:"NULL")."\n";
  500. echo "<b>Last Function Call:</b> " . ($this->func_call?$this->func_call:"None")."\n";
  501. echo "<b>Last Rows Returned:</b> ".count($this->last_result)."\n";
  502. echo "</font></pre></font></blockquote></td></tr></table>";
  503. echo "\n<hr size=1 noshade color=dddddd>";
  504. // Stop output buffering and capture debug HTML
  505. $html = ob_get_contents();
  506. ob_end_clean();
  507. // Only echo output if it is turned on
  508. if ( $this->debug_echo_is_on || $return == false)
  509. {
  510. echo $html;
  511. }
  512. $this->vardump_called = true;
  513. return $html;
  514. }
  515. /**
  516. * Show values of any variable type "nicely"
  517. *
  518. * @param mixed $mixed Variable to show
  519. * @param bool $return Return the results or show on screen
  520. * @return mixed This is the return value description
  521. *
  522. */
  523. public function dumpvar($mixed, $return=false)
  524. {
  525. $this->vardump($mixed, $return);
  526. }
  527. /**
  528. * Displays the last query string that was sent to the database & a
  529. * table listing results (if there were any).
  530. * (abstracted into a seperate file to save server overhead).
  531. *
  532. * @param bool $return Return the results, or display right away
  533. * @return string The debug table is $return = true
  534. *
  535. */
  536. public function debug($return=false)
  537. {
  538. // Start outup buffering
  539. ob_start();
  540. echo "<blockquote>";
  541. // Only show ezSQL credits once..
  542. if ( ! $this->debug_called )
  543. {
  544. echo "<font color=800080 face=arial size=2><b>ezSQL</b> (v".EZSQL_VERSION.") <b>Debug..</b></font><p>\n";
  545. }
  546. if ( $this->error )
  547. {
  548. echo "<font face=arial size=2 color=000099><b>Last Error --</b> [<font color=000000><b>$this->error ($this->errno)</b></font>]<p>";
  549. }
  550. if ( $this->from_disk_cache )
  551. {
  552. echo "<font face=arial size=2 color=000099><b>Results retrieved from disk cache</b></font><p>";
  553. }
  554. echo "<font face=arial size=2 color=000099><b>Query</b> [$this->num_queries] <b>--</b> ";
  555. echo "[<font color=000000><b>$this->last_query</b></font>]</font><p>";
  556. echo "<font face=arial size=2 color=000099><b>Query Result..</b></font>";
  557. echo "<blockquote>";
  558. if ( $this->col_info )
  559. {
  560. // =====================================================
  561. // Results top rows
  562. echo "<table cellpadding=5 cellspacing=1 bgcolor=555555>";
  563. echo "<tr bgcolor=eeeeee><td nowrap valign=bottom><font color=555599 face=arial size=2><b>(row)</b></font></td>";
  564. for ( $i=0; $i < count($this->col_info); $i++ )
  565. {
  566. echo "<td nowrap align=left valign=top><font size=1 color=555599 face=arial>{$this->col_info[$i]->type} {$this->col_info[$i]->max_length}</font><br><span style='font-family: arial; font-size: 10pt; font-weight: bold;'>{$this->col_info[$i]->name}</span></td>";
  567. }
  568. echo "</tr>";
  569. // ======================================================
  570. // print main results
  571. if ( $this->last_result )
  572. {
  573. $i=0;
  574. foreach ( $this->get_results(null,ARRAY_N) as $one_row )
  575. {
  576. $i++;
  577. echo "<tr bgcolor=ffffff><td bgcolor=eeeeee nowrap align=middle><font size=2 color=555599 face=arial>$i</font></td>";
  578. foreach ( $one_row as $item )
  579. {
  580. echo "<td nowrap><font face=arial size=2>$item</font></td>";
  581. }
  582. echo "</tr>";
  583. }
  584. } // if last result
  585. else
  586. {
  587. echo "<tr bgcolor=ffffff><td colspan=".(count($this->col_info)+1)."><font face=arial size=2>No Results</font></td></tr>";
  588. }
  589. echo "</table>";
  590. } // if col_info
  591. else
  592. {
  593. echo "<font face=arial size=2>No Results</font>";
  594. }
  595. echo "</blockquote></blockquote><hr noshade color=dddddd size=1>";
  596. // Stop output buffering and capture debug HTML
  597. $html = ob_get_contents();
  598. ob_end_clean();
  599. // Only echo output if it is turned on
  600. if ( $this->debug_echo_is_on || $return == false )
  601. {
  602. echo $html;
  603. }
  604. $this->debug_called = true;
  605. return $html;
  606. }
  607. /**********************************************************************
  608. * Naughty little function to ask for some remuniration!
  609. */
  610. public function donation()
  611. {
  612. return "<font size=1 face=arial color=000000>If ezSQL has helped <a href=\"https://www.paypal.com/xclick/business=justin%40justinvincent.com&item_name=ezSQL&no_note=1&tax=0\" style=\"color: 0000CC;\">make a donation!?</a> &nbsp;&nbsp;<!--[ go on! you know you want to! ]--></font>";
  613. }
  614. }