sort mysql query by filtered query

Question

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.

Adriaan Stander · Accepted Answer · 2010-03-12 22:23:02Z

2

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

answered Mar 12, 2010 at 22:23

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AxelEckenberger · Accepted Answer · 2010-03-13 14:50:32Z

1

(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.

edited Mar 13, 2010 at 14:50

answered Mar 12, 2010 at 22:24

AxelEckenberger

17k3 gold badges50 silver badges73 bronze badges

1 Comment

kalpaitch Over a year ago

I'm afraid to joy with this as yet, have a look at my updated code above.

Collectives™ on Stack Overflow

sort mysql query by filtered query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related