Revision: 34068
Updated Code
at October 17, 2010 03:22 by Cory
Updated Code
<?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;
}
?>
Revision: 34067
Updated Code
at October 16, 2010 11:01 by Cory
Updated Code
<?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)
*/
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;
}
?>
Revision: 34066
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
at October 16, 2010 10:59 by Cory
Initial Code
<?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://github.com/cborrow/BlahFramework
* @package Database
* @license MIT License (http://www.opensource.org/licenses/mit-license.php)
*/
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;
}
?>
Initial URL
Initial Description
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.
Initial Title
MySQL Database Class
Initial Tags
mysql, database, php
Initial Language
PHP