PDO Database Connection: Access MySQL databases using the PDO extension

/**************************************************************************
 *
 * Title:         Class 'connDB' (class_db_pdo.inc.php)
 *
 * Version:       1.3
 *
 * Copyright:     (c) 2012 Volker Rubach - All rights reserved
 *
 * Description:   This class provide a connection handling via PDO to
 *                a MySQL database and execution of all SQL commands.
 *
 *************************************************************************/
  

 Importend notes
 ---------------
 Save 'class_db_pdo.inc.php' and 'conf_db_pdo.inc.php' under 'CGI-BIN'
 in the root folder. This directory exists in most cases, is not acces-
 sible from outside, but locally executed PHP scripts can access this
 folder and can use the class.

 If the hosting package has not a protected 'CGI-BIN' directory, manual
 a secure directory should be set up, then the class and config stored
 there.

 These measures increase the security against unauthorized access to
 the database, because the credentials are not as freely available!!!


 Include config file in class
 ----------------------------
 The config file should be in the same folder with the class. Then
 it will work automatically. If the file saved in another, please
 change the path in the include statement.

 <code>
 	require_once( 'conf_db_pdo.inc.php' );
 </code>


 Declaration of variables
 ------------------------
 $this->dbHost    CONF: MySQL host name
 $this->dbName    CONF: MySQL database name
 $this->dbUser    CONF: MySQL username
 $this->dbPass    CONF: MySQL password
 $this->confPDO   PDO attributes
 $this->dbc       Database connection
 $errMsg          Error message
 $sql             SQL statement from PHP
 $params          Parameter from PHP [Array]
 $param           Single Parameter for bind
 $value           Value for bind
 $type            Type of the value [Boolen / Integer / Null / String]


 Error handling
 --------------
 trigger_error( $errMsg->getMessage() . ";E_USER_NOTICE" );
 trigger_error( $errMsg->getMessage() . ";E_USER_WARNING" );
 trigger_error( $errMsg->getMessage() . ";E_USER_ERROR" );


 Include class
 -------------
 <code>
   include( '../ <path> /class_db_pdo.inc.php' );
 </code>


 Create instance
 ---------------
 <code>
   $db = new connDB();
 </code>

 Usage: Row count
 ----------------
 To get the number of processed rows, in a SELECT, INSERT, UPDATE, or
 DELETE, you need to call 'rowCount' after the database function.

 <code>
   $count = $db->rowCount();
 </code>


 Usage: 'Single value / row [SELECT]'
 ------------------------------------
 Select that use 'Prepared Statement' and if needed 'Bind Parameter'.
 Returns a single value or an row, that can be directly accessed.

 <code>
   $sql = "SELECT < column > FROM < table >";
   $db->exec($sql, $params);
   $value = $db->single();
   echo $value['< column >'] . "\n";
 </code>

 <code>
   $sql = "SELECT * FROM < table >";
   $db->exec($sql, $params);
   $row = $db->single();
   echo $row['< column >'] . "\n";
 </code>

 <code>
   $sql = "SELECT * FROM < table > WHERE < column > = :valuename";
   $params = array(':valuename' => 'value', ...);
   $db->exec($sql, $params);
   $row = $db->single();
   echo $row['< column >'] . "\n";
 </code>

 <code>
   $sql = "SELECT * FROM < table > WHERE < column_1 > = :valuename_1 AND < column_2 > = :valuename_2";
   $params = array(':valuename_1' => 'value', :valuename_2' => 'value', ...);
   $db->exec($sql, $params);
   $row = $db->single();
   echo $row['< column >'] . "\n";
 </code>

 <code>
   $sql = "SELECT * FROM < table > WHERE < column_1 > = :valuename_1 OR < column_1 > = :valuename_2";
   $params = array(':valuename_1' => 'value', :valuename_2' => 'value', ...);
   $db->exec($sql, $params);
   $row = $db->single();
   echo $row['< column >'] . "\n";
 </code>

 <code>
   $sql = "SELECT * FROM < table > WHERE < column > LIKE :valuename";
   $params = array(':valuename' => '%...$', ...);
   $db->exec($sql, $params);
   $row = $db->single();
   echo $row['< column >'] . "\n";
 </code>

 <code>
   $sql = "SELECT * FROM < table_1 > a LEFT JOIN < table_2 > b ON a.< column > = b.< column > WHERE < column > = :valuename_1";
   $params = array(':valuename_1' => 'value', ...);
   $db->exec($sql, $params);
   $row = $db->single();
   echo $row['< column >'] . "\n";
 </code>


 Usage: Result set [SELECT]
 --------------------------
 Select query that use 'Prepared Statement' and if needed 'Bind Parameter'.
 Returns an associative array, that can be looped through with 'FOREACH'.

 <code>
   $sql = "SELECT * FROM < table >";
   $db->exec($sql, $params);
   $rows = $db->resultset();
   foreach ($rows as $row)
           {
           echo $row['< column >'] . "\n";
           }
 </code>

 <code>
   $sql = "SELECT * FROM < table > WHERE < column > = :valuename_1";
   $params = array(':valuename_1' => 'value', ...);
   $db->exec($sql, $params);
   $rows = $db->resultset();
   foreach ($rows as $row)
           {
           echo $row['< column >'] . "\n";
           }
  </code>

 <code>
   $sql = "SELECT < column >  FROM < table_1 > a LEFT JOIN < table_2 > b ON a.< column > = b.< column > WHERE < column > LIKE :valuename_1";
   $params = array(':valuename_1' => 'value', ...);
   $db->exec($sql, $params);
   $rows = $db->resultset();
   foreach ($rows as $row)
           {
           echo $row['< column >'] . "\n";
           }
 </code>


 Usage: Run query [INSERT / DELETE / UPDATE / REPLACE]
 -----------------------------------------------------

 <code>
   $sql = "INSERT INTO < table > (< column_1 >, < column_2 >, ... ) VALUES (:valuename_1, :valuename_2, ...)";
   $params = array(':valuename_1' => 'value', ':valuename_2' => 'value', ...);
   $db->exec($sql, $params);
 </code>

 <code>
   $sql = "DELETE FROM < table > WHERE < column_1 > = :valuename_1";
   $params = array(':valuename_1' => 'value');
   $db->exec($sql, $params);
 </code>

 <code>
   $sql = "REPLACE INTO < table > (< column_1 >, < column_2 >, ...) VALUES (:valuename_1, :valuename_2, ...)";
   $params = array(':valuename_1' => 'value', ':valuename_2' => 'value', ...);
   $db->exec($sql, $params);
 </code>

 <code>
   $sql = "UPDATE < table > SET < column_1 > = :valuename_1 WHERE < column_2 > = :valuename_2";
   $params = array(':valuename_1' => 'value', ':valuename_2' => 'value');
   $db->exec($sql, $params);
 </code>