src/hathoora/grid/grid.php
public static function sqlRun($arrParams, $render = false)
    {
        $dsn = !empty($arrParams['dsn']) ? $arrParams['dsn'] : self::getDSN();

        // debug
        $queryTotalDebug = !empty($arrParams['queryTotalDebug']) ? $arrParams['queryTotalDebug'] : false;
        $queryRowDebug = !empty($arrParams['queryRowDebug']) ? $arrParams['queryRowDebug'] : false;
        // stores results
        $arrGridData = array('total' => null, 'rows' => array());
        // query query
        $queryTotal = !empty($arrParams['queryTotal']) ? $arrParams['queryTotal'] : null;
        $skipTotal = !empty($arrParams['skipTotal']) ? $arrParams['skipTotal'] : false;
        $totalQueryHasResults = false;
        // rows query and related variables
        $queryRow =  !empty($arrParams['queryRow']) ? $arrParams['queryRow'] : null;
        $sort = !empty($arrParams['sort']) ? $arrParams['sort'] : null;
        $order = !empty($arrParams['order']) ? $arrParams['order'] : null;
        $start = !empty($arrParams['start']) ? $arrParams['start'] : 0;
        $page = !empty($arrParams['page']) ? (int) $arrParams['page'] : 1;
        $limit = !empty($arrParams['limit']) ? (int) $arrParams['limit'] : 100;
        $orderBy = $limitClause = null;
        // don't run any sql and go with the flow
        $noSQL = isset($arrParams['noSQL']) ? $arrParams['noSQL'] : false;
        
        // set them again so we don't get undefined variable notices
        $arrParams['sort'] = $sort;
        $arrParams['order'] = $order;
        $arrParams['start'] = $start;
        $arrParams['limit'] = $limit;

        // queryRowOuterSelect wraps row query which helps in sorting derived fields
        $queryRowOuterSelect = !empty($arrParams['queryRowOuterSelect']) ? $arrParams['queryRowOuterSelect'] : false;
        
        // an array containing function to call for every row in result to which we pass $row as referrence
        // rowFunctions must be an array of arrays that can be input to call_user_func_array()
        $rowFunctions = !empty($arrParams['rowFunctions']) ? $arrParams['rowFunctions'] : null;
        
        // field to use as index when creating results set
        $primaryKey = !empty($arrParams['primaryKey']) ? $arrParams['primaryKey'] : null;
        
        // similiar to rowFunctions an array of functions to call at the end of row loops
        // to which we pass the entire $arrGridData['rows'] as referrence
        $dataFunctions = !empty($arrParams['dataFunctions']) ? $arrParams['dataFunctions'] : null;
       
        // sort
        if ($sort && $order)
        {
            $orderBy = $dsn->escape($sort) . ' ' . $dsn->escape($order);
            $orderBy = 'ORDER BY '. $orderBy;
        }
        
        // limit
        if (!$start)
        {
            $start = ($page - 1) * $limit;
            if ($start < 1) $start = 0;
        }        
        if ($limit)
            $limitClause = 'LIMIT '. $dsn->escape($start) .', '. $dsn->escape($limit);

        // get total results
        if ($noSQL != true && $skipTotal == false && $queryTotal)
        {
            if ($queryTotalDebug)
                echo 'Total Query:<br/>' . nl2br($queryTotal);
            try
            {
                $stmt = $dsn->query($queryTotal);
                if ($stmt && $stmt->rowCount())
                {
                    $totalQueryHasResults = true;
                    $row = $stmt->fetchArray();
                    $arrGridData['total'] = array_pop($row);
                }
            }
            catch (\Exception $e)
            {
                // fail silently
            }
        }

        // get the rows
        if ($noSQL != true && $queryRow && 
           ($skipTotal || (!$skipTotal && $totalQueryHasResults)))
        {
            // outer select?
            if ($queryRowOuterSelect)
            {
                // we need to remove a.field from the order, it will messup
                $orderBy = preg_replace('/ORDER BY (.+?)\./i', 'ORDER BY ', $orderBy);
                
                $query = 'SELECT o.*  FROM ( '. $queryRow .') o '. $orderBy .' '. $limitClause;
            }
            else
                $query = $queryRow . ' '. $orderBy .' '. $limitClause;            
            
            if ($queryRowDebug)
                echo 'Row Query:<br/>' . nl2br($query);

            try
            {
                $stmt = $dsn->query($query);
                if ($stmt && $stmt->rowCount())
                {
                    while ($row = $stmt->fetchArray())
                    {
                        // any row functions
                        if (is_array($rowFunctions))
                        {
                            foreach ($rowFunctions as $rowFunction)
                            {
                                if (is_callable($rowFunction))
                                    call_user_func_array($rowFunction, array(&$row));
                            }
                        }

                        if ($primaryKey && isset($row[$primaryKey]))
                            $arrGridData['rows'][$row[$primaryKey]] = $row;
                        else
                            $arrGridData['rows'][] = $row;
                    }

                    // any data functions
                    if (is_array($dataFunctions))
                    {
                        foreach($dataFunctions as $dataFunction)
                        {
                            $arrDataFunctionExtraParams = null;
                            if (count($dataFunction) > 2)
                                $arrDataFunctionExtraParams = array_pop($dataFunction);
                            if (is_callable($dataFunction))
                            {
                                $args = array();
                                $args[0] =& $arrGridData['rows'];
                                if (is_array($arrDataFunctionExtraParams))
                                    $args = array_merge($args, $arrDataFunctionExtraParams);

                                call_user_func_array($dataFunction, $args);
                            }
                        }
                    }
                }
            }
            catch (\Exception $e)
            {
                // fail silently
            }
        }

        if (!$render)
        {
            // skip totals?
            if ($skipTotal && count($arrGridData))
            {
                unset($arrGridData['total']);
                $arrGridData = $arrGridData['rows'];
            }
            return $arrGridData;
        }
        else
        {
            // merge arrGridData into arrParams
            $arrParams['data']['total'] =& $arrGridData['total'];
            $arrParams['data']['rows'] =& $arrGridData['rows'];
            return self::renderGrid($arrParams, true);
        }
    }
