MySQL相关性排序

Sauced Apples 发表于 Dev

苹果酱

我正在尝试找出排序搜索结果的最佳方法，它们需要按以下顺序进行排序，但似乎不起作用。

可能是因为正在选择列等级，但用户没有填写名为等级的输入吗？

if(isset($_POST['search'])){
   $lname = trim($_POST['lname']);
   $address = trim($_POST['address']);
   $street = trim($_POST['street']);
   $city = trim($_POST['city']);
   $county = trim($_POST['county']);

   if($lname==""){
      $error[] = "Provide Last Name."; 
   }elseif($address=""){
       $error[] = "Provide House Number/Name.";
   }elseif($street==""){
      $error[] = "Provide Street.";
   }elseif($city==""){
       $error[] = "Provide City.";
   }elseif($county==""){
       $error[] = "Provide County.";
   }else{
       try{
           $stmt = $conn->prepare("SELECT *
           FROM clients
           WHERE  lname  like :lname
           AND   address like :address
           AND   street  like :street
           AND   city    like :city
           AND   county  like :county
           ORDER BY rating 
           WHEN rating like 'badabusive' THEN 0
           WHEN rating like 'badnopay' THEN 1
           WHEN rating like 'cautionlate' THEN 2
           WHEN rating like 'cautiondiscount' THEN 3
           WHEN rating like 'cautionextra' THEN 4
           WHEN rating like 'good' THEN 5
           END, rating");

           $stmt->execute(array("lname"  => empty($lname) ? '%' : '%'. $lname .'%',
           "address" => empty($address) ? '%' : '%'. $address .'%',
           "street"  => empty($street) ? '%' : '%'. $street .'%',
           "city"    => empty($city) ? '%' : '%'. $city .'%',
           "county"  => empty($county) ? '%' : '%'. $county .'%'));

    $userRow = $stmt->fetch(PDO::FETCH_ASSOC);

                if(!$userRow){                  
                     if($user->reportsearch($id,$lname,$address,$street,$city,$county)){
                          $user->redirect('./results.php?new='.$lname.$street);
                      }
                }else{
                    if($userRow['rating'] == 'badnopay'){
                        $user->redirect('./results.php?badnopay='.$lname.$street);
                    }
                    if($userRow['rating'] == 'cautionextra'){
                        $user->redirect('./results.php?cautionextra='.$lname.$street);
                    }
                    if($userRow['rating'] == 'cautiondiscount'){
                        $user->redirect('./results.php?cautiondiscount='.$lname.$street);
                    }
                    if($userRow['rating'] == 'cautionlate'){
                        $user->redirect('./results.php?cautionlate='.$lname.$street);
                    }
                    if($userRow['rating'] == 'good'){
                        $user->redirect('./results.php?good='.$lname.$street);
                    }
                    if($userRow['rating'] == 'badabusive'){
                        $user->redirect('./results.php?badabusive='.$lname.$street);
                    }
                }
            }
        catch(PDOException $e){
             echo $e->getMessage();
        }
    } 
}

我希望结果按此顺序显示，因此，如果我有多个评级不同的结果，则希望它们按顺序显示。

它在提交时正在重新加载页面，而没有正常显示结果页面。

达韦贾尔

您将不得不更改脚本。

查询，下单后的时间不正确
您将必须打印出所需的url，可以重定向，但是它将仅将您重定向到第一行（或第一个结果）
您在数据中对badshort进行了评级，结果将为null，因为您没有“ badshort”成为值的规则。

无需发布即可进行测试的完整脚本将变为：

<?php
$lname = 'something';
$address = 'something1';
$street = 'something2';
$city = 'something3';
$county = 'something4';

try{
$stmt = $conn->prepare("SELECT *, case 
WHEN rating like 'badabusive' THEN 0 
WHEN rating like 'badnopay' THEN 1 
WHEN rating like 'cautionlate' THEN 2 
WHEN rating like 'cautiondiscount' THEN 3 
WHEN rating like 'cautionextra' THEN 4 
WHEN rating like 'good' THEN 5 
END as ratingval FROM clients 
WHERE  
lname  like :lname and
address  like :address and
street  like :street and
city  like :city and
county  like :county
order by ratingval");

$stmt->bindParam(':lname', $lname);
$stmt->bindParam(':address', $address);
$stmt->bindParam(':street', $street);
$stmt->bindParam(':city', $city);
$stmt->bindParam(':county', $county);

$stmt->execute();

$userRow = $stmt->fetchAll();
if(!$userRow){
print "nothing found";
}
else
{
foreach ($userRow as $result) {
echo $result[lname] . " " . $result[ratingval]. "<br>";
}
}
}
catch(PDOException $e){
echo $e->getMessage();
}
?>