Well our working standard is like this. Say i have a table hr_employee which contains all the information of an employee, then we would have a function like this:
/*
$filterArray = array('emp_code','first_name');
*/
function listHrEmployee($filterArray)
{
$Sql = "
SELECT
emp_code
,first_name
,middle_name
,.......
,columnN
FROM
hr_employee/* and some other tables too with join */
WHERE
condition";
$Sql = $this->parseSqlString($Sql,$filterArray);
return $this->dbQueryReturn($Sql);
}
My parser function is this(this is not a perfect function .... you wouldn't like to go through the code i guess

but then also):
Code: Select all
function parseSqlString($queryStr=NULL,$filter=NULL)
{
if(!(is_array($filter) && count($filter)>0))
return $queryStr;
$returnSql = 'SELECT ';
$selectColumns = array();
$queryStr = trim(strtoupper($queryStr));
$selectPos = substr($queryStr,0,6)=='SELECT'?true:false;
if ($selectPos)
{
$pattern = "/[[:space:]]FROM[[:space:]]/";
$fromPos = strripos(preg_replace($pattern,'****FROM****',$queryStr,1),'****FROM****');
$afterFrom = substr($queryStr,$fromPos);
if (!($fromPos === false))
{
$columnStr = trim(substr($queryStr,6,$fromPos-5));
$columns = explode(",",$columnStr);
foreach($filter as $shouldSelect)
{
foreach($columns as $chkSelect)
{
//CHECK ON ALIASES
$tmpChkSelect = explode(' ',$chkSelect);
$counter = count($tmpChkSelect);
for($i=$counter;$i>=0;$i--)
{
if(trim($tmpChkSelect[$i])!='')
{
$chkValue = $tmpChkSelect[$i];
break;
}
}
$chkPos = strripos($chkValue,$shouldSelect);
if(!($chkPos === false))//IF SEARCH SUCCESS ON ALIAS
{
$chkPos = strripos($chkSelect,$shouldSelect);
if(!($chkPos === false))
{
$beforeCharacter = $chkSelect[$chkPos-1];
$afterCharacter = $chkSelect[$chkPos+strlen($shouldSelect)];
//echo $chkSelect.','.$shouldSelect.'---'.$chkPos.'---'.$beforeCharacter.'---'.$afterCharacter.'<br>';
if((!isset($beforeCharacter) || trim($beforeCharacter)=='' || strcmp($beforeCharacter,'.')==0) && (trim($afterCharacter)=='' || !isset($afterCharacter)))
{
array_push($selectColumns,$chkSelect);
break;
}
}
}
}
}
$selectColumnsCount = count($selectColumns);
if($selectColumnsCount>0)
{
foreach($selectColumns as $key=>$val)
{
$returnSql.=$val;
if($key!=$selectColumnsCount-1)
$returnSql.=',';
}
$returnSql.=$afterFrom;
return $returnSql;
}
else
return $queryStr;
}
else
return $queryStr;
}
else
return $queryStr;
}
Now i am thinking that function "parseSqlString" will be simpler if i just did something like this(which i have not yet done though):
Code: Select all
function parseSqlString($queryStr=NULL,$filter=NULL)
{
$Sql =" SELECT ";
foreach($filter as $val)
{
$Sql.=$val;
//CONCATENATE COMMA AT THE END
}
$Sql.=" FROM (".$queryStr.")";
return $Sql;
}
All this is done so that whenever i have to fetch data from any table i would have to use just a single function. Your suggestions will be highly appreciated
