Page 1 of 1

Dragon of a class... [solved]

Posted: Wed Mar 01, 2006 12:13 pm
by dreamline
K guyz,
I'm trying to create a class that contains all the possible queries on my database. The result below is what's part of my idea... If I wanna start a query then first I need to know what relations there are between the several tables. Usually if you join 2 tables or more then you need to set table relations.

For instance: select * from site_songs sng, site_users usr, site_categories cat where sng.useridnumber = usr.user_idnumber and sng.category = cat.category. Well i'm sure you catch my drift. Basically what i wanted to write is a class that does create these relationships automatically when i call certain tables.

Anyways here's how i done it, but it's become a huge dragon with firespitting and all.. So any ideas would be great, especially when there's a way to write it with more simplicity.. Anyways here we go:

Code: Select all

$MyQuery=new Sql_query();
$MyQuery->$query_Tables(array('users','songs','subcategories','comments'));






class Sql_query
	{
	//Table aliases
	var $alias_user				='usr';
	var $alias_song				='sng';
	var $alias_category			='cat';
	var $alias_subcategory		='sub';
	var $alias_comment			='cmt';
//-------------------------------------------------------------------------------------------------------------------
	function query_Tables($tables_to_use=array('user'))
		{
		$tables=array	(	
							'users' 		=>	array	(	'table_name'		=> USER_TABLE
															,'table_alias' 		=> array('prim' => $this->alias_user)
															,'table_relations'	=> array(	'songs'	=> 'idnummer'
																							,'comments'	=> 'idnummer'
																						)
														)                   	
							,'songs'		=>	array	(	'table_name'		=> USER_SONGS_TABLE
															,'table_alias'		=> array('prim' => $this->alias_song)
															,'table_relations'	=> array(	'users'			=> 'idnummer'
																							,'comments'		=> 'nummer'
																							,'categories' 	=> 'categorie'
																							,'subcategories'=> array('categorie','subcategorie')
																						)
														)                   	

												)
                                                                            	
														)
							,'categories'	=>	array	(	'table_name'		=> SONG_CATEGORY_TABLE
															,'table_alias'		=> array('prim' => $this->alias_category)
															,'table_relations'	=> array(	'categories' => 'categorie'
																							,'songs' => 'categorie'
																						)
														)
							,'subcategories'=>	array	(	'table_name'		=> SONG_SUBCATEGORY_TABLE
															,'table_alias'		=> array('prim' 			=> $this->alias_subcategory)
															,'optional_tables'	=> array('songs','categories')
															,'mandatory_fields'	=> array(	'songs'			=> array('categorie','subcategorie')
																							,'categories'	=> 'categorie'
																						)
														)
							,'comments'		=>	array	(	'table_name'		=> USER_COMMENTS_TABLE
															,'table_alias' 		=>array('prim' => $this->alias_comment)
															,'mandatory_tables'	=> array('users','songs')
															,'mandatory_fields'	=> array(	'users' => 'idnummer'
																							,'songs' => 'nummer'
																						)
														)
						);
//----------------------------
	foreach ($tables_to_Use as $i => $value)
		{
		//-----
		$table_name				=$tables[$value]['table_name'];
		$table_alias			=$tables[$value]['table_alias']['prim'];
		$db_table_name			=$table_name . ' ' . $table_alias;
		//------
		$from_tables[]=$db_table_name;	//Contains the real table names and aliases!
		if ((isset($tables[$value]['table_relations'])) && ($i>0))
			{
			//If table_relations exists and $i > 0 then you are able to set table relations directly
			$where_clause[]=$this->create_Standardrelation($value,$from_tables,$tables);
			}
			else
			{
			if (isset($tables[$value]['optional_tables']))
				{
				$where_clause[]=$this->check_OptionalTables($value,$from_tables,$tables);

				}
				else
				{
				if (isset($tables[$value]['mandatory_tables']))
					{
					$this->check_MandatoryTables($value,$from_tables,$tables);
					}
				}
			}
		}
	//Create full select * from clause
	$full_from_clause='select * from ' . implode(',',$from_tables);
	//Create full whee clause
	$full_where_clause=' where ' . implode(' and ',$where_clause);
	//Print full sql query
	print_r($full_from_clause .'<br>'. $full_where_clause);
	}
