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.
精彩评论