Location: PHPKode > scripts > MyLibSQLGen > mylibsqlgen/class_mylib_SQLGen-1.0.php
<?php
/*
Created By		: Fadjar Nurswanto <hide@address.com>
DATE			: 2006-08-02
PRODUCTNAME		: class MyLibSQLGen
PRODUCTVERSION	: 1.0.0
DESCRIPTION		: class yang berfungsi untuk menggenerate SQL
DENPENCIES		:
*/
class MyLibSQLGen
{
	var $Result;
	var $Tables=Array();
	var $Values=Array();
	var $Fields=Array();
	var $Conditions=Array();
	var $Condition;
	var $LeftJoin=Array();
	var $Search;
	var $Sort="ASC";
	var $Order;
	var $Error;

	function MyLibSQLGen(){}
	function BuildCondition()
	{
		$funct="BuildCondition";
		$className=get_class($this);
		$conditions=$this->getConditions();
		if(!$conditions){$this->dbgDone($funct);return true;}
		if(!is_array($conditions))
		{
			$this->Error="$className::$funct \nVariable conditions not Array";
			return;
		}
		for($i=0;$i<count($conditions);$i++)
		{
			$this->Condition.=$conditions[$i]["condition"]." ".$conditions[$i]["connection"]." ";
		}
		return true;
	}
	function BuildLeftJoin()
	{
		$funct="BuildLeftJoin";
		$className=get_class($this);
		if(!$this->getLeftJoin()){$this->Error="$className::$funct \nProperty LeftJoin was empty";return;}

		$LeftJoinVars=$this->getLeftJoin();

		$hasil=false;
		foreach($LeftJoinVars as $LeftJoinVar)
		{
			@$hasil.=" LEFT JOIN ".$LeftJoinVar["table"];
			foreach($LeftJoinVar["on"] as $var)
			{
				@$condvar.=$var["condition"]." ".$var["connection"]." ";
			}
			$hasil.=" ON (".$condvar.")";
			unset($condvar);
		}

		$this->ResultLeftJoin=$hasil;

		return true;
	}
	function BuildOrder()
	{
		$funct="BuildOrder";
		$className=get_class($this);
		if(!$this->getOrder()){$this->Error="$className::$funct \nProperty Order was empty";return;}
		if(!$this->getFields()){$this->Error="$className::$funct \nProperty Fields was empty";return;}

		$Fields=$this->getFields();
		$Orders=$this->getOrder();
		if(ereg(",",$Orders)){$Orders=explode(",",$Order);}
		if(!is_array($Orders)){$Orders=Array($Orders);}

		foreach($Orders as $Order)
		{
			if(!is_numeric($Order)){$this->Error="$className::$funct \nProperty Order not Numeric";return;}
			if($Order > count($this->Fields)){$this->Error="$className::$funct \nMax value of property Sort is ".count($this->Fields);return;}

			@$xorder.=$Fields[$Order].",";
		}

		$this->ResultOrder=" ORDER BY ".substr($xorder,0,-1);

		return true;
	}
	function BuildSearch()
	{
		$funct="BuildSearch";
		$className=get_class($this);

		if(!$this->getSearch()){$this->Error="$className::$funct \nProperty Search was empty";return;}
		if(!$this->getFields()){$this->Error="$className::$funct \nProperty Fields was empty";return;}

		$Fields=$this->getFields();
		$xvalue=$this->getSearch();

		if(is_array($xvalue))
		{
			foreach($Fields as $field)
			{
				if(@$xvalue[$field])
				{
					$Values=explode(" ",$xvalue[$field]);
					foreach($Values as $Value)
					{
						@$hasil.=$field." LIKE '%".$Value."%' OR ";
					}
					if($hasil)
					{
						@$hasil_final.="(".substr($hasil,0,-4).") AND ";
						unset($hasil);
					}
				}
			}
			$hasil=$hasil_final;
		}
		else
		{
			foreach($Fields as $field)
			{
				$Values=explode(" ",$xvalue);
				foreach($Values as $Value)
				{
					@$hasil.=$field." LIKE '%".$Value."%' OR ";
				}
			}
		}

		$this->ResultSearch=substr($hasil,0,-4);
		return true;
	}
	function clear_all_assign()
	{
		$this->Result=null;
		$this->ResultSearch=null;
		$this->ResultLeftJoin=null;
		$this->Result=null;
		$this->Tables=Array();
		$this->Values=Array();
		$this->Fields=Array();
		$this->Conditions=Array();
		$this->Condition=null;
		$this->LeftJoin=Array();
		$this->Sort="ASC";
		$this->Order=null;
		$this->Search=null;
		$this->fieldSQL=null;
		$this->valueSQL=null;
		$this->partSQL=null;
		$this->Error=null;
		return true;
	}
	function CombineFieldValue($manual=false)
	{
		$funct="CombineFieldsPostVar";
		$className=get_class($this);
		$fields=$this->getFields();
		$values=$this->getValues();
		if(!is_array($fields))
		{
			$this->Error="$className::$funct \nVariable fields not Array";
			return;
		}
		if(!is_array($values))
		{
			$this->Error="$className::$funct \nVariable values not Array";
			return;
		}
		if(count($fields)!=count($values))
		{
			$this->Error="$className::$funct \nCount of fields and values not match";
			return;
		}
		for($i=0;$i<count($fields);$i++)
		{
			@$this->fieldSQL.=$fields[$i].",";
			if($fields[$i] == "pwd" || $fields[$i] == "password" || $fields[$i] == "pwd")
			{
				@$this->valueSQL.="password('".$values[$i]."'),";
				@$this->partSQL.=$fields[$i]."=password('".$values[$i]."'),";
			}
			else
			{
				if(is_numeric($values[$i]))
				{
					@$this->valueSQL.=$values[$i].",";
					@$this->partSQL.=$fields[$i]."=".$values[$i].",";
				}
				else
				{
					@$this->valueSQL.="'".$values[$i]."',";
					@$this->partSQL.=$fields[$i]."='".$values[$i]."',";
				}
			}
		}
		$this->fieldSQL=substr($this->fieldSQL,0,-1);
		$this->valueSQL=substr($this->valueSQL,0,-1);
		$this->partSQL=substr($this->partSQL,0,-1);
		return true;
	}
	function getDeleteSQL()
	{
		$funct="getDeleteSQL";
		$className=get_class($this);
		$Tables=$this->getTables();
		if(!$Tables || !count($Tables))
		{
			$this->dbgFailed($funct);
			$this->Error="$className::$funct \nTable was empty";
			return;
		}
		for($i=0;$i<count($Tables);$i++)
		{
			@$Table.=$Tables[$i].",";
		}
		$Table=substr($Table,0,-1);

		$sql="DELETE FROM ".$Table;

		if($this->getConditions())
		{
			if(!$this->BuildCondition()){$this->dbgFailed($funct);return;}
			$sql.=" WHERE ".$this->getCondition();
		}
		$this->Result=$sql;
		return true;
	}
	function getInsertSQL()
	{
		$funct="getInsertSQL";
		$className=get_class($this);
		if(!$this->getValues()){$this->Error="$className::$funct \nProperty Values was empty";return;}
		if(!$this->getFields()){$this->Error="$className::$funct \nProperty Fields was empty";return;}
		if(!$this->getTables()){$this->Error="$className::$funct \nProperty Tables was empty";return;}

		if(!$this->CombineFieldValue()){$this->dbgFailed($funct);return;}
		$Tables=$this->getTables();

		$sql="INSERT INTO ".$Tables[0]." (".$this->fieldSQL.") VALUES (".$this->valueSQL.")";

		$this->Result=$sql;

		return true;
	}
	function getUpdateSQL()
	{
		$funct="getUpdateSQL";
		$className=get_class($this);

		if(!$this->getValues()){$this->Error="$className::$funct \nProperty Values was empty";return;}
		if(!$this->getFields()){$this->Error="$className::$funct \nProperty Fields was empty";return;}
		if(!$this->getTables()){$this->Error="$className::$funct \nProperty Tables was empty";return;}

		if(!$this->CombineFieldValue()){$this->dbgFailed($funct);return;}
		if(!$this->BuildCondition()){$this->dbgFailed($funct);return;}
		$Tables=$this->getTables();

		$sql="UPDATE ".$Tables[0]." SET ".$this->partSQL." WHERE ".$this->getCondition();

		$this->Result=$sql;

		return true;
	}
	function getQuerySQL()
	{
		$funct="getQuerySQL";
		$className=get_class($this);

		if(!$this->getFields()){$this->Error="$className::$funct \nProperty Fields was empty";return;}
		if(!$this->getTables()){$this->Error="$className::$funct \nProperty Tables was empty";return;}

		$Fields=$this->getFields();
		$Tables=$this->getTables();
		foreach($Fields as $Field){@$sql_raw.=$Field.",";}
		foreach($Tables as $Table){@$sql_table.=$Table.",";}

		$this->Result="SELECT ".substr($sql_raw,0,-1)." FROM ".substr($sql_table,0,-1);

		if($this->getLeftJoin())
		{
			if(!$this->BuildLeftJoins()){$this->dbgFailed($funct);return;}
			$this->Result.=" ".$this->ResultLeftJoin;
		}
		if($this->getConditions())
		{
			if(!$this->BuildCondition()){$this->dbgFailed($funct);return;}
			$this->Result.=" WHERE (".$this->Condition.") ";
		}
		if($this->getSearch())
		{
			if(!$this->BuildSearch()){$this->dbgFailed($funct);return;}
			if($this->ResultSearch)
			{
				if(eregi("WHERE",$this->Result)){$this->Result.=" AND ".$this->ResultSearch;}
				else{$this->Result.=" WHERE ".$this->ResultSearch;}
			}
		}
		if($this->getOrder())
		{
			if(!$this->BuildOrder()){$this->dbgFailed($funct);return;}
			$this->Result.=" ".$this->ResultOrder;
		}
		if($this->getSort())
		{
			if(@$this->ResultOrder)
			{
				$this->Result.=" ".$this->getSort();
			}
		}

		return true;
	}