public static function sqlRun($arrParams, $render = false)
    {
        $dsn = !empty($arrParams['dsn']) ? $arrParams['dsn'] : self::getDSN();

        // debug
        $queryTotalDebug = !empty($arrParams['queryTotalDebug']) ? $arrParams['queryTotalDebug'] : false;
        $queryRowDebug = !empty($arrParams['queryRowDebug']) ? $arrParams['queryRowDebug'] : false;
        // stores results
        $arrGridData = array('total' => null, 'rows' => array());
        // query query
        $queryTotal = !empty($arrParams['queryTotal']) ? $arrParams['queryTotal'] : null;
        $skipTotal = !empty($arrParams['skipTotal']) ? $arrParams['skipTotal'] : false;
        $totalQueryHasResults = false;
        // rows query and related variables
        $queryRow =  !empty($arrParams['queryRow']) ? $arrParams['queryRow'] : null;
        $sort = !empty($arrParams['sort']) ? $arrParams['sort'] : null;
        $order = !empty($arrParams['order']) ? $arrParams['order'] : null;
        $start = !empty($arrParams['start']) ? $arrParams['start'] : 0;
        $page = !empty($arrParams['page']) ? (int) $arrParams['page'] : 1;
        $limit = !empty($arrParams['limit']) ? (int) $arrParams['limit'] : 100;
        $orderBy = $limitClause = null;
        // don't run any sql and go with the flow
        $noSQL = isset($arrParams['noSQL']) ? $arrParams['noSQL'] : false;
        
        // set them again so we don't get undefined variable notices
        $arrParams['sort'] = $sort;
        $arrParams['order'] = $order;
        $arrParams['start'] = $start;
        $arrParams['limit'] = $limit;

        // queryRowOuterSelect wraps row query which helps in sorting derived fields
        $queryRowOuterSelect = !empty($arrParams['queryRowOuterSelect']) ? $arrParams['queryRowOuterSelect'] : false;
        
        // an array containing function to call for every row in result to which we pass $row as referrence
        // rowFunctions must be an array of arrays that can be input to call_user_func_array()
        $rowFunctions = !empty($arrParams['rowFunctions']) ? $arrParams['rowFunctions'] : null;
        
        // field to use as index when creating results set
        $primaryKey = !empty($arrParams['primaryKey']) ? $arrParams['primaryKey'] : null;
        
        // similiar to rowFunctions an array of functions to call at the end of row loops
        // to which we pass the entire $arrGridData['rows'] as referrence
        $dataFunctions = !empty($arrParams['dataFunctions']) ? $arrParams['dataFunctions'] : null;
       
        // sort
        if ($sort && $order)
        {
            $orderBy = $dsn->escape($sort) . ' ' . $dsn->escape($order);
            $orderBy = 'ORDER BY '. $orderBy;
        }
        
        // limit
        if (!$start)
        {
            $start = ($page - 1) * $limit;
            if ($start < 1) $start = 0;
        }        
        if ($limit)
            $limitClause = 'LIMIT '. $dsn->escape($start) .', '. $dsn->escape($limit);

        // get total results
        if ($noSQL != true && $skipTotal == false && $queryTotal)
        {
            if ($queryTotalDebug)
                echo 'Total Query:<br/>' . nl2br($queryTotal);
            try
            {
                $stmt = $dsn->query($queryTotal);
                if ($stmt && $stmt->rowCount())
                {
                    $totalQueryHasResults = true;
                    $row = $stmt->fetchArray();
                    $arrGridData['total'] = array_pop($row);
                }
            }
            catch (\Exception $e)
            {
                // fail silently
            }
        }

        // get the rows
        if ($noSQL != true && $queryRow && 
           ($skipTotal || (!$skipTotal && $totalQueryHasResults)))
        {
            // outer select?
            if ($queryRowOuterSelect)
            {
                // we need to remove a.field from the order, it will messup
                $orderBy = preg_replace('/ORDER BY (.+?)\./i', 'ORDER BY ', $orderBy);
                
                $query = 'SELECT o.*  FROM ( '. $queryRow .') o '. $orderBy .' '. $limitClause;
            }
            else
                $query = $queryRow . ' '. $orderBy .' '. $limitClause;            
            
            if ($queryRowDebug)
                echo 'Row Query:<br/>' . nl2br($query);

            try
            {
                $stmt = $dsn->query($query);
                if ($stmt && $stmt->rowCount())
                {
                    while ($row = $stmt->fetchArray())
                    {
                        // any row functions
                        if (is_array($rowFunctions))
                        {
                            foreach ($rowFunctions as $rowFunction)
                            {
                                if (is_callable($rowFunction))
                                    call_user_func_array($rowFunction, array(&$row));
                            }
                        }

                        if ($primaryKey && isset($row[$primaryKey]))
                            $arrGridData['rows'][$row[$primaryKey]] = $row;
                        else
                            $arrGridData['rows'][] = $row;
                    }

                    // any data functions
                    if (is_array($dataFunctions))
                    {
                        foreach($dataFunctions as $dataFunction)
                        {
                            $arrDataFunctionExtraParams = null;
                            if (count($dataFunction) > 2)
                                $arrDataFunctionExtraParams = array_pop($dataFunction);
                            if (is_callable($dataFunction))
                            {
                                $args = array();
                                $args[0] =& $arrGridData['rows'];
                                if (is_array($arrDataFunctionExtraParams))
                                    $args = array_merge($args, $arrDataFunctionExtraParams);

                                call_user_func_array($dataFunction, $args);
                            }
                        }
                    }
                }
            }
            catch (\Exception $e)
            {
                // fail silently
            }
        }

        if (!$render)
        {
            // skip totals?
            if ($skipTotal && count($arrGridData))
            {
                unset($arrGridData['total']);
                $arrGridData = $arrGridData['rows'];
            }
            return $arrGridData;
        }
        else
        {
            // merge arrGridData into arrParams
            $arrParams['data']['total'] =& $arrGridData['total'];
            $arrParams['data']['rows'] =& $arrGridData['rows'];
            return self::renderGrid($arrParams, true);
        }
    }
Run a sql statement

@param array $arrParams that have
$arrParams that have:
dsn: which dsn to use
sort: the field to sort on
order: the order asc|desc
start: the start of limit

@todo complete this
@param bool $render
@return array|mixed