Posted By

Cory on 10/16/10

Tagged

Versions (?)

Last Edited at 10/16/10 10:59am

Statistics

Viewed 806 times

Favorited by 3 user(s)

Related snippets

MySQL Database Class

/ Published in: PHP

This is my fairly easy to use MySQL database class, I use this on my Experiment framework BlahFramework, anyways I thought someone else may find this useful or just study the code to get an awesome idea of there own. Enjoy.

Highlighted TextExpand

<?php
/**
* Copyright (c) 2010 Cory Borrow
*
* 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.
*
* Database.php
*
* This file add's a simple and effective way of accessing/querying a MySQL database
* 
* @author Cory Borrow
* @copyright Copyright 2010 Cory Borrow
* @link http://snipplr.com/view/42405/mysql-database-class/
* @package Database
* @license MIT License (http://www.opensource.org/licenses/mit-license.php)
*/

/*
* A quick example
*
* include "Database.php";
*
* $db = new Database("host", "user", "pass", "dbname");
* $db->where("date", time(), WhereOperator::$LessThan)
* 	 ->where("date", time() - 1000, WhereOperator::$MoreThan)
* 	 ->select("posts");
*
* $results = $db->fetchAll();
*/

class Database
{
    /**
    * MySQL database host name
    * @access public
    * @var string
    */
    public $Host;

/**
    * MySQL datbase username
    * @access public
    * @var string
    */
    public $User;

/**
    * MySQL database password
    * @access public
    * @var string
    */
    public $Pass;

/**
    * MySQL database name
    * @access public
    * @var string
    */
    public $Name;

/**
    * Last preformed SQL query string
    * @access public
    * @var string
    */
    public $LastQuery;

/**
    * Last MySQL error
    * $access public
    * @var string
    */
    public $LastError;

/**
    * Where and or-where statements
    * @access protected
    * @var array
    */
    protected $where;

/**
    * Order by statement
    * @access protected
    * @var string
    */
    protected $orderBy;

/**
    * Group by statement
    * @access protected
    * @var string
    */
    protected $groupBy;

/**
    * Limit statement
    * @access protected
    * @var string
    */
    protected $limit;

/**
    * Database connection handle
    * @access protected
    * @var resource
    */
    protected $conn;

/**
    * MySQL result handle
    * @access protected
    * @var resource
    */
    protected $result;

/**
    * Constructor, set's host, username, password, and db name
    */
    public function __construct($host, $user, $pass, $name)
    {
        if(!empty($host))
            $this->Host = $host;
        else
            $this->Host = "localhost";

if(!empty($user) && !is_null($pass) && !empty($name))
        {
            $this->User = $user;
            $this->Pass = $pass;
            $this->Name = $name;
        }
    }

/**
    * Connect's to mysql database
    * @access public
    */
    public function connect()
    {
        if(!$this->conn)
        {
            $this->conn = mysql_connect($this->Host, $this->User, $this->Pass);

if($this->conn)
                mysql_select_db($this->Name, $this->conn);
        }
    }

/**
    * Fetch's first row from MySQL result
    * @return resource|null MySQL Result or null on faliure
    * @access public
    */
    public function fetch()
    {
        if($this->conn && $this->result)
        {
            return mysql_fetch_object($this->result);
        }
        return null;
    }

/**
    * Fetch's all MySQL row's from MySQL result
    * @return array|null MySQL rows or null on faliure
    * @access public
    */
    public function fetchAll()
    {
        if($this->conn && $this->result)
        {
            $rows = array();

while($row = $this->fetch())
                $rows[] = $row;

return $rows;
        }
        return null;
    }

/**
    * Gets the number of rows affected by the last query
    * @return interger Number of rows affected
    * @access public
    */
    public function affectedRows()
    {
        if($this->conn)
        {
            return mysql_affected_rows($this->conn);
        }
    }

/**
    * Gets the number of rows returned by the last query
    * @return integer Number of rows returned
    * @access public
    */
    public function returnedRows()
    {
        if($this->conn && $this->result)
        {
            return mysql_num_rows($this->result);
        }
    }

/**
    * Add's a where statement to the sql query
    * @param string $key The field key to match against
    * @param mixed $value The value to match against the database field
    * @param interger $op A WhereOperator value of the type of match
    * @return Instance of Database class
    * @access public
    */
    public function where($key, $value, $op)
    {
        if($this->conn)
        {
            $value = mysql_real_escape_string($value, $this->conn);
        }

switch($op)
        {
        case WhereOperator::$Equal:
            $this->where[] = "{$key} = '{$value}'";
            break;
        case WhereOperator::$NotEqual:
            $this->where[] = "{$key} != '{$value}'";
            break;
        case WhereOperator::$LessThan:
            $this->where[] = "{$key} < '{$value}'";
            break;
        case WhereOperator::$MoreThan:
            $this->where[] = "{$key} > '{$value}'";
            break;
        case WhereOperator::$LessThanOrEqual:
            $this->where[] = "{$key} <= '{$value}'";
            break;
        case WhereOperator::$MoreThanOrEqual:
            $this->where[] = "{$key} >= '{$value}'";
            break;
        case WhereOperator::$Like:
            $this->where[] = "{$key} LIKE '{$value}'";
            break;
        case WhereOperator::$NotLike:
            $this->where[] = "{$key} NOT LIKE {$value}";
            break;
        case WhereOperator::$In:
            $this->where[] = "{$key} IN {$value}";
            break;
        case WhereOperator::$NotIn:
            $this->where[] = "{$key} NOT IN {$value}";
            break;
        case WhereOperator::$IsNull:
            $this->where[] = "{$key} IS NULL {$value}";
            break;
        case WhereOperator::$IsNotNull:
            $this->where[] = "{$key} IS NOT NULL {$value}";
            break;
        case WhereOperator::$Contains:
            $this->where[] = "{$key} CONTAINS {$value}";
            break;
        case WhereOperator::$NotContains:
            $this->where[] = "{$key} NOT CONTAINS {$value}";
            break;
        case WhereOperator::$Between:
            $this->where[] = "{$key} BETWEEN {$value}";
            break;
        case WhereOperator::$NotBetween:
            $this->where[] = "{$key} NOT BETWEEN {$value}";
            break;
        case WhereOperator::$BeginsWith:
            $this->where[] = "{$key} BEGINS WITH {$value}";
            break;
        case WhereOperator::$NotBeginsWith:
            $this->where[] = "{$key} NOT BEGINS WITH {$value}";
            break;
        }
        return $this;
    }

/**
    * Add's an or-where statement to the sql query
    * @param string $key The field key to match against
    * @param mixed $value The value to match against the database field
    * @param integer $op A WhereOperator value of the type of match
    * @return Instance of Database class
    * @access public
    */
    public function orWhere($key, $value, $op = 0)
    {
        return $this->where("OR {$key}", $value, $op);
    }

/**
    * Add's an order by statement to the sql query
    * @param string $key The mysql field key to order by
    * @param boolean $desc A boolean value to order the results in descending order
    * @return Instance of Database class
    * @access public
    */
    public function orderBy($key, $desc)
    {
        $this->orderBy = "ORDER BY {$key}";
        $this->orderBy .= ($desc) ? " DESC " : " ASC ";
        return $this;
    }

/**
    * Add's a group by statement to the sql query
    * @param string $key The mysql field key to group by
    * @return Instance of Database class
    * @access public
    */
    public function groupBy($key)
    {
        $this->groupBy = "GROUP BY {$key} ";
        return $this;
    }

/**
    * Add's a limit statement to the sql query
    * @param integer $start The number of rows to limit or the row to start from
    * @param integer $length The number of rows to return [optional]
    * @return Instance of Database class
    * @access public
    */
    public function limit($start, $length = 0)
    {
        $this->limit = "LIMIT {$start}";
        $this->limit .= ($length == 0) ? ", {$length} " : " ";
        return $this;
    }

/**
    * Builds a SQL select statement and preforms query
    * @param string $table The table to preform a query against
    * @param array $keys The field keys to return [optional]
    * @access public
    */
    public function select($table, $keys = array())
    {
        if($this->conn)
        {
            if(is_array($keys) && count($keys) > 0)
                $keys = implode(", ", $keys);
            else
                $keys = "*";

$sql = "";
            $sql .= "SELECT {$keys} FROM {$table} ";
            $sql = $this->appendWhere($sql);

$sql .= $this->groupBy;
            $sql .= $this->orderBy;
            $sql .= $this->limit;

$this->query($sql);
        }
    }

/**
    * Builds a SQL update statement and preforms query
    * @param string $table The table to preform a query against
    * @param array $values The field key's and there $values to update
    * @access public
    */
    public function update($table, array $values)
    {
        if($this->conn)
        {
            $sql = "";
            $sql .= "UPDATE {$table} SET ";
            $counter = 0;
            $values = $this->cleanArray($values);

foreach($values as $key => $value)
            {
                if($counter < count($values) - 1)
                    $sql .= "{$key} = '{$value}', ";
                else
                    $sql .= "{$key} = '{$value}' ";
                $counter++;
            }

$sql = $this->appendWhere($sql);
            $this->query($sql);
        }
    }

/**
    * Builds a SQL insert statement and preforms query
    * @param string $table The table to preform a query against
    * @param array $values An array of key's and value's to insert into the table.
    * @access public
    */
    public function insert($table, array $values)
    {
        if($this->conn)
        {
            $keys = array_keys($values);
            $keysStr = implode(", ", $keys);
            $valuesStr = implode("', '", $this->cleanArray($values));

$sql = "";
            $sql .= "INSERT INTO {$table} ";
            $sql .= "({$keysStr}) VALUES ('{$valuesStr}')";
            $this->query($sql);
        }
    }

/**
    * Builds a SQL delete statement and preforms query
    * @param string $table The table to preform a query against
    * @access public
    */
    public function delete($table)
    {
        if($this->conn)
        {
            $sql = "";
            $sql .= "DELETE FROM {$table} ";
            $sql = $this->appendWhere($sql);
            $this->query($sql);
        }
    }

/**
    * Gets the last id inserted
    * @return interger
    * @access public
    */
    public function lastInsertId()
    {
        if($this->conn)
        {
            return mysql_insert_id($this->conn);
        }
    }

/**
    * Preforms a SQL query
    * @param string $sql A sql string to query against a database with
    * @access public
    */
    public function query($sql)
    {
        if($this->conn)
        {
            $this->LastQuery = $sql;
            $this->result = mysql_query($sql, $this->conn);
            $this->LastError = mysql_error($this->conn);
            $this->where = array();
            $this->orderBy = "";
            $this->groupBy = "";
            $this->limit = "";
        }
    }

/**
    * Builds and appends the where statements to a sql query
    * @param string $sql A sql query string
    * @return string The complete query string
    * @access private
    */
    private function appendWhere($sql)
    {
        for($i = 0; $i < count($this->where); $i++)
        {
            if($i == 0)
            {
                $sql .= "WHERE {$this->where[$i]} ";
            }
            else
            {
                if(substr($this->where[$i], 0, 2) == "OR")
                    $sql .= "{$this->where[$i]} ";
                else
                    $sql .= "AND {$this->where[$i]} ";
            }
        }
        return $sql;
    }

/**
    * Preforms mysql_real_escape_string on all strings in an array
    * @param array $arr An array to clean
    * @return array The cleaned array.
    * @access private
    */
    private function cleanArray(array $arr)
    {
        foreach($arr as $item)
        {
			if(is_string($item))
				$item = mysql_real_escape_string($item);
        }
        return $arr;
    }
}

class WhereOperator
{
    public static $Equal = 0;
    public static $Like = 1;
    public static $LessThan = 2;
    public static $MoreThan = 4;
    public static $LessThanOrEqual = 8;
    public static $MoreThanOrEqual = 10;
    public static $NotEqual = 20;
    public static $In = 40;
    public static $NotIn = 80;
    public static $Contains = 100;
    public static $NotContains = 200;
    public static $Between = 400;
    public static $NotBetween = 800;
    public static $BeginsWith = 1000;
    public static $NotBeginsWith = 2000;
    public static $NotLike = 4000;
    public static $IsNull = 8000;
    public static $IsNotNull = 10000;
}
?>

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.