Location: PHPKode > scripts > MySQL access class > mysql-access-class/class.mysql.inc.php
<?php
/**
* @author Bimal Poudel
* @copyright since 2006, Bimal Poudel
* @package mysql
*/

/**
* MySQL basic class
* @package mysql
*/
class mysql
{
	/**
	* @var RESOURCE Database resultset
	*/
	var $RESULTSET;

	/**
	* @var RESOURCE MySQL connection resource
	*/
	var $CONNECTION;
	
	/**
	* @var integer Number of rows affected by last {@link function query} attempt.
	*/
	var $ROWS = 0;
	
	/**
	* @var array List of columns retrived on the last {@link query} attempt.
	*/
	var $FIELDS = array();

	/**
	* @var integer Number columns retrived on the last {@link query} attempt.
	*/
	var $FIELDS_COUNTER = 0;

	/**
	* @var object Metadata of each columns ({@link FIELDS}) retrived.
	*/
	var $META_DATA;

	/**
	* @var array Association of columns and values of one row.
	* @see mysql::next_record()
	*/
	var $row_data;
	
	/**
	* Default constructor calls our actual constution method.
	*/
	function __construct()
	{
		$this->mysql();
	}

	/**
	* Constructor
	* <br>Connects to the database with the assigned server details.
	* <br>Reuses the connection resources if possible.
	*/
	function mysql()
	{
		if(!defined('MYSQL_CONNECTION'))
		{
			global $dbinfo;
			if(!isset($dbinfo[$dbinfo['dbindex']]['dbcon']))
			{
				require_once(dirname(__FILE__)."/config.mysql.inc.php");
			}
		}
		$this->CONNECTION = MYSQL_CONNECTION;
		#$this->CONNECTION = $dbinfo[$dbinfo['dbindex']]['dbcon'];
		# define('MYSQL_CONNECTION', $dbinfo[$dbinfo['dbindex']]['dbcon']); # done already!
		return(false);
	} # mysql()

	/**
	* Attempt any kind of database queery.
	* @param string $query Valid SQL to hit
	* @return boolean Success / Failure  status while making a query
	*/
	function query($sql='')
	{
		$success=false;
		if($sql=='')
		{
			return($success);
		}
		#echo($sql);

		$meta = array();
		$this->__RESET_VARIABLES(); # start as a fresh; always!
		
		if($this->RESULTSET=mysql_query($sql, $this->CONNECTION))
		{
			$this->FIELDS_COUNTER = @mysql_num_fields($this->RESULTSET) + 0;
			for($i=0; $i<$this->FIELDS_COUNTER; ++$i)
			{
				$meta = mysql_fetch_field($this->RESULTSET, $i);
				$this->FIELDS[$i]=$meta->name;
				$this->META_DATA[$meta->name]=$meta;
			}
			$success=true;
		}
		else
		{
			if($_SERVER['SERVER_NAME']=='localhost')
				echo("SQL Error! {$sql}<HR>".mysql_error($this->CONNECTION));
			#die("SQL Error! {$sql}<br />".mysql_error());
			return($success);
		}

		/**
		* Put here for compatinility with UPDATE/DELETE statements
		* They do not give usable RESOURCEs.
		*/
		$this->FIELDS_COUNTER = @mysql_num_fields($this->RESULTSET)+0;
		$this->ROWS = @mysql_affected_rows($this->CONNECTION)+0;

		return($success);
	} # query()
	
	/**
	* Get a first record of a query
	* @param $sql string
	* @return array
	*/
	function row($sql='#')
	{
		$row = array();
		$this->query($sql);
		if($this->ROWS>0)
		{
			$this->next_record();
			$row = $this->row_data;
		}
		return($row);
	} # row()
	
	/**
	* Seek one record, if available.
	* <br>Sets an association of the database columns and values selected.
	* @return boolean Was seeking to next record possible?
	*/
	function next_record()
	{
		$success=true;
		$row_data=@mysql_fetch_array($this->RESULTSET, MYSQL_ASSOC) or $success=false;
		if(is_array($row_data) && $success==true)
			$this->row_data=$row_data;
		return($success);
	} # next_record()
	
