MySQL Relevance Ordering

Question

I am trying to work out the best way to order search results, they need to be ordered as below, but it doesn't appear to work.

Could this be because the column rating is being selected but the user is not filling in an input named rating?

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

I want the results to be shown in this order, so if I have multiple results which have different ratings then I want them to show as ordered.

It is reloading page on submit and not showing the results page as it normally should.

Not a duplicate, Thanks for the `CASE` I had missed that but now it only reloads the page — Sauced Apples, Nov 04 '15 at 15:28

score 1 · Accepted Answer · edited Nov 05 '15 at 08:35

1

You will have to change your script.

The query, the when after the order is incorrect
You will have to print out the url's you want, redirect is possible, but it will redirect you to the first row only (or first result)
You have a rating of badshort in your data, this will result to null, because you don't have a rule for 'badshort' to become a value.

The complete script to test without post would become:

<?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();
}
?>

edited Nov 05 '15 at 08:35

Sauced Apples

1,163
2
14
37

answered Nov 04 '15 at 15:35

davejal

6,009
10
39
82

I'm afraid that didn't work. – Sauced Apples Nov 04 '15 at 16:00
could you post the error if any? – davejal Nov 04 '15 at 16:02
There is no error, it just refreshes the search page as before – Sauced Apples Nov 04 '15 at 16:02
could you try it in your database manager or phpmyadmin – davejal Nov 04 '15 at 16:04
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':lname AND address like :address AND street like :street AND city like' at line 8 – Sauced Apples Nov 04 '15 at 16:06
I edited the query, could you test again in your db admin – davejal Nov 04 '15 at 16:08
It's showing results in phpmyadmin but not on page, it was working fine before adding the order. – Sauced Apples Nov 04 '15 at 16:11
post the one that was working fine – davejal Nov 04 '15 at 16:13
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/94221/discussion-between-davejal-and-saucedapples). – davejal Nov 04 '15 at 16:13
1

You are not adding appropriate `%` wildcards to the parameter values you are binding. Without this, this might a well be `=` – Mike Brant Nov 04 '15 at 18:15
@Mike completely agree with you – davejal Nov 04 '15 at 18:19

MySQL Relevance Ordering

1 Answers1