[Summary view] [Print] [Text view]

   1  <?php
   2  if (!defined('QFDB_SECURITY')) {
   3      die("You can't access this file directly...");
   4  }
   5  
   6  
   7  /**
   8   * MySQL Class of Quick Form DataBase (QFDB) Framework
   9   *
  10   * @author Everaldo Wanderlei Uavniczak <everaldouav [at] gmail.com>
  11   * @since 0.1
  12   * @package quickformdb
  13   * @see http://www.qfdb.net/
  14   * @license BSD License <http://www.qfdb.net/bsd_license.txt>
  15   */
  16  
  17  
  18  abstract class qfdb_db implements qfdb_db_interface {
  19  
  20  
  21      /**
  22       * Connect a Specific Data Base
  23       *
  24       * @since: 0.1
  25       * @param: void
  26       * @return: void
  27       * @access: protected
  28       * @see: quickformdb::db
  29       * @see: quickformdb::db_connect()
  30       * @see: quickformdb::init_db()
  31       */
  32      protected function sql_connect()
  33      {
  34          if ($this->db['persistency'] === true) {
  35              $this->db['connect_id'] = @mysql_pconnect($this->db['host'], $this->db['user'], $this->db['pass']);
  36          } else {
  37              $this->db['connect_id'] = @mysql_connect($this->db['host'], $this->db['user'], $this->db['pass']);
  38          }
  39  
  40          if (!$this->db['connect_id']) {
  41              $this->add_critical_error( sprintf(QFDBE_DB_CONNECTION_ERROR.$this->make_error_ref('QFDBE_DB_CONNECTION_ERROR'), $this->sql_error_msg()) );
  42              return;
  43          }
  44  
  45          // delete values for security reason
  46          $this->db['host'] = QFDBM_DB_MSG_DELETED_PROPERTIES;
  47          $this->db['user'] = QFDBM_DB_MSG_DELETED_PROPERTIES;
  48          $this->db['pass'] = QFDBM_DB_MSG_DELETED_PROPERTIES;
  49  
  50          $this->sql_select_db();
  51      } // end sql_connec()
  52  
  53  
  54  
  55      /**
  56       * Select a DataBase
  57       *
  58       * @since: 0.1
  59       * @param: string    $database     DataBase's Name
  60       * @param: bool      $update_qfdb  Update quickformdb::qdb['db']?
  61       * @return: bool
  62       * @access: public
  63       * @see: quickformdb::db
  64       * @see: quickformdb::db_set_table()
  65       * @see: quickformdb::init_db()
  66       */
  67      public function sql_select_db($database='', $update_qfdb = false)
  68      {
  69          $db = (trim($database)) ? trim($database) : $this->db['db'];
  70  
  71          if (!is_bool($update_qfdb)) {
  72              $update_qfdb = false;
  73          }
  74  
  75          if (!$this->sql_is_connected(__METHOD__)) {
  76              return false;
  77          }
  78  
  79          if (!mysql_select_db($db)) {
  80              $this->add_critical_error( sprintf(QFDBE_DB_SELECT_DB_FAIL.$this->make_error_ref('QFDBE_DB_SELECT_DB_FAIL'), $db, $this->sql_error_msg()) );
  81              $this->sql_close();
  82              $this->db['connect_id'] = false;
  83              return false;
  84          }
  85  
  86          if ($update_qfdb && $database) {
  87              $this->db['db'] = trim($database);
  88          }
  89          return true;
  90      } // end sql_select_db()
  91  
  92  
  93  
  94      /**
  95       * Verify if has open connection with a DataBase
  96       *
  97       * @since: 0.4
  98       * @param: void
  99       * @return: bool
 100       * @access: public
 101       * @see: quickformdb::db
 102       */
 103      public function sql_is_connected($method=QFDBE_DB_FUNCTION_NOT_INFORMED)
 104      {
 105          if (!$this->db['connect_id']) {
 106              $this->add_critical_error( sprintf(QFDBE_DB_CONNECTION_REQUIRED.$this->make_error_ref('QFDBE_DB_CONNECTION_REQUIRED'), $method) );
 107              return false;
 108          }
 109          return true;
 110      } // end sql_is_connected()
 111  
 112  
 113  
 114      /**
 115       * Verify if has $result Query Object
 116       *
 117       * @since: 0.4
 118       * @param: string  $method  Method that call this
 119       * @return: bool
 120       * @access: public
 121       * @see: quickformdb::db
 122       */
 123      public function sql_is_result($method=QFDBE_DB_FUNCTION_NOT_INFORMED)
 124      {
 125          if (!$this->db['query_result']) {
 126              $this->add_warning( sprintf(QFDBE_DB_NO_QUERY_ID.$this->make_error_ref('QFDBE_DB_NO_QUERY_ID'), $method) );
 127              return false;
 128          }
 129          return true;
 130      } // end sql_is_result()
 131  
 132  
 133  
 134      /**
 135       * Return SQL Error Message
 136       *
 137       * @since: 0.4
 138       * @param: void
 139       * @return: mixed
 140       * @access: string
 141       * @see: quickformdb::db
 142       */
 143      public function sql_error_msg()
 144      {
 145          return QFDBM_DB_ERRNO . mysql_errno() . QFDBM_DB_ERROR . mysql_error();
 146      } // end sql_error_msg()
 147  
 148  
 149  
 150      /**
 151       * Close a DataBase Connection
 152       *
 153       * @since: 0.1
 154       * @param: resource  $connect_id   DataBase Link Identifier
 155       * @return: bool
 156       * @access: public
 157       * @see: quickformdb::db
 158       */
 159      public function sql_close($connect_id = false)
 160      {
 161          if (!$this->sql_is_connected(__METHOD__)) {
 162              return false;
 163          }
 164  
 165          $connect_id = ($connect_id !== false) ? $connect_id : $this->db['connect_id'];
 166  
 167          if ($this->db['query_result'] && $connect_id === false) {
 168              @mysql_free_result($this->db['query_result']);
 169              $this->db['query_result'] = false;
 170          }
 171  
 172          $this->db['connect_id'] = false;
 173          return @mysql_close($connect_id);
 174      } // end sql_close()
 175  
 176  
 177      /**
 178       * Free  Result Memory
 179       *
 180       * @since: 0.4
 181       * @param: void
 182       * @return: bool
 183       * @access: public
 184       * @see: quickformdb::db
 185       */
 186      public function sql_free_result($result = false)
 187      {
 188          if (!$this->sql_is_result(__METHOD__)) {
 189              return false;
 190          }
 191  
 192          if ($result === false) {
 193              $result  = $this->db['query_result'];
 194              $this->db['query_result'] = false;
 195          }
 196  
 197      return @mysql_free_result($result);
 198      } // end sql_free_result()
 199  
 200  
 201      /**
 202       * Send a SQL Query
 203       *
 204       * @since: 0.1
 205       * @param: string  $query   SQL Query
 206       * @return: resource
 207       * @access: public
 208       * @see: quickformdb::db
 209       * @see: qfdb_db::sql_query2array()
 210       * @see: qfdb_db::sql_fetchrow()
 211       */
 212      public function sql_query($query)
 213      {
 214          if (!$this->sql_is_connected(__METHOD__)) {
 215              return false;
 216          }
 217  
 218          $this->db['query_result'] = false;
 219          if (trim($query)) {
 220              $this->db['query_result'] = @mysql_query($query, $this->db['connect_id']);
 221              if (!$this->db['query_result']) {
 222                  $this->add_critical_error( sprintf(QFDBE_DB_QUERY_ERROR.$this->make_error_ref('QFDBE_DB_QUERY_ERROR'), $query, $this->sql_error_msg()) );
 223              }
 224          } else {
 225              $this->add_critical_error(QFDBE_DB_EMPTY_QUERY.$this->make_error_ref('QFDBE_DB_EMPTY_QUERY'));
 226              return false;
 227          }
 228          return $this->db['query_result'];
 229      } // end sql_query()
 230  
 231  
 232  
 233      /**
 234       * Send a SQL Query and return one result in array
 235       *
 236       * @since: 0.4
 237       * @param: string  $query   SQL Query
 238       * @return: array of mixed
 239       * @access: public
 240       * @see: quickformdb::db
 241       * @see: qfdb_db::sql_query()
 242       */
 243      public function sql_query2array($query)
 244      {
 245          if ($result = $this->sql_query($query)) {
 246              if ($row = $this->sql_fetchrow($result)) {
 247                  foreach ($row as $key => $value) {
 248                      $rarray[$key] = $value;
 249                  }
 250                  return $rarray;
 251              }
 252              return false;
 253          } else {
 254              return false;
 255          }
 256      } // end sql_query2array()
 257  
 258  
 259  
 260      /**
 261       * Fetch a result row as an associative array and a numeric array
 262       *
 263       * @since: 0.1
 264       * @param: resource  $query_id   SQL Result Resource
 265       * @return: array of mixed
 266       * @access: public
 267       * @see: quickformdb::db
 268       * @see: qfdb_db::sql_query()
 269       */
 270      public function sql_fetchrow($query_id=false)
 271      {
 272          if (!$query_id) {
 273              $query_id = $this->db['query_result'];
 274          }
 275          if ($query_id) {
 276              return @mysql_fetch_array($query_id);
 277          } else {
 278              return false;
 279          }
 280      } // end sql_fetchrow()
 281  
 282  
 283  
 284      /**
 285       * Get number of rows in result (used in SELECT query)
 286       *
 287       * @since: 0.1
 288       * @param: resource  $query_id   SQL Query Object
 289       * @return: mixed
 290       * @access: public
 291       * @see: quickformdb::db
 292       * @see: qfdb_db::sql_query()
 293       * @see: qfdb_db::sql_affectedrows()
 294       */
 295      public function sql_numrows($query_id = false)
 296      {
 297          if (!$query_id) {
 298              $query_id = $this->db['query_result'];
 299          }
 300  
 301          if ($query_id) {
 302              return @mysql_num_rows($query_id);
 303          } else {
 304              return false;
 305          }
 306      } // end sql_numrows()
 307  
 308  
 309  
 310      /**
 311       * Get number of affected rows in previous SQL operation (used to INSERT, UPDATE, REPLACE and DELETE query)
 312       *
 313       * @since: 0.1
 314       * @param: resource  $connect_id   DataBase Link Identifier
 315       * @return: mixed
 316       * @access: public
 317       * @see: quickformdb::db
 318       * @see: qfdb_db::sql_query()
 319       * @see: qfdb_db::sql_numrows()
 320       */
 321      public function sql_affectedrows($connect_id = false)
 322      {
 323           if (!$connect_id) {
 324              $connect_id = $this->db['connect_id'];
 325          }
 326          if ($this->db['connect_id']) {
 327              return @(mysql_affected_rows($this->db['connect_id']));
 328          } else {
 329              return false;
 330          }
 331      } // end sql_affectedrows()
 332  
 333  
 334  
 335      /**
 336       * Get the ID generated from the previous INSERT operation
 337       *
 338       * @since: 0.1
 339       * @param: resource  $connect_id   DataBase Link Identifier
 340       * @return: int
 341       * @access: public
 342       * @see: quickformdb::db
 343       * @see: qfdb_db::sql_query()
 344       */
 345      public function sql_insertid($connect_id = false)
 346      {
 347          if (!$connect_id) {
 348              $connect_id = $this->db['connect_id'];
 349          }
 350  
 351          return @mysql_insert_id($connect_id);
 352      } // end sql_insertid()
 353  
 354  
 355  
 356      /**
 357       * Return SQL error in array
 358       *
 359       * @since: 0.1
 360       * @param: resource  $connect_id   DataBase Link Identifier
 361       * @return: array of mixed
 362       * @access: public
 363       * @see: quickformdb::db
 364       */
 365      public function sql_error($connect_id = false)
 366      {
 367          if ($connect_id) {
 368              $connect_id = $this->db['connect_id'];
 369          }
 370  
 371          $result["message"] = @mysql_error($connect_id);
 372          $result["code"] = @mysql_errno($connect_id);
 373          return $result;
 374      } // end sql_error()
 375  
 376  
 377  
 378       /**
 379       * Scape SQL Special Chars
 380       *
 381       * @since: 0.5
 382       * @param: string  $query   SQL Query to Scape
 383       * @param: bool  $all   Scape % and _ char too
 384       * @return: string
 385       * @access: public
 386       * @see: qfdb_db::sql_strip_slashes()
 387       */
 388      public function sql_scape_string($query, $all = true)
 389      {
 390          // verify if magic_quotes on
 391          if (get_magic_quotes_gpc()) {
 392              $string = stripslashes($query);
 393          }
 394  
 395           // scape % and _ (underscore) qualifiers that match 0 or more characteres and
 396           // any single character respectively in the SQL LIKE operator
 397          if ($all) {
 398              if ($this->db['connect_id']) {
 399                  return addcslashes(mysql_real_escape_string($query, $this->db['connect_id']), '%_');
 400              } else {
 401                  return addcslashes(addslashes($query), '%_');
 402              }
 403          } else {
 404              if ($this->db['connect_id']) {
 405                  return mysql_real_escape_string($query, $this->db['connect_id']);
 406              } else {
 407                  return addslashes($query);
 408              }
 409          }
 410      } // end sql_scape_string()
 411  
 412  
 413  
 414      /**
 415       * Un-quote string quoted with addslashes()
 416       *
 417       * @since: 0.5
 418       * @param: string  $query   SQL Query to Strip Slashes
 419       * @param: bool  $all   Strip '\%' and '\_' char too
 420       * @return: string
 421       * @access: public
 422       * @see: qfdb_db::sql_scape_string()
 423       */
 424      public function sql_strip_slashes($query, $all = false)
 425      {
 426          if ($all) {
 427              return stripcslashes(stripslashes($query));
 428          } else {
 429              return stripslashes($query);
 430          }
 431      } // end sql_strip_slashes()
 432  
 433  
 434  
 435      /**
 436       * Verify if Table Exists in DataBase
 437       *
 438       * @since: 0.5
 439       * @param: string  $tablename   Table Name
 440       * @return: string
 441       * @access: public
 442       * @see: qfdb_db::sql_get_fields()
 443       * @see: qfdb_db::sql_get_primary_key()
 444       */
 445      public function sql_table_exists($tablename)
 446      {
 447          $result = $this->sql_query("SHOW TABLES FROM `{$this->db['db']}`");
 448  
 449          if (!$result) {
 450              return false;
 451          }
 452  
 453          while ($row = $this->sql_fetchrow($result)) {
 454              if ($row[0] == $tablename) {
 455                  return true;
 456              }
 457          }
 458          return false;
 459      } // end sql_table_exists()
 460  
 461  
 462  
 463      /**
 464       * Get fields from a DataBase
 465       *
 466       * @since: 0.5
 467       * @param: string  $tablename   Table Name
 468       * @return: array of string
 469       * @access: public
 470       * @see: qfdb_db::sql_table_exists()
 471       * @see: qfdb_db::sql_get_primary_key()
 472       */
 473      public function sql_get_fields($tablename)
 474      {
 475          if (!$this->sql_is_connected(__METHOD__)) {
 476              return array();
 477          }
 478  
 479          $table = $this->sql_scape_string($tablename, false);
 480          $result = mysql_query("SHOW COLUMNS FROM  `$tablename ");
 481  
 482          if (!$result) {
 483              return array();
 484          }
 485  
 486          $ar_return = array();
 487          while ($row = $this->sql_fetchrow($result)) {
 488              $ar_return[] = $row['Field'];
 489          }
 490          return $ar_return;
 491      } // end sql_get_fields()
 492  
 493  
 494  
 495      /**
 496       * Return the Primary Key or a DataBase Table
 497       *
 498       * @since: 0.5
 499       * @param: string  $tablename   Table Name
 500       * @return: string
 501       * @access: public
 502       * @see: qfdb_db::sql_get_fields()
 503       * @see: qfdb_db::sql_table_exists()
 504       */
 505      public function sql_get_primary_key($tablename)
 506      {
 507          $result =  $this->sql_query("SHOW COLUMNS FROM `$tablename");
 508  
 509          if (!$result) {
 510              return false;
 511          }
 512  
 513          while ($row = $this->sql_fetchrow($result)) {
 514              if ($row['Key'] === 'PRI') {
 515                  return $row['Field'];
 516              }
 517          }
 518          return false;
 519      } // end sql_get_primary_key()
 520  
 521  } // end class
 522