PHP Classes

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    
Ratings Unique User Downloads Download Rankings
Not enough user ratingsTotal: 671 This week: 1All time: 4,815 This week: 560Up
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

Picture of VR
Name: VR <contact>
Classes: 3 packages by
Country: ???
Age: 54
All time rank: 1455
Week rank: 411 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:671
This week:1
All time:4,815
This week:560Up