PageRenderTime 20ms CodeModel.GetById 23ms RepoModel.GetById 0ms app.codeStats 1ms

/class.ez_sql.php

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