Wrong Result Codeigniter Query

0
votes

I'am new in codeigniter and php. This is my models:

function getRelated($newsId, $catId){
    $this->db->select('a.id, a.category, a.title, a.photo, a.caption, a. summary, a.detail, a.page_views, b.cat_id');
    $this->db->from('news as a');
    $this->db->order_by("time", "desc");
    $this->db->join('news_categories as b', 'a.id = b.news_id');
    $this->db->where('a.id <>', $newsId);
    $this->db->where('b.cat_id', $catId);
    $query = $this->db->get();
    echo $this->db->last_query();
    return $query->result();
}

this is my controller:

public function detail($id, $catId){
        $this->load->model('news_model');
        $data['list'] = $this->news_model->get($id);
        $data['related'] = $this->news_model->getRelated($id, $catId);

        $this->load->view('page_header');           
        $this->load->view('news_detail', $data);            
        $this->load->view('page_footer');
}

And this is the result of query in views:

SELECT a.id, a.category, a.title, a.photo, a.caption, a. summary, a.detail, a.page_views, b.cat_id FROM news as a JOIN news_categories as b ON a.id = b.news_id WHERE a.id <> '2' AND b.cat_id = 'title%20one%20test1' ORDER BY time DESC

As you see above (views), the result of cat_id is title.. Whats wrong with my code?

my view file (news_detail):

<h3>Related News</h3>
<?php
$i = 0;
foreach ($related as $key => $value) {
    if (++$i > 3)
        break;
    ?>
    <div class="list">
        <a class="title" href="news/detail/<?= $value->id . "/" . $value->title ?>"><?= $value->title ?></a>
    </div>
<?php
}?>

The category of post is multiple choice. So, every post can have more than one category. What should i do?

Thanks in advance

4
phpcodeigniter
Your SQL looks fine. I am confused as to what the issue is. Can you post the relevant section of your view named news_detail.php? thanks - MonkeyZeus
It looks like your $catId is a string, not an integer. How is your route setup look like? So if you haven't setup any routes, de default should be the_controller_name/detail/2/3 - Iamzozo
Hi @MonkeyZeus and Iamzozo : I just updated my question. I forgot to tell you that every post can have more than one category. What shoud i do? Thanks - Sleepyhead
can you please tell me what is $id and $cat_id - Vinie

4 Answers

0
votes

You are doing mistake here

<a class="title" href="news/detail/<?= $value->id . "/" . $value->title ?>"><?= $value->title ?></a>

When you defined any function like

public function detail($id, $catId){ }

then $id will be first segment after method name detail i.e. $value->id and $catId will be second segment after method name i.e. $value->title.

so add catID as a second segment in link as below <a class="title" href="news/detail/<?= $value->id . "/" . $value->catID ?>"><?= $value->title ?></a>

0
votes

I've modified your code. Try this...!

function getRelated($newsId, $catId){
        $this->db->select('a.id, a.category, a.title, a.photo, a.caption, a. summary, a.detail, a.page_views, b.cat_id');
        $this->db->from('news as a');
        $this->db->order_by("time", "desc");
        $this->db->join('news_categories as b', 'a.id = b.news_id and a.id<>'.$newID.'and b.cat_id=$catID','inner');
        $query = $this->db->get();
        echo $this->db->last_query();
        return $query->result();
}
0
votes

Change anchor tag in view file (news_detail).

Here you are sending title rather than category id

replace

<a class="title" href="news/detail/<?= $value->id . "/" . $value->title?>"><?= $value->title ?></a>

by

<a class="title" href="news/detail/<?= $value->id . "/" . $value->cat_id?>"><?= $value->title ?></a>
0
votes

replace view file (news_detail):

<h3>Related News</h3>
<?php
$i = 0;
foreach ($related as $key => $value) {
    if (++$i > 3)
        break;
    ?>
    <div class="list">
        <a class="title" href="news/detail/<?= $value->id . "/" . $value->cat_id ?>"><?= $value->title ?></a>
    </div>
<?php
}?>