CODEWITHSUNDEEP
phpmysqltagscategoriesarticle
RXMESH
RXMESH

Reputation: 286

Multiple Categories for a single Article (PHP MYSQL)

Need help showing all the categories for a single article post.

Here´s my database structure:

blog_posts table

postID int, primary, auto increment.

postTitle varchar

postCont text

blog_categories table

catID int, primary, auto increment.

catName varchar

blog_posts_categories table

postID int

catID int

Database content:

blog_posts

postID  |  postTitle  | postCont

  1          Post1       Cont1
  2          Post2       Cont2
  3          Post3       Cont3
  4          Post4       Cont4
  5          Post5       Cont5

blog_categories

catID   |   catName

  1          Music
  2          Games
  3        Technology

blog_posts_categories

postID  |  catID

  1          1
  1          2
  1          3
  2          2
  3          3
  4          1
  4          2
  5          2
  5          3

here's the code i use to fetch data to the single post. an example post page URL would look like this: http://domain.com/viewpost.php?postID=1

<?php require('includes/config.php'); 

$stmt = $db->prepare("  SELECT * 
                        FROM blog_posts 
                        LEFT JOIN  blog_posts_categories ON blog_posts.postID=blog_posts_categories.postID 
                        INNER JOIN blog_categories ON blog_posts_categories.catID=blog_categories.catID 
                        WHERE blog_posts.postID = :postID");

$stmt->execute(array(':postID' => $_GET['id']));
$row = $stmt->fetch();

//if post does not exists redirect user to homepage.
if($row['postID'] == ''){
    header('Location: ./');
    exit;
}

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title><?php echo $row['postTitle'];?> | Website</title>
    <link rel="stylesheet" href="style/normalize.css">
    <link rel="stylesheet" href="style/main.css">
</head>
<body>

    <div id="wrapper">

        <h1>Single Post Page</h1>
        <hr />
        <p><a href="./">Home</a> | Categories: <?php echo ''.$row['catName'].''; ?></p>

        <div>
            <?php 
                echo '<h1>'.$row['postTitle'].'</h1>';
                echo '<p>'.$row['postCont'].'</p>'; 
            ?>
        </div>

    </div>

</body>
</html>

for now this code just echo's 1 single Category name but i'd pretty much like it to show all the categories that are linked to that post nice in order. I just can't wrap my head around this don't know what to type I am pretty much still a beginner. Anyway thanks for your time!

Upvotes: 0

Views: 5199

Answers (2)

Ballantine
Ballantine

Reputation: 338

If your query return more than one line, you have to loop on the result of this query to achieve what you want.

An example can be :

while($row = $stmt->fetch()) { // Your stuff here }

If you use PDO, you can use fetchAll instead of fetch, and you will get an array instead of a single row (but you always need to loop).

Upvotes: 2

colburton
colburton

Reputation: 4715

That is already getting you the desired result, but in multiple lines. You are only fetching the first one. So you have only one category in the result.

One way to do what you want is this:

SELECT *, GROUP_CONCAT(catName) AS allCategories FROM ...

Then you only get one result line. Echo it with $row['allCategories'] instead of $row['catName']

Upvotes: 0

Related Questions