A database lightweight wrapper to PDO. Reduces the complexities of it's object-oriented interface to a simple functional interface more in the spirit of the original PHP mysql library.

README.md

Purpose

The original PHP mysql library was fairly quick to code due to it's simple functional interface. But there were some rough edges. Also it is now deprecated and most developers are moving to PDO since it is more database independent.

Unfortuantly PDO is overly complicated. This is mostly due to PHP's poor object system and the PDO developers desire to make the system object-oriented.

This is a PDO wrapper that brings back the simplicity of the original mysql library by providing a functional interface. It also polishing some rough edges (like allowing easy placeholder binding).

Example Usage

connect('mysql:dbname=mydb', 'user', 'pass');
$users = fetch_all(query("SELECT * FROM users"));
$user = fetch(query("SELECT * FROM users WHERE id = ?", $_GET['id']));
save('users', {
  'first_name' => 'Joe',
  'last_name' => 'Blow',
});

Design Decisions

See the comments on each method for usage. Below are some general guiding principals.

No Namespace or Prefix

This library is not namespaced and doesn't use prefixes. Obviously this is dangerous as you can get conflicts. But the apps this is designed for are so small it is rarely an issue in practice. This might be something that changes in the future. But for now I enjoy the quick easy function names too much and conflicts are not a pain point. Long method names and namespaces would be a pain point.

One Database

Similar to the way the original mysql library had a default database. Currently specifying an alternate database is not supported (you could just swap out the global if you really wanted).

This is obviously not suitable for complex apps but for simply apps it make the code nicer. Multiple database are not currently a pain point. Typing the database I want to use every time is would be a pain point. So for now, just one database.

database.php

<?php
#https://gist.github.com/4693389

# Connect to the database. All future queryies will be executed on
# this connection. Example DSN for mysql is:
#
#     mysql:dbname=myblog
function connect($dsn, $username, $password) {
  global $database;
  try {
    $database = new PDO($dsn, $username, $password, array(
      PDO::ATTR_PERSISTENT => true
    ));
    $database->setAttribute(
      PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC
    );
  } catch (PDOException $e) {
    trigger_error($e->getMessage(), E_USER_ERROR);
  }
}
$database = null;

# Connect to database is environment variables set
if( isset($_ENV['DB_DSN']) && isset($_ENV['DB_USERNAME']) && isset($_ENV['DB_PASSWORD']) )
  connect($_ENV['DB_DSN'], $_ENV['DB_USERNAME'], $_ENV['DB_PASSWORD']);

# Wraps a callback in a transaction so that if any DB error occurs within the
# callback all actions are reverted.
function transaction($callback) {
  global $database;
  try {
    $database->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $database->beginTransaction();
    call_user_func($callback);
    $database->commit();
    $database->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
  } catch (Exception $e) {
    $database->rollBack();
    $database->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
    trigger_error($e->getMessage(), E_USER_ERROR);
  }
}

# Will execute the given $sql statement against the database. Supports
# either positional or named bindings. Examples:
#
#   query('SELECT * FROM widgets WHERE id = ?', $_GET['id']);
#   query('SELECT * FROM widgets WHERE id = :id', array('id' => $_GET['id']))
function query($sql /*, bind args */) {
  global $database;

  $args = func_get_args();
  if( count($args) == 1 ) {
    # No arguments to bind so use easier query interface
    $result = $database->query($sql);
    if(!$result) trigger_error(
      print_r($database->errorInfo(), true)." for SQL $sql",
      E_USER_ERROR
    );
    return $result;
  } else {
    $query = $database->prepare($sql);
    if(!$query) trigger_error(
      print_r($database->errorInfo(), true)." when preparing SQL $sql",
      E_USER_ERROR
    );
    if( is_array($args[1]) ) {
      foreach($args[1] as $name => $value) {
        $type = _pdo_type($value);
        if( !$query->bindValue(":$name", $value, $type) )
          trigger_error(
            print_r($query->errorInfo(), true).
              " when binding $name with $value for SQL $sql",
            E_USER_ERROR
          );
      }
    } else {
      for($i=1; $i<count($args); $i++) {
        $value = $args[$i];
        $type = _pdo_type($args[$i]);
        if( empty($value) && $type == PDO::PARAM_NULL) $value = null;
        if( !$query->bindValue($i, $value, $type) )
          trigger_error(
            print_r($query->errorInfo(), true).
              " when binding placeholder $i with $value for SQL $sql",
            E_USER_ERROR
          );
      }
    }
    $result = $query->execute();
    if($result === false) trigger_error(
      print_r($query->errorInfo(), true)." when executing SQL $sql",
      E_USER_ERROR
    );
    return $query;
  }
}

# Will import a large SQL file. Provides two advantages over simply
# feeding a file directly to `query`:
#
# * Reads from a file in a memory efficient way so if the import is
#   very large we don't execeed memory limits.
# * Breaks up the file into multiple query submissions to avoid
#   packet size limits in MySQL.
#
# Based heavily on: http://stackoverflow.com/a/2011454/120067
#
# This import is not fool proof. It does have a few faults but we accept
# these for the simiplicity. To avoid pitfalls keep individual statments
# from exceeding limits (memory and packet size) and put each statement
# on it's own line.
#
# (You can have multiple statements on the same line but it will
# increase them together raising your chance of hitting limits).
function data_import($filename) {
  $file = fopen($filename, 'r');
  $query = array();
  while( !feof($file) ) {
    $query[] = fgets($file);
    if( preg_match('/;\s*$/S', end($query)) ) {
      query(trim(implode('', $query)));
      $query = array();
    }
  }
  return fclose($file);
}

