Page 1 of 1

combining 2 query results

Posted: Tue Jan 30, 2007 3:59 am
by hame22
Hi I have 2 queries:

Code: Select all

$result = mysql_query("SELECT  DATE_FORMAT(news_date, '%d-%m-%Y') as news_date2, news_id, news_headline, news_summary, news_body, news_image, news_image_detail, news_author, news_tj, news_pdf, news_pdf, news_tj_issue, news_price, news_rating, news_votes, news_tags ,
                MATCH(news_headline, news_body) AGAINST('$k') AS score 
                FROM news 
            WHERE MATCH(news_headline, news_body) AGAINST('$k')  ORDER BY score DESC");

and 

$result2 = mysql_query("Select DATE_FORMAT(thread_date, '%d-%m-%Y') as thread_date2, thread_id, thread_title, thread_body,
MATCH(thread_title, thread_body) AGAINST('$k') AS score
FROM HRD
WHERE MATCH(thread_title, thread_body) AGAINST('$k')  ORDER BY score DESC");
what I want to do is combine these 2 queries and then sort the results by the score.

Any ideas as to the best way to achieve this?

Thanks in advance

Posted: Tue Jan 30, 2007 4:14 am
by dude81
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Not a right place to post but still ,  You need a common table field for joining both queries, for me as I look , news_date and thread_date seems to be common for both. If so,

[syntax="sql"]
SELECT  DATE_FORMAT(nw.news_date, '%d-%m-%Y') as news_date2,  nw.news_id,  nw.news_headline,                 nw.news_summary,   nw.news_body,      nw.news_image,      nw.news_image_detail,   nw.news_author,       nw.news_tj,            nw.news_pdf,      nw.news_pdf, nw.news_tj_issue,         nw.news_price,       nw.news_rating,           nw.news_votes,     nw.news_tags , MATCH(nw.news_headline, nw.news_body) AGAINST('$k')  AS  score,  DATE_FORMAT(td.thread_date, '%d-%m-%Y') as thread_date2,   td.thread_id,  td.thread_title,          td.thread_body, MATCH(td.thread_title, td.thread_body)    FROM news as nw,  HRD as td    WHERE
 
//This might not be necessary if thread and news tables refer same

((MATCH(nw.news_headline, nw.news_body) AGAINST('$k'))&&(MATCH(td.thread_title, td.thread_body) AGAINST('$k')) 

//This is the joining of tables

AND news_date2=thread_date2  ORDER BY score DESC");


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Tue Jan 30, 2007 8:12 am
by feyd
Come on now.. I don't think I should need to move threads of people who've posted coming on 200 times.

Posted: Wed Jan 31, 2007 8:50 am
by dude81
$result1 and $result2 are resources as per your query .
A resource is a kind of pointer, then your question means you want a pointer to point to two different pointers which I dont think is possible. Better do at query level.