MySQL Dataset

<?php
//+---------------------------------------------------------------------------------------------------------------------+
//| Module name: dataset.class.php																							|
//|	Description:  A PHP class that stores the database table(s) in MySQL database into 								|
//				  temporary database/tables as dataset in the 3D array form												|																			|
//+---------------------------------------------------------------------------------------------------------------------+
//| Author:			Theo Sumual ([email protected])																							|
//| Date written:	28/08/2003																							|
//|	Version:		1.0.0																								|
//+---------------------------------------------------------------------------------------------------------------------+																		
/*
**	Last Modified		Modified By		Description
**  30/08/2003			Theo			add search functions
**	30/08/2003			Theo			add get_queryset function
**  31/08/2003			Theo			add indexing features for the dataset
** 
** 
*/
define("INDEX_NUM",0);
define("INDEX_KEY",1);
define("NO_QUERY",'');
define("ALL_TABLES",'All');
class Dataset
{
	
	var $classname = "Dataset";
	var $dataset = array(); //The array of arrays of arrays :)  
	var $db_tables = array(); 
	var $dbuser;
	var $dbpassword;
	var $dbhost;
	var $dbname;
	var $dbconn;
	var $error;
	function Dataset($dbHost,$dbUser,$dbPassword,$dbName)
	{
		$this->dbhost = $dbHost;
		$this->dbuser = $dbUser;
		$this->dbpassword = $dbPassword;
		$this->dbname = $dbName;
		
		
		if(!isset($db_tables) || count($db_tables) < 1)
		{
			$this->open_db();
			$this->get_database_tables();
			$this->close_db();
		}
		$this->clear_dataset();
	}
	
	//open a database connection
	function open_db()
	{
		$this->dbconn = mysql_connect($this->dbhost,$this->dbuser,$this->dbpassword) or $this->catch_error("Database Connection Error: ",mysql_error());
		mysql_select_db($this->dbname,$this->dbconn);
	}
	
	//close database connecttion
	function close_db()
	{
		if(!mysql_close($this->dbconn))
		{
			$this->catch_error("Close Database Connection Error: ",mysql_error());
		}
	}
	
	//remove all elements in dataset array
	function clear_dataset()
	{
		array_splice($this->dataset,0,count($this->dataset));
	}
	
	//get all the table names in the database and store in the db_tables array
	function get_database_tables()
	{
		$tables = mysql_list_tables($this->dbname,$this->dbconn) or die("Database Retrieve Error: ".mysql_error());
						
		for($i=0;$i<mysql_num_rows($tables);$i++)
		{
			$this->db_tables[$i] = strtoupper(mysql_tablename($tables,$i));
		}
		mysql_free_result($tables);
	}
	
	function get_dataset()
	{
		return $this->dataset;
	}
	//recreate the dataset array and return the dataset array
	function set_dataset($dbTable=ALL_TABLE,$tableIndex=INDEX_KEY,$rowIndex=INDEX_KEY,$fieldIndex=INDEX_KEY)
	{
		//$this->clear_dataset();
		$this->construct_dataset($dbTable,NO_QUERY,$tableIndex,$rowIndex,$fieldIndex);
		
		if(is_array($this->dataset)) //check if the dataset constructed correctly
			return true;
		else
			return false;
	}
	
	//recreate the dataset and only contains one table (dataset[tablename])
	//as a result of the specified query
	function set_queryset($dbTable,$dbQuery,$tableIndex=INDEX_KEY,$rowIndex=INDEX_KEY,$fieldIndex=INDEX_KEY)
	{
		//$this->clear_dataset();
		$this->construct_dataset($dbTable,$dbQuery,$tableIndex,$rowIndex,$fieldIndex);
		
		if(is_array($this->dataset)) //check if the dataset constructed correctly
			return true;
		else
			return false;
	}
	
