3 +----------------------------------------------------------------+
\r
6 | Copyright (c) 2006 Justin Vincent |
\r
9 | - Justin Vincent (justin@visunet.ie) |
\r
10 | - http://php.justinvincent.com |
\r
12 | File Information: |
\r
13 | - MYSQL Database Class |
\r
14 | - class-mysql.php |
\r
16 +----------------------------------------------------------------+
\r
21 define('EZSQL_VERSION','2.0');
\r
22 define('OBJECT','OBJECT',true);
\r
23 define('ARRAY_A','ARRAY_A',true);
\r
24 define('ARRAY_N','ARRAY_N',true);
\r
25 define('EZSQL_CORE_ERROR','ezSQLcore can not be used by itself (it is designed for use by database specific modules).');
\r
28 ### ezSQL Core Class
\r
29 // Core class containg common functions to manipulate query result sets once returned
\r
31 var $trace = false; // same as $debug_all
\r
32 var $debug_all = false; // same as $trace
\r
33 var $debug_called = false;
\r
34 var $vardump_called = false;
\r
35 var $show_errors = false;
\r
36 var $num_queries = 0;
\r
37 var $last_query = null;
\r
38 var $last_error = null;
\r
39 var $col_info = null;
\r
40 var $captured_errors = array();
\r
41 var $all_queries = '';
\r
44 function ezSQLcore() { }
\r
46 ## Connect to DB - over-ridden by specific DB class
\r
47 function connect() { die(EZSQL_CORE_ERROR); }
\r
49 ## Select DB - over-ridden by specific DB class
\r
50 function select() { die(EZSQL_CORE_ERROR); }
\r
52 ## Basic Query - over-ridden by specific DB class
\r
53 function query() { die(EZSQL_CORE_ERROR); }
\r
55 ## Format a string correctly for safe insert - over-ridden by specific DB class
\r
56 function escape() { die(EZSQL_CORE_ERROR); }
\r
58 ## Return database specific system date syntax
\r
59 function sysdate() { die(EZSQL_CORE_ERROR); }
\r
61 ## Print SQL/DB error - over-ridden by specific DB class
\r
62 function register_error($err_str) {
\r
63 // Keep track of last error
\r
64 $this->last_error = $err_str;
\r
65 // Capture all errors to an error array no matter what happens
\r
66 $this->captured_errors[] = array ('error_str' => $err_str, 'query' => $this->last_query);
\r
70 function show_errors() { $this->show_errors = true; }
\r
73 function hide_errors() { $this->show_errors = false; }
\r
75 ## Kill cached query results
\r
78 $this->last_result = null;
\r
79 $this->col_info = null;
\r
80 $this->last_query = null;
\r
83 ## Get one variable from the DB - see docs for more detail
\r
84 function get_var($query=null,$x=0,$y=0) {
\r
85 // Log how the function was called
\r
86 $this->func_call = "\$db->get_var(\"$query\",$x,$y)";
\r
87 // If there is a query then perform it if not then use cached results..
\r
89 $this->query($query);
\r
91 // Extract var out of cached results based x,y vals
\r
92 if ($this->last_result[$y]){
\r
93 $values = array_values(get_object_vars($this->last_result[$y]));
\r
95 // If there is a value return it else return null
\r
96 return (isset($values[$x]) && $values[$x]!=='')?$values[$x]:null;
\r
99 ## Get one row from the DB - see docs for more detail
\r
100 function get_row($query=null,$output=OBJECT,$y=0) {
\r
101 // Log how the function was called
\r
102 $this->func_call = "\$db->get_row(\"$query\",$output,$y)";
\r
103 // If there is a query then perform it if not then use cached results..
\r
105 $this->query($query);
\r
107 // If the output is an object then return object using the row offset..
\r
108 if ($output == OBJECT) {
\r
109 return $this->last_result[$y]?$this->last_result[$y]:null;
\r
110 // If the output is an associative array then return row as such..
\r
111 } elseif ($output == ARRAY_A) {
\r
112 return $this->last_result[$y]?get_object_vars($this->last_result[$y]):null;
\r
113 // If the output is an numerical array then return row as such..
\r
114 } elseif ($output == ARRAY_N) {
\r
115 return $this->last_result[$y]?array_values(get_object_vars($this->last_result[$y])):null;
\r
116 // If invalid output type was specified..
\r
118 $this->print_error(" \$db->get_row(string query, output type, int offset) -- Output type must be one of: OBJECT, ARRAY_A, ARRAY_N");
\r
122 ## Function to get 1 column from the cached result set based in X index see docs for usage and info
\r
123 function get_col($query=null,$x=0) {
\r
124 // If there is a query then perform it if not then use cached results..
\r
126 $this->query($query);
\r
129 // Extract the column values
\r
130 for ($i=0; $i < count($this->last_result); $i++) {
\r
131 $new_array[$i] = $this->get_var(null,$x,$i);
\r
136 ## Return the the query as a result set - see docs for more details
\r
137 function get_results($query=null, $output = OBJECT) {
\r
138 // Log how the function was called
\r
139 $this->func_call = "\$db->get_results(\"$query\", $output)";
\r
140 // If there is a query then perform it if not then use cached results..
\r
142 $this->query($query);
\r
144 // Send back array of objects. Each row is an object
\r
145 if ($output == OBJECT) {
\r
146 return $this->last_result;
\r
147 } elseif ($output == ARRAY_A || $output == ARRAY_N) {
\r
148 if ($this->last_result) {
\r
150 foreach($this->last_result as $row) {
\r
151 $new_array[$i] = get_object_vars($row);
\r
152 if ($output == ARRAY_N) {
\r
153 $new_array[$i] = array_values($new_array[$i]);
\r
164 ## Function to get column meta data info pertaining to the last query see docs for more info and usage
\r
165 function get_col_info($info_type="name",$col_offset=-1) {
\r
166 if ($this->col_info) {
\r
167 if ($col_offset == -1) {
\r
169 foreach($this->col_info as $col) {
\r
170 $new_array[$i] = $col->{$info_type};
\r
175 return $this->col_info[$col_offset]->{$info_type};
\r
180 ## Dumps the contents of any input variable to screen in a nicely formatted and easy to understand way - any type: Object, Var or Array
\r
181 function vardump($mixed='') {
\r
182 echo "<p><table><tr><td bgcolor=ffffff><blockquote><font color=000090>";
\r
183 echo "<pre><font face=arial>";
\r
184 if (!$this->vardump_called) {
\r
185 echo "<font color=800080><b>ezSQL</b> (v".EZSQL_VERSION.") <b>Variable Dump..</b></font>\n\n";
\r
187 $var_type = gettype ($mixed);
\r
188 print_r(($mixed?$mixed:"<font color=red>No Value / False</font>"));
\r
189 echo "\n\n<b>Type:</b> " . ucfirst($var_type) . "\n";
\r
190 echo "<b>Last Query</b> [$this->num_queries]<b>:</b> ".($this->last_query?$this->last_query:"NULL")."\n";
\r
191 echo "<b>Last Function Call:</b> " . ($this->func_call?$this->func_call:"None")."\n";
\r
192 echo "<b>Last Rows Returned:</b> ".count($this->last_result)."\n";
\r
193 echo "</font></pre></font></blockquote></td></tr></table>".$this->donation();
\r
194 echo "\n<hr size=1 noshade color=dddddd>";
\r
195 $this->vardump_called = true;
\r
198 ## Alias for the above function
\r
199 function dumpvar($mixed) { $this->vardump($mixed); }
\r
201 ## Displays the last query string that was sent to the database & a table listing results (if there were any). (abstracted into a seperate file to save server overhead).
\r
203 echo "<blockquote>";
\r
204 // Only show ezSQL credits once..
\r
205 if (!$this->debug_called) {
\r
206 echo "<font color=800080 face=arial size=2><b>ezSQL</b> (v".EZSQL_VERSION.") <b>Debug..</b></font><p>\n";
\r
208 if ($this->last_error) {
\r
209 echo "<font face=arial size=2 color=000099><b>Last Error --</b> [<font color=000000><b>$this->last_error</b></font>]<p>";
\r
211 echo "<font face=arial size=2 color=000099><b>Query</b> [$this->num_queries] <b>--</b> ";
\r
212 echo "[<font color=000000><b>$this->last_query</b></font>]</font><p>";
\r
213 echo "<font face=arial size=2 color=000099><b>Query Result..</b></font>";
\r
214 echo "<blockquote>";
\r
215 if ($this->col_info) {
\r
216 // Results top rows
\r
217 echo "<table cellpadding=5 cellspacing=1 bgcolor=555555>";
\r
218 echo "<tr bgcolor=eeeeee><td nowrap valign=bottom><font color=555599 face=arial size=2><b>(row)</b></font></td>";
\r
219 for ($i=0; $i < count($this->col_info); $i++) {
\r
220 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>";
\r
223 // print main results
\r
224 if ($this->last_result) {
\r
226 foreach ($this->get_results(null,ARRAY_N) as $one_row) {
\r
228 echo "<tr bgcolor=ffffff><td bgcolor=eeeeee nowrap align=middle><font size=2 color=555599 face=arial>$i</font></td>";
\r
229 foreach ($one_row as $item) {
\r
230 echo "<td nowrap><font face=arial size=2>$item</font></td>";
\r
235 echo "<tr bgcolor=ffffff><td colspan=".(count($this->col_info)+1)."><font face=arial size=2>No Results</font></td></tr>";
\r
239 echo "<font face=arial size=2>No Results</font>";
\r
241 echo "</blockquote></blockquote>".$this->donation()."<hr noshade color=dddddd size=1>";
\r
242 $this->debug_called = true;
\r
245 ## Naughty little function to ask for some remuniration!
\r
246 function donation() {
\r
247 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> <!--[ go on! you know you want to! ]--></font>";
\r
252 ### ezSQL MYSQL Class Variables
\r
253 $ezsql_mysql_str = array
\r
255 1 => 'Require $dbuser and $dbpassword to connect to a database server',
\r
256 2 => 'Error establishing mySQL database connection. Correct user/password? Correct hostname? Database server running?',
\r
257 3 => 'Require $dbname to select a database',
\r
258 4 => 'mySQL database connection is not active',
\r
259 5 => 'Unexpected error while trying to select database'
\r
261 if ( ! function_exists ('mysql_connect') ) die('<b>Fatal Error:</b> ezSQL_mysql requires mySQL Lib to be compiled and or linked in to the PHP engine');
\r
262 if ( ! class_exists ('ezSQLcore') ) die('<b>Fatal Error:</b> ezSQL_mysql requires ezSQLcore (ez_sql_core.php) to be included/loaded before it can be used');
\r
265 ### ezSQL MYSQL Class
\r
266 class ezSQL_mysql extends ezSQLcore {
\r
268 ## Constructor - allow the user to perform a qucik connect at the same time as initialising the ezSQL_mysql class
\r
269 function ezSQL_mysql($dbuser='', $dbpassword='', $dbname='', $dbhost='localhost') {
\r
270 if ($dbuser && $dbname) {
\r
271 $this->quick_connect($dbuser, $dbpassword, $dbname, $dbhost);
\r
275 ## Short hand way to connect to mySQL database server and select a mySQL database at the same time
\r
276 function quick_connect($dbuser='', $dbpassword='', $dbname='', $dbhost='localhost') {
\r
277 $return_val = false;
\r
278 if ( ! $this->connect($dbuser, $dbpassword, $dbhost,true) ) ;
\r
279 else if ( ! $this->select($dbname) ) ;
\r
280 else $return_val = true;
\r
281 return $return_val;
\r
284 ## Try to connect to mySQL database server
\r
285 function connect($dbuser='', $dbpassword='', $dbhost='localhost'){
\r
286 global $ezsql_mysql_str; $return_val = false;
\r
287 // Must have a user and a password
\r
289 $this->register_error($ezsql_mysql_str[1].' in '.__FILE__.' on line '.__LINE__);
\r
290 $this->show_errors ? trigger_error($ezsql_mysql_str[1],E_USER_WARNING) : null;
\r
291 // Try to establish the server database handle
\r
292 } else if (!$this->dbh = @mysql_connect($dbhost,$dbuser,$dbpassword)) {
\r
293 $this->register_error($ezsql_mysql_str[2].' in '.__FILE__.' on line '.__LINE__);
\r
294 $this->show_errors ? trigger_error($ezsql_mysql_str[2],E_USER_WARNING) : null;
\r
296 $return_val = true;
\r
298 return $return_val;
\r
301 ## Try to select a mySQL database
\r
302 function select($dbname='') {
\r
303 global $ezsql_mysql_str; $return_val = false;
\r
304 // Must have a database name
\r
306 $this->register_error($ezsql_mysql_str[3].' in '.__FILE__.' on line '.__LINE__);
\r
307 $this->show_errors ? trigger_error($ezsql_mysql_str[3],E_USER_WARNING) : null;
\r
308 // Must have an active database connection
\r
309 } else if (!$this->dbh) {
\r
310 $this->register_error($ezsql_mysql_str[4].' in '.__FILE__.' on line '.__LINE__);
\r
311 $this->show_errors ? trigger_error($ezsql_mysql_str[4],E_USER_WARNING) : null;
\r
312 // Try to connect to the database
\r
313 } else if (!@mysql_select_db($dbname,$this->dbh)) {
\r
314 // Try to get error supplied by mysql if not use our own
\r
315 if ( !$str = @mysql_error($this->dbh)) {
\r
316 $str = $ezsql_mysql_str[5];
\r
318 $this->register_error($str.' in '.__FILE__.' on line '.__LINE__);
\r
319 $this->show_errors ? trigger_error($str,E_USER_WARNING) : null;
\r
321 $return_val = true;
\r
323 return $return_val;
\r
326 ## Format a mySQL string correctly for safe mySQL insert (no mater if magic quotes are on or not)
\r
327 function escape($str) {
\r
328 return mysql_escape_string(stripslashes($str));
\r
331 ## Return mySQL specific system date syntax
\r
332 function sysdate() {
\r
336 ## Perform mySQL query and try to detirmin result value
\r
337 function query($query) {
\r
338 // For reg expressions
\r
339 $query = trim($query);
\r
340 // Initialise return
\r
342 // Flush cached values..
\r
344 // Log how the function was called
\r
345 $this->func_call = "\$db->query(\"$query\")";
\r
346 // Keep track of the last query for debug..
\r
347 $this->last_query = $query;
\r
348 // Perform the query via std mysql_query function..
\r
349 $this->result = @mysql_query($query,$this->dbh);
\r
350 $this->num_queries++;
\r
351 $this->all_queries .= $query.'<br />';
\r
352 // If there is an error then take note of it..
\r
353 if ($str = @mysql_error($this->dbh)) {
\r
354 $this->register_error($str);
\r
355 $this->show_errors ? trigger_error($str,E_USER_WARNING) : null;
\r
358 // Query was an insert, delete, update, replace
\r
359 if (preg_match("/^(insert|delete|update|replace)\s+/i",$query)) {
\r
360 $this->rows_affected = @mysql_affected_rows();
\r
361 // Take note of the insert_id
\r
362 if (preg_match("/^(insert|replace)\s+/i",$query)) {
\r
363 $this->insert_id = @mysql_insert_id($this->dbh);
\r
365 // Return number fo rows affected
\r
366 $return_val = $this->rows_affected;
\r
367 // Query was a select
\r
369 // Take note of column info
\r
371 while ($i < @mysql_num_fields($this->result)) {
\r
372 $this->col_info[$i] = @mysql_fetch_field($this->result);
\r
375 // Store Query Results
\r
377 while ($row = @mysql_fetch_object($this->result)) {
\r
378 // Store relults as an objects within main array
\r
379 $this->last_result[$num_rows] = $row;
\r
382 @mysql_free_result($this->result);
\r
383 // Log number of rows the query returned
\r
384 $this->num_rows = $num_rows;
\r
385 // Return number of rows selected
\r
386 $return_val = $this->num_rows;
\r
388 // If debug ALL queries
\r
389 $this->trace || $this->debug_all ? $this->debug() : null ;
\r
390 return $return_val;
\r
393 function mysql_version() {
\r
394 return mysql_get_server_info( $this->dbh ) ;
\r