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\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
  26 \r
  27 \r
  28 ### ezSQL Core Class\r
  29 // Core class containg common functions to manipulate query result sets once returned\r
  30 class ezSQLcore{\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
  42 \r
  43         ## Constructor\r
  44         function ezSQLcore() { }\r
  45 \r
  46         ## Connect to DB - over-ridden by specific DB class\r
  47         function connect() { die(EZSQL_CORE_ERROR); }\r
  48 \r
  49         ## Select DB - over-ridden by specific DB class\r
  50         function select() { die(EZSQL_CORE_ERROR); }\r
  51 \r
  52         ## Basic Query - over-ridden by specific DB class\r
  53         function query() { die(EZSQL_CORE_ERROR); }\r
  54 \r
  55         ## Format a string correctly for safe insert - over-ridden by specific DB class\r
  56         function escape() { die(EZSQL_CORE_ERROR); }\r
  57 \r
  58         ## Return database specific system date syntax\r
  59         function sysdate() { die(EZSQL_CORE_ERROR); }\r
  60 \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
  67         }\r
  68 \r
  69         ## Show Errors\r
  70         function show_errors() { $this->show_errors = true; }\r
  71         \r
  72         ## Hide Errors\r
  73         function hide_errors() { $this->show_errors = false; }\r
  74 \r
  75         ## Kill cached query results\r
  76         function flush() {\r
  77                 // Get rid of these\r
  78                 $this->last_result = null;\r
  79                 $this->col_info = null;\r
  80                 $this->last_query = null;\r
  81         }\r
  82 \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
  88                 if ($query) {\r
  89                         $this->query($query);\r
  90                 }\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
  94                 }\r
  95                 // If there is a value return it else return null\r
  96                 return (isset($values[$x]) && $values[$x]!=='')?$values[$x]:null;\r
  97         }\r
  98 \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
 104                 if ($query) {\r
 105                         $this->query($query);\r
 106                 }\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
 117                 } else {\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
 119                 }\r
 120         }\r
 121 \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
 125                 if ($query) {\r
 126                         $this->query($query);\r
 127                 }\r
 128 \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
 132                 }\r
 133                 return $new_array;\r
 134         }\r
 135 \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
 141                 if ($query) {\r
 142                         $this->query($query);\r
 143                 }\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
 149                                 $i=0;\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
 154                                         }\r
 155                                         $i++;\r
 156                                 }\r
 157                                 return $new_array;\r
 158                         } else {\r
 159                                 return null;\r
 160                         }\r
 161                 }\r
 162         }\r
 163 \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
 168                                 $i=0;\r
 169                                 foreach($this->col_info as $col) {\r
 170                                         $new_array[$i] = $col->{$info_type};\r
 171                                         $i++;\r
 172                                 }\r
 173                                 return $new_array;\r
 174                         } else {\r
 175                                 return $this->col_info[$col_offset]->{$info_type};\r
 176                         }\r
 177                 }\r
 178         }\r
 179 \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
 186                 }\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
 196         }\r
 197 \r
 198         ## Alias for the above function\r
 199         function dumpvar($mixed) { $this->vardump($mixed); }\r
 200 \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
 202         function debug() {\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
 207                 }\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
 210                 }\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
 221                         }\r
 222                         echo "</tr>";\r
 223                         // print main results\r
 224                         if ($this->last_result) {\r
 225                                 $i=0;\r
 226                                 foreach ($this->get_results(null,ARRAY_N) as $one_row) {\r
 227                                         $i++;\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
 231                                         }\r
 232                                         echo "</tr>";\r
 233                                 }\r
 234                         } else {\r
 235                                 echo "<tr bgcolor=ffffff><td colspan=".(count($this->col_info)+1)."><font face=arial size=2>No Results</font></td></tr>";\r
 236                 }\r
 237                 echo "</table>";\r
 238                 } else {\r
 239                         echo "<font face=arial size=2>No Results</font>";\r
 240                 }\r
 241                 echo "</blockquote></blockquote>".$this->donation()."<hr noshade color=dddddd size=1>";\r
 242                 $this->debug_called = true;\r
 243         }\r
 244 \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> &nbsp;&nbsp;<!--[ go on! you know you want to! ]--></font>";\r
 248         }\r
 249 }\r
 250 \r
 251 \r
 252 ### ezSQL MYSQL Class Variables\r
 253 $ezsql_mysql_str = array\r
 254 (\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
 260 );\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
 263 \r
 264 \r
 265 ### ezSQL MYSQL Class\r
 266 class ezSQL_mysql extends ezSQLcore {\r
 267 \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
 272                 }\r
 273         }\r
 274 \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
 282         }\r
 283 \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
 288                 if (!$dbuser){\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
 295                 } else {\r
 296                         $return_val = true;\r
 297                 }\r
 298                 return $return_val;\r
 299         }\r
 300 \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
 305                 if (!$dbname) {\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
 317                         }\r
 318                         $this->register_error($str.' in '.__FILE__.' on line '.__LINE__);\r
 319                         $this->show_errors ? trigger_error($str,E_USER_WARNING) : null;\r
 320                 } else {\r
 321                         $return_val = true;\r
 322                 }\r
 323                 return $return_val;\r
 324         }\r
 325 \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
 329         }\r
 330 \r
 331         ## Return mySQL specific system date syntax\r
 332         function sysdate() {\r
 333                 return 'NOW()';\r
 334         }\r
 335 \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
 341                 $return_val = 0;\r
 342                 // Flush cached values..\r
 343                 $this->flush();\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
 356                         return false;\r
 357                 }\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
 364                         }\r
 365                         // Return number fo rows affected\r
 366                         $return_val = $this->rows_affected;\r
 367                 // Query was a select\r
 368                 } else {\r
 369                         // Take note of column info\r
 370                         $i=0;\r
 371                         while ($i < @mysql_num_fields($this->result)) {\r
 372                                 $this->col_info[$i] = @mysql_fetch_field($this->result);\r
 373                                 $i++;\r
 374                         }\r
 375                         // Store Query Results\r
 376                         $num_rows=0;\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
 380                                 $num_rows++;\r
 381                         }\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
 387                 }\r
 388                 // If debug ALL queries\r
 389                 $this->trace || $this->debug_all ? $this->debug() : null ;\r
 390                 return $return_val;\r
 391         }\r
 392 }\r
 393 ?>