In PHP, take a query string and convert it into an SQL String

README.md

Sample usage

$qs = "?@fields=name,id&@where=name[ne]john+doe&@limit=0&@offset=100&@order=-timestamp";

$theClass = new queryStringToSQL('users'); // pass in the name of the table

$sqlString = $theClass->getSQLString($qs); // pass in the query string

$sqlString contains

SELECT name,id FROM users WHERE name != 'john doe' LIMIT 0,100 ORDER BY timestamp DESC

queryStringParser.php

<?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;
                }
        }
    }