CodeIgniter Forums

Full Version: CI Forum - get_where function for forum and topics
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Pages: 1 2 3
Hello, can i get a little help with the get_where function? how can i use the function in order to make an SQL which looks like this:

$sql_categories_classic = "SELECT * FROM forums WHERE forum_id = {$topic_forum_id} ORDER BY forum_position ASC";

The {topic_forum_id} is from another SQL, in a procedural style. Basically i don't know how to do it in OOP. I figured out how to display the forums, but i don't know how to display all related topics inside a particular forum. This is how i display the forums.

The model
$this->db->select('*');
$this->db->from('forums');
$this->db->order_by('forum_position', 'asc');
$query = $this->db->get();
return $query->result_array();


The controller
$this->load->model('Community_model');
$data['forums'] = $this->Community_model->get_forums();

The view
<?php foreach($forums as $forum) : ?>

<?php
$category_id = $forum["forum_id"];
$category_position = $forum["forum_position"];
$category_title = $forum["forum_title"];

echo "<div id='forum_{$forum_id}_head' class='forum_head'>{$forum_title}</div>";
echo "<div id='forum_{$forum_id}_body'></div>";
?> 

<?php endforeach ?>

----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----


How do i go from here? How can i use the get_where? i tried with db->join('topics.topic_id') but it mixed up and all topics displayed in all forums. I tried asking at stackoverflow, but they downvoted me for easy question and maybe for trying to learn smt... lol. I hope im not bothering u guys. Thanks beforehand!
Not tested but should work.
PHP Code:
$this->db->get_where('forums', array('forum_id' => $topic_forum_id))->order_by('forum_position','ASC')->result_array(); 
Yea that should work, but how can i reach the $topic_forum_id ,which is from another table in the database. What would be the best way? I should have said earlier that i wanted a cleaner way. Thanks!

Basically how to make relations between tables in the database with codeigniter.
Make an index, forum and thread controllers.

1. Index: Generate a query listing all categories:
Choosing CodeIgniter
Installation & Setup
Model-View-Controller
Libraries & Helpers
Best Practices
General Help
2. Forum: User selects "General Help" and you will retrieve 24 (id) from the url
3. Forum: Query your database for threads
4. Thread: User selects "CI Forum - get_where function for forum and topics" and you retrieve 69636 (id) from the url
5. Thread: Query your database for posts

https://www.codeigniter.com/userguide3/t...ction.html
I think i managed to do it, but i have another problem now! This is what i am getting as a result.

https://imgur.com/a/cGsW3 - Click for image.

The problem is that the topics must be displayed only under the "Community" forum. Here is how my model looks like after using your advise.

Code:
    class Community_model extends CI_Model {

        public function get_categories()
        {
            $this->db->order_by('category_position', 'asc');
            $q = $this->db->get('categories');
            return $q->result_array();
        }

        public function get_forums()
        {
            $this->db->order_by('forums.forum_position', 'ASC');
            $this->db->join('categories', 'categories.category_id = forums.forum_category_id');
            $query = $this->db->get_where('forums', array('forum_category_id' => 2));
            return $query->result_array();
        }
    }
When i print_r on the get_forums function, i can see that it only got 4 forums, which are related only to category with id 2. This means that i am displaying the data in the view in a wrong way. Can i get some help with the view?
Code:
    <?php foreach($categories 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 foreach($forums as $forum) : ?>
                
                <?php
                $forum_category_id = $forum["forum_category_id"];

                echo "<div id='{forum_category_id}'>test";
                echo "</div>";
                ?>

            <?php endforeach ?>

       <?php endforeach ?>
You will need to check if the forum belongs to the same category.
In your case, you will need to include an if statement.
PHP Code:
                <?php
                $forum_category_id 
$forum["forum_category_id"];
                if(
$category_id === $forum_category_id) {
                    echo 
"<div id='{forum_category_id}'>test";
                    echo 
"</div>";
                }
                ?>

A more recommended way are to return the categories and forum topics in the same query. So that you don't need to re-loop $forums every time.
1. Print category
2. Print forum(s) as long as previous cat_id matches the next one
3. No match. Close tags and start from 1.
you can use this code

function getLatestUnansweredForums($searchData) {
        $this->db->select('tbl_forums_posts.id,tbl_forums_posts.slug,tbl_forums_posts.sub_category_id,tbl_forums_posts.user_id,tbl_forums_posts.subject,tbl_forums_posts.add_date');
        $this->db->select('tbl_forums_sub_category.sub_category_name');
        $this->db->where('tbl_forums_posts.id NOT IN (select post_id from tbl_forums_comments)',NULL,FALSE);
        $this->db->where('tbl_forums_posts.status','Active');
        $this->db->order_by('tbl_forums_posts.add_date','DESC');
        $query = $this->db->get('tbl_forums_posts');
        $result = $query->result();
        return $result;
    }
@jreklund thanks! I will test out both ways to check them out and i think that returning the results in 1 query would be the best way because the view stays fresh. Thank you!

EDIT: Actually how can i check them both in the model? Can you give me an example for something simple? Say we have:

forums - forum_id, forum_title
topics - topic_id, topic_title
(12-29-2017, 05:50 AM)Knightwalker Wrote: [ -> ]Actually how can i check them both in the model? Can you give me an example for something simple? Say we have:

forums - forum_id, forum_title
topics - topic_id, topic_title
Dump your real example database and include it here or on pastebin. It's easier to work on real data.
You should read up on how to do JOIN's in MySQL, as this are as easy as them comes. This query can be pasted directly into $this->db->query(); as you don't need to supply an external ID.
Okay, but for the sake of simplicity i was asking for an easy example, because my actual goal is to create a relation for a few other things
Pastebin -> https://pastebin.com/YFuBAqEP.
Desired Result - > https://imagebin.ca/v/3mMimV8pn72l

categories -> they display categories_classic and categories_modern
categories_modern -> they display all forums related only to them in a modern view
categories_classic -> they display all forums related only to them in a classic view.

forums - > they display all the topics related to them on a new page
Pages: 1 2 3