Left Join 3 Tables and Show True or False on Empty Cells

[eluser]jshultz[/eluser]
Ok, this may be confusing so I hope I explain it correctly.

I have 4 tables:

business photos video category

I want to display "featured" businesses on the home page (or elsewhere) and I want to show a Yes or No in the table row based upon whether or not there are photos or videos for that business.

Example: Joes Crab Shack has no videos or photos but is featured. So, when his row is echoed out it will show the Business Name and Business Owner but there will be no data in the photo or video cells thus in the video and photos column it will say No. Otherwise, if the opposite was true, it would say Yes.

Maybe I'm doing this wrong? Is there a better way? I might be making this harder then what it needs to be so I'm totally open to suggestions.

Model:

Code:

function frontPageList() {
    $this->db->select('b.busname, b.busowner, b.webaddress', 'p.photoname', 'v.title');
    $this->db->select(COUNT(1) from photos WHERE busid = b.id) AS photo_count;
    $this->db->from ('business AS b');
    $this->db->where('featured', '1');
    $this->db->join('photos AS p', 'p.busid = b.id', 'left');
    $this->db->join('video AS v', 'v.busid = b.id', 'left');
    return $this->db->get();
    
}

my select(COUNT(1)... line is not working obviously.

Controller:

Code:

function index()
    {
        $this->load->model('Business_model');
        $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'] = 'welcome_message'; // pass the actual view to use as a parameter
        $this->load->view('container',$data);
    }

View (which isn't done):

Code:

<table id="businessTable">
    <thead><tr><th>Business Name</th><th>Business Owner</th><th>Web</th><th>Photos</th><th>Videos</th></tr></thead>
        &lt;?php foreach ($featured->result() as $row): ?&gt;
            <tr>
                <td>&lt;?=$row->busname?&gt;</td>
                <td>&lt;?=$row->busowner?&gt;</td>
                <td><a >webaddress?&gt;">Visit Site</a></td>
                <td></td>
                <td></td>
            </tr>
        &lt;?php endforeach; ?&gt;

</table>

[eluser]must[/eluser]
hey,
i don't have much experience with joins, but depending on what i read in some tutorials and wikipedia left joins will return null on fields that doesn't match.
So i think that you can test for the value of $row->photoname in your view (if it's null echo "no" else display the photoname or echo yes) same goes for video. and of course you wouldn't need this line anymore:

Code:

$this->db->select(COUNT(1) from photos WHERE busid = b.id) AS photo_count;

[eluser]jshultz[/eluser]
[quote author="must" date="1268960776"]hey,
i don't have much experience with joins, but depending on what i read in some tutorials and wikipedia left joins will return null on fields that doesn't match.
So i think that you can test for the value of $row->photoname in your view (if it's null echo "no" else display the photoname or echo yes) same goes for video. and of course you wouldn't need this line anymore:

Code:

$this->db->select(COUNT(1) from photos WHERE busid = b.id) AS photo_count;

[/quote]

I thought about this but you're the first one to suggest it. How would I test the value of the row? I'm sure it would be some sort of if-then-else statement but wasn't sure how to frame it.

[eluser]must[/eluser]
something like :

Code:

<td>&lt;?php if(is_null($row->photoname)):?&gt;
no
&lt;?php else:?&gt;
yes
&lt;?php endif?&gt;</td>
<td>&lt;?php if(is_null($row->title)):?&gt;
no
&lt;?php else:?&gt;
yes
&lt;?php endif;?&gt;
</td>

[eluser]jshultz[/eluser]
It almost worked, except I'm getting the errors: Message: Undefined property: stdClass::$photoname and Message: Undefined property: stdClass::$title

[eluser]must[/eluser]
can you print_r($row) or print_r($featured->result()) and tell us the result?

[eluser]must[/eluser]
try replacing

Code:

$this->db->select('b.busname, b.busowner, b.webaddress', 'p.photoname', 'v.title');

with

Code:

$this->db->select('b.busname, b.busowner, b.webaddress, p.photoname, v.title');

[eluser]jshultz[/eluser]
[quote author="must" date="1268963694"]can you print_r($row) or print_r($featured->result()) and tell us the result?[/quote]

well, I see part of the problem:

Code:

stdClass Object ( [busname] => Turquoise Spider [busowner] => Danny Clark [webaddress] => http://turquoisespider.com )

it wasn't getting all of the results.

You were right, the extra ' were making a difference. it's all working correctly now: http://welcometojerome.com SWEET! THANK YOU THANK YOU THANK YOU!

[eluser]must[/eluser]
I'm glad I could help