1
Vote

improved limit

description

Improved limit to handle some situations without errors
 
public function limit($sql, $count, $offset = 0)
 {
    $count = intval($count);
    if ($count <= 0) {
        require_once 'Zend/Db/Adapter/Exception.php';
        throw new Zend_Db_Adapter_Exception("LIMIT argument count=$count is not valid");
    }
 
    $offset = intval($offset);
    if ($offset < 0) {
        /** @see Zend_Db_Adapter_Exception */
        require_once 'Zend/Db/Adapter/Exception.php';
        throw new Zend_Db_Adapter_Exception("LIMIT argument offset=$offset is not valid");
    }
 
    $orderby = stristr($sql, 'ORDER BY');
    if ($orderby !== false) {
        $sort  = (stripos($orderby, ' desc') !== false) ? 'desc' : 'asc';
        $order = str_ireplace('ORDER BY', '', $orderby);
        $order = trim(preg_replace('/\bASC\b|\bDESC\b/i', '', $order));
    }
 
    $sql = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . ($count+$offset) . ' ', $sql);
 
    $sql = 'SELECT * FROM (SELECT TOP ' . $count . ' * FROM (' . $sql . ') AS inner_tbl';

    if(strpos($order,".")!==false){
        $order = substr($order, strpos($order,'.')+1);
    }

    if ($orderby !== false) {
        $sql .= ' ORDER BY inner_tbl.' . $order . ' ';
        $sql .= (stripos($sort, 'asc') !== false) ? 'DESC' : 'ASC';
    }
    $sql .= ') AS outer_tbl';
    if ($orderby !== false) {
        $sql .= ' ORDER BY outer_tbl.' . $order . ' ' . $sort;
    }
 
    return $sql;
}

comments