How to create categories and associate them with posts

Hi guys, I'm using the Codeigniter framework, I'm fine. However, I would need help with the creation of categories associated with any hypothetical post. I'm creating a small script where every post has to be associated with a category, but it is not practical to follow the right practices for creating tables in databases.

If anyone can help me, I would be grateful.

In the database I guess I can create these tables and fields:

Post: id, title, description, category
Categories: id, category_name

So, I create categories within "Categories".
Subsequently, each post has the classic id, title, description, and assigned category.

In my home, I show all the results.
While in the category page I get only the results associated with the category.

This is how I think it should be done in words, if anyone can advise me on an article maybe it would be better. Thanks to anyone who will help me!

I think you may have to decide if it is 1-1 relation or 1-many.  For example, if post only belongs to one category or possible a post
may have more than one category it is up to you to think of designing your database.

Either case the query to show all results is straight forward.

Regards

I would like posts to have only one category. Could you give me an example of the schema that should have the database? for example: category: id_cat, id_post, etc

I'm doing some tests, like counting how many posts are in a category. Unfortunately I can not get it running because it tells me it can not pick up the variable, but if I do "echo" me it prints correctly.

Anyway, I cleaned up everything and now only show all the results in the "love" category. How can I make the variable of all categories in place?

Controller:

PHP Code:

public function category($slug_category) {
        $this->load->view('post/category');
    } 


Model

PHP Code:

public function countPostCat() {
        $this->db->like('id');
        $this->db->from('post');
        $this->db->where('slug_category', 'love');
        return $this->db->count_all_results();
    } 


Views

PHP Code:

(<?php echo $this->admin_model->countPostCat(); ?>) 


In your model, create function that groups the results on 'slug_category' and counts how many records are in each category.

PHP Code:

public function count_per_category()
{
  $query = $this->db
  ->select('slug_category, COUNT(id) as cat_count')
  ->from('posts')
  ->group_by('slug_category')
  ->order_by('slug_category')
  ->get();
 
 return $query->result_array();
} 


Call this function from your controller, not from the view.
Controller:

PHP Code:

$data['cpcs'] = $this->admin_model->count_per_category();
$this->load->view('post/category',$data); 


In the view:

PHP Code:

<table>
<tr><th>Category</th><th>Count</th></tr>
<?php foreach($cpcs as $cpc) : ?>
  <tr>
    <td><?= $cpc['slug_category'];?></td>
    <td><?= $cpc['cat_count'];?></td>
  </tr>
<?php endforeach; ?>
</table> 


Thanks a lot, you've been helpful. Unfortunately, however, you show me all the categories and their relative counts. I would like to show every single category, I'll explain it better.

If you go to miosito.com/category/love

I show counting articles and articles

Thank you anyway, you have been kind

PS: Okay, okay, I did this

PHP Code:

public function category($slug_category) {

        $data['cpcs'] = $this->post_model->count_per_category();
        $data['count'] = $this->post_model->countPostCat($slug_category);
        
        $this->load->view('post/category', $data); 
    } 


If you go to miosito.com/category/love
I show counting articles and articles

No, it doesn't. It gives a blank page.

If you just want the count of one category, you could do this:

PHP Code:

public function count_per_category($category=NULL)
{
 $this->db
  ->select('slug_category, COUNT(id) as cat_count')
  ->from('posts');
  if ($category) $this->db->where('slug_category',$category);
  $query = $this->db
  ->group_by('slug_category')
  ->order_by('slug_category')
  ->get();
 
 return $query->result_array();
} 


Now, the function is flexible. You can use it to get the count of all categories, or just one category.