Mapping together 2 tables.

[luqman93]

Hello.

I am building a custom blog solution and am stuck here.

I have two tables: articles and categories.

And a third article_category which maps articles to a number of categories. It has 3 fields (id, article_id, category_id).

I am currently stuck on building the sql query. I have thought of a quick fix which means multiple query calls but im trying to minimize the number of db calls.

Basically what i want to do is when i loop over the results to be able to have access to each and every category related to each articles.

kinda like:

<?php

$q = mysql_query("THE UNKNOWN QUERY WHERE `article_id` = $aid");

$result = mysql_fetch_assoc($q);

foreach ($result['category'] as $cat):

echo $cat['title'].

// etc..

endforeach;

?>

Any help would be appreciated. 😃

[bretticus]

Basically what i want to do is when i loop over the results to be able to have access to each and every category related to each articles.

<?php 

$q = mysql_query("SELECT `title` FROM `categories` 
INNER JOIN `article_category` USING(`category_id`) 
WHERE `article_id` = $aid");

$result = mysql_fetch_assoc($q); 

foreach ($result['category'] as $cat): 

echo $cat['title']. 

// etc.. 

endforeach; 
?>

You mean join the tables right?

If you need data from the articles table...

$q = mysql_query("SELECT `title` FROM `categories` 
INNER JOIN `article_category` USING(`category_id`) 
INNER JOIN `articles`  USING(`article_id`) 
WHERE `article_id` = $aid");