//-------------------------------------------------------------------------------------------------------------------
function create_Standardrelation($value,$from_tables,$tables)
	{
	foreach ($tables[$value]['table_relations'] as $table_id => $fieldname)
		{
		$table_alias	=$tables[$table_id]['table_alias']['prim'];
		$db_table_name	=$tables[$table_id]['table_name'];
		$db_table_full	=$db_table_name . ' ' . $table_alias;
		//Check if $db_table_full is in array $from_tables
		if (in_array($db_table_full,$from_tables)==true)
			{
			//Table has been found so start creating the table relations
			if ($table_id != $value)
				{
				//$table_id != $value not implemented yet, but if you use the same table inside itself this will exclude a relation
				//For instance if you have the table array songs and you add 'songs' to the table_relations then relations to itself will be excluded
				$array_field_alias	=$table_alias;
				if (isset($tables[$table_id]['table_relations'][$value]))
					{
					//Compose the relation field before the =
					$array_field_name	=$tables[$table_id]['table_relations'][$value];
					//Compose the relation field after the =
					$relational_field_alias	=$tables[$value]['table_alias']['prim'];
					$relational_field_name	=$tables[$value]['table_relations'][$table_id];
					//Return the complete relation
					return $array_field_alias.'.'.$array_field_name.'='.$relational_field_alias.'.'.$relational_field_name;
					}
				}
			}
		}
	}
//-------------------------------------------------------------------------------------------------------------------
function check_MandatoryTables($value,$from_tables,$tables)
	{
                //This function checks if all of the tables mentioned in 'mandatory_tables' exists in the from_tables array. Not all are needed
	foreach ($tables[$value]['mandatory_tables'] as $table_id)
		{
		$table_alias	=$tables[$table_id]['table_alias']['prim'];
		$db_table_name	=$tables[$table_id]['table_name'];
		$db_table_full	=$db_table_name . ' ' . $table_alias;
		//Check if $db_table_full is in array $from_tables
		if (in_array($db_table_full,$from_tables)==true)
			{
			$array_field_alias		=$table_alias;
			$array_field_name		=$tables[$table_id]['table_relations'][$value];
			$relational_field_alias	=$tables[$value]['table_alias']['prim'];
			$relational_field_name	=$tables[$value]['mandatory_fields'][$table_id];
			$return_value[]=$array_field_alias . '.' . $array_field_name . ' = ' . $relational_field_alias . '.' . $relational_field_name;
			}
		return implode(',',$return_value);
		}
	
	}
//-------------------------------------------------------------------------------------------------------------------
function check_OptionalTables($value,$from_tables,$tables)
	{
                //This function checks if one of the tables mentioned in 'optional_tables' exists. Not all are needed!
	$count=count($tables[$value]['optional_tables']);
	for ($i=0 ; $i < $count ; $i++)
		{
		$table_name		=$tables[$value]['optional_tables'][$i];
		$table_alias	=$tables[$table_name]['table_alias']['prim'];
		$db_table_name	=$tables[$table_name]['table_name'];
		$mandatory_table=$db_table_name . ' ' . $table_alias;
		if (in_array($mandatory_table,$from_tables))
			{
			$count_fields=count($tables[$value]['mandatory_fields'][$table_name]);
			$array_field_alias	=$tables[$table_name]['table_alias']['prim'];
			for ($f=0;$f<$count;$f++)
				{
				$array_field_name		=$tables[$table_name]['table_relations'][$value][$f];
				
				$relational_field_alias	=$tables[$value]['table_alias']['prim'];
				$relational_field_name	=$tables[$value]['mandatory_fields'][$table_name][$f];
				
				$return_value[]=$array_field_alias . '.' . $array_field_name . ' = ' . $relational_field_alias . '.' . $relational_field_name;
				
				}
			return implode(' and ' , $return_value);
			}
		}
	}
//-------------------------------------------------------------------------------------------------------------------
} //End class
As you can see it's not perfect and you have to start the function with the table parameters in the right order otherwise the checks for mandatory_tables and optional_tables go wrong, but i'm sure you get an idea... :D

So any help is appreciated. It's working for now, but i didn't include any error handling and there's still some bugs.... :) But i basically would like to know if there's a more simple way to do the exact same thing....


Edit: K, i think I got it... Basically reprogrammed the complete class and sofar it's working.. It's not the best method, but it's better than the class i posted here.. Hopefully this saves me a lot of time generating table relations.. :D I even included error handling now.. LOL