includes/ezSQL/ez_sql_mysql.php

   1 <?php
   2
   3         /**********************************************************************
   4         *  Author: Justin Vincent (jv@jvmultimedia.com)
   5         *  Web...: http://twitter.com/justinvincent
   6         *  Name..: ezSQL_mysql
   7         *  Desc..: mySQL component (part of ezSQL databse abstraction library)
   8         *
   9         */
  10
  11         /**********************************************************************
  12         *  ezSQL error strings - mySQL
  13         */
  14
  15         $ezsql_mysql_str = array
  16         (
  17                 1 => 'Require $dbuser and $dbpassword to connect to a database server',
  18                 2 => 'Error establishing mySQL database connection. Correct user/password? Correct hostname? Database server running?',
  19                 3 => 'Require $dbname to select a database',
  20                 4 => 'mySQL database connection is not active',
  21                 5 => 'Unexpected error while trying to select database'
  22         );
  23
  24         /**********************************************************************
  25         *  ezSQL Database specific class - mySQL
  26         */
  27
  28         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');
  29         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');
  30
  31         class ezSQL_mysql extends ezSQLcore
  32         {
  33
  34                 var $dbuser = false;
  35                 var $dbpassword = false;
  36                 var $dbname = false;
  37                 var $dbhost = false;
  38                 var $encoding = false;
  39                 var $rows_affected = false;
  40
  41                 /**********************************************************************
  42                 *  Constructor - allow the user to perform a qucik connect at the
  43                 *  same time as initialising the ezSQL_mysql class
  44                 */
  45
  46                 function ezSQL_mysql($dbuser='', $dbpassword='', $dbname='', $dbhost='localhost', $encoding='')
  47                 {
  48                         $this->dbuser = $dbuser;
  49                         $this->dbpassword = $dbpassword;
  50                         $this->dbname = $dbname;
  51                         $this->dbhost = $dbhost;
  52                         $this->encoding = $encoding;
  53                 }
  54
  55                 /**********************************************************************
  56                 *  Short hand way to connect to mySQL database server
  57                 *  and select a mySQL database at the same time
  58                 */
  59
  60                 function quick_connect($dbuser='', $dbpassword='', $dbname='', $dbhost='localhost', $encoding='')
  61                 {
  62                         $return_val = false;
  63                         if ( ! $this->connect($dbuser, $dbpassword, $dbhost,true) ) ;
  64                         else if ( ! $this->select($dbname,$encoding) ) ;
  65                         else $return_val = true;
  66                         return $return_val;
  67                 }
  68
  69                 /**********************************************************************
  70                 *  Try to connect to mySQL database server
  71                 */
  72
  73                 function connect($dbuser='', $dbpassword='', $dbhost='localhost')
  74                 {
  75                         global $ezsql_mysql_str; $return_val = false;
  76
  77                         // Keep track of how long the DB takes to connect
  78                         $this->timer_start('db_connect_time');
  79
  80                         // Must have a user and a password
  81                         if ( ! $dbuser )
  82                         {
  83                                 $this->register_error($ezsql_mysql_str[1].' in '.__FILE__.' on line '.__LINE__);
  84                                 $this->show_errors ? trigger_error($ezsql_mysql_str[1],E_USER_WARNING) : null;
  85                         }
  86                         // Try to establish the server database handle
  87                         else if ( ! $this->dbh = @mysql_connect($dbhost,$dbuser,$dbpassword,true,131074) )
  88                         {
  89                                 $this->register_error($ezsql_mysql_str[2].' in '.__FILE__.' on line '.__LINE__);
  90                                 $this->show_errors ? trigger_error($ezsql_mysql_str[2],E_USER_WARNING) : null;
  91                         }
  92                         else
  93                         {
  94                                 $this->dbuser = $dbuser;
  95                                 $this->dbpassword = $dbpassword;
  96                                 $this->dbhost = $dbhost;
  97                                 $return_val = true;
  98                         }
  99
 100                         return $return_val;
 101                 }
 102
 103                 /**********************************************************************
 104                 *  Try to select a mySQL database
 105                 */
 106
 107                 function select($dbname='', $encoding='')
 108                 {
 109                         global $ezsql_mysql_str; $return_val = false;
 110
 111                         // Must have a database name
 112                         if ( ! $dbname )
 113                         {
 114                                 $this->register_error($ezsql_mysql_str[3].' in '.__FILE__.' on line '.__LINE__);
 115                                 $this->show_errors ? trigger_error($ezsql_mysql_str[3],E_USER_WARNING) : null;
 116                         }
 117
 118                         // Must have an active database connection
 119                         else if ( ! $this->dbh )
 120                         {
 121                                 $this->register_error($ezsql_mysql_str[4].' in '.__FILE__.' on line '.__LINE__);
 122                                 $this->show_errors ? trigger_error($ezsql_mysql_str[4],E_USER_WARNING) : null;
 123                         }
 124
 125                         // Try to connect to the database
 126                         else if ( !@mysql_select_db($dbname,$this->dbh) )
 127                         {
 128                                 // Try to get error supplied by mysql if not use our own
 129                                 if ( !$str = @mysql_error($this->dbh))
 130                                           $str = $ezsql_mysql_str[5];
 131
 132                                 $this->register_error($str.' in '.__FILE__.' on line '.__LINE__);
 133                                 $this->show_errors ? trigger_error($str,E_USER_WARNING) : null;
 134                         }
 135                         else
 136                         {
 137                                 $this->dbname = $dbname;
 138                                 if($encoding!='')
 139                                 {
 140                                         $encoding = strtolower(str_replace("-","",$encoding));
 141                                         $charsets = array();
 142                                         $result = mysql_query("SHOW CHARACTER SET");
 143                                         while($row = mysql_fetch_array($result,MYSQL_ASSOC))
 144                                         {
 145                                                 $charsets[] = $row["Charset"];
 146                                         }
 147                                         if(in_array($encoding,$charsets)){
 148                                                 mysql_query("SET NAMES '".$encoding."'");
 149                                         }
 150                                 }
 151
 152                                 $return_val = true;
 153                         }
 154
 155                         return $return_val;
 156                 }
 157
 158                 /**********************************************************************
 159                 *  Format a mySQL string correctly for safe mySQL insert
 160                 *  (no mater if magic quotes are on or not)
 161                 */
 162
 163                 function escape($str)
 164                 {
 165                         // If there is no existing database connection then try to connect
 166                         if ( ! isset($this->dbh) || ! $this->dbh )
 167                         {
 168                                 $this->connect($this->dbuser, $this->dbpassword, $this->dbhost);
 169                                 $this->select($this->dbname, $this->encoding);
 170                         }
 171
 172                         return mysql_real_escape_string(stripslashes($str));
 173                 }
 174
 175                 /**********************************************************************
 176                 *  Return mySQL specific system date syntax
 177                 *  i.e. Oracle: SYSDATE Mysql: NOW()
 178                 */
 179
 180                 function sysdate()
 181                 {
 182                         return 'NOW()';
 183                 }
 184
 185                 /**********************************************************************
 186                 *  Perform mySQL query and try to detirmin result value
 187                 */
 188
 189                 function query($query)
 190                 {
 191
 192                         // This keeps the connection alive for very long running scripts
 193                         if ( $this->num_queries >= 500 )
 194                         {
 195                                 $this->num_queries = 0;
 196                                 $this->disconnect();
 197                                 $this->quick_connect($this->dbuser,$this->dbpassword,$this->dbname,$this->dbhost,$this->encoding);
 198                         }
 199
 200                         // Initialise return
 201                         $return_val = 0;
 202
 203                         // Flush cached values..
 204                         $this->flush();
 205
 206                         // For reg expressions
 207                         $query = trim($query);
 208
 209                         // Log how the function was called
 210                         $this->func_call = "\$db->query(\"$query\")";
 211
 212                         // Keep track of the last query for debug..
 213                         $this->last_query = $query;
 214
 215                         // Count how many queries there have been
 216                         $this->num_queries++;
 217
 218                         // Start timer
 219                         $this->timer_start($this->num_queries);
 220
 221                         // Use core file cache function
 222                         if ( $cache = $this->get_cache($query) )
 223                         {
 224                                 // Keep tack of how long all queries have taken
 225                                 $this->timer_update_global($this->num_queries);
 226
 227                                 // Trace all queries
 228                                 if ( $this->use_trace_log )
 229                                 {
 230                                         $this->trace_log[] = $this->debug(false);
 231                                 }
 232
 233                                 return $cache;
 234                         }
 235
 236                         // If there is no existing database connection then try to connect
 237                         if ( ! isset($this->dbh) || ! $this->dbh )
 238                         {
 239                                 $this->connect($this->dbuser, $this->dbpassword, $this->dbhost);
 240                                 $this->select($this->dbname,$this->encoding);
 241                         }
 242
 243                         // Perform the query via std mysql_query function..
 244                         $this->result = @mysql_query($query,$this->dbh);
 245
 246                         // If there is an error then take note of it..
 247                         if ( $str = @mysql_error($this->dbh) )
 248                         {
 249                                 $is_insert = true;
 250                                 $this->register_error($str);
 251                                 $this->show_errors ? trigger_error($str,E_USER_WARNING) : null;
 252                                 return false;
 253                         }
 254
 255                         // Query was an insert, delete, update, replace
 256                         $is_insert = false;
 257                         if ( preg_match("/^(insert|delete|update|replace|truncate|drop|create|alter)\s+/i",$query) )
 258                         {
 259                                 $this->rows_affected = @mysql_affected_rows($this->dbh);
 260
 261                                 // Take note of the insert_id
 262                                 if ( preg_match("/^(insert|replace)\s+/i",$query) )
 263                                 {
 264                                         $this->insert_id = @mysql_insert_id($this->dbh);
 265                                 }
 266
 267                                 // Return number fo rows affected
 268                                 $return_val = $this->rows_affected;
 269                         }
 270                         // Query was a select
 271                         else
 272                         {
 273
 274                                 // Take note of column info
 275                                 $i=0;
 276                                 while ($i < @mysql_num_fields($this->result))
 277                                 {
 278                                         $this->col_info[$i] = @mysql_fetch_field($this->result);
 279                                         $i++;
 280                                 }
 281
 282                                 // Store Query Results
 283                                 $num_rows=0;
 284                                 while ( $row = @mysql_fetch_object($this->result) )
 285                                 {
 286                                         // Store relults as an objects within main array
 287                                         $this->last_result[$num_rows] = $row;
 288                                         $num_rows++;
 289                                 }
 290
 291                                 @mysql_free_result($this->result);
 292
 293                                 // Log number of rows the query returned
 294                                 $this->num_rows = $num_rows;
 295
 296                                 // Return number of rows selected
 297                                 $return_val = $this->num_rows;
 298                         }
 299
 300                         // disk caching of queries
 301                         $this->store_cache($query,$is_insert);
 302
 303                         // If debug ALL queries
 304                         $this->trace || $this->debug_all ? $this->debug() : null ;
 305
 306                         // Keep tack of how long all queries have taken
 307                         $this->timer_update_global($this->num_queries);
 308
 309                         // Trace all queries
 310                         if ( $this->use_trace_log )
 311                         {
 312                                 $this->trace_log[] = $this->debug(false);
 313                         }
 314
 315                         return $return_val;
 316
 317                 }
 318
 319                 /**********************************************************************
 320                 *  Close the active mySQL connection
 321                 */
 322
 323                 function disconnect()
 324                 {
 325                         @mysql_close($this->dbh);
 326                 }
 327
 328         }