Linux ve Hosting

MySQL deki LIMIT komutunun MSSQL de direk karsiligi malesef yok .
PHP kullananlar icin webde cok guzel bir fonksiyon buldum.. MSSQL icin SQL sorgusunu yine LIMIT kullanarak yapiyorsunuz ve bu fonksiyon sizin icin sorguyu MSSQL uyumlu hale getiriyor...

ornek

  $sql=mssql_limit_query("SELECT * FROM URUNLIST WHERE BARKOD ORDER BY URUNADI ASC LIMIT 2,8");

    $res=mssql_query($sql,$conn);

seklinde uygulayabilirsiniz...

Ve fonksiyonumuz...

// #######################################################################################################
function invertOrder($order){
    if (strtolower($order) == 'asc'){
        return 'DESC';
    }
    else {
        return 'ASC';
    }
}

// #######################################################################################################
function mssql_limit_query($sql){ // mubarek bi fonksiton limitten TOP a cevirir
    if (strpos(strtolower($sql), ' limit ') === false){
        return $sql;
    }

    //Extract out the limit and order by clauses.
    if (preg_match('/LIMITs*(([0-9]+),)?s*([0-9]+)/', $sql, $matches)){
        $skip = $matches[2];
        $n = $matches[3];

        $start = strpos(strtolower($sql), ' order by ');
        if ($start !== false){

            $orderby = array();
            $thisorder = array();

            $order = trim(substr($sql, $start+strlen(' order by ')));
            $pos = strpos($order, ' ');

            while ($pos !== false){
                $word = substr($order, 0, $pos);
                if ($word{strlen($word)-1} == ','){
                    if (strtolower($word) == 'asc' || strtolower($word) == 'desc'){
                        $thisorder[1] = $word;
                        $orderby[] = $thisorder;
                        $thisorder = array();
                    }
                    else {
                        $word = substr($word, 0, -1);
                        $thisorder[0] = $word;
                        $thisorder[1] = 'ASC';
                        $orderby[] = $thisorder;
                        $thisorder = array();
                    }
                }
                else if (strtolower($word) == 'asc' || strtolower($word) == 'desc'){
                    $thisorder[1] = $word;
                    $orderby[] = $thisorder;
                    $thisorder = array();
                }
                else if (strtolower($word) == 'having' || strtolower($word) == 'limit'){
                    if (!empty($thisorder[0])){
                        if (empty($thisorder[1])){
                            $thisorder[1] = 'asc';
                        }
                        $orderby[] = $thisorder;
                    }
                    break;
                }
                else {
                    $thisorder[0] = $word;
                }
                $order = trim(substr($order, $pos));
                $pos = strpos($order, ' ');
            }
        }

        $base_sql = preg_replace('/LIMITs*(([0-9]+),)?s*([0-9]+)/', '', $sql);

        if (empty($skip)){
            $sql = preg_replace('/^s*SELECTs/', 'SELECT TOP '.$n.' ', $base_sql);
            return $sql;
        }
        else {
            $outer_order = '';
            $inner_order = '';
            foreach ($orderby as $val){
                $outer_order .= $val[0].' '.$val[1].', ';
                $inner_order .= $val[0].' '.invertOrder($val[1]).', ';
            }

            if (empty($outer_order)){
                $outer_order = 1;
            }
            else {
                $outer_order = substr($outer_order, 0, -2);
            }

            if (empty($inner_order)){
                $inner_order = 1;
            }
            else {
                $inner_order = substr($inner_order, 0, -2);
            }

            // edited by hidayet (20080803)
            $h_pos=strpos(strtolower($base_sql)," from ");
            $h_pox=strpos(strtolower($base_sql)," order by ");
            $h_sql="SELECT COUNT(*) as CNT ".substr($base_sql,$h_pos,(strlen($base_sql)-$h_pos)-( strlen($base_sql) - $h_pox));
            $h_res=mssql_query($h_sql);
            $h_cnt=mssql_fetch_assoc($h_res);

            $nskip=$n+$skip;

            if ($h_cnt["CNT"] + $n > $nskip)
            {
                $n = $h_cnt["CNT"] % $n;
            } // if sonu

            // end of edit (20080803)            

            $sql  = "SELECT * FROM (n";
            $sql .= "   SELECT TOP ".$n." * FROM (n";
            $sql .= "       ".preg_replace('/^s*SELECTs/', 'SELECT TOP '.($nskip).' ', $base_sql)."n";
            $sql .= "   ) As table_inner ORDER BY ".$inner_order." n";
            $sql .= ") As table_outer ORDER BY ".$outer_order."n";
            return $sql;
        }
    }
    return false;
}