	//update the current table in data set
	//separate the fields and value to update by ,
	function update_record($tableName,$recordId,$recordFields,$recordValues)
	{
		$f_array = explode(',',$recordFields);
		$v_array = explode(',',$recordValues);
		
		if(count($f_array) != count($v_array))
			$this->catch_error("Number of values doesn't match number of fields");
			
		for($i=0;$i<count($f_array);$i++)
		{
			$fieldName = $f_array[$i];
			$this->dataset[$tableName][$recordId][$fieldName] = $v_array[$i];
		}
		//return $this->dataset[$tableName][$recordId][$fieldName];
	}
	
	//construct the dataset as associative 3D array by default or numeric indexed if the $indexType is specified
	//dataset[table][row][field]
	//table = name of the retrieved tables in database or table index in dataset
	//row = concatenated primary key for each row of table start with '_' or row number in the table
	//field = column names of the table or column number in the row
	
	//$foo = $dataset['FooTable']['_Foo01]['fooName']; ==> returns the value in the FooTable at row index _Foo01 in field fooName
	//$foo = $dataset['FooTable']['_Foo01']; ==> returns associative 1D array where columns name in FooTable as index
	//$foo = $dataset['FooTable']; ==> returns associative 2D array where concatenated primary key of the FooTable as index
	//$foo = $dataset; ==> returns associative 3D array where tablename as index
	function construct_dataset($dbTable=ALL_TABLE,$dbQuery=NO_QUERY,$tableIndex=INDEX_KEY,$rowIndex=INDEX_KEY,$fieldIndex=INDEX_KEY)
	{
		$this->open_db();
		if($dbTable == ALL_TABLE)
		{
			$this->construct_all_table($tableIndex,$rowIndex,$fieldIndex);
			$this->close_db();
			return $this->dataset;
			exit;
		}
		else if($dbTable != ALL_TABLE && strpos($dbTable,',') !== false)
		{
			$this->construct_multi_table($dbTable,$tableIndex,$rowIndex,$fieldIndex);
			$this->close_db();
			return $this->dataset;
			exit;
		}
		else if(is_array($dbTable))
		{
			foreach($dbTable as $table_name => $table_query)
			{
				$data_table = $this->construct_table($table_name,$table_query,$rowIndex,$fieldIndex);
				if(is_array($data_table))//check if the $data_table is an array
				{
					if($tableIndex == INDEX_KEY)
						$this->dataset[$table_name] = $data_table;
					else if($tableIndex == INDEX_NUM)
						array_push($this->dataset,$data_table);
				}
				else
				{
					$this->close_db();
					$this->catch_error("Construct Dataset ERROR $dbTable is not Exists",mysql_error());
					exit;
				}
			}
			$this->close_db();
			return $this->dataset;
			exit;
		}
		else if($dbTable != ALL_TABLE && $dbQuery != NO_QUERY)
		{
			$data_table = (strpos($dbQuery,"SELECT",0) !== false)? $this->construct_query_table($dbQuery,INDEX_NUM,$fieldIndex): 
															  $this->construct_table($dbTable,$dbQuery,$rowIndex,$fieldIndex);
			if(is_array($data_table))//check if the $data_table is an array
			{
				if($tableIndex == INDEX_KEY)
					$this->dataset[$dbTable] = $data_table;
				else if($tableIndex == INDEX_NUM)
					array_push($this->dataset,$data_table);
				$this->close_db();
				return $this->dataset;
				
			}
			else
			{
				$this->catch_error("Construct Dataset ERROR $dbTable is not Exists",mysql_error());
			}
			exit;
		}
		else if($dbTable != ALL_TABLE)
		{
			$data_table = $this->construct_table($dbTable,NO_QUERY,$rowIndex,$fieldIndex);
			if(is_array($data_table))//check if the $data_table is return an array
			{
				if($tableIndex == INDEX_KEY)
					$this->dataset[$dbTable] = $data_table;
				else if($tableIndex == INDEX_NUM)
					array_push($this->dataset,$data_table);
				$this->close_db();
				return $this->dataset;
				exit;
			}
			else
			{
				$this->catch_error("Construct Dataset ERROR: Table $dbTable doesn't exists",mysql_error());
			}
		}
		else
		{
			$this->close_db();
			$this->catch_error("Construct Dataset ERROR",mysql_error());
		}
	}
	
