CODEWITHSUNDEEP
mysqljoinfilter
kalpaitch
kalpaitch

Reputation: 5271

sort mysql query by filtered query

I have two mysql queries:

$sql = "SELECT * FROM content WHERE threadName LIKE '%$filter%' ORDER BY lastUpdated desc";

and

$sql = "SELECT * FROM content ORDER BY lastUpdated desc";

The end result is to have all rows returned from a particular table 'content' but have those that match the variable $filter at the top. Is there either a single query that could combine these two or should I be using a JOIN?

............still to no luck. This is what I currently have:

$sql = "SELECT * FROM content WHERE threadName LIKE '%$filter%' ORDER BY lastUpdated desc UNION SELECT * FROM content WHERE threadName NOT LIKE '%$filter%' ORDER BY lastUpdated desc";

which gives:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/andrew/public_html/PHP/threadDisplay.php  on line 20

although the first part (before the UNION) works when used on its own.

Upvotes: 3

Views: 1947

Answers (2)

AxelEckenberger
AxelEckenberger

Reputation: 16926

(SELECT *
FROM   content
WHERE  threadName LIKE '%$filter%')
UNION
(SELECT *
FROM   content
WHERE  threadName NOT LIKE '%$filter%')
ORDER BY lastUpdated desc

Edit: MySQL needs some bracketing and requires to do the order after the union, sorry forgot about that.

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166356

How about using a CASE STATEMENT.

Something like

SELECT  * 
FROM    content 
ORDER BY CASE 
            WHEN threadName LIKE '%$filter%' THEN 0
            ELSE 1
        END ASC,
        lastUpdated desc

Upvotes: 2

Related Questions