includes/class-mysql.php

   1 <?php\r
   2 /*\r
   3 +----------------------------------------------------------------+\r
   4 |                                                                                                                                                                                       |\r
   5 |       ezSQL                                                                                                                                                           |\r
   6 |       Copyright (c) 2006 Justin Vincent                                                                                       |\r
   7 |                                                                                                                                                                                       |\r
   8 |       File Written By:                                                                                                                                        |\r
   9 |       - Justin Vincent (justin@visunet.ie)                                                                                    |\r
  10 |       - http://php.justinvincent.com                                                                                          |\r
  11 |                                                                                                                                                                                       |\r
  12 |       File Information:                                                                                                                                       |\r
  13 |       - MYSQL Database Class                                                                                                          |\r
  14 |       - class-mysql.php                                                                                                                                       |\r
  15 |                                                                                                                                                                                       |\r
  16 +----------------------------------------------------------------+\r
  17 */\r
  18 \r
  19 \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
  31 \r
  32 \r
  33 ### ezSQL Core Class\r
  34 // Core class containg common functions to manipulate query result sets once returned\r
  35 class ezSQLcore{\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
  47 \r
  48         ## Constructor\r
  49         function ezSQLcore() { }\r
  50 \r
  51         ## Connect to DB - over-ridden by specific DB class\r
  52         function connect() { die(EZSQL_CORE_ERROR); }\r
  53 \r
  54         ## Select DB - over-ridden by specific DB class\r
  55         function select() { die(EZSQL_CORE_ERROR); }\r
  56 \r
  57         ## Basic Query - over-ridden by specific DB class\r
  58         function query() { die(EZSQL_CORE_ERROR); }\r
  59 \r
  60         ## Format a string correctly for safe insert - over-ridden by specific DB class\r
  61         function escape() { die(EZSQL_CORE_ERROR); }\r
  62 \r
  63         ## Return database specific system date syntax\r
  64         function sysdate() { die(EZSQL_CORE_ERROR); }\r
  65 \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
  72         }\r
  73 \r
  74         ## Show Errors\r
  75         function show_errors() { $this->show_errors = true; }\r
  76         \r
  77         ## Hide Errors\r
  78         function hide_errors() { $this->show_errors = false; }\r
  79 \r
  80         ## Kill cached query results\r
  81         function flush() {\r
  82                 // Get rid of these\r
  83                 $this->last_result = null;\r
  84                 $this->col_info = null;\r
  85                 $this->last_query = null;\r
  86         }\r
  87 \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
  93                 if ($query) {\r
  94                         $this->query($query);\r
  95                 }\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
  99                 }\r
 100                 // If there is a value return it else return null\r
 101                 return (isset($values[$x]) && $values[$x]!=='')?$values[$x]:null;\r
 102         }\r
 103 \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
 109                 if ($query) {\r
 110                         $this->query($query);\r
 111                 }\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
 122                 } else {\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
 124                 }\r
 125         }\r
 126 \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
 130                 if ($query) {\r
 131                         $this->query($query);\r
 132                 }\r
 133 \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
 137                 }\r
 138                 return $new_array;\r
 139         }\r
 140 \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
 146                 if ($query) {\r
 147                         $this->query($query);\r
 148                 }\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
 154                                 $i=0;\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
 159                                         }\r
 160                                         $i++;\r
 161                                 }\r
 162                                 return $new_array;\r
 163                         } else {\r
 164                                 return null;\r
 165                         }\r
 166                 }\r
 167         }\r
 168 \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
 173                                 $i=0;\r
 174                                 foreach($this->col_info as $col) {\r
 175                                         $new_array[$i] = $col->{$info_type};\r
 176                                         $i++;\r
 177                                 }\r
 178                                 return $new_array;\r
 179                         } else {\r
 180                                 return $this->col_info[$col_offset]->{$info_type};\r
 181                         }\r
 182                 }\r
 183         }\r
 184 \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
 191                 }\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
 201         }\r
 202 \r
 203         ## Alias for the above function\r
 204         function dumpvar($mixed) { $this->vardump($mixed); }\r
 205 \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
 207         function debug() {\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
 212                 }\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
 215                 }\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
 226                         }\r
 227                         echo "</tr>";\r
 228                         // print main results\r
 229                         if ($this->last_result) {\r
 230                                 $i=0;\r
 231                                 foreach ($this->get_results(null,ARRAY_N) as $one_row) {\r
 232                                         $i++;\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
 236                                         }\r
 237                                         echo "</tr>";\r
 238                                 }\r
 239                         } else {\r
 240                                 echo "<tr bgcolor=ffffff><td colspan=".(count($this->col_info)+1)."><font face=arial size=2>No Results</font></td></tr>";\r
 241                 }\r
 242                 echo "</table>";\r
 243                 } else {\r
 244                         echo "<font face=arial size=2>No Results</font>";\r
 245                 }\r
 246                 echo "</blockquote></blockquote>".$this->donation()."<hr noshade color=dddddd size=1>";\r
 247                 $this->debug_called = true;\r
 248         }\r
 249 \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> &nbsp;&nbsp;<!--[ go on! you know you want to! ]--></font>";\r
 253         }\r
 254 }\r
 255 \r
 256 \r
 257 ### ezSQL MYSQL Class Variables\r
 258 $ezsql_mysql_str = array\r
 259 (\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
 265 );\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
 268 \r
 269 \r
 270 ### ezSQL MYSQL Class\r
 271 class ezSQL_mysql extends ezSQLcore {\r
 272 \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
 277                 }\r
 278         }\r
 279 \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
 287         }\r
 288 \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
 293                 if (!$dbuser){\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
 300                 } else {\r
 301                         $return_val = true;\r
 302                 }\r
 303                 return $return_val;\r
 304         }\r
 305         \r
 306         ## Close\r
 307         function close() {\r
 308                 return mysql_close($this->dbh);\r
 309         }\r
 310 \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
 315                 if (!$dbname) {\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
 327                         }\r
 328                         $this->register_error($str.' in '.__FILE__.' on line '.__LINE__);\r
 329                         $this->show_errors ? trigger_error($str,E_USER_WARNING) : null;\r
 330                 } else {\r
 331                         $return_val = true;\r
 332                 }\r
 333                 return $return_val;\r
 334         }\r
 335 \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
 339         }\r
 340 \r
 341         ## Return mySQL specific system date syntax\r
 342         function sysdate() {\r
 343                 return 'NOW()';\r
 344         }\r
 345 \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
 351                 $return_val = 0;\r
 352                 // Flush cached values..\r
 353                 $this->flush();\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
 366                         return false;\r
 367                 }\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
 374                         }\r
 375                         // Return number fo rows affected\r
 376                         $return_val = $this->rows_affected;\r
 377                 // Query was a select\r
 378                 } else {\r
 379                         // Take note of column info\r
 380                         $i=0;\r
 381                         while ($i < @mysql_num_fields($this->result)) {\r
 382                                 $this->col_info[$i] = @mysql_fetch_field($this->result);\r
 383                                 $i++;\r
 384                         }\r
 385                         // Store Query Results\r
 386                         $num_rows=0;\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
 390                                 $num_rows++;\r
 391                         }\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
 397                 }\r
 398                 // If debug ALL queries\r
 399                 $this->trace || $this->debug_all ? $this->debug() : null ;\r
 400                 return $return_val;\r
 401         }\r
 402         \r
 403         function mysql_version() {\r
 404                 return  mysql_get_server_info( $this->dbh ) ;\r
 405         }\r
 406 }\r