Correct PHP loop to display post categories

Question

I have two tables “categories” and “posts”, I’m trying to create a category.php page that displays the different categories with post titles as links to the original posts. I’ve tried different variations of loops but can’t seem to get it right. I’m hoping someone could point me in the right direction.

$query = "SELECT post_id, title, body, category_id, posted
      FROM posts
      INNER JOIN categories ON categories.category_id = posts.category_id";

$result = mysqli_query($dbc, $query)
      or die('Error querying database.');

while ($row = mysqli_fetch_array($result)){
            <a href='index.php?id=<?php echo $post['post_id']; ?>' ><?php echo $post['title']; ?></a></h2>
 <p>
    Posted on <?php echo date('d-m-y h:i:s',strtotime($post['date_posted'])); ?>
    In <a href='category.php?id=<?php echo $post['category_id']; ?>' ><?php echo $post['name']; ?></a>
 </p>
    echo "<hr />";
}

Thank you for any input

`while ($row = mysqli_fetch_array($result)){` but accessing `$post`. Are you sure? — u_mulder, Jan 23 '16 at 17:50
Consult these following links http://php.net/manual/en/mysqli.error.php and http://php.net/manual/en/function.error-reporting.php and apply that to your code. also view HTML source. you're already in PHP also. syntax errors. — Funk Forty Niner, Jan 23 '16 at 17:54

score 0 · Answer 1 · answered Jan 23 '16 at 17:57

Forgive my syntax (it may be off a bit), but this should give you a good idea:

$categories = array(
    'sports' => array(
        post1,
        post2,
    ),
    'weather' => array(
        post1,
        post2,
    ),
);

Get all of your categories first, then loop through each and get all the posts related to that category. Then you can loop through the array normally like:

foreach($categories as $category){
    //display category title
    foreach($category['posts'] as $post){
        //display post info
    }
}

score 0 · Answer 2 · answered Jan 23 '16 at 18:00

You have some issues in your code.

Modified code:

$query = "SELECT post_id, title, body, category_id, posted FROM posts INNER JOIN categories ON categories.category_id = posts.category_id"; 

$result = mysqli_query($dbc, $query) or die('Error querying database.');

 while ($row = mysqli_fetch_array($result)){ 
?> 

<a href="index.php?id=<?php echo $row['post_id']; ?>" >

<?php echo $row['title']; ?></a></h2> <p>
 Posted on
 <?php 
echo date('d-m-y h:i:s',strtotime($row['date_posted'])); 
?> 

In 
<a href="category.php?id=<?php echo $row['category_id']; ?>">

<?php 
echo $row['name']; 
?></a> </p> 
<hr />
<?php } ?>

Issues:

array index using with single quote inside the single quote.
wrong variable using post.
html tags inside the php

You can use single quotes in this case. You're opening and closing php tags every time... Doesn't affect... — palawer, Jan 23 '16 at 18:03

score 0 · Answer 3 · answered Jan 23 '16 at 18:05

You are using a variable called $post inside your while loop, but you are retrieving each row in that while loop into a variable called $row.

Fix that and many of your issues will disappear.

You also need to stop and start the PHP interpreter when you want to output HTML in the way that you have done here, note the ?> and <?php I addded

$query = "SELECT post_id, title, body, category_id, posted
      FROM posts
      INNER JOIN categories ON categories.category_id = posts.category_id";

$result = mysqli_query($dbc, $query)
      or die('Error querying database.');

// change this to use a variable called $post
while ($post = mysqli_fetch_array($result)){
?>

    <a href='index.php?id=<?php echo $post['post_id']; ?>' ><?php echo $post['title']; ?></a>

    <!-- Dont think this should be here -->
    </h2>

    <p>Posted on <?php echo date('d-m-y h:i:s',strtotime( $post['date_posted'] ) );?>In <a href='category.php?id=<?php echo $post['category_id']; ?>' ><?php echo $post['name']; ?></a>
    </p>

<?php
    echo "<hr />";
}
?>

Thank you for your help and corrections, I have fixed the code as you suggest but now I get Notice: Undefined index: category — Solano, Jan 23 '16 at 18:16
I dont see anywhere in this code where an array index of `category` is being used do you mean `category_id` — RiggsFolly, Jan 24 '16 at 00:11

YounesM · Answer 4 · 2016-01-23T17:54:39.703

There is HTML code inside <?php ?> try this :

<?php $query = "SELECT post_id, title, body, category_id, posted
      FROM posts
      INNER JOIN categories ON categories.category_id = posts.category_id";

$result = mysqli_query($dbc, $query)
      or die('Error querying database.');

while ($row = mysqli_fetch_array($result)){ ?>
            <a href='index.php?id=<?php echo $post['post_id']; ?>' ><?php echo $post['title']; ?></a></h2>
 <p>
    Posted on <?php echo date('d-m-y h:i:s',strtotime($post['date_posted'])); ?>
    In <a href='category.php?id=<?php echo $post['category_id']; ?>' ><?php echo $post['name']; ?></a>
 </p>
    echo "<hr />";
<?php } ?>

Correct PHP loop to display post categories

4 Answers4