Variable in one query is getting into another query in view

Question

I have two foreach statements. The variable from one one is somehow getting into another and i'm not sure how to fix it.

Here's my controller:

// Categories Page Code
    function categories($id)
    {
        $this->load->model('Business_model');
        $data['businessList']   = $this->Business_model->categoryPageList($id);
        $data['catList']    = $this->Business_model->categoryList();
        $data['featured']   = $this->Business_model->frontPageList();
        $data['user_id']    = $this->tank_auth->get_user_id();
        $data['username']   = $this->tank_auth->get_username();
        $data['page_title'] = 'Welcome To Jerome - Largest Ghost Town in America';
        $data['page'] = 'category_view'; // pass the actual view to use as a parameter
        $this->load->view('container',$data);

    }

What happens is categories will only show the businesses in a certain category.

The businesses are pulled from the database using the categoryPageList($id) function.

Here is that function:

function categoryPageList($id) {
    $this->db->select('b.id, b.busname, b.busowner, b.webaddress, p.thumb, v.title, c.catname, s.specname, p.thumb, c.id');
    $this->db->from ('business AS b');
    $this->db->where('b.category', $id);
    $this->db->join('photos AS p', 'p.busid = b.id', 'left');
    $this->db->join('video AS v', 'v.busid = b.id', 'left');
    $this->db->join('specials AS s', 's.busid = b.id', 'left');
    $this->db->join('category As c', 'b.category = c.id', 'left');
    $this->db->group_by("b.id");
    return $this->db->get();
}

And here is the view:

<h2>Welcome to Jerome, Arizona</h2> 

<p>Choose the Category of Business you are interested in:<br/> <?php foreach ($catList->result() as $row): ?>
            <a href="/site/categories/<?=$row->id?>"><?=$row->catname?></a>, &nbsp;
            <?php endforeach; ?></p>


<table id="businessTable" class="tablesorter">
    <thead><tr><th>Business Name</th><th>Business Owner</th><th>Web</th><th>Photos</th><th>Videos</th><th>Specials</th></tr></thead>
        <?php if(count($businessList) > 0) : foreach ($businessList->result() as $crow): ?>

            <tr>
                <td><a href="/site/business/<?=$crow->id?>"><?=$crow->busname?></a></td>
                <td><?=$crow->busowner?></td>
                <td><a href="<?=$crow->webaddress?>">Visit Site</a></td>
                <td>
                    <?php if(isset($crow->thumb)):?>
                    yes
                    <?php else:?>
                    no
                    <?php endif?>

                </td>

                <td>
                    <?php if(isset($crow->title)):?>
                    yes
                    <?php else:?>
                    no
                    <?php endif?>


                </td>
                <td>
                    <?php if(isset($crow->specname)):?>
                    yes
                    <?php else:?>
                    no
                    <?php endif?>


                </td>
            </tr>
        <?php endforeach; ?>

        <?php else : ?>
        <td colspan="4"><p>No Category Selected</p></td>

        <?php endif; ?>

</table>

The problem occurs here. <?=$crow->id?> should be showing the row id from the business table. Instead, it's showing the row ID of the category table. so, if i'm viewing /site/categories/6 <?=$crow->id?> will show 6 when it should be showing 10 (the row ID of the only business in that category at this time.

How can I fix this?

Answer 1

Your SELECT clause in categoryPageList contains two id columns; both that of the business and category table. Thus, you only fetch the business id.

$this->db->select('b.id, b.busname, b.busowner, b.webaddress, p.thumb, v.title, c.catname, s.specname, p.thumb, c.id');

Removing b.id should do the trick.

$this->db->select('b.busname, b.busowner, b.webaddress, p.thumb, v.title, c.catname, s.specname, p.thumb, c.id');

In case you need both values give them aliases.

$this->db->select('b.id business_id, b.busname, b.busowner, b.webaddress, p.thumb, v.title, c.catname, s.specname, p.thumb, c.id category_id');