The Database access library : pdo_access.inc.php
The file freelance writer pdo_access.inc.php contains especially functions to easily access databases. This file is, like all our tools GNU/GPL3 licensed.
As follows the documented source code of pdo_access.inc.php: if you are just interested in one particular function you may also copy past it's code, but be careful, most functions depend on other in pdo_access.inc.php defined functions.
Also the the general library stdfuncs.inc.php is required.
Or just download the whole file : pdo_access.inc.php.zip (~6 KB)
<?php // pdo_access.inc.php /* * Copyright (C) 2004-2007 Manuel Kägi, kaegi(at)gmx(dot)ch * * This program is free software; you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation; either version 2 of the License, or (at * your option) any later version. * * This program is distributed in the hope that it will be useful, but * WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program; if not, write to the Free Software * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. */ /** * GENERAL NOTES * ============================ * * 1. This class requires methods form stdfuncs.inc.php (can be found at www.corvent.ch) * stdfuncs.inc.php will be looked up in a folder "core" going off from any php-include path. * If you dont want a subfolder for stdfuncs.inc.php just change the require_once argument. * * 2. Generrally you can access all DBMS for which a PDO-driver is available, but tested is only MySQL and SQLite * The class-Variable "specEscFunction" specifies the correct escaping functions according to the DBMS * * 3. The Values you get from getSQLResultsAsArray are formatted in a way to use them as data for all * tableXXXX in the class arrayfunc in stdfuncs.inc.php (tableSort, tableGroup etc..) * * 4. To inspect the results from any getSQLReusltXXXX() function use debug::show(), defined in stdfuncs.inc.php * * 5. All SQL-Staements will measure their time using the debug::startMessung and stopMessung methods * if you are intrested in these results you can print them to the standard Output using * debug::showLaufzeit ("Laufzeit" means "runtime" in german), You can do this after all your * Db-Operations are done, you will get a nicely formatted overview over all SQL-Statements and their * runtime consumption. * This measurings need very little time itself, if you eigther want to switch them off just * remove all "debug::startMessung" and "debug::stopMessung" commands * */ require_once("core/stdfuncs.inc.php"); class pdo_access { var $db; var $debugMode = true; var $dsn; var $user; var $pw; var $escapingFunction = "addslashes"; var $specEscFunction = array('sqlite'=>'sqlite_escape_string', 'sqlite2'=>'sqlite_escape_string'); /** * Instantiates a pdo_access Object and connects to the specified Database Server * * @param string $dbServerSpec the dsn of the database to connect with * {dbType}:{dsn} * @param string $user the username (if you use a password protected database) * @param szring $pw: the password to use */ function pdo_access($dbServerSpec, $user="", $pw="") { $this->dsn = $dbServerSpec; $this->user = $user; $this->pw = $pw; try { // $this->db = new PDO($dbServerSpec, $user, $pw, array(PDO::ATTR_PERSISTENT => true)); $this->db = new PDO($dbServerSpec, $user, $pw); $this->db->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL); } catch (PDOException $e) { print("pdo_access: Connection failed: ".$e->getMessage()."\n"); print("DSN = '".$this->dsn."'\n"); } } // SELECT SQL's // ======================= /** * Performs a SELECT query and returns the result as an array of hashtables representing the records * * @param string $sql, the SQL to perform (e.g. "SELECT * FROM table") * @param string $keyCol - OPTIONAL - if set the value of the specified column will be used as index in the array of the record-Haschtables, * typically "id" OR "username", this column should be unique over the whole result of the query, * otherwise you wont get all records, the later ones with the same value in the keyCol will * overwrite the earlier ones * * If $keyCol is not set the indizes of the records will go from 0 to the queryresult's length - 1 * * @return array an array of haschtables representing the result of the query */ function getSQLResultAsArray($sql, $keyCol = "") { debug::startMessung("getSQLResultAsArray"); $result = $this->select($sql, PDO::FETCH_ASSOC); // debug::show($result, "result"); if ($keyCol != "") { $hashedResult = array(); foreach ($result as $row) { $key = $row[$keyCol]; unset($row[$keyCol]); $hashedResult[$key] = $row; } $result = $hashedResult; } debug::stopMessung("getSQLResultAsArray"); return $result; } /** * Performs a SELECT query and returns the result as a Hashtable * * The values of the first column will bey used as Key of the Hashtable * If the COLUMN-Count is 2, the value of second Column will be used as value, this is the intended way to use this method * If the COLUMN-Count is 1, the value will always be the same as the key * If the COLUMN-Count is greater than 2 the value will be the whole record (as a hashtable) * * @param string $sql, the SQL-Query to perform, typically "SELECT id, name FROM users"; * * @return array the result as hashtable */ function getSQLResultAsHashtable($sql) { debug::startMessung("getSQLResultAsHashtable"); $colCount = 0; $result = array(); $numericArray = $this->select($sql, PDO::FETCH_NUM); if (count($numericArray > 0)) $colCount = count($numericArray[0]); foreach ($numericArray as $row) { $key = $row[0]; if ($colCount == 2) $value = $row[1]; if ($colCount > 2) $value = $row; if ($colCount == 1) $value = $key; $result[$key] = $value; } debug::stopMessung("getSQLResultAsHashtable"); return $result; } /** * Performs a SELECT query and returns values found in the firs Column as numeric array * * @param string $sql, the SQL-Query to perform, typically "SELECT name FROM users"; * * @return array, the first-col values */ function getSQLResultFirstCol($sql) { debug::startMessung("getSQLResultFirstCol"); $result = array(); $numericArray = $this->select($sql, PDO::FETCH_NUM); foreach ($numericArray as $row) { $result[] = $row[0]; } debug::stopMessung("getSQLResultFirstCol"); return $result; } /** * Performs a SELECT query and returns the found (first) record as hashtable * The query itself should be designed so that its result will countain only one record * * @param string $sql, the SQL-Query to perform, typically "SELECT * FROM users WHERE id='12'"; * @param boolean $allowMultipleMatch, - OPTIONAL - if the method schould ignore if there are more than one record in the querys result * - true => if a query brings more than one record, the first one is returned * - false => if a query brings more than one record, boolean false is returned * The intention is to indicate possible inconsitency of the database * This behavior is default * * @return array, the record as hashtable */ function getSQLResultFirstRow($sql, $allowMultipleMatch = false) { debug::startMessung("getSQLResultFirstRow"); $assocArray = $this->select($sql, PDO::FETCH_ASSOC); debug::stopMessung("getSQLResultFirstRow"); if (count($assocArray) == 1) return $assocArray[0]; if (count($assocArray) > 1 && $allowMultipleMatch) $assocArray[0]; return false; } /** * Performs a SELECT query and returns the first cell of found (first) record. * * @param string $sql, the SQL-Query to perform, typically "SELECT name FROM users WHERE id='12'"; * @param boolean $allowMultipleMatch, - OPTIONAL - if the method schould ignore if there are more than one record in the querys result * - true => if a query brings more than one record, the first cell of the first record is returned * - false => if a query brings more than one record, boolean false is returned * The intention is to indicate possible inconsitency of the database * This behavior is default * * @return string, the found value * * */ function getSQLResultFirstCell($sql, $allowMultipleMatch = false) { debug::startMessung("getSQLResultFirstCell"); $numericArray = $this->select($sql, PDO::FETCH_NUM); debug::stopMessung("getSQLResultFirstCell"); if (count($numericArray) == 1) return $numericArray[0][0]; if (count($numericArray) > 1 && $allowMultipleMatch) $numericArray[0][0]; return false; } // COUNT / DISTINCT SQL's // =================================== /** * Counts all records of a table that fulfill the given condition * * @param string $table, the table to query * @param string $cond - OPTIONAL - the condition the records to count must fulfill * - default: no condition, all records of the table will be counted * * countRecords($table, $cond) * is a shortcut for * getSQLResultFirstCell("SELECT COUNT(*) FROM $table WHERE $cond"); * * @return int the number of records found */ function countRecords($table, $cond = "") { $sql = "SELECT COUNT(*) FROM $table"; if ($cond != "") $sql.=" WHERE $cond"; $count = $this->getSQLResultFirstCell($sql); if (! $count === false) $count = (int) $count; return $count; } /** * Returns all different values found in a column of a table * Optionally a condition that the records must fulfill can be given * * @param string $table, the table to query * @param string $column, the column whose distinct values interest * @param string $cond - OPTIONAL - the condition the records to count must fulfill * * getDistinctValues($table, $column, $cond) * is a shortcut for * getSQLResultFirstCol("SELECT DISTINCT($column) FROM $table WHERE $cond"); */ function getDistinctValues($table, $column, $cond = "") { $sql = "SELECT DISTINCT($column) FROM $table"; if ($cond != "") $sql.=" WHERE $cond"; return $this->getSQLResultFirstCol($sql); } /** * Counts all different values found in a column of a table * Optionally a condition that the records must fulfill can be given * * @param string $table, the table to query * @param string $column, the column whose distinct values interest * @param string $cond - OPTIONAL - the condition the records to count must fulfill * * countDistinctValues($table, $column, $cond) * is a shortcut for * getSQLResultFirstCell("SELECT COUNT(DISTINCT($column)) FROM $table WHERE $cond"); */ function countDistinctValues($table, $column, $cond = "") { $sql = "SELECT COUNT(DISTINCT($column)) FROM $table"; if ($cond != "") $sql.=" WHERE $cond"; $count = $this->getSQLResultFirstCell($sql); if (! $count === false) $count = (int) $count; return $count; } // INSERT / UPDATE / DELETE // =================================== /** * Inserts values, given as hashtable into the given table * The keys of the hashtable MUST represten the columnnames of the table * Keys that do not exist as column in the table will lead to an SQL-Error! * The values will be escaped according to the used databse system (actully the known DBMS ar MySQL and SQLite * Values given in the third argument (also a hashtable) will not be escaped * * @param string $tablename, the name of the table to insert the values into * @param array $valueArray, the values to inser as hashtable (associative array) * @param array $functions, - OPTIONAL - the unescaped values to insert as hashtable * e.g. $functions = array('pw' => "PASSWORD('secret')"); * if this would be in the $valueArray argument, "PASSWORD('secret')" would be inserted what hardly will be your intention * * @return int the inserted id if an auto_increment column is in the table * int the number of inserted rows if no auto_increment col available * boolean false on error */ function insertValues($tablename, $valueArray, $functions = array()) { debug::startMessung("insertValues"); $clist = ""; $vlist = ""; if (is_array($valueArray)) { foreach ($valueArray as $col => $value) { $functions[$col] = "'".$this->escapeForDbQuery($value)."'"; if (is_null($value)) $functions[$col] = "NULL"; } } foreach ($functions as $col => $value) { $clist.= "$col, "; $vlist.= "$value, "; } $clist = substr($clist, 0, -2); $vlist = substr($vlist, 0, -2); $sql = "INSERT INTO $tablename ($clist) VALUES ($vlist)"; debug::stopMessung("insertValues"); return $this->insert($sql, $tablename); } /** * Updates values, given as hashtable in the given table in every record that fulfills the given condition * The keys of the hashtable MUST represten the columnnames of the table * Keys that do not exist as column in the table will lead to an SQL-Error! * The values will be escaped according to the used databse system (actully the known DBMS ar MySQL and SQLite * Values given in the forth argument (also a hashtable) will not be escaped * * @param string $tablename, the name of the table to updete the values in * @param string $condition, coundtion the records must fulfill to be updated (e.g. "id=12") * @param array $valueArray, new values as hashtable (associative array) * @param array $functions, - OPTIONAL - the unescaped new values as hashtable * e.g. $functions = array('pw' => "PASSWORD('secret')"); * if this would be in the $valueArray argument, the "pw" field would be updated to "PASSWORD('secret')" what hardly will be your intention * * @return int the number of updated records * boolean false on error * * NOTES: * - use "===" to check for failures, integer 0 would mean: no SQL-Error, but no record found * that fulfills the given condition * - if the condition is empty nothing will be done to avoid that you update a whole * table to the same values by a mistake. If you want to do so give something like * "true" or "1 = 1" as condition */ function updateValues($tablename, $condition, $valueArray, $functions=array()) { debug::startMessung("updateValues"); $list = ""; if (is_array($condition)) { foreach ($condition as $col => $value) $c[] = "$col='".$this->escapeForDbQuery($value)."'"; $condition = implode(" AND ", $c); } debug::startMessung($tablename." WHERE ".$condition); if (is_array($valueArray)) { foreach ($valueArray as $col => $value) { $functions[$col] = "'".$this->escapeForDbQuery($value)."'"; if (is_null($value)) $functions[$col] = "NULL"; } } foreach ($functions as $col => $value) $list.= "$col=$value, "; $list = substr($list, 0, -2); $retVal = false; if ($condition != "") { // Damit nicht die ganze Tabelle verholzt werden kann!! $sql = "UPDATE $tablename SET $list WHERE $condition"; $retVal = $this->execute($sql); } debug::stopMessung($tablename." WHERE ".$condition); debug::stopMessung("updateValues"); return $retVal; } /** * Escapes values for the update- and insert querys * Normally "addslashes", in case of an SQLite DBMS "sqlite_escape_string" is used * * @param string $value, the unescaped value * @return string the escaped value */ private function escapeForDbQuery($value) { $escFunction = $this->escapingFunction; $serverType = substr($this->dsn, 0, strpos($this->dsn, ":")); if (isset($this->specEscFunction[$serverType])) $escFunction = $this->specEscFunction[$serverType]; return $escFunction($value); } /** * Deletes all records from a table that fulfill the given condition * * @param string $tablename, the name of the table to delete records from * @param string $condition, coundtion the records must fulfill to be deleted (e.g. "id=12") * * @return int the number of updated records * boolean false on error * * NOTES: * - use "===" to check for failures, integer 0 would mean: no SQL-Error, but no record found * that fulfills the given condition, and so nothing deleted * - if the condition is empty nothing will be done to avoid that you update a whole * table to the same values by a mistake. If you want to do so give something like * "true" or "1 = 1" as condition */ function delete($table, $cond) { if (is_array($cond)) { foreach ($cond as $col => $value) $c[] = "$col='".$this->escapeForDbQuery($value)."'"; $cond = implode(" AND ", $c); } $affectedRows = false; if ($cond != "") $affectedRows = $this->execute("DELETE FROM $table WHERE $cond"); return affectedRows; } /** * Sets if on errors a debug-message should be printed to the standard output * The default is true, set it to false in a productional environment. * * @param boolen $debug, show errors or not. */ function showErrors($debug) { $this->debugMode = $debug; } // INTERNA // =================================== // Führt einen Select SQL aus, gibt das resultat numerisch oder assoziative zurück // für return_mode die PDO::FETCH_xxx Konstanten verwenden /** * The internal function to perform SELECT-Querys * * @param string $sql, the SQL to perform * @return_mode the return mode according to the PDO->fetchAll function * * @return array an array of the results; */ private function select($sql, $return_mode) { $result = false; if (is_object($this->db)) { debug::startMessung("SQL: ".$sql); $result = array(); $statement = $this->db->query($sql); if ($statement === false) { if ($this->handleError($sql) == true) { $statement = $this->db->query($sql); } } if ($statement === false) { $this->showError($sql); } else { $result = $statement->fetchAll($return_mode); } debug::stopMessung("SQL: ".$sql); } return $result; } /** * Internal insrting function * * @param $sql, the SQL-INSERT Statement * * @return int, the newly created id if an auto_increment col is in the table * int, number of inserted rows if no auto_increment col * boolean false on error */ private function insert($sql, $tablename="") { $retVal = $this->db->exec($sql); if ($retVal === false) { if ($this->handleError($sql) == true) { $retVal = $this->db->exec($sql); } } if ($retVal === false) { $this->showError($sql); } else { if ($this->db->lastInsertId() > 0) $retVal = $this->db->lastInsertId(); } return $retVal; } /** * Internal SQL-Execution of UPDATE and DELETE-Statements * * @param $sql, the SQL Statement * * @return int, number of affected rows (updated oder deleted) * boolean false on error */ public function execute($sql) { $affectedRows = $this->db->exec($sql); if ($affectedRows === false) { if ($this->handleError($sql) == true) { $affectedRows = $this->db->exec($sql); } } if ($affectedRows === false) $this->showError($sql); return $affectedRows; } /** * Internal Error handling * Tries to reconnect on a failureCode HY000 * * @return if the SQL-Call should be tried again (i.e. a reconnect has been done) */ private function handleError($sql) { $this->showError($sql); $rawError = $this->db->errorInfo(); $retry = false; if ($rawError[0] == "HY000") { // Dieser Fehlercode bedeutet dass dir Verbindung neu aufgebaut werden muss. // Und zwar ohne persistent! // Problem PDO behaltet die Verbindungen sonst ewig (länger als 8h was mehr als der wait_timeout der DbServer ist) if ($this->debugMode) print("Try to reconnect..."); $this->db = new PDO($this->dsn, $this->user, $this->pw); //$this->db = new PDO($this->dsn, $this->user, $this->pw, array(PDO::ATTR_PERSISTENT => true)); $retry = true; } return $retry; } /** * shows a niocely formatted erroe message if an SQL-Statement fails if debugMode is true (default) * @uses debug::show * @see stdfuncs.inc.php * * @param string $sql, the SQL that failed. */ private function showError($sql) { if ($this->debugMode) { $rawError = $this->db->errorInfo(); $error['code'] = $rawError[0]." / ".$rawError[1]; $error['sql'] = $sql; $error['message'] = $rawError[2]; debug::show($error, "SQL-Fehler"); } } // HILFS-FUNKTION // =================================== /** * Creates from a user entered european date (i.e. "dd.mm.yyyy") an SQL-Date * using wildcards (%) * * e.g. %.%.2007 will get 2007-%-% * 10.%.2007 will get 2007-%-10 * 10.2007 will get 2007-10-% NOTE the leading dot * 10.8 will get 10-08-2007 (in the year 2007 !) * 10.8.2007 gets 2007-08-10 * * @param string $euroDate the in eurpean manner entered date * * @return string the wildcarded SQL-formatted date */ function createWildCardedSQLDate($euroDate) { $euroDate = trim($euroDate); if (substr($euroDate, -1) == "%") { $euroDate = substr($euroDate, 0, -1); } if ($euroDate == "") { $sqlFilterDate = "%"; } else { $ed = explode(".", $euroDate); if (count($ed) == 1) $ed[1] = date("m"); // Fehlenden Monat durach Aktuellen Monat ersetzen if (count($ed) == 2) $ed[2] = date("Y"); // Fehlendes Jahr durch aktuelles Ersetzen if ($ed[0] == "") $ed[0] = "%"; // Leerstring als Wildcard interpretieren if ($ed[1] == "") $ed[1] = "%"; if ($ed[2] == "") $ed[2] = "%"; if (strlen($ed[0]) == 1 && $ed[0] != "%") $ed[0] = "0".$ed[0]; // Falls Tag = [0..9] : zweistellig machen if (strlen($ed[1]) == 1 && $ed[1] != "%") $ed[1] = "0".$ed[1]; // Falls Monat = [0..9] : zweistellig machen if (strlen($ed[2]) == 1 && $ed[2] != "%") $ed[2] = substr(date('Y'), 0, 3).$ed[2]; // Jahr vierstellig machen if (strlen($ed[2]) == 2) $ed[2] = substr(date('Y'), 0, 2).$ed[2]; // Jahr vierstellig machen if (strlen($ed[2]) == 3) $ed[2] = substr(date('Y'), 0, 1).$ed[2]; // Jahr vierstellig machen krsort($ed); $sqlFilterDate = join("-", $ed); } // print("Aus $euroDate => $sqlFilterDate<br>\n"); return $sqlFilterDate; } } ?>
