<?php
/**
* SimpleSQL - The Simplest Way to Query
* Copyright (c) 2004 Paul Williamson <
[email protected]>
*
* This class is ment to shortcut common MySQL database access tasks.
* FOR PHP 5 ONLY! This class will not work on any versons < PHP5!
* Also, This class was designed for users who want to use the new
* PHP5 Improved MySQL Extension (mysqli) but do not have MySQL > 4.1.
* Mysqli allows you to access the functionality provided by MySQL > 4.1
* Later on in the future, I will add a layer to this class that allows
* mysqli functionality.
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
* as published by the Free Software Foundation; either version 2
* of the License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* @category Database
* @package SimpleSQL
* @author Paul Williamson <
[email protected]>
* @readme ./docs/README.txt
* @example ./docs/EXAMPLES.txt
* @standards ./docs/CODING_STANDARDS.txt
* @version $Id: simplesql.class.php5,v 2.0 2004/08/08 19:37:45 paul Exp $
*/
define ('FETCH_ASSOC', 0);
define ('FETCH_NUM', 1);
define ('FETCH_OBJECT', 2);
class simplesql
{
/**
* Constants;
*
* VERSION; SimpleSQL Version @const string
* LOG_PATH; Path to logs @const string
* MAX_SIZE; Max size of log file @const int
* DATE_FORMAT; Date Format for Log @const string
* @access global
*/
const VERSION = 'SimpleSQL v2.0.1 PHP5';
const LOG_PATH = '.';
const MAX_SIZE = 2000000;
const DATE_FORMAT = 'F jS, Y, H:i:s T';
/**
* Database Variables;
*
* You may hardcode these variables and not have to worry
* about providing any arguments when you create a new class
*
* $fetch_default; How the result is returned @var int
*
* $host; Database Server @var string
* $user; Database Username @var string
* $pass; Database Password @var string
* $dbname; Database name @var string
* $socket; Database Socket @var string
* $port; Database Port @var int
*
* $resultrows; Number of rows from query @var int
* $table; Last table used in query @var string
*
* $connection; Connection link identifier @var resource
* $preg_sql_noquote; Regular Expression
* pattern for fix_sql() @var string
* $preg_sql_quote; Regular Expression
* pattern for fix_sql() @var string
* $preg_lst_noquote; Regular Expression
* pattern for fix_list() @var string
* $preg_lst_quote; Regular Expression
* pattern for fix_list() @var string
* Be careful with backrefs
* @access mixed
*/
public $fetch_default = FETCH_OBJECT;
protected $host = 'localhost';
protected $user = 'root';
protected $pass = '';
protected $dbname = 'database';
protected $socket;
protected $port = 3306;
protected $resultrows = 0;
protected $table = '';
private $connection = false;
private $preg_sql_noquote = '/^([a-zA-Z0-9\._]+ *= *[^, ]+)((, )|$)(.*)/i';
private $preg_sql_quote = '/^([a-zA-Z0-9\._]+( *= *[\'"`].*?[\'"`])?)((, )|$)(.*)/i';
private $preg_lst_noquote = '/^([a-zA-Z0-9\._]*) *(,|$) *(.*)$/is';
private $preg_lst_quote = '/^{quote}(.*?){quote} *(,|$) *(.*)$/is';
/**
* Common Variables;
*
* $tracklog; Put every event in array @var bool
* $showlog; Print log array to screen (debug) @var bool
* $logtofile; Log tracks to a data file @var bool
* $logfulltofile; Entire log array is @var bool
* serilized into file
* @access protected
*/
protected $common = array(
'tracklog' => true,
'showlog' => false,
'logtofile' => true,
'logfulltofile' => true
);
/**
* Debug Variables;
* $session; Session ID for class @var string
* $birth; Time the class began @var float
* $querytime; Time spent executing queries @var float
* $querycount; Number of executed queries @var int
* $log; Log of actions & errors @var array
* $log; Log of just errors @var array
* $result; Result from query @var resource
*/
protected $session;
protected $birth = 0;
protected $querytime = 0;
protected $querycount = 0;
protected $log = array();
protected $error_log = array();
protected $result = array();
/**
* Constructor; sets up connection varables and connects
*
* @param string $db_host Server
* @param string $db_user Username
* @param string $db_pass Password
* @param string $db_name Database Name
* @param int $db_port Database Port
* @param string $db_socket Database Socket
* @author Paul Williamson <
[email protected]>
* @access private
* @return void
*/
function __construct($db_host = NULL, $db_user = NULL,
$db_pass = NULL, $db_name = NULL,
$db_port = 3306, $db_socket = '')
{
$this->birth = $this->timenow();
$this->session = md5(round($this->birth, 3)*1000);
$this->log=array(
'session' => $this->session,
'date' => date("F jS, Y, H:i:s T"),
'birth' => $this->birth,
'age' => $this->age(),
'mysql' => array()
);
$this->set_param($db_host, 'host');
$this->set_param($db_user, 'user');
$this->set_param($db_pass, 'pass');
$this->set_param($db_name, 'dbname');
$this->set_param($db_port, 'port');
$this->set_param($db_socket, 'socket');
try {
$this->connect();
} catch (Exception $e) {
$this->track('error', '__construct', self::VERSION
. " session Id: {$this->log['session']}"
. ' could not establish a connection'
. ' to server ' . $this->host . ' and'
. ' therefore could not be created.'
. ' View error log for more details.',
$e
);
trigger_error(self::VERSION
. " session Id: {$this->log['session']} could not"
. ' be created! The class will now terminate. Exception:'
. ' ' . $e->getmessage(),
E_USER_ERROR
);
}
$this->track('event', '__construct', self::VERSION
. " session Id: {$this->log['session']} has been"
. " constructed @ {$this->log['date']}"
);
}
/**
* Destruct; destroys connection and reports logs
*
* @param boolean $re Reconnect
* @author Paul Williamson <
[email protected]>
* @access private
* @return void
*/
function __destruct()
{
$this->kill();
$this->track('event', '__destruct', self::VERSION
. " session Id: {$this->log['session']} has been"
. ' destructed @ ' . date(self::DATE_FORMAT) . '.'
);
$this->log['mysql'] = array_merge($this->log['mysql'],
array(
'querycount' => $this->querycount,
'querytime' => $this->querytime
)
);
$this->log['age'] = $this->age();
if ($this->common['logtofile']) $this->do_log();
if ($this->common['logfulltofile']) $this->do_fulllog();
if ($this->common['showlog']) $this->printlog();
}
/**
* Call; Picks up any undeclaired methods
*
* @param string $m Method called
* @author Paul Williamson <
[email protected]>
* @access private
* @return void
*/
function __call($m)
{
$from = trim($m);
$this->track('error', $from,
'Method does not exists!'
);
}
/**
* Get; Get a variable's value
*
* @param string $var variable
* @author Paul Williamson <
[email protected]>
* @access private
* @return mixed
*/
function __get($var)
{
if (isset($this->common[$var])) {
return $this->common[$var];
} else {
$this->track('error',
'__get',
"Variable {$var} does not exists"
);
return null;
}
}
/**
* Set; Set a variable's value
*
* @param string $var variable
* @param string $val value to be assigned
* @author Paul Williamson <
[email protected]>
* @access private
* @return void
*/
function __set($var, $val)
{
if (isset($this->common[$var])) {
$this->common[$var] = $val;
} else {
$this->track('error',
'__set',
"Variable {$var} does not exists"
);
}
}
/**
* SelectDB; Selects the database
*
* @param string $db Database to select
* @param resouce $conn conection resource
* @author Paul Williamson <
[email protected]>
* @access public
* @return boolean
*/
public function selectdb($db, $conn)
{
if (!@mysql_select_db($db, $conn)) {
$this->track('error', 'connect',
"Could not select database {$this->dbname}.",
mysql_error($this->connection)
);
$this->connection = false;
return false;
}
return true;
}
/**
* Connect; connects to a database
*
* @param boolean $re Reconnect
* @author Paul Williamson <
[email protected]>
* @access public
* @return boolean
*/
public function connect($re = false)
{
$server = "{$this->host}:{$this->port}"
. (!$this->socket ? '' : $this->socket);
$r = $re ? 'Rec' : 'C';
if (!$this->connection || !$re) {
if (($this->connection = @mysql_connect($server, $this->user, $this->pass)) === false) {
$this->track('error', 'connect',
"Could not open a connection to `{$server}`.",
mysql_error()
);
throw new Exception(mysql_error($this->connection));
return false;
}
if (!$this->selectdb($this->dbname, $this->connection)) {
throw new Exception('Could not select database: ' . $this->dbname);
return false;
}
} else {
if (!mysql_ping($this->connection)) {
$this->track('error', 'connect',
"Could not reconnect to `{$server}`.",
mysql_error($this->connection)
);
throw new Exception(mysql_error($this->connection));
return false;
}
}
$this->track('event', 'connect',
"{$r}onnected to `{$server}`."
);
return true;
}
/**
* Query; Sends query to database
*
* @param string $query Query String
* @author Paul Williamson <
[email protected]>
* @access public
* @return resource
*/
final public function query($query)
{
$start = $this->timenow();
$result = @mysql_query($query, $this->connection);
$querytime = $this->timenow()-$start;
$querydeath = $this->age();
if ($result !== false) {
$this->track('event', 'query',
"Query: {$query}", '',
$querydeath
);
} else {
$this->track('error', 'query',
"Query Failed: `{$query}`",
mysql_error($this->connection),
$querydeath
);
throw new Exception(mysql_error($this->connection));
return false;
}
$this->querytime += $querytime;
$this->querycount ++;
$data = array();
if (substr(trim(strtoupper($query)), 0, 6) == 'SELECT') {
$data = mysql_fetch_array(mysql_query("EXPLAIN {$query}",
$this->connection),
MYSQL_ASSOC
);
$this->resultrows = mysql_num_rows($result);
}
$type = '';
$rows = 0;
$key = '';
$possible_keys = '';
$Extra = '';
$intensity = 1;
extract($data);
if ($querytime > 0.05) $intensity++;
if ($querytime > 0.1) $intensity++;
if ($querytime > 1) $intensity++;
if ($type == 'ALL') $intensity++;
if ($type == 'index') $intensity++;
if ($type == 'range') $intensity++;
if ($type == 'ref') $intensity++;
if ($rows >= 200) $intensity++;
if (!empty($possible_keys) && empty($key)) {
$intensity++;
}
if ((strpos($Extra, 'Using filesort') !== false) || (strpos($Extra, 'Using temporary') !== false)) {
$intensity++;
}
$data['query'] = $query;
$data['query_time'] = $querytime;
$data['query_death'] = $querydeath;
$data['query_intensity'] = $intensity;
$data['result_rows'] = $this->resultrows;
$this->log['mysql'][] = $data;
if (isset($Comment)) {
$this->track('error', 'query',
"Query Warning",
$Comment
);
throw new Exception($Comment);
$result = false;
}
return $result;
}
/**
* Fetch_Query; Send a query and fetch the results
*
* @param mixed $query Query that will be sent
* @param int $fetch Controls the result datatype
* @author Paul Williamson <
[email protected]>
* @access public
* @return mixed
*/
public function fetch_query($query, $fetch = NULL)
{
$this->set_param($fetch, 'fetch_default');
$return = false;
try {
if (($result = $this->query($query)) !== false) {
while($row = $this->fetch($result, $fetch)) {
$return[] = $row;
}
$this->result = $return;
}
} catch (Exception $e) {
$this->track('error', 'fetch_query',
$e->getmessage()
);
}
$this->track('event', 'fetch_query',
'Successful Call (Query: `' . $query . '`)'
);
return $return;
}
/**
* Fetch_Col; Fetch (a) Coloumn(s) of data from the database
*
* @param mixed $field Field(s) that will be used
* @param mixed $table Table that will be used
* @param int $fetch Controls the result datatype
* @param string $order field to order by (append DESC if needed)
* @param mixed $limit Limit the result to this number
* @author Paul Williamson <
[email protected]>
* @access public
* @return mixed
*/
public function fetch_col($field = NULL, $table = NULL,
$fetch = NULL, $order = NULL, $limit = NULL)
{
$this->set_param($table, 'table');
$this->set_param($fetch, 'fetch_default');
$sql = array();
$return = false;
if (empty($field)) {
unset($field);
$field = $this->fetch(
$this->query("SHOW COLUMNS FROM {$table}"),
FETCH_NUM
);
$field = $field[0];
}
for ($z = 0; $z < 2; $z++) {
$a = $z ? 'table' : 'field';
$sql[$a] = is_array($$a) ? implode(", ", $$a) : $$a;
}
$sql['order'] = $order;
$sql['limit'] = $limit;
try {
$query = $this->fix_sql($sql);
if (($result = $this->query($query)) !== false) {
while($row = $this->fetch($result, $fetch)) {
$return[] = $row;
}
$this->result = $return;
}
} catch (Exception $e) {
$this->track('error', 'fetch_col',
$e->getmessage()
);
}
$this->track('event', 'fetch_col',
'Successful Call (Query: `' . $query . '`)'
);
return $return;
}
/**
* Fetch_Row; Fetch (a) Row(s) of data from the database
*
* This function is nearly identical to Fetch Column but instead
* this function will fetch all fields in the table instead of
* the ones listed in the arguments. Also this function has all
* optional arguments.
*
* @param mixed $field Field(s) that will be used
* @param mixed $table Table that will be used
* @param int $fetch Controls the result datatype (array or object)
* @param string $order field to order by (append DESC if needed)
* @param mixed $limit Limit the result to this number
* @author Paul Williamson <
[email protected]>
* @access public
* @return mixed
*/
public function fetch_row($field = NULL, $table = NULL,
$fetch = NULL, $order = NULL, $limit = NULL)
{
$this->set_param($table, 'table');
$this->set_param($fetch, 'fetch_default');
$sql = array();
$return = false;
for ($z = 0; $z < 2; $z++) {
$a = $z ? 'table' : 'field';
$sql[$a] = is_array($$a) ? implode(", ", $$a) : $$a;
}
$sql['order'] = $order;
$sql['limit'] = $limit;
try {
$query = $this->fix_sql($sql, 'ALL');
if (($result = $this->query($query)) !== false) {
while($row = $this->fetch($result, $fetch)) {
$return[] = $row;
}
$this->result = $return;
}
} catch (Exception $e) {
$this->track('error', 'fetch_row',
$e->getmessage()
);
}
$this->track('event', 'fetch_row',
'Successful Call (Query: `' . $query . '`)'
);
return $return;
}
/**
* Fetch_Search; Fetch data bassed on a search pattern
*
* This function will pass a regular expression string ($find)
* to the fix_sql private function.
*
* @param string $find The varable that will be found in the DB (regexp)
* @param mixed $field Field(s) that will be used
* @param mixed $table Table that will be used
* @param int $fetch Controls the result datatype (array or object)
* @param string $order field to order by (append DESC if needed)
* @param mixed $limit Limit the result to this number
* @author Paul Williamson <
[email protected]>
* @access public
* @return mixed
*/
public function fetch_search($find, $field = NULL, $table = NULL,
$fetch = NULL, $order = NULL, $limit = NULL)
{
$this->set_param($table, 'table');
$this->set_param($fetch, 'fetch_default');
$sql = array();
$return = false;
if (empty($field)) {
unset($field);
$field = $this->fetch(
$this->query("SHOW COLUMNS FROM {$table}"),
FETCH_NUM
);
$field = $field[0];
}
for ($z = 0; $z < 2; $z++) {
$a = $z ? 'table' : 'field';
$sql[$a] = is_array($$a) ? implode(", ", $$a) : $$a;
}
$sql['search'] = addcslashes($find, '\'');
$sql['order'] = $order;
$sql['limit'] = $limit;
try {
$query = $this->fix_sql($sql, 'ALL');
if (($result = $this->query($query)) !== false) {
while($row = $this->fetch($result, $fetch)) {
$return[] = $row;
}
$this->result = $return;
}
} catch (Exception $e) {
$this->track('error', 'fetch_search',
$e->getmessage()
);
}
$this->track('event', 'fetch_search',
'Successful Call (Query: `' . $query . '`)'
);
return $return;
}
/**
* Insert; Insert data into a table
*
* @param mixed $fields Field(s) that will inserted data
* @param mixed $values Value(s) that will be inserted
* @param string $table Table that will be used
* @author Paul Williamson <
[email protected]>
* @access public
* @return boolean
*/
public function insert($fields, $values, $table = NULL)
{
$this->set_param($table, 'table');
$sql = array();
try {
for ($i = 0; $i < 2; $i++) {
$a = $i ? 'fields' : 'values';
${substr($a, 0, 5)} = !is_array($$a) ?
$this->fix_list($$a) :
$$a;
}
} catch (Exception $e) {
$this->track('error', 'insert',
$e->getmessage()
);
return false;
}
if (count($field) != count($value)) {
$this->track('error', 'insert',
'Fields and Values do not match in array size'
);
return false;
}
unset($fields);
foreach ($field as $f) {
if (!$this->field_exists($f, $table)) {
$this->track('error', 'insert',
"Field: {$f} does not exists in Table: {$table}"
);
return false;
}
$fields .= "{$f}, ";
}
$sql['insert'] = $table;
$sql['field'] = $this->list_trim($fields);
$sql['value'] = $value;
try {
$query = $this->fix_sql($sql);
$this->query($query);
} catch (Exception $e) {
$this->track('error', 'insert',
$e->getmessage()
);
return false;
}
$this->track('event', 'insert',
'Successful Call (Query: `' . $query . '`)'
);
return true;
}
/**
* Delete; Delete data from a table
*
* Call this function with limit argument set to -1
* to truncat the table.
*
* @param mixed $wfield Row that will be deleted
* @param boolean $pass A verification array, set it to true
* if you are sure you do not want to add a limit to a no-where-clause
* DELETE query
* @param string $table Table that will be used
* @param mixed $limit Limit the result to this number
* @author Paul Williamson <
[email protected]>
* @access public
* @return boolean
*/
public function delete($wfield = NULL, $pass = false, $table = NULL, $limit = NULL)
{
$this->set_param($table, 'table');
$sql = array();
if (!$pass && is_null($wfield)) {
$this->track('error', 'delete',
'Function refused to execute query because '
. 'table ' . $table . ' would have been truncated. '
. 'To override this error message, set the second '
. 'argument ($pass) to true.'
);
return false;
}
$sql['delete'] = $table;
$sql['field'] = $wfield;
$sql['limit'] = $limit;
try {
$query = $this->fix_sql($sql);
$this->query($query);
} catch (Exception $e) {
$this->track('error', 'delete',
$e->getmessage()
);
return false;
}
$this->track('event', 'delete',
'Successful Call (Query: `' . $query . '`)'
);
return true;
}
/**
* Update; Update data in a table
*
* @param mixed $fields Field(s) that will be changed
* @param mixed $values Value(s) that will be assigned
* @param string $wfields Where fields clause
* @param string $table Table that will be used
* @param mixed $limit Limit the result to this number
* @author Paul Williamson <
[email protected]>
* @access public
* @return boolean
*/
public function update($fields, $values, $wfields = NULL, $table = NULL, $limit = NULL)
{
$this->set_param($table, 'table');
$sql = array();
try {
for ($i = 0; $i < 2; $i++) {
$a = $i ? 'fields' : 'values';
${substr($a, 0, 5)} = !is_array($$a) ?
$this->fix_list($$a) :
$$a;
}
} catch (Exception $e) {
$this->track('error', 'update',
$e->getmessage()
);
return false;
}
if (count($field) != count($value)) {
$this->track('error', 'update',
'Fields and Values do not match in array size'
);
return false;
}
unset($fields);
$num = count($field);
for ($i = 0; $i < $num; $i++) {
$f = $field[$i];
$v = $value[$i];
if (!$this->field_exists($f, $table)) {
$this->track('error', 'update',
"Field: {$f} does not exists in Table: {$table}"
);
return false;
}
$fields .= "{$f}, ";
$set .= "`{$f}` = '" . addcslashes($v, '\'') . "', ";
}
$sql['update'] = $table;
$sql['field'] = $wfields;
$sql['set'] = $this->list_trim($set);
$sql['limit'] = $limit;
try {
$query = $this->fix_sql($sql);
$this->query($query);
} catch (Exception $e) {
$this->track('error', 'update',
$e->getmessage()
);
return false;
}
$this->track('event', 'update',
'Successful Call (Query: `' . $query . '`)'
);
return true;
}
/**
* Get_LastQuery; return string of the last executed query
*
* @author Paul Williamson <
[email protected]>
* @access public
* @return string
*/
public function get_lastquery()
{
$num = count($this->log['mysql']) - 1;
return $this->log['mysql'][$num]['query'];
}
/**
* Get_Result; return result from last SELECT query
*
* @author Paul Williamson <
[email protected]>
* @access public
* @return mixed
*/
public function get_result()
{
return $this->result;
}
/**
* Get_Rows; return num rows from last SELECT query
*
* @author Paul Williamson <
[email protected]>
* @access public
* @return int
*/
public function get_rows()
{
return $this->resultrows;
}
/**
* Get_Log; return result from last SELECT query
*
* @param string $key If provided, function will return array of
* only the key given. track, mysql, session, etc..
* @author Paul Williamson <
[email protected]>
* @access public
* @return array
*/
public function get_log($key = NULL)
{
return is_null($key) ? $this->log : $this->log[$key];
}
/**
* Get_Error; Return the last error (default) or if the first
* argument set to true then it will return the full error array
*
* @param boolean $full Return the full array (default false)
* @author Paul Williamson <
[email protected]>
* @access public
* @return mixed
*/
public function get_error($full = false)
{
return $full ? $this->error_log : end($this->error_log);
}
/**
* Debug;
*
* Not quite finished with this function. I plan to
* make this function return a nice HTML layed out page
* of all the logs, stored in raw file, and optomization solutions.
*
* @author Paul Williamson <
[email protected]>
* @access public
* @return string
*/
public function debug()
{
//incomplete
}
/**
* Field_Exists; See if field exists in givin table
*
* @param string $field Field to be found
* @param string $table Table that will be searched
* @author Paul Williamson <
[email protected]>
* @access public
* @return boolean
*/
public function field_exists($field, $table = NULL)
{
$this->set_param($table, 'table');
$exists = false;
if (is_array($field) || is_array($table)) {
$this->track('error', 'field_exists',
'Function does not accept arrays'
);
return $exists;
}
try {
$result = mysql_query('SHOW COLUMNS FROM ' . $table, $this->connection);
while($row = $this->fetch($result, FETCH_ASSOC)) {
if (strtolower($row['Field']) == strtolower($field)) {
$exists = true;
break;
}
}
} catch(Exception $e) {
$this->track('error', 'field_exists',
$e->getmessage()
);
return $exists;
}
return $exists;
}
/**
* Track; Build log array
*
* @param string $type Type of entry
* @param string $from Function orgin
* @param string $msg Message
* @param string $error Server error message
* @author Paul Williamson <
[email protected]>
* @access private
* @return void
*/
private function track($type, $from, $msg, $error = '', $age = '')
{
if (!empty($error)) $this->error_log[] = $error;
if (!$this->common['tracklog']) return;
$new_entry = array(
'type' => $type,
'from' => $from,
'msg' => trim($msg),
'age' => empty($age) ? $this->age() : $age
);
$this->log['track'][] = empty($error) ?
$new_entry :
array_merge($new_entry,
array(
'error' => $error
)
);
}
/**
* Fix_Sql; Fix the SQL array and return a queryable string
*
* @param array $sql SQL array
* @param string $type Type of fetch
* @author Paul Williamson <
[email protected]>
* @access protected
* @return string
*
* The long do statment in the fields case simply strips the field
* string into an array and then makes a where clause if needed
*/
final protected function fix_sql($sql, $Extra = NULL)
{
if (!is_array($sql)) {
$this->track(
"error", "fix_sql",
"No array found in `{$sql}`."
);
return false;
}
$sql_type = 'SELECT';
foreach ($sql as $type => $value) {
switch($type) {
case 'delete':
$delete = 'DELETE FROM `' . $value . '`';
$sql_type = 'DELETE';
break; //case 'delete'
case 'insert':
$insert = 'INSERT INTO `' . $value . '` ';
$sql_type = 'INSERT';
break; //case 'insert'
case 'update':
$update = 'UPDATE `' . $value . '`';
$sql_type = 'UPDATE';
break; //case 'insert'
case 'set':
$sql_set = 'SET ' . $value;
break; //case 'insert'
case 'value':
foreach ($value as $v) {
$sql_values .= "'" . addcslashes($v, '\'') . "', ";
}
$this->list_trim($sql_values);
break; //case 'insert'
case 'search':
$like = "LIKE '%{$value}%'";
break; //case 'search'
case 'table':
$tables = $sql['table'];
$from = 'FROM `' . $tables . '`';
break; //case 'table'
case 'field':
if (is_null($value)) {
$select = '*';
break;
}
$num_to_wheres = 0;
$buf = $value;
do {
$pre_buf = $buf;
for ($i = 0; $i < 2; $i++) {
$p = $i ? $this->preg_sql_noquote :
$this->preg_sql_quote;
$r = $i ? '$4' : '$5';
while(preg_match($p, $buf)) {
$fields[] = preg_replace($p, '$1', $buf);
$buf = preg_replace($p, $r, $buf);
if (preg_match('/=/', end($fields))) $num_to_wheres++;
}
}
if ($buf == $pre_buf) {
$this->track(
"error", "fix_sql",
"Parse error in Fields `{$value}`."
);
throw new Exception("Parse error in Fields `{$value}`.");
return false;
}
} while(!empty($buf));
$in_fields = array();
$num_fields = count($fields);
for ($i = 0; $i < $num_fields; $i++) {
$field = $fields[$i];
if (preg_match('/=/', $field)) {
$sql_where .= $this->fix_where($field);
}
$field = preg_replace(
'/(^[a-zA-Z0-9\._]+).*$/i',
'$1',
$field
);
if (array_search($field, $in_fields) === false) {
$sql_fields .= '`' . $field . '`, ';
}
$in_fields[] = $field;
}
$this->list_trim($sql_where, ' AND ');
$this->list_trim($sql_fields);
break; //case 'field'
case 'order':
$order = is_null($value) ? '' : 'ORDER BY ' . $value;
break; //case 'order'
case 'limit':
if (!preg_match('/^[0-9]+(, ?[0-9]+)?$/', $value)) {
$limit = $value;
} else {
$limit = '';
}
break; //case 'limit'
} //switch($type)
} //foreach ($sql as $type => $value)
$select = $Extra != 'ALL' ? 'SELECT ' .
$sql_fields :
'SELECT *';
$where = !empty($sql_where) ?
'WHERE ' . $sql_where :
'';
$insert = $insert . '(' . $sql_fields . ') VALUES (' . $sql_values . ')';
$like = !empty($sql_where) ?
$like : 'WHERE ' . $sql_fields .
' ' . $like;
switch($sql_type) {
case 'SELECT':
$query = "{$select} {$from} {$where} {$like} {$order} {$limit}";
break;
case 'INSERT':
$query = $insert;
break;
case 'UPDATE':
$query = "{$update} {$sql_set} {$where} {$limit}";
break;
case 'DELETE':
$query = "{$delete} {$where} {$limit}";
break;
}
return trim(preg_replace('/ +/', ' ', $query)) . ';';
}
/**
* Fix_Where; Return a where clause with proper MySQL syntax
*
* @param string $field Field that will be included in where clause
* @author Paul Williamson <
[email protected]>
* @access protected
* @return string
*/
protected function fix_where($field)
{
$parts = preg_split('/ *= */i', $field, 2);
$wfield = '`' . str_replace('`', '', $parts[0]) . '`=';
// addcslashes to prevent query parse error
$wfield .= '\'' . addcslashes(
preg_replace('/(^[\'"`])|([\'"`]$)/i', '',
$parts[1]
),
'\''
) . '\'';
return $wfield . ' AND ';
}
/**
* Fix_List; Return an array of broken elements from a list
* seperated by commas and quotes
*
* @param string $list List to be converted to array
* @author Paul Williamson <
[email protected]>
* @access protected
* @return array
*/
protected function fix_list($list)
{
$buf = $list;
$result = array();
do {
$pre_buf = $buf;
for ($i = 0; $i < 2; $i++) {
$q = $i ? preg_replace('/^ *([`\'"]).*/is', '$1', $buf) : '';
$p = $i ? str_replace('{quote}', $q, $this->preg_lst_quote) :
$this->preg_lst_noquote;
while (preg_match($p, $buf) && !empty($buf)) {
$result[] = preg_replace($p, '$1', $buf);
$buf = preg_replace($p, '$3', $buf);
}
}
if ($buf == $pre_buf) {
$this->track(
"error", "fix_list",
"Parse error in List `{$list}`."
);
throw new Exception("Parse error in List `{$list}`.");
return false;
}
} while(!empty($buf));
return $result;
}
/**
* Set_Param; Set an argument value if already exists
*
* @param string &$arg Argument varable
* @param string $name Argument name
* @author Paul Williamson <
[email protected]>
* @access private
* @return void
*/
private function set_param(&$arg, $name)
{
$arg = (is_null($arg)) ? $this->$name : $arg;
if (is_null($arg)) {
$this->track('error', 'set_param',
'Parameter `' . $name . '` is not set'
);
throw new Exception('Parameter `' . $name . '` is not set');
trigger_error('Parameter `' . $name . '` is not set', E_USER_ERROR);
}
$this->$name = $arg;
}
/**
* Fetch; Impliment the mysql_fetch_* functions
*
* @param resource $res Query result
* @param int $fetch Fetch Type
* @author Paul Williamson <
[email protected]>
* @access protected
* @return mixed
*/
protected function fetch($res, $fetch)
{
switch ($fetch) {
case FETCH_ASSOC:
$this->fetch_default = $fetch;
return mysql_fetch_assoc($res);
break;
case FETCH_NUM:
$this->fetch_default = $fetch;
return mysql_fetch_row($res);
break;
case FETCH_OBJECT:
$this->fetch_default = $fetch;
return mysql_fetch_object($res);
break;
default:
return mysql_fetch_assoc($res);
}
}
/**
* List_Trim; Trim the end of a list from a loop
*
* Function will take out extra ', ' on the end of a list string
*
* @param string &$str String to trim
* @author Paul Williamson <
[email protected]>
* @access protected
* @return string
*/
protected function list_trim(&$str, $tpat = ', ')
{
$preg_trim = '/(.*?)' . $tpat . '$/i';
$str = preg_replace($preg_trim, '$1', $str);
return $str;
}
/**
* Kill; Disconnect from database
*
* @author Paul Williamson <
[email protected]>
* @access private
* @return void
*/
private function kill()
{
@mysql_close($this->connection);
$this->track('event', 'kill',
"Connect to {$this->host} has been forced to close."
);
}
/**
* TimeNow; returns the current time
*
* In PHP 5, microtime() was givin a boolean
* param, if true, microtime() would return a float
* which is exactly what this function does.
*
* @author Paul Williamson <
[email protected]>
* @access protected
* @return float
*/
protected function timenow()
{
/**
* For PHP4:
*
* list($a, $b) = explode(' ', microtime());
* return $a + $b;
*
* PHP5 makes it less painful with the same result.
* Thanks to the new boolean argument provided by microtime()
*/
return microtime(true);
}
/**
* Age; how long the class has been alive
*
* @author Paul Williamson <
[email protected]>
* @access protected
* @return float microtime
*/
protected function age()
{
return $this->timenow()-$this->birth;
}
/**
* Do_FullLog; Write log track array to a file
*
* @author Paul Williamson <
[email protected]>
* @access private
* return void
*/
/**
* Do_Log; Write full log array to a file
*
* @author Paul Williamson <
[email protected]>
* @access private
* return void
*/
private function do_fulllog()
{
$path = self::LOG_PATH;
if (is_dir($path)) {
for ($i = 1; file_exists($file = $path . '/simplesql_raw.' . $i); $i++) {
if (filesize($file) < self::MAX_SIZE) break;
}
if ($handle = fopen($file, 'a')) {
$add = '$Id' . serialize($this->log) . '/$Id' . "\n";
if (fwrite($handle, $add) === false) {
trigger_error("Cannot write to file `{$file}`.", E_WARNING);
}
} else {
trigger_error("Cannot open file `{$file}` for append.", E_WARNING);
}
} else {
trigger_error("Cannot find path `{$path}`.", E_WARNING);
}
}
/**
* Do_Log; Write log track array to a file
*
* @author Paul Williamson <
[email protected]>
* @access private
* return void
*/
private function do_log()
{
if (!$this->common['tracklog']) return ;
$path = self::LOG_PATH;
if (is_dir($path)) {
for ($i = 1; file_exists($file = $path . '/simplesql_log.' . $i); $i++) {
if (filesize($file) < self::MAX_SIZE) break;
}
if ($handle = fopen($file, 'a')) {
$add = "\n\n=========================";
$add .= "\nLog for session ID {$this->log['session']}
@ {$this->log['date']}";
$add .= "\n=========================\n";
foreach ($this->log['track'] as $line => $arr) {
if (is_int($line)) {
$tab = strlen($arr['from']) < 8 ? "\t" : '';
$add .= "\t{$line}: " . strtoupper($arr['type'])
. "\t" . strtoupper($arr['from']) . "{$tab}\t{$arr['msg']}\n";
}
}
if (fwrite($handle, $add) === false) {
trigger_error("Cannot write to file `{$file}`.", E_WARNING);
}
} else {
trigger_error("Cannot open file `{$file}` for append.", E_WARNING);
}
} else {
trigger_error("Cannot find path `{$path}`.", E_WARNING);
}
}
/**
* PrintLog; Print the log array to the screen
*
* @author Paul Williamson <
[email protected]>
* @access public
* @return void
*/
public function printlog()
{
print '<pre>';
print_r($this->log);
print '</pre>';
}
} // end simplesql
?>
Download
