db.php

Go to the documentation of this file.

<?php
  /**
   *  @file db.php
   *  Everything connected with accessing database.
   */

  /*
  Easy PHP Framework

  Copyright (c) 2005 Michal Molhanec

  This software is provided 'as-is', without any express or implied
  warranty. In no event will the authors be held liable for any damages
  arising from the use of this software.

  Permission is granted to anyone to use this software for any purpose,
  including commercial applications, and to alter it and redistribute
  it freely, subject to the following restrictions:

      1. The origin of this software must not be misrepresented;
         you must not claim that you wrote the original software.
         If you use this software in a product, an acknowledgment
         in the product documentation would be appreciated but
         is not required.

      2. Altered source versions must be plainly marked as such,
         and must not be misrepresented as being the original software.

      3. This notice may not be removed or altered from any
         source distribution.
  */
  
  /**
   *  See <a href='http://www.php.net/manual/en/ref.mysqli.php'>mysqli
   *  class documentation</a> for details.
   */
  class Db extends mysqli {

    /**
     *  Establishes database connection.
     *  All data has to be in <a href="http://dev.mysql.com/doc/mysql/en/charset-unicode.html">UTF-8</a>.
     *  @throw Exception If cannot connect.
     */
    function __construct() {
      global $mysql_host, $mysql_username, $mysql_password, $mysql_dbname;
      parent::__construct($mysql_host, $mysql_username, $mysql_password, $mysql_dbname);
      if (mysqli_connect_errno()):
        throw new Exception(sprintf("Can't connect to MySQL Server. Errorcode: %s\n", mysqli_connect_error()));
      endif;
      parent::query("SET NAMES 'utf8'"); // we work in UTF-8
    }

    /**
     *  @throws Exception On error.
     *  @return mysqli_result object.
     */
    function query($sql) {
      $result = parent::query($sql);
      if (!$result):
        throw new Exception(sprintf("Error in query %s. Error message: %s\n", $sql, $this->error));
      endif;
      return $result;
    }

  }

  /**
   *  This object represent result of a SQL query.
   *  You can use this object in foreach() construct.
   *  For details see
   *  <a href='http://www.php.net/~helly/php/ext/spl/interfaceIterator.html'>
   *  Iterator interface documentation</a>.
   */
  class DbQuery implements Iterator {

    private $sql;
    private $result = NULL;
    private $row;

    function __construct($sql) {
      $this->sql = $sql;
    }

    /**
     *  @throw Exception. On SQL error.
     */
    function rewind() {
      if (!is_null($this->result)):
        $this->result->close();
        $this->result = NULL;
      endif;
      $db = new Db();
      $this->result = $db->query($this->sql);
      $this->row = $this->result->fetch_object();
    }

    function current() {
      return $this->row;
    }

    /** Unimplemented. */
    function key() { return 0; }

    function next() {
      $this->row = $this->result->fetch_object();
    }

    function valid() {
      return $this->result and $this->row;
    }
    
  }

  /**
   *  @return DbQuery object.
   */
  function query($sql) {
    $q = new DbQuery($sql);
    return $q;
  }

  /**
   *  Executes INSERT or UPDATE SQL statement.
   *  @throw Exception($errormsg) If the SQL statement violates
   *    primary key or unique index uniqueness.
   *  @throw Exception If the SQL statement fails for some
   *    other reason.
   */
  function insert_unique($sql, $errormsg) {
    $db = new Db();
    try {
      $db->query($sql);
    }
    catch (Exception $e) {
      if ($db->errno == 1062):
        throw new Exception($errormsg);
      else:
        throw $e;
      endif;
    }
    $db->close();
  }

  /**
   *  @param[in] $sql SQL statement in a form of "SELECT COUNT(...) FROM ...".
   *  @return TRUE if statement executed succesfully and it returned
   *    value of 1, FALSE otherwise.
   *  @throw Exception On SQL error.
   */
  function exists($sql) {
    $db = new Db();
    $result = $db->query($sql);

    /*
      This clearly shows how is PHP badly designed language.
      You cannot simply write
      $count = $result->fetch_row()[0];
      You even cannot write
      $count = ($result->fetch_row())[0];
    */
    $count = $result->fetch_row();
    $count = $count[0];
    
    $db->close();
    if ($count == 1):
      return TRUE;
    endif;
    return FALSE;
  }

  /**
   *  Exception class used by one_line_query() function.
   */
  class RecordNotFound extends Exception {}
  
  /**
   *  Performs SQL query that return only one record.
   *  @return Fetched object.
   *  @throw RecordNotFound($errormsg) if the record does not exists.
   *  @throw Exception On SQL error.
   */
  function one_line_query($sql, $errormsg = 'Error.') {
    $db = new Db();
    $result = $db->query($sql);
    $obj = $result->fetch_object();
    if (!$obj):
      throw new RecordNotFound($errormsg);
    endif;
    return $obj;
  }

  /**
   *  For INSERT/UPDATE/DELETE.
   *  @throw Exception On SQL error.
   */
  function update($sql) {
    $db = new Db();
    $db->query($sql);
    $db->close();
  }

  /**
   *  For INSERT with autoincremented PK.
   *  @return New record PK value or 0 if it's not INSERT/UPDATE statement
   *    or the table does not have autoincremented column.
   *  @throw Exception On SQL error.
   */
  function insert($sql) {
    $db = new Db();
    $db->query($sql);
    $id = $db->insert_id;
    $db->close();
    return $id;
  }

  /**
   *  Helper for build_insert_sql_from_array().
   *  @return $obj
   */
  function get_identity_value($obj) {
    return $obj;
  }
  
  /**
   *  Helper for build_insert_sql_from_array().
   *  @return $obj->get_id().
   */
  function get_id_value($obj) {
    return $obj->get_id();
  }
  
  /**
   *  Creates string which you can pass to INSERT SQL command
   *  as VALUES based on an array. This is useful for adding multiple
   *  values in one SQL command.
   *  Example:
   *  @code
   *    $a = array('a', 'b', 'c');
   *    $s = build_insert_sql_from_array($a, '1', '2', TRUE);
   *  @endcode
   *  will generate:
   *  @code
   *    $s == "(1, 'a', 2),(1, 'b', 2),(1, 'c', 2)";
   *  @endcode
   *  and you can use it like this:
   *  @code
   *    db_query("INSERT INTO table VALUES $s");
   *  @endcode
   *  @param[in] $array Array containing e.g. strings or objects etc.
   *  @param[in] $pre String prepended for each value.
   *  @param[in] $post String appended for each value.
   *  @param[in] $quote Should be the value quoted?
   *  @param[in] $value_getter Function which for each element in the array
   *    returns its value. Default is identity.
   *  @return String usable in SQL command.
   */
  function build_insert_sql_from_array($array, $pre = '', $post = '', $quote = FALSE, $value_getter = 'get_identity_value') {
    if ($pre):
      $pre = "$pre,";
    endif;
    if ($post):
      $post = ",$post";
    endif;
    $sqllines = array();
    foreach ($array as $obj):
      $value = $value_getter($obj);
      if ($quote):
        $sqllines[] = "($pre '$value' $post)";
      else:
        $sqllines[] = "($pre $value $post)";
      endif;
    endforeach;
    return join(',', $sqllines);
  }

?>

---