Location: PHPKode > scripts > Crutch MySQLd > CrutchMySQL.php
<?php

/**
 * CrutchMySQL
 * Little Helper for communicating with the MySQL database.
 *
 * @date		10/05/2010
 * @author		Ladislav Vondracek, http://www.twitter.com/Lawondyss
 * @copyright	2010 Ladislav Vondracek
 * @license		http://www.apache.org/licenses/LICENSE-2.0 Apache License, Version 2.0
 */
class CrutchMySQL
{
	/**
	 * Debug constants
	 */
	const DETAIL = 'DETAIL';
	const EXTEND = 'EXTEND';
	const ERROR = 'ERROR';

	/**
	 * Result type
	 * @var array
	 */
	private $result_type = array(
		'ASSOC' => MYSQL_ASSOC,
		'NUM' => MYSQL_NUM,
		'BOTH' => MYSQL_BOTH,
	);


	/**
	 * @var string
	 */
	private $server;

	/**
	 * @var string
	 */
	private $username;

	/**
	 * @var string
	 */
	private $password;

	/**
	 * @var string
	 */
	private $database;

	/**
	 * @var string
	 */
	private $charset;

	/**
	 * MySQL link
	 * @var result
	 */
	private $link;

	/**
	 * Array with results
	 * @var array
	 */
	private $results;

	/**
	 * File with errors
	 * @var string
	 */
	private $file_error_log;

	/**
	 * @var boolean
	 */
	private $show_sql;

	/**
	 * @var boolean
	 */
	private $show_err;


	/**
	 * SETTERS
	 */
	// file with errors
	public function setFileErrorLog($filepath)
	{
		//absolute path
		$this->file_error_log = dirname($_SERVER['DOCUMENT_ROOT']).'/'.$filepath;
	}


	/**
	 * Constuctor
	 */
	public function __construct($username, $password, $database, $charset='utf8', $server='localhost')
	{
		// inicialiton
		$this->results = array();

		$this->server	= $server;
		$this->username	= $username;
		$this->password	= $password;
		$this->database	= $database;
		$this->charset	= $charset;

		// developer
		if($_SERVER['SERVER_ADDR'] == '127.0.0.1')
		{
			$this->debug(self::EXTEND);
		}
		// production
		else
		{
			$this->debug(self::ERROR);
		}

		// default file with errors
		$this->setFileErrorLog('mysql_error.log');
	}


	/**
	 * Connect to database, set database and charset
	 *
	 * @param bool $persistent Classic or persistent connection.
	 * @return bool State connection.
	 */
	public function connect($persistent=TRUE)
	{
		// connect
		if($persistent)
		{
			$this->link = @mysql_connect($this->server, $this->username, $this->password);
		}
		else
		{
			$this->link = @mysql_connect($this->server, $this->username, $this->password);
		}

		if(!$this->link)
		{
			$this->logger('Error connect to database.', mysql_error());

			return FALSE;
		}
		else
		{
			// select database
			if(!@mysql_select_db($this->database, $this->link))
			{
				$this->logger('Error select database.', mysql_error());

				return FALSE;
			}

			// set charset
			if(!@mysql_set_charset($this->charset, $this->link))
			{
				$this->logger('Error set charset.', mysql_error());

				return FALSE;
			}
		}

		return TRUE;
	}


	/**
	 * Execute SQL query
	 *
	 * @param string $sql SQL query to the database.
	 * @return resource Resource from query or false.
	 */
	public function execute($sql)
	{
		// show SQL string
		if($this->show_sql)
		{
			echo '<pre class="sql">'.$sql.'</pre>';
		}

		// query execution
		$result = @mysql_query($sql, $this->link);

		// error
		if(!$result)
		{
			$this->logger('Request to the server failed.', mysql_error(), $sql);

			return FALSE;
		}
		// OK
		else
		{
			return $result;
		}

	}


