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 classSo any help is appreciated. It's working for now, but i didn't include any error handling and there's still some bugs....
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..