Displaying a User's Posts by Category

3
votes

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 />';

}
2
phpmysqlselectblogscategories
Your ON condition does not make any sense (there is no condition, just a list of columns) and your ID could lead to a duplicate column error (if both tables have an ID column). You should add error handling to see what is wrong exactly. - jeroen
thanks for your reply. - Richard H

2 Answers

1
votes

Your SQL for listing posts could check to see if a categoryID exists in the URL, and if it does, use it to filter the results.. Then, you would just need to create some links to the same page to add the required category id to the URL.

<a href='?cat=1'>Cat 1</a> | <a href='?cat=2'>Cat 2</a> | etc..

SQL

$sql = "SELECT * FROM POSTS";
if(isset($_GET['cat'])){
    $catID = (int)$_GET['cat']; //or something similar
    $sql .= " LEFT JOIN category USING categoryID WHERE categoryID = $catID";
}
1
votes

Your SQL is wrong I think this should work

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