	//construct all the tables in database into dataset array
	function construct_all_table($tableIndex=INDEX_KEY,$rowIndex=INDEX_KEY,$fieldIndex=INDEX_KEY)
	{
		$tables = mysql_list_tables($this->dbname,$this->dbconn) or $this->catch_error("Tables Retrieve Error: ",mysql_error());
						
		for($i=0;$i<mysql_num_rows($tables);$i++)
		{
			$table_name = mysql_tablename($tables,$i);
			$data_table = $this->construct_table($table_name,NO_QUERY,$rowIndex,$fieldIndex);
			
			if(is_array($data_table))
			{
				if($tableIndex == INDEX_KEY)
					$this->dataset[$table_name] = $data_table;
				else if($tableIndex == INDEX_NUM)
					array_push($this->dataset,$data_table);
			}
		}
		mysql_free_result($tables);
	}
	
	//construct specified tables ( >1 table) from database into dataset
	function construct_multi_table($tableNames,$tableIndex=INDEX_KEY,$rowIndex=INDEX_KEY,$fieldIndex=INDEX_KEY)
	{
		$tables = explode(',',$tableNames);
		for($i=0;$i<count($tables);$i++)
		{
			$table_name = $tables[$i];
			
			$data_table = (array_key_exists($table_name,$this->dataset))? $this->dataset[$table_name] : $this->construct_table($table_name,NO_QUERY,$rowIndex,$fieldIndex);
			
			if(is_array($data_table))
			{
				if($tableIndex == INDEX_KEY)
				{
					$this->dataset[$table_name] = $data_table;
				}
				else if($tableIndex == INDEX_NUM)
					array_push($this->dataset,$data_table);
				
			}
		}
	}
	
	//construct 1 table only into dataset with specified query
	//$tableName = table name
	//$queryRecord = WHERE condition (eg. id=001, name='Foo', name='Foo' OR name='otherFoo')
	function construct_table($tableName,$queryRecord=NO_QUERY,$rowIndex=INDEX_KEY,$fieldIndex=INDEX_KEY)
	{
		//check if the $tableName exists in the database
		if(!in_array(strtoupper($tableName),$this->db_tables))
			return -1;
		
		$table = array();
		$result = array();
		$fields = mysql_list_fields($this->dbname,$tableName,$this->dbconn) or $this->catch_error("$tableName Table Retrieve Fields Error: ",mysql_error());
		$query = ($queryRecord == NO_QUERY)? "SELECT * FROM `".$tableName."` ORDER BY ".mysql_field_name($fields,0): 
											"SELECT * FROM `".$tableName."` WHERE ".$queryRecord." ORDER BY ".mysql_field_name($fields,0);
		$rows = mysql_query($query,$this->dbconn) or $this->catch_error("$tableName Table Retrieve Records Error: ",mysql_error());
			
		while($record = mysql_fetch_array($rows,MYSQL_ASSOC))
		{
			//store each fields value in array, field name as array key
			
			for($i=0;$i<mysql_num_fields($rows);$i++)
			{
				//check if the field is primary key
				$field_info = mysql_fetch_field($rows,$i);
				$primary_key = $field_info->primary_key;
				if($primary_key)
				{
					$array_key .= '_'.$record[$field_info->name];
				}
				//echo('field name '.$field_info->name.' is primary key: '.$field_info->primary_key.'<br>');
				if($fieldIndex == INDEX_KEY)
					$result[$field_info->name] = $record[$field_info->name];
				else if($fieldIndex == INDEX_NUM)
					$result[$i] = $record[$field_info->name];
			}
			
			if($rowIndex == INDEX_KEY)
				$table[$array_key] = $result;
			else if($rowIndex == INDEX_NUM)
				array_push($table,$result);
				
				
				
			$array_key = '';
		}
		mysql_free_result($fields);
		mysql_free_result($rows);
		
		return $table;
	}
	
