library/adapters/mysql_backup.php

   1 <?php
   2         // @title       MySQL DB Connection class
   3         // @author      Matt Todd <matt@matttoddphoto.com>
   4         // @created     2005-09-28
   5         // @desc        Used to connect and interact with MySQL databases.  Inherits
   6         //              basic functionality and structure from the DBAbstract class.
   7
   8 //      include_once 'db.php';
   9 //      include_once 'stdexception.php';
  10
  11         class mysql extends database {
  12                 // defined in the 'database' class
  13                 // public $config, $handle, $query, $result, $is_connected; // state and configuration
  14
  15                 // functions to override
  16 /*              abstract public function connect();
  17                 abstract public function disconnect();
  18                 abstract public function select_db($database);
  19                 abstract public function query($query);
  20                 abstract public function iterate();
  21                 abstract public function select($params, $table);
  22 */
  23
  24                 // overridden functions
  25                 public function connect() {
  26                         // establish connection
  27                         if(!($this->handle = @mysql_pconnect($this->config['host'], $this->config['username'], $this->config['password']))) die(mysql_error()); // throw new DBConnectionException(mysql_errno(), mysql_error(), (__FILE__ . ', line ' . __LINE__));
  28                         $this->is_connected = true;
  29                         return true;
  30                 }
  31
  32                 public function disconnect() {
  33                         if(mysql_close($this->db)) {
  34                                 $this->is_connected = false;
  35                                 $this->db = null;
  36                                 return true;
  37                         } else {
  38                                 return false;
  39                         }
  40                 }
  41
  42                 public function select_db($database = null) {
  43                         if(!empty($database)) $this->config['database'] = $database; else $database = $this->config['database'];
  44                         if($this->is_connected) {
  45                                 if(!@mysql_select_db($database, $this->handle)) throw new CouldNotSelectDB(mysql_errno(), mysql_error(), (__FILE__ . ', line ' . __LINE__));
  46                                 return true;
  47                         } else {
  48                                 throw new CouldNotSelectDB("0002", "Not connected to the Database", (__FILE__ . ', line ' . __LINE__));
  49                         }
  50                 }
  51
  52                 public function query($query) {
  53                         $this->query = $query;
  54                         // print $query . "<br />\n";
  55
  56                         @mysql_free_result($this->result);
  57
  58                         Debug::log("Executed '{$this->query}'", 'sql', 'info', 'MySQL');
  59
  60 //                      try {
  61                                 if(!($this->result = @mysql_query($this->query, $this->db))) throw new CouldNotIterate(mysql_errno(), mysql_error(), (__FILE__ . ', line ' . __LINE__));
  62                                 return true;
  63 //                      }
  64 //                      catch(DBException $e) {
  65 //                              // $this->result = null;
  66 //                              print_r($e);
  67 //                              $this->error = $e;
  68 //                              return false;
  69 //                      }
  70                 }
  71
  72                 public function get_row() {
  73                         try {
  74                                 switch($this->row_result_type) {
  75                                         case "self::ROWRESTYPE_INDEXED":
  76                                         case self::ROWRESTYPE_INDEXED:
  77                                                 if(!($this->row = @mysql_fetch_row($this->result))) throw new DBException(mysql_errno(), mysql_error(), (__FILE__ . ', line ' . __LINE__));
  78                                                 return $this->row;
  79                                                 break;
  80                                         default:
  81                                         case "self::ROWRESTYPE_ASSOC":
  82                                         case self::ROWRESTYPE_ASSOC:
  83                                                 if(!($this->row = @mysql_fetch_assoc($this->result))) throw new DBException(mysql_errno(), mysql_error(), (__FILE__ . ', line ' . __LINE__));
  84                                                 return $this->row;
  85                                                 break;
  86                                         case "self::ROWRESTYPE_OBJECT":
  87                                         case self::ROWRESTYPE_OBJECT:
  88                                                 if(!($this->row = @mysql_fetch_object($this->result))) throw new DBException(mysql_errno(), mysql_error(), (__FILE__ . ', line ' . __LINE__));
  89                                                 return $this->row;
  90                                                 break;
  91                                 }
  92                         }
  93                         catch(DBException $e) {
  94                                 print_r($e);
  95                                 $this->row = null;
  96                                 $this->error = $e;
  97                                 return false;
  98                         }
  99                 }
 100
 101                 public function iterate($result = null) {
 102                         if($result == null) $result = $this->result;
 103 //                      try {
 104                                 if(!($this->row = @mysql_fetch_assoc($result)) && $this->row != false) throw new CouldNotIterate(mysql_errno(), mysql_error(), (__FILE__ . ', line ' . __LINE__));
 105                                 // print_r($this->row);
 106                                 return $this->row;
 107 //                      }
 108 //                      catch(DBException $e) {
 109 //                              print_r($e);
 110 //                              $this->row = null;
 111 //                              $this->error = $e;
 112 //                              return false;
 113 //                      }
 114                 }
 115                 public function select($params, $table) {
 116                         // where operators
 117                         $where_operator = !empty($params["where_operator"]) ? $params["where_operator"] : "AND";
 118
 119                         // compile list of columns to select (usually defaults to "*")
 120                         if(is_array($params["columns"])) {
 121                                 $columns = implode($params["columns"], ", ");
 122                         } else {
 123                                 // if nothing is specified, select everything
 124                                 $columns = !empty($params["columns"]) ? $params["columns"] : "*";
 125                         }
 126
 127                         // compile the list of WHERE arguments
 128                         if(is_array($params["where"])) {
 129                                 foreach($params["where"] as $column=>$values) {
 130                                         if(is_array($values) && is_int($column)) {
 131                                                 // support for passing in arrays of ('key','value')
 132                                                 if($where != "") $where .= " {$where_operator} "; // append value of $strWhereOperator
 133                                                 $where .= "{$values[0]}='{$values[1]}'";
 134                                         } elseif(is_array($values)) {
 135                                                 foreach($values as $value) {
 136                                                         if($clause != "") $clause .= " OR ";
 137                                                         $clause .= "{$column}='{$value}'";
 138                                                 }
 139                                                 if($where != "") $where .= " {$where_operator} "; // append value of $strWhereOperator
 140                                                 $where .= $clause;
 141                                         } else {
 142                                                 if($where != "") $where .= " {$where_operator} "; // append value of $strWhereOperator
 143                                                 $where .= "{$column}='{$values}'";
 144                                         }
 145                                 }
 146                         } else {
 147                                 if(!empty($params["where"])) $where = $params["where"];
 148                         }
 149                         if(!empty($where)) $where = "WHERE {$where}";
 150
 151                         // order by
 152                         if(!empty($params["order_by"])) $order_by = "ORDER BY " . $params["order_by"];
 153
 154                         // limit
 155                         if(!empty($params["limit"])) $limit = "LIMIT " . $params["limit"];
 156
 157                         // "SELECT {$columns} FROM {$table} {$strWhere}{$strOrderBy};"
 158                         $query = 'SELECT %s FROM %s %s %s %s;';
 159
 160                         // debug purposes
 161                         // print sprintf($query, $columns, $table, $where, $order_by, $limit) . "<br />\n";
 162
 163                         // execute query
 164                         $this->query(sprintf($query, $columns, $table, $where, $order_by, $limit));
 165                 }
 166                 public function insert($params, $table) {
 167                         // get columns of the table
 168                         $table_columns = $this->explain_table($table);
 169
 170                         // compile list of columns and values to insert
 171                         foreach($params as $column=>$value) {
 172                                 // check if it's an association
 173                                 if(is_array($value) || is_object($value)) continue;
 174
 175                                 // don't insert 'id' values
 176                                 if($column == "id") continue;
 177
 178                                 // also, don't update if it's not one of the columns in the table (uh, duh)
 179                                 if(!in_array($column, $table_columns)) continue;
 180
 181                                 // add commas
 182                                 if(!empty($columns)) $columns .= ', ';
 183                                 if(!empty($values)) $values .= ', ';
 184
 185                                 // add slashes to help prevent SQL Injection problems
 186                                 // $value = addslashes($value);
 187
 188                                 // assemble query strings
 189                                 $columns .= "{$column}";
 190                                 $values .= "'{$value}'";
 191                         }
 192
 193                         // "INSERT INTO {$table} ({$strColumns}) VALUES ({$values});"
 194                         $query = 'INSERT INTO %s (%s) VALUES (%s);';
 195
 196                         // debug purposes
 197                         // print sprintf($query, $table, $columns, $values);
 198
 199                         // execute query
 200                         $this->query(sprintf($query, $table, $columns, $values));
 201                 }
 202                 public function update($params, $table) {
 203                         // get columns of the table
 204                         $table_columns = $this->explain_table($table);
 205
 206                         // compile list of columns and values to insert
 207                         foreach($params as $column=>$value) {
 208                                 // check if it's an association
 209                                 if(is_array($value) || is_object($value)) continue;
 210
 211                                 // don't update 'id' values (in the where clause)
 212                                 if($column == "id") continue;
 213
 214                                 // also, don't update if it's not one of the columns in the table (uh, duh)
 215                                 if(!in_array($column, $table_columns)) continue;
 216
 217                                 // add commas
 218                                 if(!empty($columns)) $columns .= ', ';
 219
 220                                 // add slashes to help prevent SQL Injection problems
 221                                 // $value = addslashes($value);
 222
 223                                 // assemble query strings
 224                                 $columns .= "{$column}='{$value}'";
 225                         }
 226
 227                         // "UPDATE {$table} SET {$columns} WHERE id='{$params[id]}';"
 228                         $query = 'UPDATE %s SET %s WHERE id=\'%s\';';
 229
 230                         // debug purposes
 231                         // print sprintf($query, $table, $columns, $params['id']);
 232
 233                         // execute query
 234                         $this->query(sprintf($query, $table, $columns, $params['id']));
 235                 }
 236                 public function delete($params, $table) {
 237                         // get params
 238                         $id = empty($params['id']) ? $params['where']['id'] : $params['id'];
 239
 240                         // "DELETE FROM {$table} WHERE id='{$nId}';"
 241                         $query = 'DELETE FROM %s WHERE id=\'%s\' LIMIT 1;';
 242
 243                         // execute query
 244                         $this->query(sprintf($query, $table, $id));
 245                         // print "DELETE FROM {$table} WHERE id='{$nId}';";
 246                 }
 247                 public function delete_all($params, $table) {
 248                         // where operators
 249                         $where_operator = !empty($params["where_operator"]) ? $params["where_operator"] : "AND";
 250
 251                         // compile list of columns to select (usually defaults to "*")
 252                         if(is_array($params["columns"])) {
 253                                 $columns = implode($params["columns"], ", ");
 254                         } else {
 255                                 // if nothing is specified, select everything
 256                                 $columns = !empty($params["columns"]) ? $params["columns"] : "*";
 257                         }
 258
 259                         // compile the list of WHERE arguments
 260                         if(is_array($params["where"])) {
 261                                 foreach($params["where"] as $column=>$value) {
 262                                         if($where != "") $where .= " {$where_operator} "; // append value of $strWhereOperator
 263                                         $where .= "{$column}='{$value}'";
 264                                 }
 265                         } else {
 266                                 if(!empty($params["where"])) $where = $params["where"];
 267                         }
 268                         if(!empty($where)) $where = "WHERE {$where}";
 269
 270                         if(!empty($params['limit'])) $limit = "LIMIT {$params[limit]}"; // for when in 'delete' // else $limit = "LIMIT 1";
 271
 272                         // "SELECT {$columns} FROM {$table} {$strWhere}{$strOrderBy};"
 273                         $query = 'DELETE FROM %s %s %s;';
 274
 275                         // debug purposes
 276                         // print sprintf($query, $table, $where, $limit) . "<br />\n";
 277
 278                         // execute query
 279                         $this->query(sprintf($query, $table, $where, $limit));
 280                 }
 281
 282                 // functions
 283                 public function get_last_id() {
 284                         return mysql_insert_id();
 285                 }
 286                 public function rows_found() {
 287                         return mysql_num_rows($this->result);
 288                 }
 289                 public function affected_rows() {
 290                         return mysql_affected_rows($this->db);
 291                 }
 292
 293                 // get table column names (to make sure 'updates' and 'inserts' don't try to update a column that doesn't exist)
 294                 public function explain_table($table) {
 295                         // query
 296                         $query = "EXPLAIN {$table};";
 297
 298                         // get table details
 299                         $result = @mysql_query($query, $this->db);
 300
 301                         // loop through details
 302                         while($row = @mysql_fetch_assoc($result)) {
 303                                 $rows[] = $row;
 304                         }
 305
 306                         foreach($rows as $column) {
 307                                 $columns[] = $column['Field'];
 308                         }
 309
 310                         return $columns;
 311                 }
 312         }
 313
 314         class CouldNotConnect extends StdException { }
 315         class CouldNotSelectDB extends StdException { }
 316         class CouldNotIterate extends StdException { }
 317         class CouldNotFind extends StdException { }
 318         class CouldNotSave extends StdException { }
 319         class CouldNotDelete extends StdException { }
 320 ?>