File: sql.class.php

<?php
/**
 * @file	:	sql.class.php
 * @desc	:	basic mysql functions
 * @lmod	:	13.05.2007
 */

class sql
{
	// database connection handler
	var $connection = false;
	
	// query results holder
	var $res;
	
	// query stats
	var $num_rows;
	var $num_flds;
	var $num_aff;
	var $last_id;
	
	// default fetch row
	var $fetch_method = 'object';

/**
 * @desc						:	the constructor, connect to mySQL server
 * @param	$dbhost	:	string	:	database host
 * @param	$dbuser	:	string	:	database user
 * @param	$dbpass	:	string	:	database user pass
 * @param	$dbname	:	string	:	database name
 * @return			:	boolean	:	connection status
 */
	function sql($dbhost=0, $dbuser=0, $dbpass=0, $dbname=0)
	{
		// get my globals
		global $_DBHOST, $_DBUSER, $_DBNAME, $_DBPASS;
		
		// if some of the connection values is missing replace it
		$dbhost = $dbhost ? $dbhost : $_DBHOST;
		$dbuser = $dbuser ? $dbuser : $_DBUSER;
		$dbpass = $dbpass ? $dbpass : $_DBPASS;
		$dbname = $dbname ? $dbname : $_DBNAME;
		
		// try to connect
		if (!$this->connection = $this->connect($dbhost, $dbuser, $dbpass, $dbname))
		{
			$this->error('connection error');
			return false;
		}
		
		return true;
	}
	
/**
 * @desc						:	set default fetch method
 * @param $method	:	string	:	method name(object, array, assoc, row)
 */
	function setFetchMethod($method)
	{
		$this->fetch_method = $method;
	}
	
/**
 * @desc						:	display error message
 * @param	$err	:	sting	:	error message
 */
	function error($error)
	{
		print $error . (mysql_error() ? ': ' . mysql_error() : '' );
	}

/**
 * @desc						:	connect to mySQL server
 * @param	$dbhost	:	string	:	database host
 * @param	$dbuser	:	string	:	database user
 * @param	$dbpass	:	string	:	database user pass
 * @param	$dbname	:	string	:	database name
 * @return			:	boolean	:	connection status
 */
	function connect($dbhost, $dbuser, $dbpass, $dbname)
	{
		// if there is connection, disconnect first
		if ($this->connection) { $this->disconnect(); }
		
		// default database handler
		$dbh = false;
		
		// connect to mySQL server and database
		if (!$dbh = mysql_connect($dbhost, $dbuser, $dbpass))
		{
			return false;
		}
		elseif (mysql_select_db($dbname))
		{
			return $dbh;
		}
		
		return false;
	}
	
/**
 * @desc				:	disconnect from mySQL server
 * @return	:	boolean	:	is disconnection succesful
 */
	function disconnect()
	{
		// if there isn't connection, just return true;
		if (!$this->connection) { return true; }
		
		// disconnect
		if (mysql_close($this->connection)) { return true; }
		
		return false;
	}
	
/**
 * @desc						:	makes mySQL query
 * @param	$query	:	string 	:	query
 * @param	$index	:	int		:	query index
 * @return			:	boolean	:	query status
 */
	function query($query, $index=0)
	{
		// query
		if (!$this->res[$index] = mysql_query($query, $this->connection))
		{
			// if query fail show error
			$this->error('<strong>invalid query</strong>:<br />' . $query . '<br />');
			return false;
		}
		
		// statistical information
		$this->num_rows[$index]	= @mysql_num_rows($this->res[$index]);
		$this->num_flds[$index]	= @mysql_num_fields($this->res[$index]);
		$this->num_aff[$index]	= @mysql_affected_rows();
		$this->last_id			= @mysql_insert_id($this->connection);
		
		return true;
	}
	
/**
 * @desc						:	makes mySQL query and return its result + free this result
 * @param	$query	:	string 	:	query
 * @param	$index	:	int		:	query index
 * @return	$row	:	boolean	:	query result
 */
	function query1($qry, $index=0, $method=false)
	{
		$this->query($qry, $index);
		$row = $this->getrow($index, false, $method);	
		
		if ($row)
		{
			$this->free_result($index);
		}
		
		return $row;
	}
	
/**
 * @desc						:	fetch result
 * @param	$index	:	int		:	result index
 * @param	$rown	:	int		:	which row to fetch (count starts at 0)
 * @param	$method	:	string	:	fetch method (object, array, assoc, row)
 * @return 	$row	:			:	row data
 */
	function getrow($index=0, $rown=false, $method=false)
	{
		// check if there is a result to fetch
		if (!$this->getnumrows($index))
		{
			return false;
		}
		
		// check if we seek for row that is invalid for that result
		if ($rown !== false && ($row > ($this->getnumrows($index) - 1)) )
		{
			$this->error('invalid row');
			return false;
		}
		elseif ($rown != false)
		{
			// seek for selected row number
			mysql_data_seek($this->res[$index], $rown);
		}
		
		// fetch row depending on fetching method
		$method = $method ? $method : $this->fetch_method;
		
		switch ($method)
		{
			case 'array':
				$row = @mysql_fetch_array($this->res[$index]);
				break;
			case 'assoc':
				$row = @mysql_fetch_assoc($this->res[$index]);
				break;
			case 'row':
				$row = @mysql_fetch_row($this->res[$index]);
				break;
			default:
				// 'object' method is the default
				$row = @mysql_fetch_object($this->res[$index]);
				break;
		}
		
		if (!$row || $rown !== false)
		{
			$this->free_result($index);
		}
		
		return $row;
	}

/**
 * @desc					:	get the number of selected rows
 * @param	$index	:	int	:	result index
 * @return			:	int	:	rows number
 */
	function getnumrows($index=0) 
	{
		return $this->num_rows[$index];
	}

/**
 * @desc					:	get the number of selected fields
 * @param	$index	:	int	:	result index
 * @return			:	int	:	fields number
 */
	function getnumflds($index=0) 
	{
		return $this->num_flds[$index];
	}

/**
 * @desc					:	get the number of affected rows
 * @param	$index	:	int	:	result index
 * @return			:	int	:	affected rows number
 */
	function getnumaff($index=0)
	{
		return $this->num_aff[$index];
	}

/**
 * @desc			:	get last insert id, generated by INSERT query
 * @return 	:	int	:	last insert id
 */
	function getlastid() 
	{
		return $this->last_id;
	}
	
/**
 * @desc						:	free mysql result from mysql and the class	
 * @param	$index	:	int		:	result index
 * @return 			:	boolean	:	status
 */
	function free_result($index=0) 
	{
		// unset(delete) all statistical information for the selected result
		mysql_free_result($this->res[$index]);
		
		unset($this->num_rows[$index]);
		unset($this->num_flds[$index]);
		unset($this->num_aff[$index]);
		unset($this->res[$index]);
		
		// free mysql result
		return true;
	}
	
/**
 * @desc				:	get next free index number
 * @return	$i	:	int	:	next index
 */
	function nextindex()
	{
		for($i=0; $this->num_rows[$i]; $i++); 
		
		return $i;
	}
	
/**
 * @desc						:	escape mySQl string
 * @param	$str	:	string	:	original string
 * @return 			:	string	:	escaped string
 */
	function escape($str)
	{
		$str = stripslashes($str);
		$str = mysql_real_escape_string($str);
		
		return $str;
	}
	
/**
 * @desc						:	full mysql escape(mainly for search queryies)
 * @param	$str	:	string	:	original string
 * @return	$str	:	string	:	escaped string
 */
	function escape_full($str)
	{
		$str = $this->escape($str);
		$str = preg_replace("/(\_|\%)/", '\\\$1', $str);
		
		return $str;
	}
	
/**
 * @desc					:	quote strings(the function arguments)
 * @return $str	:	string	:	quoted strings
 */
	function quote()
	{
		$str	= array();
		$args	= func_get_args();
		
		foreach ($args as $val)
		{
			$str[] = '"'  . $this->escape($val) . '" ';
		}
		
		return join(', ', $str);
	}
	
/**
 * @desc			:	generates where conditions
 * @param	$cond	:	contitions
 * @return			:	sql where conditions
 */
	function where($cond)
	{
		if (!$cond)
		{
			return '';
		}
		
		if (is_numeric($cond))
		{
			return "WHERE id='{$cond}' ";
		}
		
		if (is_array($cond))
		{
			$vals = array();
			foreach($cond as $field => $value)
			{
				$vals[] = is_numeric($field) ? $value : $field . '=' . $this->quote($value);
			}
			
			return 'WHERE ' . join(' AND ', $vals) . ' ';
		}
		
		return "WHERE {$cond} ";
	}
	
/*
 * @desc							:	generate insert query
 * @param	$table		:	string	:	table name
 * @param	$values		:	array	:	values to insert ([field name] => field value)
 * @param	$avalues	:	array	:	addition values (without '')
 * @return				:	int		:	last added primary key or false on error 
 */
	function insert($table, $values, $avalues=0)
	{
		if (!is_array($values)) { return false; }
		
		// set $flds and $vals param to array 
		$flds = array();
		$vals = array();
		
		// construct query
		foreach ($values as $key => $val)
		{
			$flds[] = $key;
			$vals[] = "'{$val}'";
		}
		
		// if there is additional values
		if (is_array($avalues))
		{
			foreach ($avalues as $key => $val)
			{
				$flds[] = $key;
				$vals[] = $val;
			}
		}
		
		// strip commas and whitespace from the end
		$flds = join(', ', $flds);
		$vals = join(', ', $vals);
		
		// try to make insert query and return last added id
		if ($this->query("INSERT INTO {$table} ( {$flds} ) VALUES ( {$vals} )"))
		{
			return $this->getlastid();
		}
		
		return false;
	}

/*
 * @desc							:	generate update query
 * @param	$table		:	string	:	table name
 * @param	$values		:	array	:	values to update ([field name] => field value)
 * @param	$cond		:	string	:	conditions for update
 * @return				:	boolean	:	query status
 */
	function update($table, $values, $cond=0)
	{
		if (!is_array($values)) { return false; }
		
		// add update to the query first
		$qry = array();
		foreach ((array)$values as $key => $val)
		{
			$qry[] = is_numeric($key) ? $val : $key . "='{$val}'";
		}
		
		// now generate query
		return $this->query("UPDATE {$table} SET " . join(', ', $qry) . $this->where($cond));
	}
	
/**
 * @desc							:	set info in $table
 * @param	$table		:	string	:	table name
 * @param	$values		:	array	:	values to set ([field name] => field value)
 * @param	$key		:	string	:	table primary key, can be array with [key] => kval
 * @param	$kval		:	string	:	primary key value
 * @return				:	int		:	setted datebase record id
 */
	function set($table, $values, $key=0, $kval=0)
	{
		// check ig $key is array [key] => kval
		if (is_array($key))
		{
			// gen. conditions string
			$cond = array();
			foreach ($key as $kname => $kval)
			{
				$cond[] = "{$kname}='{$kval}' ";
			}
			$cond = join('AND ', $cond);
				
			// get first key as primary
			reset($key);
			$kval = current($key);
			$key  = key($key);
		}
		elseif ($kval) // if kval is setted
		{
			// gen. conditions string
			$cond = "{$key}='{$kval}'";
		}
		
		// check if there are setted conditions
		if ($cond)
		{
			// check if record with that conditions exists
			$this->query("SELECT {$key} FROM {$table} WHERE {$cond} LIMIT 1");
			
			if ($this->getnumrows() > 0)
			{
				// if there are no values delete record
				if (!is_array($values))
				{
					// delete record
					return $this->query("DELETE FROM {$table} WHERE {$cond}");
				}
				
				// update record
				if ($this->update($table, $values, $cond))
				{
					return $kval;
				}

				return 0;
			}
		}
		
		// insert new record
		if ($this->insert($table, $values))
		{
			return $this->getlastid();
		}
		
		return 0;
	}
	
/**
 * @desc								:	delete information from table(s)
 * @param	$table	:	string/array	:	table(s) name(s)
 * @param	$cond	:	string/int		:	condiotions for delete(if some)	
 * @return			:	boolean			:	is deleting succesful
 */
	function delete($table, $cond=0)
	{
		// check if $table is a single table or a list of tables
		if (!is_array($table))
		{
			// delete record(s) from one table 
			return $this->query("DELETE FROM {$table} ". $this->where($cond));
		}
		
		// in case $table is array
		// it must contain the list of tables
		// from which we must delete record(s)
		foreach ($table as $tname)
		{
			if (!$this->delete($tname, $cond))
			{
				return false;
			}
		}
		
		return true;
	}
/**
 * @desc							:	make selet query and execute it
 * @param	$table		:	string	:	table
 * @param	$fields		:	string	:	fields to select
 * @param	$cond		:	string	:	select conditions
 * @param	$limit		:	string	:	limit conditions (if 1 return only the first recods
 * @param	$index		:	int		:	query index
 * @return				:	boolean	:	query status
 */
	function select($table, $fields='*', $cond=false, $limit=false, $index=0)
	{
		$qry  = 'SELECT ' . ( is_array($fields) ? join(',', $fields) : $fields ) . " FROM {$table} ";
		$qry .= $this->where($cond);
		$qry .= $limit ? "LIMIT {$limit} " : '';
	
		if ($limit == 1)
		{
			return $this->query1($qry, $index);
		}
			
		if (!$this->query($qry, $index))
		{
			return false;
		}
					
		return true;	
	}
	
/**
 * @desc						:	dumps table(s) structure
 * @param	$table	:	string	:	table to dump
 * @param	$prefix	:	string	:	prefix for table code
 * @param	$suffix	:	string	:	suffix for table code
 * @param	$type	:	string	:	tables TYPE or ENGINE (def: MyISAM)
 * @return	$str	:	string	:	sql codes for table(s) structure
 */
	function table_info($table=0, $prefix=0, $suffix=0, $type='MyISAM')
	{
		// set sring to dumping
		$str = '';

		if (!$table)
		{
			// select all tables
			$this->query('SHOW TABLES ');
			
			while ($row = $this->getrow(0, false, 'array')) 
			{				
				$str .= $this->table_info(current($row), $prefix, $suffix, $type);
			}
			
			return $str;
		}
		elseif (is_array($table))
		{
			// select listed tables
			foreach ($table as $tbl)
			{
				$str .= $this->table_info(current($row), $prefix, $suffix, $type);
			}
			
			return $str;
		}
		
		// describe table
		$this->query("DESCRIBE {$table}", 1);
			
		// create table query string
		$flds  = "CREATE TABLE {$table}";
		$flds .= "\n(\n"; 
			
		while ($res = $this->getrow(1)) 
		{
			$flds .= "\t" . $res->Field . "\t\t" . strtoupper(trim($res->Type));
			$flds .= $res->Null == 'NO' 						? ' NOT NULL'					: ' ';
			$flds .= $res->Default && $res->Default != 'NULL'	? "DEFAULT '{$res->Default}'"	: '';
			$flds .= $res->Extra								? ' ' . strtoupper($res->Extra)	: '';
			$flds .= $res->Key == 'PRI' ?						' PRIMARY KEY'					: ''; 
			$flds .= ",\n";
		}
			
		$flds  = rtrim($flds, ",\n");
		$flds .= "\n)";
		$flds .= $type ? " TYPE={$type}; " : ''; 
		$flds .= "\n";
			
		// add table info to return string
		$str .= $prefix ? $prefix : '';
		$str .=  "\n {$flds}";
		$str .= $suffix ? $suffix : ''; 
		$str .= "\n\n";
			
		return $str;
	}
	
/**
 * @desc						:	dumps table(s) content
 * @param	$table	:	string	:	table to dump
 * @param	$prefix	:	string	:	prefix for table code
 * @param	$suffix	:	string	:	suffix for table code
 * @param	$skip	:	array	:	fields to skip
 * @return	$str	:	string	:	sql codes for table(s) content
 */
	function table_content($table=0, $prefix=0, $suffix=0, $skip=array())
	{
		// set sring to dumping
		$str = '';
		
		// if there is not table added just show contend from all tables
		if (!$table)
		{
			// select all tables
			$this->query('SHOW TABLES ');
			
			while ($row = $this->getrow(0, false, 'array')) 
			{
				$str .= $this->table_content(current($row), $prefix, $suffix, $skip);
			}
			
			return $str;
		}
		elseif (is_array($table))
		{
			// select form listed tables
			foreach ($table as $tbl)
			{
				$str .= $this->table_content(current($row), $prefix, $suffix, $skip);
			}
			
			return $str;
		}
		
		// select *(all) from table
		$this->query("SELECT * FROM {$table} ", 1);

		while($res = $this->getrow(1))
		{
			// cover result object to array 
			$res = get_object_vars($res);
			
			// dump table content
			$keys = array();
			$vals = array();
			
			foreach ($res as $key => $val)
			{
				// skip this field ?
				if (in_array($key, (array) $skip))
				{
					continue;
				}
					
				$keys[] = $key;
				$vals[] = "'" . $val . "'";
			}
			$keys = join(', ', $keys);
			$vals = join(', ', $vals);
				
			$str .= $prefix ? $prefix : '';
			$str .= "INSERT INTO {$table} ( {$keys} ) VALUES ( {$vals} ) ";
			$str .= $suffix ? $suffix : '';
			$str .= "\n";
		}
		
		return $str;
	}
}

?>