php MySQL select priority

Question

$query = "SELECT * FROM posts WHERE language='$lang' AND (title LIKE '%$search%' OR author LIKE '%$search%' OR year LIKE '%$search%')";

This does exactly what it should do. But what I'd like to do is having "title" as a priority. But as it looks now (every search is in a dropdown of html) it simple show's it without an priority. So the title can be at the very bottom, and the author at the top. Wrong order. I'd like to somehow always have the title at top.

How?

$output = '';  
      $lang = $_SESSION["lang"];
      $search = $_POST["query"];
      $query = "SELECT * FROM posts WHERE language='$lang' AND (title LIKE '%$search%' OR author LIKE '%$search%' OR year LIKE '%$search%')";  
      $result = mysqli_query($connect, $query);  
      $output = '<ul class="list-unstyled">';  
      if(mysqli_num_rows($result) > 0)  
      {  
           while($row = mysqli_fetch_array($result))  
           { 

                $output .= '<a href="'.$url.'/'.$lang.'/'.$row["url"].'/"><li>'.$row["book"].'</li></a>';  
           }  
      }  
      else  
      {  
           $output .= 'Not found.';  
      }  
      $output .= '</ul>';  
      echo $output;

Use order by clause in your query. – SanketR Sep 08 '16 at 09:37 — SanketR, Sep 08 '16 at 09:37
I have posted the query as an answer – SanketR Sep 08 '16 at 09:42 — SanketR, Sep 08 '16 at 09:42

SanketR · Answer 1 · 2016-09-08T11:27:16.453

1

You can split up the query.

  $output = '';  
  $lang = $_SESSION["lang"];
  $search = $_POST["query"];



      $query2 = "SELECT * FROM posts WHERE language='$lang' AND title LIKE '%$search%'";
      $result2 = mysqli_query($connect, $query2);  
      $output = '<ul class="list-unstyled">';  
      if(mysqli_num_rows($result2) > 0)  
      {  
           while($row = mysqli_fetch_array($result2))  
           { 

                $output .= '<a href="'.$url.'/'.$lang.'/'.$row["url"].'/"><li>'.$row["book"].'</li></a>';  
           }  
      }  
      else  
      {  
           $output .= 'Not found.';  
      }  

      $query = "SELECT * FROM posts WHERE language='$lang' AND (author LIKE '%$search%' OR year LIKE '%$search%')";  
      $result = mysqli_query($connect, $query);  


      if(mysqli_num_rows($result) > 0)  
      {  
       while($row = mysqli_fetch_array($result))  
       { 

            $output .= '<a href="'.$url.'/'.$lang.'/'.$row["url"].'/"><li>'.$row["book"].'</li></a>';  
       }  
     }  
      else  
     {  
       $output .= 'Not found.';  
     }  


  $output .= '</ul>'; 
  echo $output;

edited Sep 08 '16 at 11:27

answered Sep 08 '16 at 09:42

SanketR

1,182
14
35

I misunderstood your question in the beginning. Modified the code. You can split your query into 2 parts and then concatenate the output. – SanketR Sep 08 '16 at 10:04
Could you check if there's any issue in query? I have used the same code you had provided. – SanketR Sep 08 '16 at 11:22
This isn't a "order by name" solution, like a priority. Still gets far way down, since it's not based on the title – mezt Sep 08 '16 at 11:22
@mezt I have provided a solution using a scoring system below. See if that works. – Mohamed Yasin Sep 15 '16 at 03:46

score 0 · Answer 2 · edited May 23 '17 at 12:08

ORDER BY should do the trick for you here:

http://www.w3schools.com/sql/sql_orderby.asp

$query = "
SELECT book
     , title
     , url
  FROM posts 
 WHERE language='$lang' 
   AND ( 
         title LIKE '%$search%' 
         OR 
         author LIKE '%$search%' 
         OR 
         year LIKE '%$search%'
        )
  ORDER BY title ASC
       , author ASC
       , book ASC
";

I've added an optional order by 'author' and 'book' too (the priority of ordering starts with 'title', then 'author' and finally 'book') - you can change this to whatever you need though in ASC (ascending) or DESC (descending) order.

I'd also recommend you consider using bind params rather than passing in variables directly into your SQL to prevent SQL Injection.

Mysqli Bind Param Documentation

http://php.net/manual/en/mysqli-stmt.bind-param.php

Really good SO post here with help and more info about SQL Injection

How can I prevent SQL injection in PHP?

Also - try to avoid using SELECT * FROM... where possible, and only SELECT out the information you need. You'll be able to INDEX it better this way too (meaning quicker retrieval of data from the database).

Mohamed Yasin · Answer 3 · 2016-09-12T05:36:10.367

You could use a scoring system to give each match a score and then sort by the match score. So a match for title gets a higher score and a match for author gets the next highest and so on. I'll rewrite just the query here:

SELECT *,
(
    CASE
        WHEN title LIKE '%$search%'  THEN 100
        WHEN author LIKE '%$search%' THEN 10
        WHEN year LIKE '%$search%'   THEN 1
    END
) AS matchScore
FROM posts
WHERE
language='$lang' AND
(title LIKE '%$search%' OR author LIKE '%$search%' OR year LIKE '%$search%')
ORDER BY matchScore DESC

php MySQL select priority

3 Answers3