	//construct a numeric index table array for more advance query
	//eg. query for more than one table in the database
	function construct_query_table($queryRecord,$rowIndex=INDEX_NUM,$fieldIndex=INDEX_KEY)
	{
		$table = array();
		$result = array();
		$rows = mysql_query($queryRecord,$this->dbconn) or die("Table Query Error: $queryRecord ".mysql_error());
				
		while($record = mysql_fetch_array($rows))
		{
			//store each fields value in array, field name as array key
			
			for($i=0;$i<mysql_num_fields($rows);$i++)
			{
				//check if the field is primary key
				$field_info = mysql_fetch_field($rows,$i);
				$primary_key = $field_info->primary_key;
				if($primary_key)
				{
					$array_key .= '_'.$record[$field_info->name];
				}
				
				if($fieldIndex == INDEX_KEY)
					$result[mysql_field_name($rows,$i)] = $record[mysql_field_name($rows,$i)];
				else if($fieldIndex == INDEX_NUM)
					$result[$i] = $record[mysql_field_name($rows,$i)];
			}
			
			array_push($table,$result);
				
				
			$array_key = '';
		}
		mysql_free_result($rows);
		
		return $table;

	}
	
	//search in specified dataset table and return as numeric indexing 2D array
	function search_record($dataSetTable,$keyWord,$fieldName='',$fieldJoins='')
	{
		return ($fieldName == '')? $this->search_all_field($dataSetTable,$keyWord) : $this->search_in_field($dataSetTable,$keyWord,$fieldName,$fieldJoins);
	}
	
	function search_all_field($dataSetTable,$keyWord)
	{
		$found = array();
		$key_word_match = false;
		$num_of_match = 0;
		foreach($dataSetTable as $key => $record)
		{
			foreach($record as $field => $value)
			{
				if(strpos($value,$keyWord,0) !== false)
				{
					//$match++;
					$key_word_match = true;
				}
			}
	
			if($key_word_match == true)
			{
				$found[$num_of_match] = $record;
				$num_of_match++;
				
			}
			$key_word_match = false;
		}
		
		if($num_of_match > 0)
		{
			return $found;
		}
		else
		{
			return -1;
		}
	}
		
	function search_in_field($dataSetTable,$keyWord,$fieldName,$fieldJoins='')
	{
		$k_word_array = explode(',',$keyWord);
		$f_name_array = explode(',',$fieldName);
		if(count($k_word_array) != count($f_name_array))
			$this->catch_error("Number of keywords doesn't match number of fields");
		
		$found = array();
		$key_word_match = false;
		$num_of_match = 0;
		
		for($i=0;$i<count($k_word_array);$i++)
		{
			foreach($dataSetTable as $key => $record)
			{
				//$value = $record[$f_name_array[$i]];
				switch($fieldJoins)
				{
					default:
						if(strpos($record[$f_name_array[$i]],$k_word_array[$i],0) !== false)
						{
							$key_word_match = true;
						}
					break;
					case '':
						if(strpos($record[$f_name_array[$i]],$k_word_array[$i],0) !== false)
						{
							$key_word_match = true;
						}
					break;
					case 'OR':
						if(strpos($record[$f_name_array[$i]],$k_word_array[$i],0) !== false || 
							strpos($record[$f_name_array[$i+1]],$k_word_array[$i+1],0) !== false)
						{
						//$match++;
							$key_word_match = true;
						}
					break;
					case 'AND':
						if(strpos($record[$f_name_array[$i]],$k_word_array[$i],0) !== false && 
							strpos($record[$f_name_array[$i+1]],$k_word_array[$i+1],0) !== false)
						{
						//$match++;
							$key_word_match = true;
						}
					break;
					case 'NOT':
						if(strpos($record[$f_name_array[$i]],$k_word_array[$i],0) !== false && 
							strpos($record[$f_name_array[$i+1]],$k_word_array[$i+1],0) === false)
						{
						//$match++;
							$key_word_match = true;
						}
					break;
				}
				
				if($key_word_match == true)
				{
					$found[$num_of_match] = $record;
					$num_of_match++;
				}
				$key_word_match = false;
			}
		}
		
		if($num_of_match > 0)
		{
			return $found;
		}
		else
		{
			return -1;
		}

	}
	
	
	//search in current dataset and return as 2D array
	function search($keyWord,$tableName=ALL_TABLES,$tableField='',$rowIndex=INDEX_NUM)
	{
		if($tableName == ALL_TABLES)
		{
			return $this->search_in_dataset($keyWord);
			exit;
		}
		else if($tableName != ALL_TABLES)
		{
			if($tableField == '')
			{
				return $this->search_in_table($keyWord,$tableName);
				exit;
			}
			else
			{
				return $this->search_in_table_field($keyWord,$tableName,$tableField);
				exit;
			}
		}
		else
		{
			$this->catch_error("Table $tableName doesn't exists in dataset");
		}
	}
	