# Returns a SQL "WHERE" condition based on the given associative
# array. Ideal for search. If the value is "empty" then it is not
# included in the where condition at all (i.e. it does not turn into
# "foo IS NULL"). An array can be given as the second argument so that
# only the fields listed will be processed. This allows you to easily
# use the request associative arrays but not having to worry about
# someone hacking the URL to manipulate the SQL. For example:
#
#     where($_REQUEST, array('district', 'county'))
#
# The result of the above might be:
#
#     district = 493 AND county = 'Paulding'
#
# Note that the values are automatically escaped and quoted if needed.
# They field names are also automatically escaped. If $only is not
# given then all keys are processed.
function where($conditions, $only=null) {
  if( empty($conditions) ) $conditions = array();
  if( !$only ) $only = array_keys($conditions);
  $where = array();
  foreach($only as $field) {
    if( empty($conditions[$field]) ) continue;
    $field = preg_replace('/[^a-zA-Z0-9_\.]+/', '', $field);
    $value = $conditions[$field];
    if( is_array($value) ) {
      $value = implode(',', array_map(function($v) {return db_quote($v);}, $value));
      $where[] = "($field IN ($value))";
    } else {
      $value = db_quote($value);
      $where[] = "($field = $value)";
    }
  }
  # Dummy query so we can just concat with WHERE or AND without
  # having to check if a empty string was returned.
  if( count($where) == 0 ) $where[] = "(1=1)";
  return implode(' AND ', $where);
}

# Will pull the next row as an asscociated array from the object
# returned by the "query" method. By default returns an associated
# array but you can override by providing the appropiate PDO constant.
function fetch($results, $fetch_style=PDO::FETCH_ASSOC) {
  return $results->fetch($fetch_style);
}

# Like fetch but will get all rows an array. Like fetch you can provide
# a $fetch_style using the appropiate PDO constant. Default to an
# associated hash.
function fetch_all($results, $fetch_style=PDO::FETCH_ASSOC) {
  $rows = array();
  while( $row = fetch($results, $fetch_style) )
    $rows[] = $row;
  return $rows;
}

# Returns the value of the first row and column from the result. Useful
# if the SQL is designed to just give us a single value:
#
#   fetch_value(query("SELECT email FROM users WHERE id = ?"), $_GET['id']);
function fetch_value($results, $default=null) {
  $row = fetch($results, PDO::FETCH_NUM);
  if($row)
    return $row[0];
  else
    return $default;
}

# Returns the number of records updated by a query.
function records_updated($results) {return $results->rowCount();}

# Indicates if some sort of update was successful
function update_happened($results) {return records_updated($results) > 0;}

# In general SQL binding is better. But if needed...
# Note that this does NOT include the quotes around the value itself
# but simply escapes the problem characters. In that way it acts like
# mysql_real_escape_string intead of PDO::quote().
function db_escape($val) {
  global $database;
  return substr($database->quote($val, _pdo_type($val)), 1, -1);
}

# Will automatically quote the value if needed. So a string will
# get surrounded by a single quote but an integer or NULL will not.
# The value will be automatically passed through escape()
function db_quote($val) {
  $val = db_escape($val);
  if( _pdo_type($val) == PDO::PARAM_STR ) $val = "'$val'";
  return $val;
}

# Will insert the given data into the given table. Returns the ID of
# the new record. Note this ID aspect is somewhat database dependent
# and we may need to add work-arounds if ported outside of MySQL.
function insert($table, $data) {
  global $database;
  $fields = implode(', ', array_keys($data));
  $placeholders = implode(', ', array_fill(0, count(array_keys($data)), '?'));
  $sql = "INSERT INTO $table ($fields) VALUES ($placeholders)";
  $args = array_values($data);
  array_unshift($args, $sql);
  call_user_func_array('query', $args);
  return $database->lastInsertId();
}

# Will update all records in $table with $data meeting $criteria.
# If $criteria is not given then it uses "id" from $data.
function update($table, $data, $criteria=null) {
  $fields = array();
  foreach($data as $fld => $val)
    $fields[] = "$fld = ?";
  $fields = implode(', ', $fields);
  $args = array_values($data);
  if( !$criteria ) {
    $criteria = "id = ?";
    $args[] = $data['id'];
  }
  $sql = "UPDATE $table SET $fields WHERE $criteria";
  array_unshift($args, $sql);
  $query = call_user_func_array('query', $args);
  return records_updated($query);
}

# Calls either insert or update depending if $data['id'] is set
# If inserting $data['id'] will be set after execution.
function save($table, &$data) {
  if( isset($data['id']) ) {
    update($table, $data);
  } else {
    $id = insert($table, $data);
    $data['id'] = $id;
    return $id;
  }
}

# Return a list of all columns in a table
function columns($table) {
  $sql = "SELECT column_name FROM information_schema.columns WHERE table_name = ?";
  $result = query($sql, $table);
  $columns = array();
  while($row = fetch($result)) {
    $columns[] = $row['column_name'];
  }
  return $columns;
}

# Returns a list of all tables
function tables() {
  $result = query("SELECT table_name FROM information_schema.tables");
  $tables = array();
  while($row = fetch($result)) {
    $tables[] = $row['table_name'];
  }
  return $tables;
}

# Internal function to guess the param type.
function _pdo_type($value) {
  if( is_bool($value) ) {
    return PDO::PARAM_BOOL;
  } elseif( is_numeric($value) ) {
    return PDO::PARAM_INT;
  } elseif( empty($value) ) {
    return PDO::PARAM_NULL;
  } else {
    return PDO::PARAM_STR;
  }
}