Query another table withing FOR loop of a table.

[saeed_violinist]

Dear Friends,

 

I have two tables. "news" and "categories"

 

I do a query to select 10 recent news, and one of its fields is "parent" which is ID of the category which the news belongs to it, and it is stored in table "categories"

 

I do like this but only the first category name will appear!

 

$query = $_DB->Query("SELECT news_id, parent, time, author, title, breif, image FROM news ORDER BY time ASC);  

 

and here is my loop :

 

        $count = $_DB->GetResultNumber($query); 

        for($i = 0; $i < $count; $i++) 
        {           
            $recentNews[$i]["news_id"] = $_DB->GetResultValue($query, $i, "news_id"); 
            $recentNews[$i]["parent_id"]  = $_DB->GetResultValue($query, $i, "parent");  

            // THIS ONLY RETURNS THE FIRST CATEGORY TITLE!!!!  second ans so on are empty  
            $tempQuery = $_DB->Query("SELECT name FROM categories WHERE category_id='".  $recentNews[$i]["parent_id"] ."'"); 
            $recentNews[$i]["category"] = $_DB->GetResultValue($tempQuery, $i, "name"); 
                                   
            $recentNews[$i]["time"]    = $_DB->GetResultValue($query, $i, "time");  
            $recentNews[$i]["author"]  = $_DB->GetResultValue($query, $i, "author");  
            $recentNews[$i]["title"]  = $_DB->GetResultValue($query, $i, "title");  
            $recentNews[$i]["breif"]   = $_DB->GetResultValue($query, $i, "breif");  
            $recentNews[$i]["image"]  = $_DB->GetResultValue($query, $i, "image");             
        } 

 

I guess I have to use JOIN but I dont know the right syntax for it

[kickstart]

Hi

 

Try this:-

 

SELECT a.news_id, a.parent, a.time, a.author, a.title, a.breif, a.image, b.name
FROM news a
INNER JOIN categories b
ON a.parent = b.category_id
ORDER BY time ASC

 

All the best

 

Keith

[luca200]

Of course you need a join.

By the way, your mistake on the previous code (a part from it being badly designed) was using $i instead of 0 when trying to retrieve the category