Mysql查询加入数据并按顺序显示

Question

I have a mysql query which displays the result based on the search data. suppose my search data is beauty then it will search "beauty" in title then it will search "beauty" display based on desc order then in content and display in desc order. But now i need a query where suppose there are 100 data and 10 are posted in august 2019 and few on july 2019 then data should have searched data in title with august2019, then content with august 2019, then searched title with july 2019 and content in july 2019 and so on with 2018,2017 etc.

Here is my query:

$searchtText="beauty";
$getarticleid="id not in the 1st query"

SELECT a.* 
FROM   ( 
                SELECT   articleid, 
                         articleeleganturl, 
                         articletitle, 
                         articlecontent, 
                         publishstart, 
                         published 
                FROM     articles 
                WHERE    articletitle!='' 
                AND      articletitle regexp '[[:<:]]".mysql_real_escape_string($searchtText)."[[:>:]]' 
                AND      published='Yes' 
                GROUP BY articletitle 
                ORDER BY publishstart DESC) a 
UNION ALL 
SELECT b.* 
FROM   ( 
                SELECT   articleid, 
                         articleeleganturl, 
                         articletitle, 
                         articlecontent, 
                         publishstart, 
                         published 
                FROM     articles 
                WHERE    articletitle!='' 
                AND      articlecontent regexp '[[:<:]]".mysql_real_escape_string($searchtText)."[[:>:]]' 
                AND      published='Yes' ".$getarticleid." 
                GROUP BY articletitle 
                ORDER BY publishstart DESC )

Answer 1

I dont know if i have understood your question correctly, but the order of both unions should be done after the union, so both unified results will be sorted together:

SELECT a.* 
FROM   ( 
                SELECT   articleid, 
                         articleeleganturl, 
                         articletitle, 
                         articlecontent, 
                         publishstart, 
                         published 
                FROM     articles 
                WHERE    articletitle!='' 
                AND      articletitle regexp '[[:<:]]".mysql_real_escape_string($searchtText)."[[:>:]]' 
                AND      published='Yes' 
                GROUP BY articletitle) a 
UNION ALL 
SELECT b.* 
FROM   ( 
                SELECT   articleid, 
                         articleeleganturl, 
                         articletitle, 
                         articlecontent, 
                         publishstart, 
                         published 
                FROM     articles 
                WHERE    articletitle!='' 
                AND      articlecontent regexp '[[:<:]]".mysql_real_escape_string($searchtText)."[[:>:]]' 
                AND      published='Yes' ".$getarticleid." 
                GROUP BY articletitle 
)
ORDER BY publishstart DESC