PHP Classes
elePHPant
Icontem

PDO Database Connection: Access MySQL databases using the PDO extension

Recommend this page to a friend!
  Info   View files View files (3)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Last Updated Ratings Unique User Downloads Download Rankings
2012-02-10 (5 years ago) RSS 2.0 feedNot yet rated by the usersTotal: 653 All time: 4,727 This week: 716Up
Version License PHP version Categories
pdo-database 1.3Freeware5.0PHP 5, Databases
Description Author

This class can access MySQL databases using the PDO extension. It can:

- Connect to a given MySQL server
- Execute prepared queries using parameter values defined using arrays
- Retrieve the number of rows in a query result set
- Retrieve a single value query result
- Retrieve a whole result set into an array

Name: Volker Rubach <contact>
Classes: 3 packages by
Country: Germany Germany
Age: 50
All time rank: 145893 in Germany Germany
Week rank: 1060 Up47 in Germany Germany Up

Details
/**************************************************************************
 *
 * 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>
  Files folder image Files  
File Role Description
Plain text file class_db_pdo.inc.php Class Class
Accessible without login Plain text file conf_db_pdo.inc.php Conf. Config
Accessible without login Plain text file ReadMe.txt Doc. ReadMe

 Version Control Unique User Downloads Download Rankings  
 0%
Total:653
This week:0
All time:4,727
This week:716Up