PHP/MySQL Query In a loop, how to append to just one query?

I have a query to pull all articles out of the database..

$get_articles = $db->query("
    SELECT *, a.id AS id, s.type AS type FROM ".$prefix."articles a 
    LEFT JOIN ".$prefix."sources s ON (s.id = source_id) 
    WHERE a.type!='trashed' ORDER BY a.timestamp DESC LIMIT $start, $end");

Within the loop of this query, I do then do another query on the same table to find related articles to the 'title' of the article, stored as '$related_phrase'. The query within the loop is:

// get related articles to this entry
$get_related = $db->query("
    SELECT *, a.id AS id, MATCH (title, description) AGAINST ('$related_phrase') AS score FROM ".$prefix."articles a 
    LEFT JOIN ".$prefix."sources s ON (s.id = source_id) WHERE a.type!='trashed' AND MATCH (title, description) AGAINST ('$related_phrase') AND a.id!='$articles[id]' HAVING score > 7 
    ORDER BY a.timestamp DESC LIMIT 0, 3");

This basically means we have a query in a loop which is causing the pages to load very slowly.

What we want to do, is bring the query from within the loop, in the main query, so it's all working within one query, if that's possib开发者_开发知识库le?

Any help very much appreciated!

I don't think you would gain much speed by merging the two queries.

One thing you could try is to get a list of all articles and DISTINCT searchphrases (in e.g. temptable), and then build a query to get all related articles in one single go. Lastly match up related articles with the article list.

try this:

$articles_and_related = $db->query("
    SELECT * 
    FROM ".$prefix."articles art 
    LEFT JOIN (
               SELECT * FROM ".$prefix."articles x
               WHERE 
                 score > 7 
                 AND x.type != 'trashed' 
                 AND x.id != art.id
                 AND MATCH(x.title, x.description) AGAINST (art.title)
               LIMIT 3
    ) rel 
    LEFT JOIN ".$prefix."sources s2 ON (s2.id = rel.source_id) 
    LEFT JOIN ".$prefix."sources s ON (s.id = art.source_id) 
    WHERE 
         art.type!='trashed' 
    ORDER BY art.timestamp DESC LIMIT $start, $end");