	/**
	 * Fetch a result all rows as an associative array, a numeric array, or both
	 *
	 * @param resource/string $result Resource from query or SQL string
	 * @param string $type Array type returned.
	 * @return array Table rows for cycle.
	 */
	public function fetchAll($result, $type='ASSOC')
	{
		// key for resource
		$key = md5((string) $result);

		// save resource to array
		if(!isset($this->results[$key]))
		{
			// result from query
			if(is_resource($result))
			{
				$this->results[$key] = $result;
			}
			// SQL
			elseif(is_string($result))
			{
				$this->results[$key] = $this->execute($result);

				// error
				if(!$this->results[$key])
				{
					unset($this->results[$key]);

					return FALSE;
				}
			}
			// FALSE
			elseif($result === FALSE)
			{
				return FALSE;
			}
			// unknown
			else
			{
				$this->logger('Unknown resource.');

				return FALSE;
			}

			// call themselves
			return $this->fetchAll($result, $type);
		}
		// return rows
		else
		{
			// read next row
			$row = mysql_fetch_array($this->results[$key], $this->result_type[$type]);

			if(!$row)
			{
				// removed from memory
				mysql_free_result($this->results[$key]);

				//removed from array
				unset($this->results[$key]);
			}

			return $row;
		}
	}


	/**
	 * Fetch a result one row as an associative array, a numeric array, or both
	 *
	 * @param resource/string $result Resource from query or SQL string
	 * @param string $type Array type returned.
	 * @return array Table row.
	 */
	public function fetchSingle($result, $type='ASSOC')
	{

		// SQL
		if(is_string($result))
		{
			$result = $this->execute($result);

			// error
			if(!$result)
			{
				return FALSE;
			}
		}

		// result from query
		if(is_resource($result))
		{
			// read first row
			$row = mysql_fetch_array($result, $this->result_type[$type]);

			// removed from memory
			mysql_free_result($result);

			return $row;
		}
		// FALSE
		elseif($result === FALSE)
		{
			return FALSE;
		}
		// unknown
		else
		{
			$this->logger('Unknown resource.');

			return FALSE;
		}
	}


	/**
	 * Fetch a one cell
	 *
	 * @param resource/string $result Resource from query or SQL string
	 * @param int $field Column number (from zero).
	 * @param int $row Line number (from zero).
	 * @return mixed Value from database.
	 */
	public function fetchCell($result, $field=0, $row=0)
	{

		// SQL
		if(is_string($result))
		{
			$result = $this->execute($result);

			// error
			if(!$result)
			{
				return FALSE;
			}
		}

		// result from query
		if(is_resource($result))
		{
			// seek to row
			mysql_data_seek($result, $row);

			// read row
			$row = mysql_fetch_array($result, $this->result_type['BOTH']);

			// removed from memory
			mysql_free_result($result);

			return $row[$field];
		}
		// FALSE
		elseif($result === FALSE)
		{
			return FALSE;
		}
		// unknown
		else
		{
			$this->logger('Unknown resource.');

			return FALSE;
		}
	}


	/**
	 * Fetch a field
	 *
	 * @param resource/string $result Resource from query or SQL string
	 * @return array Array with columns name.
	 */
	public function fetchField($result)
	{
		// SQL
		if(is_string($result))
		{
			$result = $this->execute($result);

			// error
			if(!$result)
			{
				return FALSE;
			}
		}

		// result from query
		if(is_resource($result))
		{
			$fields = array();
			$count_fields = mysql_num_fields($result);

			for($i=0; $i<$count_fields; $i++)
			{
				$fields[$i] = mysql_field_name($result, $i);
			}

			return $fields;
		}
		// FALSE
		elseif($result === FALSE)
		{
			return FALSE;
		}
		// unknown
		else
		{
			$this->logger('Unknown resource.');

			return FALSE;
		}
	}


	/**
	 * Get number of rows in result
	 *
	 * @param resource $result Resource from query.
	 * @return int Number of rows.
	 */
	public function countRows($result)
	{
		// result from query
		if(is_resource($result))
		{
			return mysql_num_rows($result);
		}
		// FALSE
		elseif($result === FALSE)
		{
			return FALSE;
		}
		// unknown
		else
		{
			$this->logger('Unknown resource.');

			return FALSE;
		}
	}


	/**
	 * Get number of field in result
	 *
	 * @param resource $result Resource from query.
	 * @return int Number of columns
	 */
	public function countFields($result)
	{
		// result from query
		if(is_resource($result))
		{
			return mysql_num_fields($result);
		}
		// FALSE
		elseif($result === FALSE)
		{
			return FALSE;
		}
		// unknown
		else
		{
			$this->logger('Unknown resource.');

			return FALSE;
		}
	}


