left/right join or both?

kamy99

I have three tables:

Categories: cat_id, cat_title

Articles: article_id, article_title, article_content

Article_to_cat: cat_id, article_id

Needles to say there's a many-to-many relationship, therefore the third table.

Anyways, I have to write a query which displays each Category title once, under it two latest articles that are within that cat.

I'm a bit confused with left/right joins! 😕

My query is:

SELECT * FROM article_to_cat LEFT JOIN articles ON article_to_cat.article_id=articles.article_id  LEFT JOIN categories ON article_to_cat.cat_id=categories.cat_id

But the result displays each category many times.
You can see it here

Please help!

thanx

kamy

bpat1434

Well, (natural) left joins and right joins only differentiate in the way the data is joined.

LEFT JOIN tableB on tableA.id=tableB.aID

That will take all items from tableB and match them to tableA. And it's vice versa for right-joins. So left joins join the info FROM the right, TO the left. And right joins join the info FROM the left, TO the right.

Hope that takes some confusion away from left/right joins.

Now, to limit your query, you'd have to either use a LIMIT clause to specify you only want 2 or so, OR you loop through and limit manually via PHP by checking the cat_id. If you've gotten 2 from the cat_id, move on to the next cat_id.

I'm asking this out of sheer curiosity, but are you for or against democracy? After perusing your site, I found this:

The scariest man on Earth :

He hates Jews. He thinks he has a divine aura. And now he wants nuclear weapons. Meet Mahmoud Ahmadinejad, the lunatic president of Iran.

Just wondering (won't hurt my help on your issue either way).

kamy99

thanx for the reply.

About the article, it's not my website rather it's a project I'm working on. They gather news from different sources.

kamy99

Unfortunately I am still struggling with this problem. 😕 🙁

I need the output to look like this:

Category Name:
Related_article_title
Related_article_title
Category Name:
Related_article_title
Related_article_title

and so on.

I tried every combination of table joins I could think of, but couldn't get it.

One note is that an article can go in many cats, but not the other way!

ANY IDEAS?

Thanx

kamy

bpat1434

try doing this:

$query1 = "SELECT category_id, category_name FROM categories ORDER BY category_id ASC";
$res = mysql_query($query1);

while($r = mysql_fetch_array($res))
{
  $cid = $r['category_id'];
  echo '<b>'.$r['category_name'].':</b><br>';
  $query2 = "SELECT article_title FROM articles WHERE category_id='".$cid."' ORDER BY article_title ASC";
  $res2 = mysql_query($query2);
  while($r2 = mysql_fetch_array($res2))
  {
    echo '--] '.$r2['article_title'].'<br>';
  }
  echo '<br>';
}

That should give you something like you want... or at least a step in the right direction....