PageRenderTime 62ms CodeModel.GetById 32ms RepoModel.GetById 1ms app.codeStats 0ms

/genetics/main/classes/ez_sql.php

https://github.com/mbleed/health
PHP | 595 lines | 341 code | 139 blank | 115 comment | 44 complexity | a41370310e37da9966dc3bcb8fe7a836 MD5 | raw file
Possible License(s): LGPL-2.1
  1. <?php
  2. // ==================================================================
  3. // Author: Justin Vincent (justin@visunet.ie)
  4. // Web: http://php.justinvincent.com
  5. // Name: ezSQL
  6. // Desc: Class to make it very easy to deal with PostgreSQL database connections.
  7. //
  8. // N.B. ezSQL was converted for use with PostgreSQL
  9. // by Michael Paesold (mpaesold@gmx.at).
  10. // and then reworked to act more like mysql version by
  11. // Tom De Bruyne (tom@morefast.be)
  12. //
  13. // !! IMPORTANT !!
  14. //
  15. // Please send me a mail telling me what you think of ezSQL
  16. // and what your using it for!! Cheers. [ justin@visunet.ie ]
  17. //
  18. // ==================================================================
  19. // User Settings -- CHANGE HERE
  20. define("EZSQL_DB_USER", "mbleed"); // <-- PostgreSQL db user
  21. //define("EZSQL_DB_PASSWORD", "%gocubs"); // <-- PostgreSQL db password
  22. define("EZSQL_DB_PASSWORD", "flyheart24"); // <-- PostgreSQL db password
  23. define("EZSQL_DB_NAME", "ged"); // <-- PostgreSQL db pname
  24. define("EZSQL_DB_HOST", "genetics.cnan9we69npe.us-west-2.rds.amazonaws.com"); // <-- PostgreSQL server host
  25. // ==================================================================
  26. // ezSQL Constants
  27. define("EZSQL_VERSION","1.26");
  28. define("OBJECT","OBJECT",true);
  29. define("ARRAY_A","ARRAY_A",true);
  30. define("ARRAY_N","ARRAY_N",true);
  31. // ==================================================================
  32. // The Main Class
  33. class db {
  34. var $debug_called;
  35. var $vardump_called;
  36. var $show_errors = true;
  37. var $num_queries = 0;
  38. var $debug_all = false;
  39. var $last_query;
  40. var $col_info;
  41. // ==================================================================
  42. // DB Constructor - connects to the server and selects a database
  43. function db($dbuser, $dbpassword, $dbname, $dbhost)
  44. {
  45. $connect_str = "";
  46. if (! empty($dbhost)) $connect_str .= " host=$dbhost";
  47. if (! empty($dbname)) $connect_str .= " dbname=$dbname";
  48. if (! empty($dbuser)) $connect_str .= " user=$dbuser";
  49. if (! empty($dbpassword)) $connect_str .= " password=$dbpassword";
  50. $this->dbh = @pg_connect($connect_str);
  51. if ( ! $this->dbh )
  52. {
  53. require_once("class.phpmailer.php");
  54. $mail = new PHPMailer();
  55. $mail->ClearAddresses();
  56. $mail->From = "healtheducation@umich.edu";
  57. $mail->FromName = "HEALth System";
  58. $mail->Subject = 'HEALth database not responding';
  59. $mail->Body = 'Go to https://health.dent.umich.edu/genetics/main/index.php to check.';
  60. $mail->AddAddress('mbleed@umich.edu');
  61. if(!$mail->Send())
  62. {
  63. echo "Mailer Error: " . $mail->ErrorInfo;
  64. }
  65. $this->print_error('<h4>Error establishing a database connection!<br />The technical team has been notified.</h4>');
  66. }
  67. else
  68. {
  69. // Remember these values for the select function
  70. $this->dbuser = $dbuser;
  71. $this->dbpassword = $dbpassword;
  72. $this->dbname = $dbname;
  73. $this->dbhost = $dbhost;
  74. }
  75. }
  76. // ==================================================================
  77. // Select a DB (if another one needs to be selected)
  78. function select($db)
  79. {
  80. $this->db($this->dbuser, $this->dbpassword, $db, $this->dbhost);
  81. }
  82. // ====================================================================
  83. // Format a string correctly for safe insert under all PHP conditions
  84. function escape($str)
  85. {
  86. return pg_escape_string(stripslashes($str));
  87. }
  88. // ==================================================================
  89. // Print SQL/DB error.
  90. function print_error($str = "")
  91. {
  92. // All erros go to the global error array $EZSQL_ERROR..
  93. global $EZSQL_ERROR;
  94. // If no special error string then use mysql default..
  95. if ( !$str ) $str = pg_last_error();
  96. // Log this error to the global array..
  97. $EZSQL_ERROR[] = array
  98. (
  99. "query" => $this->last_query,
  100. "error_str" => $str
  101. );
  102. // Is error output turned on or not..
  103. if ( $this->show_errors )
  104. {
  105. // If there is an error then take note of it
  106. print "<blockquote><font face=arial size=2 color=ff0000>";
  107. print "<b>SQL/DB Error --</b> ";
  108. print "[<font color=000077>$str</font>]";
  109. print "</font></blockquote>";
  110. }
  111. else
  112. {
  113. return false;
  114. }
  115. }
  116. // ==================================================================
  117. // Turn error handling on or off..
  118. function show_errors()
  119. {
  120. $this->show_errors = true;
  121. }
  122. function hide_errors()
  123. {
  124. $this->show_errors = false;
  125. }
  126. // ==================================================================
  127. // Kill cached query results
  128. function flush()
  129. {
  130. // Get rid of these
  131. $this->last_result = null;
  132. $this->col_info = null;
  133. $this->last_query = null;
  134. }
  135. // ==================================================================
  136. // Try to get last ID
  137. function get_insert_id($query)
  138. {
  139. $this->last_oid = pg_last_oid($this->result);
  140. // try to find table name
  141. eregi ("insert *into *([^ ]+).*", $query, $regs);
  142. //print_r($regs);
  143. $table_name = $regs[1];
  144. $query_for_id = "SELECT * FROM $table_name WHERE oid='$this->last_oid'";
  145. //echo $query_for_id."<br>";
  146. $result_for_id = pg_query($this->dbh, $query_for_id);
  147. if(pg_num_rows($result_for_id)) {
  148. $id = pg_fetch_array($result_for_id,0,PGSQL_NUM);
  149. //print_r($id);
  150. return $id[0];
  151. }
  152. }
  153. // ==================================================================
  154. // Basic Query - see docs for more detail
  155. function query($query)
  156. {
  157. // For reg expressions
  158. $query = trim($query);
  159. // Flush cached values..
  160. $this->flush();
  161. // Log how the function was called
  162. $this->func_call = "\$db->query(\"$query\")";
  163. // Keep track of the last query for debug..
  164. $this->last_query = $query;
  165. // Perform the query via std pg_query function..
  166. if (!$this->result = @pg_query($this->dbh,$query)) {
  167. $this->print_error();
  168. return false;
  169. }
  170. $this->num_queries++;
  171. // If there was an insert, delete or update see how many rows were affected
  172. // (Also, If there there was an insert take note of the last OID
  173. $query_type = array("insert","delete","update","replace");
  174. // loop through the above array
  175. foreach ( $query_type as $word )
  176. {
  177. // This is true if the query starts with insert, delete or update
  178. if ( preg_match("/^$word\s+/i",$query) )
  179. {
  180. $this->rows_affected = pg_affected_rows($this->result);
  181. // This gets the insert ID
  182. if ( $word == "insert" )
  183. {
  184. $this->insert_id = $this->get_insert_id($query);
  185. // If insert id then return it - true evaluation
  186. return $this->insert_id;
  187. }
  188. // Set to false if there was no insert id
  189. $this->result = false;
  190. }
  191. }
  192. // In other words if this was a select statement..
  193. if ( $this->result )
  194. {
  195. // =======================================================
  196. // Take note of column info
  197. $i=0;
  198. while ($i < @pg_num_fields($this->result))
  199. {
  200. $this->col_info[$i]->name = pg_field_name($this->result,$i);
  201. $this->col_info[$i]->type = pg_field_type($this->result,$i);
  202. $this->col_info[$i]->size = pg_field_size($this->result,$i);
  203. $i++;
  204. }
  205. // =======================================================
  206. // Store Query Results
  207. $i=0;
  208. while ( $row = @pg_fetch_object($this->result, $i) )
  209. {
  210. // Store relults as an objects within main array
  211. $this->last_result[$i] = $row;
  212. $i++;
  213. }
  214. // Log number of rows the query returned
  215. $this->num_rows = $i;
  216. @pg_free_result($this->result);
  217. // If debug ALL queries
  218. $this->debug_all ? $this->debug() : null ;
  219. // If there were results then return true for $db->query
  220. if ( $i )
  221. {
  222. return true;
  223. }
  224. else
  225. {
  226. return false;
  227. }
  228. }
  229. else
  230. {
  231. // If debug ALL queries
  232. $this->debug_all ? $this->debug() : null ;
  233. // Update insert etc. was good..
  234. return true;
  235. }
  236. }
  237. // ==================================================================
  238. // Get one variable from the DB - see docs for more detail
  239. function get_var($query=null,$x=0,$y=0)
  240. {
  241. // Log how the function was called
  242. $this->func_call = "\$db->get_var(\"$query\",$x,$y)";
  243. // If there is a query then perform it if not then use cached results..
  244. if ( $query )
  245. {
  246. $this->query($query);
  247. }
  248. // Extract var out of cached results based x,y vals
  249. if ( $this->last_result[$y] )
  250. {
  251. $values = array_values(get_object_vars($this->last_result[$y]));
  252. }
  253. // If there is a value return it else return null
  254. return (isset($values[$x]) && $values[$x]!=='')?$values[$x]:null;
  255. }
  256. // ==================================================================
  257. // Get one row from the DB - see docs for more detail
  258. function get_row($query=null,$output=OBJECT,$y=0)
  259. {
  260. // Log how the function was called
  261. $this->func_call = "\$db->get_row(\"$query\",$output,$y)";
  262. // If there is a query then perform it if not then use cached results..
  263. if ( $query )
  264. {
  265. $this->query($query);
  266. }
  267. // If the output is an object then return object using the row offset..
  268. if ( $output == OBJECT )
  269. {
  270. return $this->last_result[$y]?$this->last_result[$y]:null;
  271. }
  272. // If the output is an associative array then return row as such..
  273. elseif ( $output == ARRAY_A )
  274. {
  275. return $this->last_result[$y]?get_object_vars($this->last_result[$y]):null;
  276. }
  277. // If the output is an numerical array then return row as such..
  278. elseif ( $output == ARRAY_N )
  279. {
  280. return $this->last_result[$y]?array_values(get_object_vars($this->last_result[$y])):null;
  281. }
  282. // If invalid output type was specified..
  283. else
  284. {
  285. $this->print_error(" \$db->get_row(string query, output type, int offset) -- Output type must be one of: OBJECT, ARRAY_A, ARRAY_N");
  286. }
  287. }
  288. // ==================================================================
  289. // Function to get 1 column from the cached result set based in X index
  290. // se docs for usage and info
  291. function get_col($query=null,$x=0)
  292. {
  293. // If there is a query then perform it if not then use cached results..
  294. if ( $query )
  295. {
  296. $this->query($query);
  297. }
  298. // Extract the column values
  299. for ( $i=0; $i < count($this->last_result); $i++ )
  300. {
  301. $new_array[$i] = $this->get_var(null,$x,$i);
  302. }
  303. return $new_array;
  304. }
  305. // ==================================================================
  306. // Return the the query as a result set - see docs for more details
  307. function get_results($query=null, $output = OBJECT)
  308. {
  309. // Log how the function was called
  310. $this->func_call = "\$db->get_results(\"$query\", $output)";
  311. // If there is a query then perform it if not then use cached results..
  312. if ( $query )
  313. {
  314. $this->query($query);
  315. }
  316. // Send back array of objects. Each row is an object
  317. if ( $output == OBJECT )
  318. {
  319. return $this->last_result;
  320. }
  321. elseif ( $output == ARRAY_A || $output == ARRAY_N )
  322. {
  323. if ( $this->last_result )
  324. {
  325. $i=0;
  326. foreach( $this->last_result as $row )
  327. {
  328. $new_array[$i] = get_object_vars($row);
  329. if ( $output == ARRAY_N )
  330. {
  331. $new_array[$i] = array_values($new_array[$i]);
  332. }
  333. $i++;
  334. }
  335. return $new_array;
  336. }
  337. else
  338. {
  339. return null;
  340. }
  341. }
  342. }
  343. // ==================================================================
  344. // Function to get column meta data info pertaining to the last query
  345. // see docs for more info and usage
  346. function get_col_info($info_type="name",$col_offset=-1)
  347. {
  348. if ( $this->col_info )
  349. {
  350. if ( $col_offset == -1 )
  351. {
  352. $i=0;
  353. foreach($this->col_info as $col )
  354. {
  355. $new_array[$i] = $col->{$info_type};
  356. $i++;
  357. }
  358. return $new_array;
  359. }
  360. else
  361. {
  362. return $this->col_info[$col_offset]->{$info_type};
  363. }
  364. }
  365. }
  366. // ==================================================================
  367. // Dumps the contents of any input variable to screen in a nicely
  368. // formatted and easy to understand way - any type: Object, Var or Array
  369. function vardump($mixed='')
  370. {
  371. echo "<p><table><tr><td bgcolor=ffffff><blockquote><font color=000090>";
  372. echo "<pre><font face=arial>";
  373. if ( ! $this->vardump_called )
  374. {
  375. echo "<font color=800080><b>ezSQL</b> (v".EZSQL_VERSION.") <b>Variable Dump..</b></font>\n\n";
  376. }
  377. $var_type = gettype ($mixed);
  378. print_r(($mixed?$mixed:"<font color=red>No Value / False</font>"));
  379. echo "\n\n<b>Type:</b> " . ucfirst($var_type) . "\n";
  380. echo "<b>Last Query</b> [$this->num_queries]<b>:</b> ".($this->last_query?$this->last_query:"NULL")."\n";
  381. echo "<b>Last Function Call:</b> " . ($this->func_call?$this->func_call:"None")."\n";
  382. echo "<b>Last Rows Returned:</b> ".count($this->last_result)."\n";
  383. echo "</font></pre></font></blockquote></td></tr></table>";
  384. echo "\n<hr size=1 noshade color=dddddd>";
  385. $this->vardump_called = true;
  386. }
  387. // Alias for the above function
  388. function dumpvar($mixed)
  389. {
  390. $this->vardump($mixed);
  391. }
  392. // ==================================================================
  393. // Displays the last query string that was sent to the database & a
  394. // table listing results (if there were any).
  395. // (abstracted into a seperate file to save server overhead).
  396. function debug()
  397. {
  398. echo "<blockquote>";
  399. // Only show ezSQL credits once..
  400. if ( ! $this->debug_called )
  401. {
  402. echo "<font color=800080 face=arial size=2><b>ezSQL</b> (v".EZSQL_VERSION.") <b>Debug..</b></font><p>\n";
  403. }
  404. echo "<font face=arial size=2 color=000099><b>Query</b> [$this->num_queries] <b>--</b> ";
  405. echo "[<font color=000000><b>$this->last_query</b></font>]</font><p>";
  406. echo "<font face=arial size=2 color=000099><b>Query Result..</b></font>";
  407. echo "<blockquote>";
  408. if ( $this->col_info )
  409. {
  410. // =====================================================
  411. // Results top rows
  412. echo "<table cellpadding=5 cellspacing=1 bgcolor=555555>";
  413. echo "<tr bgcolor=eeeeee><td nowrap valign=bottom><font color=555599 face=arial size=2><b>(row)</b></font></td>";
  414. for ( $i=0; $i < count($this->col_info); $i++ )
  415. {
  416. 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>";
  417. }
  418. echo "</tr>";
  419. // ======================================================
  420. // print main results
  421. if ( $this->last_result )
  422. {
  423. $i=0;
  424. foreach ( $this->get_results(null,ARRAY_N) as $one_row )
  425. {
  426. $i++;
  427. echo "<tr bgcolor=ffffff><td bgcolor=eeeeee nowrap align=middle><font size=2 color=555599 face=arial>$i</font></td>";
  428. foreach ( $one_row as $item )
  429. {
  430. echo "<td nowrap><font face=arial size=2>$item</font></td>";
  431. }
  432. echo "</tr>";
  433. }
  434. } // if last result
  435. else
  436. {
  437. echo "<tr bgcolor=ffffff><td colspan=".(count($this->col_info)+1)."><font face=arial size=2>No Results</font></td></tr>";
  438. }
  439. echo "</table>";
  440. } // if col_info
  441. else
  442. {
  443. echo "<font face=arial size=2>No Results</font>";
  444. }
  445. echo "</blockquote></blockquote><hr noshade color=dddddd size=1>";
  446. $this->debug_called = true;
  447. }
  448. // =======================================================
  449. // Naughty little function to ask for some remuniration!
  450. function donation()
  451. {
  452. 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>";
  453. }
  454. }
  455. $db = new db(EZSQL_DB_USER, EZSQL_DB_PASSWORD, EZSQL_DB_NAME, EZSQL_DB_HOST);
  456. ?>