Running foreach is causing duplicate results

[Jeffw]

On my crud website the homepage displays all published posts and just above that I’m putting the various categories as links that the posts are attributed to. so that the user can click the category link and display only the posts attributed to that particular category.
So far I have tried using a foreach to display the categories, and they do display, but it is displaying all of the categories the posts are attributed to which causes some categories to display multiple times. I think this is because multiple posts are tied to the same category. I tried placing a break like this but then it displays only one category. I wanted to see if I can get some advice on getting each category to display only once?
Also my database query contains some joins which i’m worried is limiting my options. When I click the category link though the posts do display as intended so I was hoping there is a way around this.Thank you

<?php foreach ($stmt as $row) { ?>
    
    <h3><table><tr><th><a href="category.php?id=<?php echo $row['category_id'] ?>"><?php echo ($row['category']) ?></th></tr></table></h3>
    
    
    <?php break; ?>
     
    <?php } ?>

[Strider64]

Wouldn’t it be better just to do that in the query? I personally don’t see why it would limit your options even if you are using multiple tables? It would be much easier unless I am not understanding you correctly?

[Jeffw]

Yes if there’s a way to do that i’m all for it. would something like that involve another WHERE? this is the query. my other idea is adding a something to the foreach to check for duplicates and only echo each one once.

       $stmt = $pdo->query("SELECT posts.id, posts.title, posts.summary, image.file, posts.image_id, 
                                  posts.member_id, posts.category_id, c.name AS category,
                           
                        CONCAT(m.first_name, ' ', m.last_name) AS author

                           FROM posts
                           JOIN category   AS c ON posts.category_id = c.id
                           JOIN member     AS m ON posts.member_id   = m.id
                           LEFT JOIN image ON posts.image_id = image.id
                           WHERE posts.published = 1")->fetchAll(); ?>

[phdr]

See php’s array_column() function to get a specific column of data (category), indexed by another specific column (category_id), which keeps only the last entry when there are duplicates, then loop over the result to produce the category links.

[Jeffw]

thank you that seems to be what I’ve been looking for. The only issue is I’m only able to get it to output a the category id as a link rather than category name.

<?php 

foreach (array_column($stmt, 'category_id', 'category') as $row) {
    
    

    ?>
    
    <h3><table><tr><th><a href="category.php?id=<?php echo $row ?>"><?php echo $row ?></th></tr></table></h3>
    
   
    
    
    <?php } ?>

[phdr]

You would want to use the foreach - foreach (iterable_expression as $key => $value) syntax since you want both the key and value.