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

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 b开发者_高级运维ut 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

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.

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.

Im assuming that the tables are set up sort of like this:

1. Business Info
   
2. Photos
   
3. Videos
   
4. ?
   

You would create a unique ID for each business (autoincrement works nicely) and whenever a photo or video is uploaded for the business that record would be inserted into the respective tables with that ID attached to it (the ID column or whatever).

Each table would allow multiple entries for the business ("one to many" as Dyllon noted).

So whenever you are displaying the featured business you would run a query against both the video and photo tables using the unique ID and look for returned rows. Rows = yes, !rows = no.

One question: Are you going to store the images in the db or file names and have them uploaded to a directory?

The only other input that I would add to the above recommendations is to structure your tables so that there are never any null values in the tables themselves, which may be the case seeing as you have four tables for this project already.

I second the multi dimensional array/object method of storing and sorting through the returned data.

A nice touch would be to use the PHP image functions to resize the first image returned as a thumbnail for the photos - you could also catch a frame for each video as well - so instead of yes or no, we would seem thumbnails for both or a 'no images/video' slug.

Not much, but my $.02.

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

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();
}

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>