Database Class

<?
// *** ------------------------------------------------------------------------------------ *** //
// *** Class.Database, a object which can work around the database handling                 *** //
// *** Orginally created: 5 november 2005                                                   *** //                                                                *** //
// *** Author: Kees Schepers & Mike de Klerk                                                *** //
// *** Last edit: 5 november 2005: creation :)                                              *** //
// *** Platform PHP4.x                                                                      *** //
// *** ------------------------------------------------------------------------------------ *** //
class dbClass {
  var $host;
  var $username;
  var $password;
  var $database;
  var $executedQuerys = 0;  
  var $connection;
  var $errors;
  var $cssClass;
  function dbClass($host = 'localhost', $username, $password, $database) {
    // ---
    // Check if variables ain't empty, if is not empty then connect to database
    // ---
    if(!empty($username) AND !empty($host) AND !empty($database)) {
      $this->host     = $host;
      $this->username = $username;
      $this->password = $password;
      $this->database = $database;
      $this->_dbConnect();
    }
    else
    {
      trigger_error('I can not connect with empty parameters; username, password and database.', E_USER_NOTICE);
    }
    $this->cssClass = 'debug';
  }
  function _dbConnect() {
    // ---
    // Just connect
    // ---
    if( $this->connection = mysql_connect($this->host, $this->username, $this->password) ) {
      if( mysql_select_db($this->database, $this->connection) ) {
        trigger_error('Connection set and database selected!',E_USER_NOTICE);
        return TRUE;
      } else {
        trigger_error('Error when selecting database.',E_USER_NOTICE);
        return FALSE;
      }
    } else {
      trigger_error('Error when connection to mysql.',E_USER_NOTICE);
      return FALSE;
    }
  }
  function dbDisconnect() {
    // ---
    // Disconnect database connection
    // ---
    mysql_close($this->connection);
  }
  function insertRow($table,$value) {
    // ---
    // With this function you can insert a record to a table which looks to the keys of
    // the values array and the fields in the table and replaces the value..
    // ---
    $sql = 'INSERT INTO '.$table.'
             (';
      // ---
      // Which and how many fields does the table have?
      // ---
      $result = $this->execQuery( 'SELECT * FROM '.$table );
      $total_fields = $this->numFields( $result );
      for($i=1;$i<$total_fields;$i++) {
        if($total_fields-$i != 1 ) {
          $sql .= '`'.$this->fieldName($result,$i).'`,';
        } else {
          $sql .= '`'.$this->fieldName($result,$i).'`';
        }
      }
      $sql .= ')
      VALUES
      (';
      for($i=1;$i<$total_fields;$i++) {
        if($total_fields-$i != 1 ) {
          $sql .= '"'.$value[$this->fieldName($result,$i)].'",';
        } else {
          $sql .= '"'.$value[$this->fieldName($result,$i)].'"';
        }
      }
      $sql .= ')';
      return $this->execQuery($sql);
  }
  function deleteRow($table,$id) {
    // ---
    // This functions deletes a record from the database.
    // ---
    return $this->execQuery('DELETE FROM `'.$table.'` WHERE `id`="'.$id.'"');
  }
  function updateRow($table,$value=array(),$id) {
    //--------
    // function to automaticly update a (set of) row(s) and looks to the $value array for a key which equals to table field name and
    // replaces the value in the mysql table..
    // Which fields does the table have? And are there values posted?
    //--------
    if( count($value) > 0 ) {
      $result = mysql_query( 'SELECT * FROM `'.$table.'`' );
      $total_fields = $this->numFields( $result );
      $sql = 'UPDATE `'.$table.'` SET ';
      for($i=1;$i<$total_fields;$i++)
      {
        if( !empty($value[$this->fieldName($result,$i)]) )
        {
            $sql .= '`'.$this->fieldName($result,$i).'`="'.$value[$this->fieldName($result,$i)].'", ';
        }
      }
      $sql = substr($sql,0,(strlen($sql)-2));
      $sql .= ' WHERE id="'.$id.'"';
      // ---
      // returns the affected rows by succeeding. Else false with a trigger error
      // ---
      return $this->execQuery($sql);
    } else {
      trigger_error('values array has no keys and values when using $obj_db->updateRow()',E_USER_NOTICE);
      return FALSE;
    }
  }
  function selectRows($table,$criterion) {
    $sql    = 'SELECT * FROM '.$table.' '.$criterion;
    $result = $this->execQuery($sql);
    if($this->numRows($result) > 0 ) {
      return $this->fetchResult($result);
    } else {
      return FALSE;
    }
  }
  function insertId() {
    return mysql_insert_id();
  }
  function affectedRows($result) {
    return mysql_affected_rows($result);
  }
  function numRows($result) {
    return mysql_num_rows($result);
  }
  function numFields($result) {
    return mysql_num_fields($result);
  }
  function fieldName($result,$numindex) {
    return mysql_field_name($result,$numindex);
  }
  function execQuery($query) {
    // ---
    // affected rows only returns a value with INSERT, UPDATE or DELETE.
    // ---
    if($result = mysql_query($query,$this->connection) OR trigger_error('Failed execute query in db_obj. Error: '.$this->sqlError().' Query: '.$query, E_USER_WARNING) ) {
      trigger_error('Query succeed with syntax: '.$query,E_USER_NOTICE);
      if(strpos($query, 'SELECT') !== FALSE ) {
        $this->executedQuerys++;
        return $result;
      } elseif( (strpos($query, 'INSERT') !== FALSE) OR (strpos($query, 'UPDATE') !== FALSE) OR (strpos($query, 'DELETE') !== FALSE) ) {
          // ---
          // affected rows returned.
          // ---
          return $this->affectedRows($this->connection);
      } else {
        $this->executedQuerys++;
        return TRUE;
      }
    } else {
      return FALSE;
    }
  }
  function fetchResult($result) {
    // ---
    // This functions fetches the result of a query, in one standard way, this will learn us to work with one method!
    // ---
    if( $this->numRows($result) > 0 ) {
      // ---
      // We make our own array, with a numeric index for a record and a text index for the fields !
      // ---
      $records = array();
      while( $record = mysql_fetch_array($result) ) {
        $records[]=$record;
      }
      return $records;
    } else {
      trigger_error('$obj_db->fetchResult() was trying to fetch a query qith no records as result!',E_USER_NOTICE);
      return FALSE;
    }
  }
  function oneRowResult($result,$row) {
    if( is_numeric($row) ) {
      return mysql_result($result,$row);
    } else {
      trigger_error('Second parameter with function oneRowResult in $obj_db was not numeric',E_USER_NOTICE);
    }
  }
  function sqlError() {
    // ---
    // Gives a error of the failed result, and then sets it in the error var of this object..
    // ---
    if( DEBUG === TRUE ) {
      $this->errors [] = mysql_error($this->connection);
    }
    return mysql_error($this->connection);
  }
  function debug() {
    // ---
    // This function drops debug info, of query results.
    // ---
    echo '<div class="'.$this->cssClass.'" style="margin-top:10px;">';
    if(count($this->errors) > 0) {
      foreach($this->errors AS $errno => $error) {
        echo 'Error NR: '.$errno.' '.$error.' <br />';
      }
    } else {
      echo 'No database errors to print!';
    }
    echo '<br />'.$this->executedQuerys.' query\'s executed.';
    echo '</div>';
  }
}
// ---
// Inialiseer een database verbinding
// ---
$database = New dbClass('localhost','testuser','testpassword','testdatabase');

// ---
// ### De functie insertRow() ###
// Stel je hebt een pagina met een html-form en als er op submit geklikt word genereer je de volgende gegevens:
// ---
$_POST['name'] = 'test';
$_POST['age'] = '16';
$_POST['birthdate'] = '22-07-1986';
$_POST['hobbys'] = 'TV Kijken, werken, vrouwen, bier, programming, fitness and many more';

// ---
// Oke stel, dat je een nieuwe record wil toevoegen in de database en je hebt volgende kolommen in je database (name,age,birthdate,hobbys)
// Dan zou je ook lekker een lange query kunnen bouwen, maar je zou ook het volgende kunnen doen:
// ---
$database->insertRow('tabelnaam',$_POST);
// ---
// De functie "insertRow()" gaat alle velden af, en kijkt of deze bestaan in de tabel, indien ja, dan zet hij deze waarden.
// Waarom doet hij dit? omdat je misschien een gepost veld hebt dat $_POST['submit'] heeft, dan zou hij die ook proberen in te voegen
// en krijg je een dikke sql error
// ---

// ---
// Stel je hebt een form op je site, en je wilt dat een gebruiker zijn gegevens kan wijzigen, dan zou je de volgende functie kunnen gebruiken:
// ---
$database->updateRow('tabelnaam',$_POST,$_GET['id']);
// ---
// Deze functie doet bijna hetzelfde als insertRow, alleen dat hij een update uitvoert op de record id, die meegegeven is als 3e parameter.
// ---

// ---
// Stel je wilt een aantal gegevens fetchen, bijvoorbeeld een lijstje van je gebruikers, dan kun je dit alsvolgt doen:
// ---
$result = $database->execQuery('SELECT * FROM users');
if( $database->numRows( $result ) > 0 ) {
    $user = $database->fetchResult($result);
    // ---
    // Laat nu de users zien.
    // ---
    echo '<table>';
    for( $userNR=0; $userNR<count($user); $userNR++ ) {
        echo '<tr>';
        echo '  <td>'.$user[$userNR]['name'].'</td>';
        echo '  <td>'.$user[$userNR]['age'].'</td>';
        echo '  <td>'.$user[$userNR]['birtdate'].'</td>';
        echo '</tr>';
    }
    echo '</table>';
} else {
    echo 'Er zijn geen resultaten.';
}

// ---
// Stel dat je nog aan het debuggen bent doe je dit:
// ---
$database->debug();
// ---
// Deze functie print allerlei handige informatie
// ---

// ---
// en altijd aan het einde van je script netjes je database verbinding sluiten!
// ---
$database->dbDisconnect();
?>