Database Class

[webdesigner25]

could someone explain it how a database class is constructed, and what it does?thx in advance

[justsomeguy]

Classes in general just group together a related set of functions. I'll try to get some comments into my database class and post it.

[justsomeguy]

This is the class I use on a lot of my projects:

<?php/****************************DB classThis class encapsulates routines for working with a MySQL database.Each instance of this class will only work with one connection tothe database, and each instance only works on a single SQL statementat a time.****************************/class db{  private $host = '';       // MySQL host  private $user = '';       // MySQL username  private $pass = '';       // MySQL password  private $db = '';         // MySQL database name  private $conn = false;    // connection reference  public $sql_stmt = '';    // current or previous SQL statement  public $params = false;   // array of parameters for the query  private $result = false;  // result object  private $rowset = false;  // rowset array  /**************************  Constructor    Connects to the database if the parameters were passed.  params:    $h: MySQL host    $u: MySQL username    $p: MySQL password    $d: MySQL database  **************************/  function __construct($h = '', $u = '', $p = '', $d = '')  {    if ($h != '')    {      $this->host = $h;      $this->user = $u;      $this->pass = $p;      $this->db = $d;      $this->connect();    }  }  /**************************  connect  Connects to the database.  Optionally sets parameters if  they were passed.    params:    $h: MySQL host    $u: MySQL username    $p: MySQL password    $d: MySQL database  **************************/  function connect($h = '', $u = '', $p = '', $d = '')  {    if ($h != '')    {      $this->host = $h;      $this->user = $u;      $this->pass = $p;      $this->db = $d;    }    $this->conn = mysql_connect($this->host, $this->user, $this->pass) or trigger_error(mysql_error(), E_USER_ERROR);    if ($this->db != '')      mysql_select_db($this->db, $this->conn) or trigger_error($this->error(), E_USER_ERROR);  }  /**************************  sql    Sets the current SQL statement.  The SQL query should  be sent in sprintf format if any parameters will be  added. See:  [url="http://www.php.net/manual/en/function.sprintf.php"]http://www.php.net/manual/en/function.sprintf.php[/url]  params:    $str: the SQL statement  **************************/  function sql($str)  {    $this->sql_stmt = $str;  }  /**************************  add_param  Adds a new parameter to the current query.  params:    $val: the parameter value    $escape: if true or undefined, the value will be      escaped and surrounded with single quotes  **************************/  function add_param($val, $escape = true)  {    if ($escape)      $val = "'" . $this->escape($val) . "'";    if (!$this->params)      $this->params = array();    $this->params[] = $val;  }  /**************************  exec    Executes a query and returns the result from mysql_query.  If parameters were sent, parameter values will be   replaced before the query is executed.  return:    The return value from mysql_query  **************************/  function exec()  {    $this->check_db();    if ($this->sql_stmt == '')      trigger_error('SQL statement not set', E_USER_ERROR);    if (is_array($this->params) && count($this->params))    {      $this->sql_stmt = vsprintf($this->sql_stmt, $this->params);      $this->params = false;    }    return mysql_query($this->sql_stmt) or trigger_error($this->error(), E_USER_ERROR);  }  /**************************  select  Executes a select query and returns the result set as an  array of rows.  Optionally accepts parameters to limit the  result to a certain page.  If parameters were sent, parameter values will be  replaced before the query is executed.  params:    $limit: the number of results to limit the result to    $page: the page number to get results for      return:    An associative array of selected records      example:    $db->sql('SELECT * FROM users WHERE username=%s');    $db->add_param('admin');    $users = $db->select();  **************************/  function select($limit = 0, $page = 0)  {    $this->check_db();    if ($this->sql_stmt == '')      trigger_error ('SQL statement not set', E_USER_ERROR);    if (is_array($this->params) && count($this->params))    {      $this->sql_stmt = vsprintf($this->sql_stmt, $this->params);      $this->params = false;    }    if ($limit)    {      $this->sql_stmt .= ' LIMIT ';      if ($page)        $this->sql_stmt .= (($page - 1) * $limit) . ',';      $this->sql_stmt .= $limit;    }    $this->result = mysql_query($this->sql_stmt) or trigger_error($this->error(), E_USER_ERROR);    // save the result records into the rowset array    $this->rowset = array();    while ($row = mysql_fetch_assoc($this->result))    {      // convert null to empty string      foreach ($row as $k => $v)      {        if (is_null($v))          $row[$k] = '';      }      $this->rowset[] = $row;    }    mysql_free_result($this->result);    return $this->rowset;  }  /**************************  delete  Executes a delete query, with an optional limit parameter.  If parameters were sent, parameter values will be  replaced before the query is executed.  params:    $limit: the maximum number of records to delete  return:    The return value from mysql_query      example:    $db->sql('DELETE FROM users WHERE id=%d');    $db->add_param(1, false);    $db->delete();  **************************/  function delete($limit = 0)  {    $this->check_db();    if ($this->sql_stmt == '')      trigger_error ('SQL statement not set', E_USER_ERROR);    if (is_array($this->params) && count($this->params))    {      $this->sql_stmt = vsprintf($this->sql_stmt, $this->params);      $this->params = false;    }    if ($limit)      $this->sql_stmt .= " LIMIT {$limit}";    return mysql_query($this->sql_stmt) or trigger_error($this->error(), E_USER_ERROR);  }  /**************************  insert  Inserts a record into the given table.  params:    $table: the table name    $params: an associative array of name/value pairs  return:    The return value from mysql_query  example:    $user = array('username' => 'admin', 'password' => '1234');    $db->insert('users', $user);  **************************/  function insert($table, $params)  {    $this->check_db();    if (!is_array($params) || count($params) == 0)      return false;    $names = '';    $values = '';    foreach ($params as $k => $v)    {      $names .= "`{$k}`,";      $values .= "'" . $this->escape($v) . "',";    }    $names = substr($names, 0, -1);    $values = substr($values, 0, -1);    $this->sql_stmt = "INSERT INTO {$table} ({$names}) VALUES ({$values})";    return mysql_query($this->sql_stmt, $this->conn) or trigger_error($this->error(), E_USER_ERROR);  }    /**************************  update    Updates one or more records in the given table.    params:    $table: the table name to update    $params: an associative array of name/value pairs    $where: the WHERE condition(s)      return:    the return value from mysql_query      example:    $user = array('password' => 'abc123');    $db->update('users', $user, 'id=1');  **************************/  function update($table, $params, $where = '')  {    $this->check_db();    if (!is_array($params) || count($params) == 0)      return false;    $cols = '';    foreach ($params as $k => $v)      $cols .= "`{$k}`='" . $this->escape($v) . "',";    $cols = substr($cols, 0, -1);    if ($where != '')      $where = " WHERE {$where}";    $sql = "UPDATE {$table} SET {$cols} {$where}";    $this->sql_stmt = $sql;    return mysql_query($sql, $this->conn) or trigger_error($this->error(), E_USER_ERROR);  }  /**************************  get_fields    Return an array of information about the fields in the table.    params:    $table: the table to get information about      return:    an associative array with 'field' and 'type' elements containing    the name and data type of each field  **************************/  function get_fields($table)  {    $this->check_db();    if ($table === '')      return false;    $retval = array();    $result = mysql_query("SHOW FIELDS FROM {$table}");    while ($row = mysql_fetch_array($result))      $retval[] = array('field' => $row['Field'], 'type' => $row['Type']);    return $retval;  }  /**************************  transform_rowset    Casts each value in the rowset to the native data type in the  given table.  The MySQL PHP extension will always return string values for all  columns.  This function will convert int, bit, float, decimal,  double, and boolean fields to the native PHP types.  params:    $rows: the rowset returned from the select method    $table: the table name the rows came from      return:    the converted rowset  **************************/  function transform_rowset($rows, $table)  {    if (!is_array($rows) || count($rows) == 0 || $table === '')      return $rows;    $tf = $this->get_fields($table);    $fields = array();    foreach ($tf as $f)      $fields[$f['field']] = $f['type'];    if (count($fields) == 0)      return $rows;    foreach ($rows as $k => $v)    {      foreach ($v as $f => $val)      {        if (!isset($fields[$f]))          continue;        if (stripos($fields[$f], 'enum') === 0)          continue;        if (stripos($fields[$f], 'int') !== false ||            stripos($fields[$f], 'bit') !== false)        {          $v[$f] = intval($val);        }        elseif (stripos($fields[$f], 'float') !== false ||                stripos($fields[$f], 'decimal') !== false ||                stripos($fields[$f], 'double') !== false)        {          $v[$f] = floatval($val);        }        elseif (stripos($fields[$f], 'bool') !== false)        {          $v[$f] = (bool)$val;        }      }      $rows[$k] = $v;    }    return $rows;  }  /**************************  escape    Escapes a value using mysql_real_escape_string.  Optionally  strips slashes if magic quotes is enabled.    params:    $str: the value to escape    $strip: true or undefined to strip slashes if magic      quotes is enabled        return:    the escaped value  **************************/  function escape($str, $strip = true)  {    if ($strip && get_magic_quotes_gpc())      $str = stripslashes($str);    return mysql_real_escape_string($str, $this->conn);  }  /**************************  insert_id    Gets the last ID as a result of an insert query.    return:    the ID  **************************/  function insert_id()  {    return mysql_insert_id($this->conn);  }    /**************************  num_rows    Returns the number of rows that the given select query   would return.    params:    $sql: the select query      return:    the number of rows the query returns  **************************/  function num_rows($sql)  {    return mysql_num_rows(mysql_query($sql, $this->conn));  }  /**************************  check_db    Exits if the database connection has not been made.  **************************/  function check_db()  {    if ($this->conn == false)      die('Database not initialized');  }  /**************************  error    Returns relevant error information.  **************************/  function error()  {    $trace = debug_backtrace();    return 'DB ' . $trace[1]['function'] . ' error: From ' . str_replace(strtolower($_SERVER['DOCUMENT_ROOT']), '', strtolower($trace[1]['file'])) . '(' . $trace[1]['line'] . '): ' . 'MySQL Error #' . mysql_errno($this->conn) . ': ' . mysql_error($this->conn) . '; SQL Query: ' . $this->sql_stmt;  }}?>