NYCPHP Meetup

[Daniel Convissor]

Hi Kristina:

On Wed, Apr 02, 2008 at 01:36:34PM -0700, Kristina Anderson wrote:
> If you guys could point me in the direction of some online examples of 
> how people modularize this type of code, I would be very grateful.  

Attached is a radically stripped down version of the database class we 
used on a recent project.  It uses PHP's mysqli extension in 
object-oriented mode.

--Dan

-- 
 T H E   A N A L Y S I S   A N D   S O L U T I O N S   C O M P A N Y
            data intensive web and database programming
                http://www.AnalysisAndSolutions.com/
 4015 7th Ave #4, Brooklyn NY 11232  v: 718-854-0335 f: 718-854-0409
-------------- next part --------------
<?php

/**
 * A class for MySQL DBMS specific methods using PHP's mysqli extension
 */
class db extends mysqli {
    /**
     * Holds mysqli::$info after executing queries
     *
     * Needed because log_warnings() runs another query that overwrites
     * mysqli::$info.
     *
     * @var string
     */
    private $query_info = '';

    /**
     * Warnings are stored here
     * @see db::log_warnings()
     * @var array
     */
    public $warnings = array();

    protected $host = '';
    protected $user = '';
    protected $pass = '';
    protected $db = '';
    protected $port = null;


    /**
     * Sets connection information properties then calls parent's constructor
     */
    public function __construct($host, $user, $pass, $db, $port=null) {
        $this->host = $host;
        $this->user = $user;
        $this->pass = $pass;
        $this->db   = $db;
        $this->port = $port;

        $return = parent::__construct($host, $user, $pass, $db, $port);
        if ($return === false) {
            log_it(LOG_NAME_DB, 'Could not connect to database: ('.
                    mysqli_connect_errno().') '.mysqli_connect_error());
            throw new Exception('Could not connect to database', 5002);
        }

        return $return;
    }

    public function __toString() {
        return get_class($this).' instance -> DB: '.$this->db.
                '   HOST: '.$this->host.'   PORT: '.$this->port;
    }

    public function esc($str) {
        return $this->real_escape_string($str);
    }

    public function log_error($sql, $code, $message, $trace) {
        $body = 'SQL ERROR:  ('.$code.') '.$message."\n".
                '[Connection: '.$this->__toString()."]\n".
                $sql;
        log_it(LOG_NAME_DB, $body);
    }

    public function log_query($sql, $trace) {
        $body  = '[Connection: '.$this->__toString()."]\n".
                $sql;
        log_it(LOG_NAME_DB, $body);
    }

    /**
     * Checks for warnings and handles them as instructed
     *
     * @param string $sql           the SQL string
     * @param bool   $log_warnings  if FALSE, warnings get put into the
     *                              $warnings property instead of being logged
     */
    public function log_warnings($sql, $log_warnings) {
        $this->warnings = array();

        if ($this->warning_count == 0) {
            return;
        }

        $info_tmp = $this->query_info;

        $warnings = $this->run_manipulate('SHOW WARNINGS');

        $error = '';
        foreach ($warnings as $warning) {
            $error .= $warning['Message']."\n";

            if (!$log_warnings) {
                $this->warnings[] = $warning['Message'];
            }
        }

        if ($log_warnings) {
            $body = 'SQL WARNING: '.$error."\n".
                    '[Connection: '.$this->__toString()."]\n".
                    $sql;
            log_it(LOG_NAME_DB, $body);
        }

        $this->query_info = $info_tmp;
    }

    public function matched_rows() {
        if (ereg('^Rows matched: ([0-9]+)', $this->query_info, $regs)) {
            return $regs[1];
        } else {
            return -100;
        }
    }

    /**
     * The method for executing data manipulation queries (INSERT, UPDATE, etc)
     *
     * @param string $sql           the SQL string to execute
     * @param bool   $log_warnings  (TRUE) if FALSE, warnings get put into the
     *                              $warnings property instead of being logged
     * @return void
     * @uses db::log_warnings()
     */
    public function run_manipulate($sql, $log_warnings=true) {
        if (LOG_QUERIES) {
            $this->log_query($sql, debug_backtrace());
        }

        if (!(parent::query($sql))) {
            $this->log_error($sql, $this->errno, $this->error,
                    debug_backtrace());
            throw new Exception('Query Error in run_manipulate()', 500);
        }

        $this->query_info = $this->info;
        $this->log_warnings($sql, $log_warnings);
    }

    /**
     * The method for executing SELECT queries, it returns the results in
     * the format specified by $output_type
     *
     * @param string $sql              the SQL string to execute
     * @param string $output_type      how the output should be formatted
     *                                 [...truncated for public consumption...]
     * @param string $key              column name to use as key for array
     *                                 output types
     * @return void
     */
    public function run_select($sql, $output_type='array', $key='') {
        if (LOG_QUERIES) {
            $this->log_query($sql, debug_backtrace());
        }

        if (!($result = parent::query($sql))) {
            $this->log_error($sql, $this->errno, $this->error,
                    debug_backtrace());
            throw new Exception('Query Error in run_select()', 500);
        }

        /*
         * This is a very simplified version of BUT ONE of the $output_type's.
         * Others are left to your imagination.
         */
        $output = array();
        while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
            if ($key != '') {
                $output[$row[$key]] = $row;
            } else {
                $output[] = $row;
            }
        }

        $result->free();
        return $output;
    }
}