CI Forum - get_where function for forum and topics

This is how I would do it. Moving modern and classic into categories and define the layout there.
https://pastebin.com/JTEKVwCT

Code:

SELECT `forums`.`category_id`, `categories`.`category_title`, `categories`.`category_layout`, `forums`.`forum_id`, `forums`.`forum_image`, `forums`.`forum_title`, `forums`.`forum_description` FROM `forums` INNER JOIN `categories` ON `forums`.`category_id`=`categories`.`category_id` ORDER BY `categories`.`category_position` ASC, `forums`.`forum_position` ASC

Sorry for taking so long to reply. @jreklund yes your naming for the database stuff and the structure is clearer. I will definitely re-do mine after i learn what i wanted to do, but still as you said i can either check with if in the view, like this:

Code:

<?php
$forum_category_id = $forum["forum_category_id"];
if($category_id === $forum_category_id) {
echo "<div id='{forum_category_id}'>test";
echo "</div>";
}?>

or get the result from those 2 tables in 1 query in the model. Can you show me how to do that?

Code:

public function get_categories()
        {
            $this->db->select('*');
            $this->db->from('categories');
            $this->db->order_by('category_position', 'asc');
            $query = $this->db->get();
            return $query->result_array();

            // SELECT * FROM categories ORDER BY category_position ASC;
        }

        public function get_categories_classic()
        {
            $this->db->select('*');
            $this->db->from('categories_classic');
            $this->db->where('category_classic_category_id', 2);
            $this->db->order_by('category_classic_position', 'asc');
            $query = $this->db->get();
            return $query->result_array();
               }

My structure is the following: 
The categories classic should be contained within categories.
Categories contains ->categories_classic and categories_modern
Categories_classic -> contains forums
Categories_modern ->contains forums
Forums -> contain topics

The above code gets my categories and then it gets all the categories_classic which are equal to 2. This 2 should be the ID of categories, but i don't know how to pull the ID of categories in another function. How can i do that?

And how can i do the thing u recommended? To get all the tables - categories, categories_classic and categories modern and  check them all for their relations, and return a result. This way i won't be checking in the view.

Thanks beforehand and happy new year!

What's the actual goal of your database structure? So that you can re-use forum(s) inside multiple categories (with different layouts)?
If that's the case a structure like this would be much more sense. Because your database dump dosen't contain a link between classic/modern and categories.
https://pastebin.com/WnrcWc4D

Categories = All categories, position and layout (modern/classic)
Forums = Forums
Layouts = Maps what forum goes into what category and in what order (position)
Note: position_id inside layouts should be layouts_id.
____________________________________________

You can pass plain SQL queries with this, it's faster then using the query builder. That's regarding the first database structure not the last one I did.

PHP Code:

