Select proper articles with the right category

[Masterslave]

Hello all,

I've a problem.
I want to display all my categorys, this is going good, so far no problem.
Under those categorys I want the proper articles, how can I accomplish that?

MySQL table

article:
-ID
-Title
-Category
-Keywords
-Description
-Author
-Comment
-Content
-Timestamp
-Favorite

category:
-ID
-category


That are my 2 MySQL tables.
Nou I doing the following to display the categorys:

$requestAllCategorys = "SELECT *
FROM $categorytable
ORDER BY category ASC";
$AllCategorys = mysql_query($requestAllCategorys) or die(mysql_error());

<div id="right">
<?php while ($row = mysql_fetch_assoc($AllCategorys))
{?>
<div class="category">
<h2><?=$row['category'];?></h2>
<ul>
<li><a href="#">Meeting Casejunkies</a></li>
</ul>
</div>
<?}?>
</div>
In the li are coming the article, but how can I put the right articles under the right category dynamicly?

If have insert 3 categorys, this is the result from the category query:

Feesten

Meeting CasejunkiesHobby

Meeting CasejunkiesThoughts

Meeting Casejunkies

I hope I explain it good to you...
Thanks in advance!

[guelphdad]

Follow the link in my signature for the categories/subcategories article. If there is anything you don't understand after reading it, I can help you adapt it to your situation.

[Masterslave]

I'll go play with this, is see that I have to use a JOIN. It's a bit new for me but also a challenge.

[Masterslave]

I tried your sig but I can't make it work.
What I want is that the articles are displayed under the right categorys dynamiclly.

Someone know how to fix it?

[Masterslave]

I try this:

<?php
$sql = "SELECT $articletable.ID, $articletable.title, $articletable.category,
$categorytable.ID, $categorytable.category
FROM $articletable left join $categorytable on $articletable.category = $categorytable.category";

$result = mysql_query($sql) or die(mysql_error());
?>

<div id="right">
<?php while ($row = mysql_fetch_assoc($AllCategorys))
{?>
<div class="category">
<h2><a href="categorie.php?category=<?=$row['category'];?>"><?=$row['category'];?></a></h2>
<ul>
<?php while ($items = mysql_fetch_assoc($result))
{?>
<li><a href="#"><?=$items['title'];?></a></li>
<?}?>
</ul>
</div>
<?}?>
</div>

Results this:

Feesten


Waarom willen leraren altijd gelijk hebben?
Angerfist Album Release Party
Hobby


Thoughts


The first item isn't in the right place, it's belongs to 'Thoughts'.
I've insert it right in my table, the first item has in the article table and the category table the value 'Thoughts'

I've also tried it with this query but it is the same result:

$sql = "SELECT $articletable.ID,$articletable.title,$articletable.category
FROM `$articletable`,$categorytable
WHERE $articletable.category=$categorytable.category";

[Masterslave]

I try this:

<?php
$sql = "SELECT $articletable.ID, $articletable.title, $articletable.category,
$categorytable.ID, $categorytable.category
FROM $articletable left join $categorytable on $articletable.category = $categorytable.category";

$result = mysql_query($sql) or die(mysql_error());
?>

<div id="right">
<?php while ($row = mysql_fetch_assoc($AllCategorys))
{?>
<div class="category">
<h2><a href="categorie.php?category=<?=$row['category'];?>"><?=$row['category'];?></a></h2>
<ul>
<?php while ($items = mysql_fetch_assoc($result))
{?>
<li><a href="#"><?=$items['title'];?></a></li>
<?}?>
</ul>
</div>
<?}?>
</div>
Results this:

Feesten


Waarom willen leraren altijd gelijk hebben?
Angerfist Album Release Party
Hobby


Thoughts

The first item isn't in the right place, it's belongs to 'Thoughts'.
I've insert it right in my table, the first item has in the article table and the category table the value 'Thoughts'

I've also tried it with this query but it is the same result:

$sql = "SELECT $articletable.ID,$articletable.title,$articletable.category
FROM `$articletable`,$categorytable
WHERE $articletable.category=$categorytable.category";


It's driving me nuts...

[guelphdad]

I tried your sig but I can't make it work.
What I want is that the articles are displayed under the right categorys dynamiclly.

Someone know how to fix it?

My solution does that. Nowhere in the code you posted have you got a variable to see if your category changes.

If you also look at your query you didn't bother with an order by clause. Failing to do so means things can show up in whatever order they are pulled from the database.

Look at the PHP part of the article again, change only the values you need to do and it should work for you.

[Masterslave]

Yeah, I did it.
My problem was that I had another while, and that was not nessesery.
Below is the code that works.


<?php
$sql = "SELECT *
FROM $articletable
RIGHT JOIN $categorytable
ON $articletable.category= $categorytable.category";

$result = mysql_query($sql) or die(mysql_error());
?>

<div id="right">
<?php while ($row = mysql_fetch_assoc($result))
{?>
<div class="category">
<h2><a href="categorie.php?category=<?=$row['category'];?>"><?=$row['category'];?></a></h2>
<ul>
<li><a href="article.php?ID=<?=$row['ID'];?>" title="<?=$row['title'];?>"><?=$row['title'];?></a></li>
</ul>
</div>
<?}?>
</div>


I'n so proud at myself.:D
Thanks for your help guelphdad.

[guelphdad]

That will print out category and subcategory for you. You will find two problems:

1) you have not used an order by clause in your sql query therefor your rows can be returned in any order. If you put in an order by category, subcategory you will be guaranteed that they will come out in an order where it is easy to print them off with the category only changing after each subcategory has been dealt with for that category

2) you have nothing in your script that checks when the category changes, if you don't do that you could print out category Horses and then subcategory Black horse, Brown horse and then a category of Dogs shows up, you print out Rottweiler and then the next row you are back to horses again.

if what you have works for you that's fine. I'm just pointing out the limitations of not having the order by clause.

[Masterslave]

That's correct guelphdad,

When I was developing, I saw that I have to add a order by clause.
So I did that.
My query looks like this at the moment.


<?php
$requestCategoriesAndArticles = "
SELECT $articletable.ID, $articletable.title, $articletable.category
FROM $articletable
RIGHT JOIN $categorytable
ON $articletable.category= $categorytable.category
ORDER BY $categorytable.category ASC, $articletable.ID DESC";

$AllCategoriesAndArticles = mysql_query($requestCategoriesAndArticles) or die(mysql_error());
?>



The only thing what is not going well is that I don't see the 3rd category, Hobby.
I think because Hobby hasn't any article under it...

[guelphdad]

I think you need to change your RIGHT join to a LEFT join.
A simple rule to remember, since in most places, we read from left to right, it is more intuitive to use a left join, then we think of the table on the left first instead of the one on the right.