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
20 ### ezSQL Constants. Might be defined in the WordPress environment if YOURLS plugin used.
\r
21 if( !defined('EZSQL_VERSION') )
\r
22 define('EZSQL_VERSION','2.0');
\r
23 if( !defined('OBJECT') )
\r
24 define('OBJECT','OBJECT',true);
\r
25 if( !defined('ARRAY_A') )
\r
26 define('ARRAY_A','ARRAY_A',true);
\r
27 if( !defined('ARRAY_N') )
\r
28 define('ARRAY_N','ARRAY_N',true);
\r
29 if( !defined('EZSQL_CORE_ERROR') )
\r
30 define('EZSQL_CORE_ERROR','ezSQLcore can not be used by itself (it is designed for use by database specific modules).');
\r
33 ### ezSQL Core Class
\r
34 // Core class containg common functions to manipulate query result sets once returned
\r
36 var $trace = false; // same as $debug_all
\r
37 var $debug_all = false; // same as $trace
\r
38 var $debug_called = false;
\r
39 var $vardump_called = false;
\r
40 var $show_errors = false;
\r
41 var $num_queries = 0;
\r
42 var $last_query = null;
\r
43 var $last_error = null;
\r
44 var $col_info = null;
\r
45 var $captured_errors = array();
\r
46 var $all_queries = '';
\r
49 function ezSQLcore() { }
\r
51 ## Connect to DB - over-ridden by specific DB class
\r
52 function connect() { die(EZSQL_CORE_ERROR); }
\r
54 ## Select DB - over-ridden by specific DB class
\r
55 function select() { die(EZSQL_CORE_ERROR); }
\r
57 ## Basic Query - over-ridden by specific DB class
\r
58 function query() { die(EZSQL_CORE_ERROR); }
\r
60 ## Format a string correctly for safe insert - over-ridden by specific DB class
\r
61 function escape() { die(EZSQL_CORE_ERROR); }
\r
63 ## Return database specific system date syntax
\r
64 function sysdate() { die(EZSQL_CORE_ERROR); }
\r
66 ## Print SQL/DB error - over-ridden by specific DB class
\r
67 function register_error($err_str) {
\r
68 // Keep track of last error
\r
69 $this->last_error = $err_str;
\r
70 // Capture all errors to an error array no matter what happens
\r
71 $this->captured_errors[] = array ('error_str' => $err_str, 'query' => $this->last_query);
\r
75 function show_errors() { $this->show_errors = true; }
\r
78 function hide_errors() { $this->show_errors = false; }
\r
80 ## Kill cached query results
\r
83 $this->last_result = null;
\r
84 $this->col_info = null;
\r
85 $this->last_query = null;
\r
88 ## Get one variable from the DB - see docs for more detail
\r
89 function get_var($query=null,$x=0,$y=0) {
\r
90 // Log how the function was called
\r
91 $this->func_call = "\$db->get_var(\"$query\",$x,$y)";
\r
92 // If there is a query then perform it if not then use cached results..
\r
94 $this->query($query);
\r
96 // Extract var out of cached results based x,y vals
\r
97 if ($this->last_result[$y]){
\r
98 $values = array_values(get_object_vars($this->last_result[$y]));
\r
100 // If there is a value return it else return null
\r
101 return (isset($values[$x]) && $values[$x]!=='')?$values[$x]:null;
\r
104 ## Get one row from the DB - see docs for more detail
\r
105 function get_row($query=null,$output=OBJECT,$y=0) {
\r
106 // Log how the function was called
\r
107 $this->func_call = "\$db->get_row(\"$query\",$output,$y)";
\r
108 // If there is a query then perform it if not then use cached results..
\r
110 $this->query($query);
\r
112 // If the output is an object then return object using the row offset..
\r
113 if ($output == OBJECT) {
\r
114 return $this->last_result[$y]?$this->last_result[$y]:null;
\r
115 // If the output is an associative array then return row as such..
\r
116 } elseif ($output == ARRAY_A) {
\r
117 return $this->last_result[$y]?get_object_vars($this->last_result[$y]):null;
\r
118 // If the output is an numerical array then return row as such..
\r
119 } elseif ($output == ARRAY_N) {
\r
120 return $this->last_result[$y]?array_values(get_object_vars($this->last_result[$y])):null;
\r
121 // If invalid output type was specified..
\r
123 $this->print_error(" \$db->get_row(string query, output type, int offset) -- Output type must be one of: OBJECT, ARRAY_A, ARRAY_N");
\r
127 ## Function to get 1 column from the cached result set based in X index see docs for usage and info
\r
128 function get_col($query=null,$x=0) {
\r
129 // If there is a query then perform it if not then use cached results..
\r
131 $this->query($query);
\r
134 // Extract the column values
\r
135 for ($i=0; $i < count($this->last_result); $i++) {
\r
136 $new_array[$i] = $this->get_var(null,$x,$i);
\r
141 ## Return the the query as a result set - see docs for more details
\r
142 function get_results($query=null, $output = OBJECT) {
\r
143 // Log how the function was called
\r
144 $this->func_call = "\$db->get_results(\"$query\", $output)";
\r
145 // If there is a query then perform it if not then use cached results..
\r
147 $this->query($query);
\r
149 // Send back array of objects. Each row is an object
\r
150 if ($output == OBJECT) {
\r
151 return $this->last_result;
\r
152 } elseif ($output == ARRAY_A || $output == ARRAY_N) {
\r
153 if ($this->last_result) {
\r
155 foreach($this->last_result as $row) {
\r
156 $new_array[$i] = get_object_vars($row);
\r
157 if ($output == ARRAY_N) {
\r
158 $new_array[$i] = array_values($new_array[$i]);
\r
169 ## Function to get column meta data info pertaining to the last query see docs for more info and usage
\r
170 function get_col_info($info_type="name",$col_offset=-1) {
\r
171 if ($this->col_info) {
\r
172 if ($col_offset == -1) {
\r
174 foreach($this->col_info as $col) {
\r
175 $new_array[$i] = $col->{$info_type};
\r
180 return $this->col_info[$col_offset]->{$info_type};
\r
185 ## 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
186 function vardump($mixed='') {
\r
187 echo "<p><table><tr><td bgcolor=ffffff><blockquote><font color=000090>";
\r
188 echo "<pre><font face=arial>";
\r
189 if (!$this->vardump_called) {
\r
190 echo "<font color=800080><b>ezSQL</b> (v".EZSQL_VERSION.") <b>Variable Dump..</b></font>\n\n";
\r
192 $var_type = gettype ($mixed);
\r
193 print_r(($mixed?$mixed:"<font color=red>No Value / False</font>"));
\r
194 echo "\n\n<b>Type:</b> " . ucfirst($var_type) . "\n";
\r
195 echo "<b>Last Query</b> [$this->num_queries]<b>:</b> ".($this->last_query?$this->last_query:"NULL")."\n";
\r
196 echo "<b>Last Function Call:</b> " . ($this->func_call?$this->func_call:"None")."\n";
\r
197 echo "<b>Last Rows Returned:</b> ".count($this->last_result)."\n";
\r
198 echo "</font></pre></font></blockquote></td></tr></table>".$this->donation();
\r
199 echo "\n<hr size=1 noshade color=dddddd>";
\r
200 $this->vardump_called = true;
\r
203 ## Alias for the above function
\r
204 function dumpvar($mixed) { $this->vardump($mixed); }
\r
206 ## 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
208 echo "<blockquote>";
\r
209 // Only show ezSQL credits once..
\r
210 if (!$this->debug_called) {
\r
211 echo "<font color=800080 face=arial size=2><b>ezSQL</b> (v".EZSQL_VERSION.") <b>Debug..</b></font><p>\n";
\r
213 if ($this->last_error) {
\r
214 echo "<font face=arial size=2 color=000099><b>Last Error --</b> [<font color=000000><b>$this->last_error</b></font>]<p>";
\r
216 echo "<font face=arial size=2 color=000099><b>Query</b> [$this->num_queries] <b>--</b> ";
\r
217 echo "[<font color=000000><b>$this->last_query</b></font>]</font><p>";
\r
218 echo "<font face=arial size=2 color=000099><b>Query Result..</b></font>";
\r
219 echo "<blockquote>";
\r
220 if ($this->col_info) {
\r
221 // Results top rows
\r
222 echo "<table cellpadding=5 cellspacing=1 bgcolor=555555>";
\r
223 echo "<tr bgcolor=eeeeee><td nowrap valign=bottom><font color=555599 face=arial size=2><b>(row)</b></font></td>";
\r
224 for ($i=0; $i < count($this->col_info); $i++) {
\r
225 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
228 // print main results
\r
229 if ($this->last_result) {
\r
231 foreach ($this->get_results(null,ARRAY_N) as $one_row) {
\r
233 echo "<tr bgcolor=ffffff><td bgcolor=eeeeee nowrap align=middle><font size=2 color=555599 face=arial>$i</font></td>";
\r
234 foreach ($one_row as $item) {
\r
235 echo "<td nowrap><font face=arial size=2>$item</font></td>";
\r
240 echo "<tr bgcolor=ffffff><td colspan=".(count($this->col_info)+1)."><font face=arial size=2>No Results</font></td></tr>";
\r
244 echo "<font face=arial size=2>No Results</font>";
\r
246 echo "</blockquote></blockquote>".$this->donation()."<hr noshade color=dddddd size=1>";
\r
247 $this->debug_called = true;
\r
250 ## Naughty little function to ask for some remuniration!
\r
251 function donation() {
\r
252 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
257 ### ezSQL MYSQL Class Variables
\r
258 $ezsql_mysql_str = array
\r
260 1 => 'Require $dbuser and $dbpassword to connect to a database server',
\r
261 2 => 'Error establishing mySQL database connection. Correct user/password? Correct hostname? Database server running?',
\r
262 3 => 'Require $dbname to select a database',
\r
263 4 => 'mySQL database connection is not active',
\r
264 5 => 'Unexpected error while trying to select database'
\r
266 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
267 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
270 ### ezSQL MYSQL Class
\r
271 class ezSQL_mysql extends ezSQLcore {
\r
273 ## Constructor - allow the user to perform a qucik connect at the same time as initialising the ezSQL_mysql class
\r
274 function ezSQL_mysql($dbuser='', $dbpassword='', $dbname='', $dbhost='localhost') {
\r
275 if ($dbuser && $dbname) {
\r
276 $this->quick_connect($dbuser, $dbpassword, $dbname, $dbhost);
\r
280 ## Short hand way to connect to mySQL database server and select a mySQL database at the same time
\r
281 function quick_connect($dbuser='', $dbpassword='', $dbname='', $dbhost='localhost') {
\r
282 $return_val = false;
\r
283 if ( ! $this->connect($dbuser, $dbpassword, $dbhost,true) ) ;
\r
284 else if ( ! $this->select($dbname) ) ;
\r
285 else $return_val = true;
\r
286 return $return_val;
\r
289 ## Try to connect to mySQL database server
\r
290 function connect($dbuser='', $dbpassword='', $dbhost='localhost'){
\r
291 global $ezsql_mysql_str; $return_val = false;
\r
292 // Must have a user and a password
\r
294 $this->register_error($ezsql_mysql_str[1].' in '.__FILE__.' on line '.__LINE__);
\r
295 $this->show_errors ? trigger_error($ezsql_mysql_str[1],E_USER_WARNING) : null;
\r
296 // Try to establish the server database handle
\r
297 } else if (!$this->dbh = @mysql_connect($dbhost,$dbuser,$dbpassword)) {
\r
298 $this->register_error($ezsql_mysql_str[2].' in '.__FILE__.' on line '.__LINE__);
\r
299 $this->show_errors ? trigger_error($ezsql_mysql_str[2],E_USER_WARNING) : null;
\r
301 $return_val = true;
\r
303 return $return_val;
\r
308 return mysql_close($this->dbh);
\r
311 ## Try to select a mySQL database
\r
312 function select($dbname='') {
\r
313 global $ezsql_mysql_str; $return_val = false;
\r
314 // Must have a database name
\r
316 $this->register_error($ezsql_mysql_str[3].' in '.__FILE__.' on line '.__LINE__);
\r
317 $this->show_errors ? trigger_error($ezsql_mysql_str[3],E_USER_WARNING) : null;
\r
318 // Must have an active database connection
\r
319 } else if (!$this->dbh) {
\r
320 $this->register_error($ezsql_mysql_str[4].' in '.__FILE__.' on line '.__LINE__);
\r
321 $this->show_errors ? trigger_error($ezsql_mysql_str[4],E_USER_WARNING) : null;
\r
322 // Try to connect to the database
\r
323 } else if (!@mysql_select_db($dbname,$this->dbh)) {
\r
324 // Try to get error supplied by mysql if not use our own
\r
325 if ( !$str = @mysql_error($this->dbh)) {
\r
326 $str = $ezsql_mysql_str[5];
\r
328 $this->register_error($str.' in '.__FILE__.' on line '.__LINE__);
\r
329 $this->show_errors ? trigger_error($str,E_USER_WARNING) : null;
\r
331 $return_val = true;
\r
333 return $return_val;
\r
336 ## Format a mySQL string correctly for safe mySQL insert (no mater if magic quotes are on or not)
\r
337 function escape($str) {
\r
338 return mysql_escape_string(stripslashes($str));
\r
341 ## Return mySQL specific system date syntax
\r
342 function sysdate() {
\r
346 ## Perform mySQL query and try to detirmin result value
\r
347 function query($query) {
\r
348 // For reg expressions
\r
349 $query = trim($query);
\r
350 // Initialise return
\r
352 // Flush cached values..
\r
354 // Log how the function was called
\r
355 $this->func_call = "\$db->query(\"$query\")";
\r
356 // Keep track of the last query for debug..
\r
357 $this->last_query = $query;
\r
358 // Perform the query via std mysql_query function..
\r
359 $this->result = @mysql_query($query,$this->dbh);
\r
360 $this->num_queries++;
\r
361 $this->all_queries .= $query.'<br />';
\r
362 // If there is an error then take note of it..
\r
363 if ($str = @mysql_error($this->dbh)) {
\r
364 $this->register_error($str);
\r
365 $this->show_errors ? trigger_error($str,E_USER_WARNING) : null;
\r
368 // Query was an insert, delete, update, replace
\r
369 if (preg_match("/^(insert|delete|update|replace)\s+/i",$query)) {
\r
370 $this->rows_affected = @mysql_affected_rows();
\r
371 // Take note of the insert_id
\r
372 if (preg_match("/^(insert|replace)\s+/i",$query)) {
\r
373 $this->insert_id = @mysql_insert_id($this->dbh);
\r
375 // Return number fo rows affected
\r
376 $return_val = $this->rows_affected;
\r
377 // Query was a select
\r
379 // Take note of column info
\r
381 while ($i < @mysql_num_fields($this->result)) {
\r
382 $this->col_info[$i] = @mysql_fetch_field($this->result);
\r
385 // Store Query Results
\r
387 while ($row = @mysql_fetch_object($this->result)) {
\r
388 // Store relults as an objects within main array
\r
389 $this->last_result[$num_rows] = $row;
\r
392 @mysql_free_result($this->result);
\r
393 // Log number of rows the query returned
\r
394 $this->num_rows = $num_rows;
\r
395 // Return number of rows selected
\r
396 $return_val = $this->num_rows;
\r
398 // If debug ALL queries
\r
399 $this->trace || $this->debug_all ? $this->debug() : null ;
\r
400 return $return_val;
\r
403 function mysql_version() {
\r
404 return mysql_get_server_info( $this->dbh ) ;
\r