$this->db->query("SELECT `forums`.`category_id`, `categories`.`category_title`, `categories`.`category_layout`, `forums`.`forum_id`, `forums`.`forum_image`, `forums`.`forum_title`, `forums`.`forum_description` FROM `forums` INNER JOIN `categories` ON `forums`.`category_id`=`categories`.`category_id` ORDER BY `categories`.`category_position` ASC, `forums`.`forum_position` ASC
")->result(); 


This should be the same, but slower.

PHP Code:

$this->db->select('`forums`.`category_id`, `categories`.`category_title`, `categories`.`category_layout`, `forums`.`forum_id`, `forums`.`forum_image`, `forums`.`forum_title`, `forums`.`forum_description`')
    ->from('forums')
    ->join('categories','`forums`.`category_id`=`categories`.`category_id`','inner')
    ->order_by('`categories`.`category_position`','ASC')
    ->order_by('`forums`.`forum_position`','ASC')
->get()
->result(); 


Yes a structure like yours seems nice, i will definitely change my structure later on, but since in vanilla php with this structure i achieved just what i wanted, before i make the changes i want to learn it in codeigniter too. Man i feel like such a noob for struggling so hard to get this right and i just can't get it.

I used your example above with the inner join and here's what happened. I will post my DB, MVC and a picture.
I selected all the categories and wanted to join inside the second category with id of 2, all the classic_categories. Instead i got 4 categories with the id of 2.
@jreklund thanks for helping me out with this, i would be glad to learn how to do this.

Picture
https://imgur.com/a/RTnE9

DB 
1.Categories 
- Category_id, category_position, category_title
- 1               , 1                        , Development
- 2,              , 2                        , Community

2.Categories_classic
- category_classic_id, category_classic_category_id, category_classic_position
- 1                         , 2                                        , 1
- 2                         , 2                                        , 2
- 3                         , 2                                        , 3
- 4                         , 2                                        , 4

Model

Code:

class Community_model extends CI_Model {

        public function __construct()
       {
           parent::__construct();
       }

        public function get_categories_with_forums()
        {
            $this->db->select('*')
           ->from('categories')
              ->join('categories_classic','`categories_classic`.`category_classic_category_id`=`categories`.`category_id`','inner')
           ->order_by('`categories`.`category_position`','ASC')
           ->order_by('`categories_classic`.`category_classic_position`','ASC');
            $query = $this->db->get();
            return $query->result_array();
        }
}

Controller

Code:

<?php

defined('BASEPATH') OR exit('No direct script access allowed');

class Community extends CI_Controller {

       public function index()
       {
           $this->load->model('Community_model');
           $data['categories_and_forums'] = $this->Community_model->get_categories_with_forums();

echo "<pre>";
print_r($data['categories_and_forums']);
echo "</pre>";
           

            $dataWidgets['login_widget'] = $this->load->view('widgets/login_widget', NULL, TRUE);
            $dataWidgets['staff_widget'] = $this->load->view('widgets/staff_widget', NULL, TRUE);
            $data['widgets'] = $this->load->view('widgets/widgets', $dataWidgets, TRUE);

            $this->load->view('templates/header', $data);
            $this->load->view('community/community', $data);
            $this->load->view('templates/footer', $data);
            

       }
}

View

Code:

   <div id="gameofthrones_content_main">

       <?php foreach($categories_and_forums as $category) : ?>

           <?php
           $category_id = $category["category_id"];
           $category_position = $category["category_position"];
            $category_title = $category["category_title"];
            
            echo "<div id='category_{$category_id}_head' class='category_head'>{$category_title}</div>";
            echo "<div id='category_{$category_id}_body'></div>";
            ?>    
    
       <?php endforeach ?>  

    </div> <!-- END id gameofthrones_content_main -->

The example SQL code I gave you are tailored for my DB structure, not your classic/modern in a separate table.

I'm not sure what you are expecting. You will need to join forums to be able to see the forum titles.
Right know you are just fetching categories and categories_classics. And there are four of them.
You got no forum_id inside categories_classics so you can't make a second join.

And forums only contains a position, not a category id. So you can't make a join from there either.

You will also need to create a separate SQL for your modern forums, that's why this ain't a great solution to start with.

If you got this working in your "vanilla php" you obviously have the SQL code. Just execute those. Codeigniter are just a tool.

Thats exacly what i want to do. Simply fetch all the categories filled with the right forums inside of them.

Im sorry for the hassle, im still in highschool and im a total noob in frameworks. In the "vanilla" using php and no frameworks was so easy, simply mix all the code in 1 page.. but later on its spaghetti code, not maintainable at all.

What i did was in 1 page.
1. Do sql for categories
2. Save query in variable
3. Display categories with foreach
4. Do sql for forums inside the above foreach
5. Save query in variable
6. Display forums in foreach

How can i achieve this with this framework? If it is hard to explain maybe a tutorial would be great. Problem is i didnt find any for codeigniter, otherwise i wouldnt be here.

You don't need a Codeigntier tutorial as you got the basic MVC correct. You need to read a couple of MySQL tutorials on how to do joins and how database relationships works.

Im not sure that i need to hit the books for databases. Databases should be just a simple container for my info. Joins or where is just a simple conditional statement. I need to know how people nest categories and subcategories in CodeIgniter!

I appreciate your help, but id wait for sm1 to actually give me a sample with how to do what i want. For whoever decides to help up and for the sake of simplicity lets say that my database looks like this and it has 2 tables.

1.Categories
- category_id, category_title
- 1          , Development
- 2,         , Community

2.Forums
- forum_id, forum_title, category_id
- 1       , test1      , 2
- 2       , test2      , 2
- 3       , test3      , 2
- 4       , test4      , 2

I want to fetch the data in the model, and display it in the view. The data must contain all the categories and within them, under the "Community" category, i must have 4 forums. How can i do this basic relationship in CodeIgniter and display my result.

Bump bump

Code:

$this->db->from('categories')
    ->join('forums', 'categories.category_id = forums.forum_category_id', 'left')
    ->get()
    ->result_array();

You now have all the data you need. Just need a loop or two and some logic applied to the HTML output.

(In the above I renamed your forum column from category_id to forum_category_id as I never repeat col names to avoid conflicts, and since this is just a made up example, I changed your example name.)