	/**
	 * Returns number of affected rows
	 *
	 * @param string $sql SQL string
	 * @return int Number of affected rows.
	 */
	public function affectedRows($sql=NULL)
	{
		// SQL
		if(isset($sql))
		{
			$result = $this->execute($sql);

			// error
			if(!$result)
			{
				return FALSE;
			}
		}

		$count = mysql_affected_rows($this->link);

		return $count;
	}


	/**
	 * Get the ID generated in the last query
	 *
	 * @return int Last ID from INSERT or REPLACE command.
	 */
	public function lastId()
	{
		return mysql_insert_id($this->link);
	}


	/**
	 * Insert
	 *
	 * @param string $table Database table.
	 * @param array/string $params Association array $name => $value or string.
	 * @param array/string $on_duplicate Association array $name => $value or string.
	 * @return mixed Last ID or false.
	 */
	public function insert($table, $params, $on_duplicate=NULL)
	{
		// start SQL
		$sql = "INSERT INTO `$table` SET ";

		// join parameters to SQL
		$sql .= $this->parameters($params);

		// adjustment in case of duplication
		if(isset($on_duplicate))
		{
			// add conditions
			$sql .= " ON DUPLICATE KEY UPDATE ";

			// join parameters to SQL
			$sql .= $this->parameters($on_duplicate);
		}

		$result = $this->execute($sql);

		if($result)
		{
			// ID inserted entry
			return self::lastId();
		}
		else
		{
			return FALSE;
		}
	}


	/**
	 * Replace
	 *
	 * @param string $table Database table.
	 * @param array/string $params Association array $name => $value or string.
	 * @return mixed Last ID or false.
	 */
	public function replace($table, $params)
	{
		// start SQL
		$sql = "REPLACE INTO `$table` SET ";

		// join parameters to SQL
		$sql .= $this->parameters($params);

		$result = $this->execute($sql);

		if($result)
		{
			// ID inserted entry
			return self::lastId();
		}
		else
		{
			return FALSE;
		}
	}


	/**
	 * Update
	 *
	 * @param array/string $tables Database table in array or string.
	 * @param array/string $params Association array $name => $value or string.
	 * @param array/string $where Related array 'AND' or string.
	 */
	public function update($tables, $params, $where='')
	{
		// start SQL
		$sql = "UPDATE ";

		// compilation tables
		if(is_array($tables))
		{
			$sql .= implode(',', $tables);
		}
		else
		{
			$sql .= $tables;
		}

		$sql .= " SET ";

		// add parameters
		$sql .= $this->parameters($params);

		// add condition
		if(!empty($where))
		{
			if(is_array($where))
			{
				$sql .= " WHERE ".implode(' AND ', $where);
			}
			else
			{
				$sql .= " WHERE $where";
			}
		}

		return $this->execute($sql);
	}


	/**
	 * Select
	 *
	 * @param array/string $tables Database table in array or string.
	 * @param array/string $where Related array 'AND' or string.
	 * @param array/string $cols Name cols in array or string.
	 */
	public function select($tables, $where='', $cols='*')
	{
		// start SQL
		$sql = "SELECT ";

		// compilation cols
		if(is_array($cols))
		{
			$sql .= implode(',', $cols);
		}
		else
		{
			$sql .= $cols;
		}

		$sql .= " FROM ";

		// compilation tables
		if(is_array($tables))
		{
			$sql .= implode(',', $tables);
		}
		else
		{
			$sql .= $tables;
		}

		// compilation condition
		if(is_array($where))
		{
			$sql .= " WHERE ".implode(' AND ', $where);
		}
		elseif(!empty($where))
		{
			$sql .= " WHERE $where";
		}

		return $this->fetchAll($sql);
	}


	/**
	 * Delete
	 *
	 * @param array/string $tables Database table in array or string.
	 * @param array/string $where Related array 'AND' or string.
	 */
	public function delete($tables, $where='')
	{
		// start SQL
		$sql = "DELETE FROM ";

		// compilation tables
		if(is_array($tables))
		{
			$sql .= implode(',', $tables);
		}
		else
		{
			$sql .= $tables;
		}

		// add condition
		if(!empty($where))
		{
			$sql .= " WHERE ";

			// compilation
			if(is_array($where))
			{
				$sql .= implode(' AND ', $where);
			}
			else
			{
				$sql .= $where;
			}
		}

		return $this->execute($sql);
	}