	function search_in_dataset($keyWord,$rowIndex)
	{
		$found = array();
		$key_word_match = false;
		$num_of_match = 0;
		foreach($this->dataset as $table_name => $table)
		{
			foreach($table as $key => $record)
			{
				foreach($record as $field => $value)
				{
					if(strpos($value,$keyWord,0) !== false)
					{
						$key_word_match = true;
						
					}
				}
				
				if($key_word_match == true)
				{
					if($rowIndex == INDEX_KEY)
						$found[$key] = $this->dataset[$table_name][$key];
					else if($rowIndex == INDEX_NUM)
						$found[$num_of_match] = $this->dataset[$table_name][$key];
					
					$num_of_match++;
				}
				//reset to matched key word to false
				$key_word_match = false;
			}
		}
		
		if($num_of_match > 0)
		{
			return $found;
		}
		else
		{
			return -1;
		}
	}
	
	function search_in_table($keyWord,$tableName,$rowIndex)
	{
		$found = array();
		$key_word_match = false;
		$num_of_match = 0;
		foreach($this->dataset[$tableName] as $key => $record)
		{
			foreach($record as $field => $value)
			{
				if(strpos($value,$keyWord,0) !== false)
				{
					$key_word_match = true;
					
				}
			}
			if($key_word_match == true)
			{
				if($rowIndex == INDEX_KEY)
					$found[$key] = $this->dataset[$table_name][$key];
				else if($rowIndex == INDEX_NUM)
					$found[$num_of_match] = $this->dataset[$table_name][$key];
				
				$num_of_match++;
			}
			//reset to matched key word to false
			$key_word_match = false;
		}
		
		if($num_of_match > 0)
		{
			return $found;
		}
		else
		{
			return -1;
		}
	}
	
	function search_in_table_field($keyWord,$tableName,$tableField,$rowIndex)
	{
		$found = array();
		$key_word_match = false;
		$num_of_match = 0;
		
		foreach($this->dataset[$tableName] as $key => $record)
		{
			if(strpos($record[$tableField],$keyWord,0) !== false)
			{
				$key_word_match = true;
			}
			
			if($key_word_match == true)
			{
				if($rowIndex == INDEX_KEY)
					$found[$key] = $this->dataset[$table_name][$key];
				else if($rowIndex == INDEX_NUM)
					$found[$num_of_match] = $this->dataset[$table_name][$key];

				$num_of_match++;
			}
			//reset to matched key word to false
			$key_word_match = false;
		}
		
		if($num_of_match > 0)
		{
			return $found;
		}
		else
		{
			return -1;
		}
	}
	
	function catch_error($errorMsg,$sqlError='')
	{
		$this->error = "$errorMsg $sqlError";
		echo $this->error;
		exit;
	}
}
?>
<?php

