Displaying a User's Posts by Category

Question

Afternoon All,

Looking for some direction with listing posts by category.

I have begun coding a CMS (as a PHP beginner) and up until now all was going well.

I have made a link to pre-set categories (Category 1, Category 2 etc etc)

I also have a categories table with ID and Category name.

A table for users posts with CatID and category name in it as well

I'm guessing I would need to join tables to be able to list any posts in specific category (Select Cat 1 to see all Cat 1 posts. Same for Cat 2, 3 etc etc)

When a user adds a post it fills the category name in users posts table but I get no CAT ID and nothing added into categories table so how do i call on this to display categorised posts?

I have a feeling I am probably thinking to much into things and over-complicating what should probably be simple to do.

Ihe code i have at the moment (see below) has no effect at all?

Please help point me in the right direction, I have tried everything.

Many thanks to all in advance

CODE:

$catSql ="SELECT        ID, Category
        FROM        categories
        LEFT JOIN   users_posts
        ON          CatID, category, BlogID";

$catQry = mysqli_query($link, $catSql);

while ($row = mysqli_fetch_assoc($catQry)){

    if($row['category_name'] != $lastCategory)
{
   $lastCategory = $row['category'];
   echo "<br /><strong>$lastCategory</strong>";
}        
echo $row['category'] . ' <br />';

}

Answer 1

Your SQL is wrong I think this should work

$catSql = "SELECT *
    FROM        categories
    LEFT JOIN   users_posts
    ON          categories.ID = users_posts.CatID";