开发者

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 th开发者_运维技巧ose 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.


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


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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