	/**
	 * Start transaction
	 */
	public function begin()
	{
		// disabling autocommit
		$this->execute('SET autocommit=0');

		// start transaction
		return $this->execute('BEGIN');
	}


	/**
	 * Rollback
	 */
	public function rollback()
	{
		// rollback transaction
		$result = $this->execute('ROLLBACK');

		// enabling autocommit
		$this->execute('SET autocommit=1');

		// OK
		if($result)
		{
			return TRUE;
		}
		// error
		else
		{
			return FALSE;
		}
	}


	/**
	 * Commit
	 */
	public function commit()
	{
		// commit transaction
		$result = $this->execute('COMMIT');

		// enabling autocommit
		$this->execute('SET autocommit=1');

		// OK
		if($result)
		{
			return TRUE;
		}
		// error
		else
		{
			return FALSE;
		}
	}


	/**
	 * Error logger
	 *
	 * @param string $message Message to display.
	 * @param string $mysql_error Error from mysql.
	 * @param string $sql SQL from query.
	 */
	private function logger($message, $mysql_error=NULL, $sql=NULL)
	{
		$backtrace = array_pop(debug_backtrace());

		$debug = basename($backtrace['file']).':'.$backtrace['line'].' {'.$backtrace['function'].'()}'."\n";
		$error_log = '============ '.date('d/m/Y H:i:s').' ============'."\n";
		$error_log .= $_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI']."\n";
		$error = $message."\n";

		if($mysql_error)
		{
			$mysql_error = str_replace('You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use', 'Syntax error', $mysql_error)."\n";
		}

		if(isset($sql))
		{
			$mysql_error .= $sql."\n";
		}

		$debug .= $mysql_error;

		if($this->show_err)
		{
			$error .= $debug;
		}

		echo '<pre class="error">'.$error.'</pre>';

		$error_log .= $message."\n".$debug."\n";

		// write to file
		file_put_contents($this->file_error_log, $error_log, FILE_APPEND);

	}


	/**
	 * Set debug mode
	 *
	 * @param string $mode Type display errors.
	 */
	public function debug($mode = self::ERROR)
	{
		if($mode == self::DETAIL)
		{
			$this->show_sql = TRUE;
			$this->show_err = TRUE;
		}
		elseif($mode == self::EXTEND)
		{
			$this->show_sql = FALSE;
			$this->show_err = TRUE;
		}
		elseif($mode == self::ERROR)
		{
			$this->show_sql = FALSE;
			$this->show_err = FALSE;
		}
		else
		{
			$this->logger('Invalid debug mode.');

			$this->show_sql = FALSE;
			$this->show_err = FALSE;
		}
	}


	/**
	 * Sanitize string
	 *
	 * @param string $string String to sanitize escaping.
	 */
	protected function sanitize($string)
	{
		if(is_null($string))
		{
			return NULL;
		}

		// auto-escaping
		if(get_magic_quotes_gpc())
		{
			// eliminate backslashes
			$string = stripslashes($string);
		}

		//escaping
		$string = mysql_real_escape_string($string, $this->link);

		return $string;
	}

	/**
	 * Get parameters string
	 *
	 * @param array/string $params Data for INSERT, REPLACE, UPDATE query.
	 */
	protected function parameters($params)
	{
		// keywords
		$keywords = array('NULL','DEFAULT');

		// breakdown by type
		if(is_array($params))
		{
			foreach($params as $name => $value)
			{
				// sanitize escaping
				$value = $this->sanitize($value);

				// breakdown by type
				if(is_null($value))
				{
					$parameters[] = "`$name`=NULL";
				}
				elseif(is_numeric($value))
				{
					$parameters[] = "`$name`=$value";
				}
				elseif(in_array($value, $keywords))
				{
					$parameters[] = "`$name`=$value";
				}
				else
				{
					$parameters[] = "`$name`='$value'";
				}
			}

			// join parameters
			$return = implode(',', $parameters);
		}
		else
		{
			$return = $params;
		}

		return $return;
	}

}

?>
Return current item: Crutch MySQLd