<?php
/*
* Database.class.php -- Version 01-Feb-2006
*
* Copyright (c) 2003-2006 Jochen Kupperschmidt
*
* Permission is hereby granted, free of charge, to any person obtaining a
* copy of this software and associated documentation files (the "Software"),
* to deal in the Software without restriction, including without limitation
* the rights to use, copy, modify, merge, publish, distribute, sublicense,
* and/or sell copies of the Software, and to permit persons to whom the
* Software is furnished to do so, subject to the following conditions:
*
* The above copyright notice and this permission notice shall be included in
* all copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
* IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
* AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
* FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
* DEALINGS IN THE SOFTWARE.
*
* The above license is the MIT License. It was copied from the website of the
* Open Source Initiative: http://www.opensource.org/licenses/mit-license.php
* _ _
* | |_ ___ _____ ___ _ _ _ ___ ___| |_
* | | . | | ._| | | | . | _| . /
* |_|_|___|_|_|_|___|_____|___|_| |_|_\
* http://homework.nwsnet.de/
*
* This is a database wrapper that provides a layer that is independent of the
* DBMS (e.g. MySQL, PostgreSQL) you actually use. It provides a set of common
* methods to retrieve results as single value, (associative) arrays and
* two-dimensional arrays for much simpler code.
*
* In addition, you can use '?' placeholders to write cleaner query strings.
* The actual values are appended to the function by appending an array
* containing the required variables.
*
* Example usage:
*
* # Create database object.
* $db = new Database('myHost', 'myUser', 'myPassword', 'myDatabase');
*
* # Fetch a single value.
* $rowCount = $db->getOne('
* SELECT COUNT(*)
* FROM `some_table`;
* ');
* printf('Row count: %s', $rowCount);
*
* # Fetch a single row using a single placeholder.
* $id = 123;
* $row = $db->getRow('
* SELECT firstname, lastname, age
* FROM `persons`
* WHERE id = ?;
* ', array($id));
* print_r($row);
*
* # Fetch multiple rows using multiple placeholders.
* $firstname = 'Joe';
* $minimumAge = 30;
* $rows = $db->getRows('
* SELECT firstname, lastname, age
* FROM `persons`
* WHERE firstname = ? AND age > ?;
* ', array($firstname, $minimumAge));
* print_r($rows);
*
* # Insert a row using placeholders with auto-escaping.
* $db->query('
* INSERT INTO `persons`
* (firstname, lastname, age)
* VALUES (?, ?, ?);
* ', array($firstname, $lastname, $age));
*
* # Insert a row using the SQL-free method with an associative array.
* $db->insert('persons', array(
* 'firstname' => $firstname,
* 'lastname' => $lastname,
* 'age' => $age
* ));
*
* # Updating/deleting:
* # If the WHERE clause would only contain the ID, you can pass just it.
* $id = 123;
*
* # Update some rows.
* $db->update('persons', 'age = age + 1', $id);
* $db->update('persons', 'firstname = ?, lastname = ?', $id,
* array('John', 'Doe'));
*
* # Delete some rows.
* $db->delete('persons', 'age < 18');
* $db->delete('persons', $id);
*/
class Database {
# Constructor. Create an instance of this class with this.
# You would generally need just one.
function Database($hostname, $username, $password, $database, $debug=False) {
$this->debug = $debug;
$this->linkID = 0;
$this->queryID = 0;
$this->queryCount = 0;
$this->errNo = 0;
$this->errStr = '';
if (! ($hostname and $username and $database)) {
$this->halt('Missing parameters.');
}
$this->linkID = mysql_connect($hostname, $username, $password);
if (! $this->linkID) {
$this->halt('linkID == False. Connect failed.');
}
if (! mysql_select_db($database, $this->linkID)) {
$this->halt("Cannot use database '$database'.");
}
}
# Halt because an error occured.
function halt($msg) {
trigger_error(
sprintf("Database error %d: %s<br />%s\n",
$this->errNo, $this->errStr, $msg),
E_USER_ERROR);
}
# ---------------------------------------------------------------- #
# Escape ' and " to prevent SQL injection.
function quote($str) {
# React corresponding to en-/disabled magic_quotes_gpc.
if (get_magic_quotes_gpc()) {
$str = stripslashes($str);
}
$str = mysql_real_escape_string($str);
# Quote string if not an integer.
if (! is_numeric($str) or (intval($str) != $str)) {
$str = "'$str'";
}
return $str;
}
# Process ? placeholders in the query string.
function buildQuery($sql, $params=array()) {
# Transform ? placeholders to %s for use with sprintf().
$sql_raw = strtr($sql, array('%' => '%%', '?' => '%s'));
# Build and execute sprintf code.
$toeval = '$sql = sprintf($sql_raw';
for ($i = 0; $i < count($params); $i++) {
$toeval .= ', $this->quote($params[' . $i . '])';
}
$toeval .= ');';
eval($toeval);
return $sql;
}
# Execute a query and return the result (if available).
function query($sql, $params=array()) {
$sql = chop($sql);
if ($params) {
$sql = $this->buildQuery($sql, $params);
}
$this->queryCount++;
if ($this->debug) {
printf("SQL query #%d: %s<br />\n", $this->queryCount, $sql);
}
$this->queryID = mysql_query($sql, $this->linkID);
$this->errNo = mysql_errno();
$this->errStr = mysql_error();
if (! $this->queryID) {
$this->halt('Invalid SQL: ' . $sql);
}
return $this->queryID;
}
# ---------------------------------------------------------------- #
# Fetch and return the value of the first column of the first row.
function getOne($sql, $params=array()) {
$result = $this->query($sql, $params);
$row = mysql_fetch_array($result);
return $row[0];
}
# Fetch a single column and return it as array.
function getCol($sql, $params=array()) {
$fields = array();
for (
$result = $this->query($sql, $params);
$row = mysql_fetch_array($result);
$fields[] = $row[0]);
return $fields;
}
# Fetch the first row and return an associative array using the column
# names as keys and the row's fields as their values.
function getRow($sql, $params=array()) {
$result = $this->query($sql, $params);
return mysql_fetch_assoc($result);
}
# Fetch multiple rows and return an array of associative arrays that use
# the column names as keys and the row's fields as their values.
function getRows($sql, $params=array()) {
$rows = array();
for (
$result = $this->query($sql, $params);
$row = mysql_fetch_assoc($result);
$rows[] = $row);
return $rows;
}
# ---------------------------------------------------------------- #
# Insert a row into a table.
function insert($table, $values) {
$this->query(
sprintf(
'INSERT INTO `%s` (%s) VALUES (%s);',
$table,
join(', ', array_keys($values)),
join(', ', array_fill(0, count($values), '?'))
),
array_values($values));
return mysql_insert_id();
}
# Update one or more rows in a table.
# If $where is an integer, the row with that ID will be updated.
function update($table, $set, $where, $params=array()) {
if (ctype_digit((string) $where)) {
$where = 'id = ' . $where;
}
$this->query(
sprintf('UPDATE `%s` SET %s WHERE %s;', $table, $set, $where),
$params);
}
# Delete one or more rows in a table.
# If $where is an integer, the row with that ID will be deleted.
function delete($table, $where, $params=array()) {
if (ctype_digit((string) $where)) {
$where = 'id = ' . $where;
}
$this->query(
sprintf('DELETE FROM `%s` WHERE %s;', $table, $where),
$params);
}
# ---------------------------------------------------------------- #
# Return the number of fields in the most recently retrieved result.
function numCols() {
return mysql_num_fields($this->queryID);
}
# Return the number of rows in the most recently retrieved result.
function numRows() {
return mysql_num_rows($this->queryID);
}
# Return the number of affected rows in the most recently executed query.
function affectedRows() {
return mysql_affected_rows($this->queryID);
}
# Return the number of queries executed so far.
function getQueryCount() {
return $this->queryCount;
}
}
?>