//Some examples how to use the class :)
/*
$ds = new Dataset('your database host','database username','database password','database name');
$dataset = ($ds->set_dataset('Foo_Table',INDEX_NUM,INDEX_NUM,INDEX_NUM))? $ds->get_dataset() : null;
echo '$dataset[0][0][0]='.$dataset[0][0][0].'<br>';

$dataset= ($ds->set_dataset('Foo_Table',INDEX_NUM,INDEX_NUM,INDEX_KEY))? $ds->get_dataset() : null;
echo '$dataset[0][0][foo_name]='.$dataset[0][0]['foo_name'].'<br>';

$dataset= ($ds->set_dataset('Foo_Table',INDEX_NUM,INDEX_KEY,INDEX_NUM))? $ds->get_dataset() : null;
echo '$dataset[0][_foo01][0]='.$dataset[0]['_foo01'][0].'<br>';

$dataset= ($ds->set_dataset('Foo_Table',INDEX_NUM,INDEX_KEY,INDEX_KEY))? $ds->get_dataset() : null;
echo '$dataset[0][_foo01][foo_name]='.$dataset[0]['_foo01']['foo_name'].'<br>';

$dataset= ($ds->set_dataset('Foo_Table',INDEX_KEY,INDEX_NUM,INDEX_NUM))? $ds->get_dataset() : null;
echo '$indexset[Foo_Table][0][0]='.$dataset['Foo_Table'][0][0].'<br>';

$dataset= ($ds->set_dataset('Foo_Table',INDEX_KEY,INDEX_NUM,INDEX_KEY))? $ds->get_dataset() : null;
echo '$dataset[Foo_Table][0][mach_id]='.$dataset['Foo_Table'][0]['foo_name'].'<br>';

$dataset= ($ds->set_dataset('Foo_Table',INDEX_KEY,INDEX_KEY,INDEX_NUM))? $ds->get_dataset() : null;
echo '$dataset[Foo_Table][_foo01][0]='.$dataset['Foo_Table']['_foo01'][0].'<br>';

$dataset= ($ds->set_dataset('Foo_Table',INDEX_KEY,INDEX_KEY,INDEX_KEY))? $ds->get_dataset() : null;
echo '$dataset[Foo_Table][_foo01][mach_id]='.$dataset['Foo_Table']['_foo01']['foo_name'].'<br>';

$multitable1 = ($ds->set_dataset('Table1,Table2',INDEX_KEY,INDEX_NUM,INDEX_NUM))? $ds->get_dataset() : null;
$multitable2 = ($ds->set_dataset(array('Table1'=>'id=10','Table2'=>'id=10'),INDEX_KEY,INDEX_NUM,INDEX_NUM))? $ds->get_dataset() : null;

//Queries for one table
//the table name must exists in database
$queryset1 = ($ds->set_queryset('Table1',"id = 10"))? $ds->get_dataset() : null;
for($i=0;$i<count($queryset['Table1']);$i++)
{
	foreach($queryset['Table1'][$i] as $field => $value)
	{
		echo $field.'='.$value.'&nbsp;';
	}
	echo '<br>';
}

//Queries for multiple tables
$query = "SELECT * FROM Table1,Table2,Table3 WHERE Table2.id = Table1.id";
//set the query result anyname you like to store it in dataset array
$queryset2 = ($ds->set_queryset('QueryTable',$query,INDEX_KEY))? $ds->get_dataset() : null;
for($i=0;$i<count($queryset['QueryTable']);$i++)
{
	foreach($queryset['QueryTable'][$i] as $field => $value)
	{
		echo $field.'='.$value.'&nbsp;';
	}
	echo '<br>';
}

$ds2 = new Dataset('your database host','database username','database password','database name');
$dataset2 = ($ds->set_dataset(ALL_TABLES))? $ds->get_dataset() : null;

//Search in current dataset
$search_result1 = $ds2->search("Theo");
$search_result2 = $ds2->search("Theo","Table1");
$search_result3 = $ds2->search("Theo",'Table1',"name");
for($i=0;$i<count($search_result3);$i++)
{
	foreach($search_result3[$i] as $field => $value)
	{
		echo $value;
	}
}

//Search by giving the table array
$search_result4 = $ds2->search_record($dataset2['Table1'],"Theo");
$search_result5 = $ds2->search_record($dataset2['Table1'],"Theo","name");
$search_result6 = $ds2->search_record($dataset2['Table1'],"10,Theo","id,name",'AND');
$search_result7 = $ds2->search_record($dataset2['Table1'],"10,Theo","id,name",'OR');

for($i=0;$i<count($search_result5);$i++)
{
	foreach($search_result5[$i] as $field => $value)
	{
		echo $value;
	}
}
*/

?>