CODEWITHSUNDEEP
phpmysqlsqlselectsql-like
Gregor
Gregor

Reputation: 81

ordering search results from mysql

I have a table which contains items, 2 attributes in my table are name(name of the item) and keywords(words to do with that item). When im searching for an item in the database i use the the following code. I have the search entry q="" stored in a variable $query. My mysql search is the following..

    $q = "SELECT * FROM items WHERE name LIKE '%$query%' OR keywords LIKE '%$query%' AND status = '1'  LIMIT $start, $per_page";

This code gets all my items where the name of the item is like the query or it contains a keyword which is like the query.. How can i order the results so that the items where the name is like the query appears before the items which contain a keyword which is like the query? just now it just orders the items in first added to the database.

Upvotes: 0

Views: 65

Answers (3)

BWS
BWS

Reputation: 3836

try something like this:

$q = 
"SELECT * FROM items WHERE name LIKE '%$query%' AND status = '1'
 UNION
 SELECT * FROM items WHERE keywords LIKE '%$query%' AND status = '1'  
 LIMIT $start, $per_page"

Upvotes: 0

Linger
Linger

Reputation: 15048

You can use a CASE statement to assign a value to OrderValue and then order by OrderValue:

SELECT *, 
  CASE WHEN name LIKE '%$query%' 
    THEN 1 
    ELSE 2 END AS OrderValue 
FROM items 
WHERE name LIKE '%$query%' 
  OR keywords LIKE '%$query%' 
  AND status = '1' 
ORDER BY OrderValue 
LIMIT $start, $per_page

Upvotes: 0

peterm
peterm

Reputation: 92785

You can try it this way

SELECT * 
  FROM items 
 WHERE (name LIKE '%$query%' 
    OR keywords LIKE '%$query%') 
   AND status = '1' 
 ORDER BY (name LIKE '%$query%') DESC, name, keywords
 LIMIT $start, $per_page

On a side note: consider to use prepared statements instead of interpolating query strings.

Upvotes: 2

Related Questions