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

Question

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.

Here's my pastebin

Answer 1

I'm going to take a stab at this.

What I would do is, when generating the table for display of "featured" businesses I would make a call to your database, the one generally made when generating the photos and videos categories and check the output. If there is something there then have it say yes, if the return from the database is null, it's a safe assumption there aren't any photos or videos and thus you can print out a No.

Answer 2

I'm going to assume here that your photos and videos tables contain more than 1 record per business, a "one to many" relationship.

In this case you're going to need to rethink your approach or at least do some formatting of your database results. Currently if Joes crab shack has more than 1 videos and/or photos you're going to have multiple results for Joes crab shack.

If you need photos/videos results you could format the results as you are receiving them now to create a multi dimensional array/object where $featured['videos'] or $featured['photos] would contain the results of your join, if your formatted array doesn't contain the 'videos' key then you have no video results.

If all you need to know is if it's returning more than 0 rows then create two new methods in your model to count videos and photos that belong to a specific business. Now it's just a matter of echo'ing 'Yes' if the methods return more than 0 rows or 'No' otherwise.

Edit: Model function should look something like this

function frontPageList() {
    $this->db->select('b.busname, b.busowner, b.webaddress');
    $this->db->select('(SELECT COUNT(1) FROM photos WHERE busid = b.id) AS photo_count', FALSE);
    $this->db->select('(SELECT COUNT(1) FROM videos WHERE busid = b.id) AS video_count', FALSE);
    $this->db->from ('business AS b');
    $this->db->where('featured', '1');
    return $this->db->get();
}

Answer 3

You can cheat a little with something like this:

SELECT b.[other stuff],
(SELECT COUNT(1) FROM photos WHERE busid = b.id) AS photo_count,
(SELECT COUNT(1) FROM videos WHERE busid = b.id) AS video_count
FROM business AS b [etc]

photo_count & video_count will return 0 or greater than 0 - easy enough to get no/yes from in PHP. That would also stop the duplicate results you'd get from having more than 1 photo/video per business.

Caveat!: converting that to the ORM you're using... I'm not sure an ORM would be okay with it. But if it's not barking over the "AS" aliasing in from() & join(), maybe you can sneak the subqueries in select() without any problems.

Answer 4

SELECT b.busname
     , b.busowner
     , b.webaddress
     , IF (EXISTS (SELECT 1 FROM photos p WHERE p.busid = b.id), 'Yes', 'No') has_photo
     , IF (EXISTS (SELECT 1 FROM video  v WHERE v.busid = b.id), 'Yes', 'No') has_video
  FROM business b
 WHERE b.featured = 1

Answer 5

Ok, here is what I was able to do to solve the problem based upon suggestions from you guys:

Model:

function frontPageList() {
    $this->db->select('b.busname, b.busowner, b.webaddress, p.photoname, v.title');
    $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();

}

Control:

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:

<table id="businessTable">
    <thead><tr><th>Business Name</th><th>Business Owner</th><th>Web</th><th>Photos</th><th>Videos</th></tr></thead>
        <?php foreach ($featured->result() as $row): ?>
            <tr>
                <td><?=$row->busname?></td>
                <td><?=$row->busowner?></td>
                <td><a href="<?=$row->webaddress?>">Visit Site</a></td>
                <td>
                    <?php if(isset($row->photoname)):?>
                    no
                    <?php else:?>
                    yes
                    <?php endif?>

                </td>

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


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

</table>