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