A set of queries in PHP to show occupancy of various cabins

Question

Okay so as I learn more about PHP and really strive to improve my code I have a few questions about a current setup of mine and how to approach it in the best way.

I think I have all the tools necessary to get this done the right way, but I am having trouble putting them together. I am also trying to separate my code better.

Scenario: the script in question is cabins.php which basically pulls information from the db to populate the page with cabin information based on the cabin id $_GET variable. So the user clicks on a cabin, then the page is populated with that cabins information. There are four sub menus to choose from for each cabin so I am accessing this information using a switch statement, but I am having trouble finding the best way to run my queries.

My data is structured that most of the cabin information is held in 'cabin_content' but I also have 'cabin_large_images' and 'cabin_small_images' which each hold images that are tied to the cabin_content table through cabin_id.

Each sub section requires different information from the cabin_content table, but each sub section ALWAYS requires all the images from cabin_large_images that match the cabin id.

So each section is like this: -we must query cabin_content and get the specific information we need -we must query cabin_large_images and get all the images with the cabin_id.

One section requires all of the above plus we have to query cabin_small_images and get all the images from that table with matching cabin id.

So right now I have it set up like this, which I know needs improvement...obviously that is why I am here...Pseudo code:

if(isset($_GET['cabin')){

 //run the query to get cabin_large_images because we know we need them regardless of the section

   if(isset($_GET['type'])){
      $type = $_GET['type'];

       switch ($type){

       case 'a':

//run the query on cabin_content for the specific info we need in this situation

       break;

       case 'b':

//run the query on cabin_content for the specific info we need in this situation

       break;

       case 'c':

//run the query on cabin_content for the specific info we need in this situation
//run an additional query on cabin_small_images

       break;


      }

 }




//populate content to view

Okay so obviously what I don't like about this is we are running as many as three queries per page load. I tried using inner joins, but I just could never access what I needed from the query properly:

SELECT cabin_name, peak_week, max_occupancy, image 
FROM cabin_content  
INNER JOIN cabin_images  
  ON cabin_content.cabin_id = cabin_images.cabin_id 
WHERE cabin_content.cabin_id = '$cabin'";

The issue here is that we can end up with multiple images from $row['images'] which we then need to iterate through in the view. Now, we complicate the situation even more when for case 'c' we also need to query cabin_small_images:

SELECT cabin_name, peak_week, max_occupancy, image, image_small 
FROM cabin_content  
INNER JOIN cabin_images  
  ON cabin_content.cabin_id = cabin_images.cabin_id 
INNER JOIN cabin_small_images 
  ON cabin_images.cabin_id = cabin_small_images.cabin_id 
WHERE cabin_content.cabin_id = '$cabin'";

So, from that I need all the regular content and I need to iterate through 'image' and 'small_images' and I just could never get it right.

Alright so in closing, if the way I have it structured is good, then fine, but I have a feeling the best way to do this is with the joins, in which case I need help accessing all the information from the query. Or I need your other conceptual suggestions on the best way to do this!

Answer 1

There is nothing wrong with using multiple queries. Three simple queries versus a single, convoluted monster query. Guess which option will cause you the least problems long term?

You get the extra overhead of parsing three queries, but, its about the same amount of work for the DBMS to get the data in both cases.

Also I think you should consider holding your images in separate files and just hold the file names in the database. You can then use HTML "<image>" tags to reference these. This will make the images "cache"able on the client side, and, will move the work of retrieving the images to the web server, which is much more efficient and will allow for a degree of parallelism when loading the users page.

Answer 2

I'm not 100% sure what you are asking.. & personally i see nothing wrong with using multiple queries. But i think what you are looking for is UNION

SELECT 
    cc.cabin_name
    ,cc.peak_week
    ,cc.max_occupancy
    ,'Large' as ImageType
    ,ci.image 
FROM cabin_content cc
INNER JOIN cabin_images ci ON cc.cabin_id = ci.cabin_id 
WHERE cc.cabin_id = '$cabin';
Union
SELECT 
    cc.cabin_name
    ,cc.peak_week
    ,cc.max_occupancy
    ,'Small' as ImageType
    ,csi.image_small
FROM cabin_content cc
INNER JOIN cabin_small_images csi ON cc.cabin_id = csi.cabin_id
WHERE cabin_content.cabin_id = '$cabin';