|
<?php |
|
class queryStringToSQL { |
|
public $tableName = ""; |
|
|
|
function __construct($tableName) { |
|
$this->tableName = $tableName; |
|
} |
|
|
|
function getSQLString($qs) { |
|
// parse the qs |
|
parse_str($qs, $qs_array); |
|
|
|
$fieldsFrom = "* FROM " . $this->tableName; // default value |
|
$where = ""; |
|
$whereValues = []; |
|
$order = ""; |
|
|
|
// find all individual pieces |
|
foreach ($qs_array as $key => $value) { |
|
switch($key){ |
|
case "@fields": |
|
$fieldsFrom = $this->getFields($value) . " FROM " . $this->tableName; |
|
break; |
|
case "@where": |
|
$where .= $this->getWhere($value); |
|
$whereValues = $this->getWhereValues($value); |
|
break; |
|
case "@limit": |
|
$limit = " LIMIT $value"; |
|
break; |
|
case "@offset": |
|
$offset = ",$value"; |
|
break; |
|
case "@order": |
|
$order .= $this->getOrder($value); |
|
break; |
|
} |
|
} |
|
|
|
$fullSQL = "SELECT " . $fieldsFrom . $where . $order . $limit . $offset; |
|
$output = array( |
|
"sql" => $fullSQL, |
|
"values" => $whereValues |
|
); |
|
return $output; |
|
} |
|
|
|
function getFields($in){ |
|
$in_array = explode(",",$in); |
|
$out=""; |
|
foreach ($in_array as $key => $value) { |
|
$out .= $value . ","; |
|
} |
|
return $in; |
|
} |
|
|
|
function getWhere($in){ |
|
// receives: stagename[eq]Alco Alco,age[gte]40 |
|
$valuesArray = []; |
|
$in_array = explode(",",$in); |
|
$out = " WHERE "; |
|
foreach ($in_array as $key => $value) { |
|
// split by operator |
|
$operator = $this->findOperator($value); |
|
$newOperator = $this->replaceOperator($operator); |
|
|
|
$keyval = explode($operator,$value); |
|
//$out .= $keyval[0] . $newOperator . urlencode($keyval[1]) . " AND "; |
|
$out .= $keyval[0] . $newOperator . ":" . $keyval[0] . " AND "; |
|
$valuesArray[$keyval[0]] = urlencode($keyval[1]); |
|
} |
|
//die(json_encode($valuesArray)); |
|
$out = substr($out,0,strlen($out)-5); // remove last AND |
|
return $out; |
|
} |
|
|
|
function getWhereValues($in){ |
|
// receives: stagename[eq]Alco Alco,age[gte]40 |
|
$valuesArray = []; |
|
$in_array = explode(",",$in); |
|
//$out = " WHERE "; |
|
foreach ($in_array as $key => $value) { |
|
// split by operator |
|
$operator = $this->findOperator($value); |
|
//$newOperator = $this->replaceOperator($operator); |
|
|
|
$keyval = explode($operator,$value); |
|
//$out .= $keyval[0] . $newOperator . urlencode($keyval[1]) . " AND "; |
|
//$out .= $keyval[0] . $newOperator . ":" . $keyval[0] . " AND "; |
|
$valuesArray[$keyval[0]] = $keyval[1]; |
|
} |
|
//die(json_encode($valuesArray)); |
|
//$out = substr($out,0,strlen($out)-5); // remove last AND |
|
return $valuesArray; |
|
} |
|
|
|
function getOrder($in){ |
|
// receives: -timestamp,-name,email |
|
$in_array = explode(",",$in); |
|
$out = " ORDER BY "; |
|
|
|
foreach ($in_array as $key => $value) { |
|
if (substr($value,0,1) == "-"){ |
|
$field = substr($value,1,strlen($value)); |
|
$out .= $field . " DESC ,"; |
|
}else{ |
|
$out .= $value . ","; |
|
} |
|
} |
|
$out = substr($out,0,strlen($out)-1); // remove last comma |
|
return $out; |
|
} |
|
|
|
function findOperator($source){ |
|
$start = strpos($source,"["); |
|
$end = strpos($source,"]"); |
|
|
|
if($start > 0 && $end > 0){ |
|
$rawoperator = substr($source,$start,($end+1)-$start); |
|
return $rawoperator; |
|
} |
|
} |
|
|
|
function replaceOperator($source){ |
|
switch($source){ |
|
case "[eq]": |
|
return "="; |
|
break; |
|
case "[ne]": |
|
return "!="; |
|
break; |
|
case "[lt]": |
|
return "<"; |
|
break; |
|
case "[lte]": |
|
return "<="; |
|
break; |
|
case "[gt]": |
|
return ">"; |
|
break; |
|
case "[gte]": |
|
return ">="; |
|
break; |
|
case "[like]": |
|
return "like"; |
|
break; |
|
} |
|
} |
|
} |