	/**
	* Reset all the internal variables to null.
	* @access private
	*/
	function __RESET_VARIABLES()
	{
		$this->ROWS = 0;
		$this->FIELDS = array();
		$this->FIELDS_COUNTER = 0;
		$this->META_DATA = array();
		$this->row_data=array();
	} # __RESET_VARIABLES();
	
	/**
	* Move the query result into an array
	* Constraint - a query should have been done already!
	* @return array Array of the query result.
	*/
	function to_array($nested_array=true)
	{
		#$RESULTSET = $this->RESULTSET; # Bakcup, for safety
		$array = array();
		#print_r($this); die();
		while($this->next_record())
		{
			$array[] = ($this->FIELDS_COUNTER==1 && $nested_array==false)?$this->row_data[$this->FIELDS[0]]:$this->row_data;
		}
		#print_r($array); die();
		#$this->RESULTSET = $RESULTSET; # restore
		return($array);
	} # to_array();

	/**
	* Similar to to_array(), but with specified column head.
	* Warning: The column name must have unique values to prevent overwrting.
	*/
	function & lead($column_name='')
	{
		$array = array ( );
		while($this->next_record())
		{
			if(!empty($this->row_data[$column_name]))
			{
				$array [$this->row_data[$column_name]] = $this->row_data;
			}
		}
		return($array);
	}
	
	/**
	* Move the query result into an associative array.
	* Suggestion: SELECT ONLY TWO COLUMNS ONLY!
	* Constraint - a query should have been done already!
	* @return array Associative array of indices and vlaues.
	*/
	function to_association($index_column='', $value_column='')
	{
		$RESULTSET = $this->RESULTSET; # Bakcup, for safety
		$array = array();
		while($this->next_record())
		{
			$array[$this->row_data[$index_column]]=$this->row_data[$value_column];
		}
		$this->RESULTSET = $RESULTSET; # restore
		return($array);
	} # to_association()

	/**
	* Extract only one column in an array!
	* If value repleats, array will contain repeated value in its own.
	* Similar to TO_ARRAY() but takes only one column instead of all columns available
	*/
	function to_columnar_array($index_column='')
	{
		$RESULTSET = $this->RESULTSET; # Bakcup, for safety
		$array = array();
		while($this->next_record())
		{
			$array[]=$this->row_data[$index_column];
		}
		$this->RESULTSET = $RESULTSET; # restore
		return($array);
	} # to_columnar_array()

	/**
	* Find a row out of a query data generated.
	* Recommended to use a query resulting only one row.
	*/
	function count_records($sql='')
	{
		$row = array();

		$totals = array();
		
		# Case sensitive matching only!
		# Theme: Disable DELETE, UPDATE
		preg_match_all('/^[\ \n\r\t]*?SELECT(.*?)FROM(.*?)WHERE(.*?);$/s', $sql, $totals, PREG_PATTERN_ORDER);
		if(!count($totals[0]))
		{
			die("<pre>You can not make this function call: <strong>row()</strong> for the invalid SELECT operation in: <em>{$sql}</em></pre>");
		}

		if($RESULTSET = mysql_query($sql, $this->CONNECTION))
		{
			$row = mysql_fetch_array($RESULTSET, MYSQL_ASSOC);
		}
		return($row);
	} # count_records()
	
	/**
	* List of mysql tables in the connected database.
	*/
	function tables()
	{
		$tables = array();
		
		$tables_list = mysql_list_tables(MYSQL_DATABASENAME);
		#print_r($db_list); die();
		$num_rows = mysql_num_rows($tables_list);
		for ($i=0; $i<$num_rows; $i++)
		{
			$tables[] = mysql_tablename($tables_list, $i);
		}
		return($tables);
	}
	
	/**
	* Terminates any existing connections and data.
	*/
	function __destruct()
	{
		return(false);

		# Gives a problem, with [session] class and other inherited class, if the following code executes.
		# So, leave as it is.
		global $dbinfo;
		@mysql_free_result($this->RESULTSET);
		if($this->CONNECTION)
			@mysql_close($this->CONNECTION);
		if($dbinfo[$dbinfo['dbindex']]['dbcon'])
		{
			@mysql_close($dbinfo[$dbinfo['dbindex']]['dbcon']);
			unset($dbinfo);
		}
	} # __desctruct()

} # class mysql
?>
Return current item: MySQL access class