	function getCondition(){return @$this->Condition;}
	function getConditions(){if(count(@$this->Conditions) && is_array(@$this->Conditions)){return @$this->Conditions;}}
	function getFields(){if(count(@$this->Fields) && is_array(@$this->Fields)){return @$this->Fields;}}
	function getLeftJoin(){if(count(@$this->LeftJoin) && is_array(@$this->LeftJoin)){return @$this->LeftJoin;}}
	function getOrder(){return @$this->Order;}
	function getSearch(){return @$this->Search;}
	function getSort(){return @$this->Sort;}
	function getTables(){if(count(@$this->Tables) && is_array(@$this->Tables)){return @$this->Tables;}}
	function getValues(){if(count(@$this->Values) && is_array(@$this->Values)){return @$this->Values;}}

	function setCondition($input){$this->Condition=$input;}
	function setConditions($input)
	{
		if(is_array($input)){$this->Conditions=$input;}
		else{$this->Error=get_class($this)."::setConditions \nParameter input not array";return;}
	}
	function setFields($input)
	{
		if(is_array($input)){$this->Fields=$input;}
		else{$this->Error=get_class($this)."::setFields \nParameter input not array";return;}
	}
	function setLeftJoin($input)
	{
		if(is_array($input)){$this->LeftJoin=$input;}
		else{$this->Error=get_class($this)."::setFields \nParameter input not array";return;}
	}
	function setOrder($input){$this->Order=$input;}
	function setSearch($input){$this->Search=$input;}
	function setSort($input){$this->Sort=$input;}
	function setTables($input)
	{
		if(is_array($input)){$this->Tables=$input;}
		else{$this->Error=get_class($this)."::setTables \nParameter input not array";return;}
	}
	function setValues($input)
	{
		if(is_array($input)){$this->Values=$input;}
		else{$this->Error=get_class($this)."::setValues \nParameter input not array";return;}
	}
}
?>
Return current item: MyLibSQLGen