Need help sorting out schema and queries

4 posts by 2 authors in: Forums > CMS Builder
Last Post: June 7, 2013   (RSS)

By ncasares - June 4, 2013

I have a site that consists of a collection of photos and static content. The photos are arranged into one or more categories and I'm displaying categories with independent viewers.

Currently, the site looks something like this:

Homepage (static links to viewers)
--> Individual viewer page
--> Individual viewer page
--> Individual viewer page

My DB looks something like this:

[Photo Categories]
= num
= category_name

[Photos]
= num
= photo_category (check box selection pulled in from Photo Categories table)
= photo (upload)

What I'm trying to do is create a third table of "homepage categories" that will let me setup groups of links to selected photo categories.The idea is that I'd like to give the user a way to create sections of photo categories that appear on the homepage. All of the records in this table would be pulled into a multi-record viewer on the homepage. Albums of galleries, essentially.

For this, I have the following table:

[Homepage Categories]
= num
= photo categories (check box selection pulled from Categories table)
= featured image (upload)

I'm struggling with the best way to setup the viewers and relationships. My thought is that I should have the following viewers:

- homepage multi-record to show Homepage Categories
- detail multi-record to show photos based on the categories passed through from the homepage viewer

I've tried this a bunch of ways and can't seem to get it right. The part that's really confusing me is how I can generate a query that selects all photos belonging to ONLY the categories setup in a Homepage Categories record.

I'd like the homepage to eventually look like this:

homepage category 1
- includes photo categories 1, 4, 5, 10
- link/query to a viewer that shows all photos in categories 1, 4, 5, 10

homepage category 2
- includes photo categories 2, 3, 6, 7
- link/query to a viewer that shows all photos in categories 2, 3, 6, 7

homepage category 3
- includes photo categories 8, 9, 5, 10
- link/query to a viewer that shows all photos in categories  8, 9, 5, 10

I feel like this should be doable but I'm not quite savvy enough.

By Daryl - June 5, 2013

Hi ncasares,

Assuming that you already have a query for ([Homepage Categories]) in your home page:

  1. Make sure you have set a "Detail Page Url" under the "Viewer Urls tab" of the Homepage Categories section. ex: \view-album.php
  2. Add a link ($records['_link']) in your homepage_categories query in your home page. This will generate a link going to: For example: "\view-album.php?AlbumNAme-1" 

    <a href="<?php echo $record['_link'] ?>"><?php echo $record['album_name'] ?></a>

  3. Create a php file, in our example, view-album.php and add these query:

      // load record from 'homepage_categories'
      list($homepage_albumRecords, $homepage_albumMetaData) = getRecords(array(
        'tableName'   => 'homepage_categories',
        'where'       => whereRecordNumberInUrl(0), //<-- this line will get the num in the URL coming from your home page
        'loadUploads' => true,
        'allowSearch' => false,
        'limit'       => '1',
      ));
      $homepage_albumRecord = @$homepage_albumRecords[0]; // get first record
      if (!$homepage_albumRecord) { dieWith404("Record not found!"); } // show error message if no record found

  4. Then, you can now get all the included [Photo Categories] in that particular Album. For example:

    showme($homepage_albumRecord['photos_categories:values']); // This will give you an array value all the photo categories num that are included in the album (name: AlbumName; num: 1)

  5. After that, you can now add the codes for querying all the [Photo Categories] that are included in the array ($homepage_albumRecord['photos_categories:values']) to show the photos that are categorized under each of the categories.

Hope this helps!

Thanks,

Daryl

Daryl Maximo
PHP Programmer - interactivetools.com

By ncasares - June 6, 2013

Hi Daryl,

Thanks for getting back to me! I had cooked up something along these lines and ended up with the following code to show the photos on the view-album.php page:

<?php
  foreach ($homepage_categoriesRecord['included_categories:values'] as $photoQuery){
    list($photosRecords, $photosMetaData) = getRecords(array(
        'tableName'   => 'photos',
        'orderBy'     => 'photo_number DESC, newphoto DESC',
        'where'       => "photo_category = $photoQuery",
    ));

    include($_SERVER['DOCUMENT_ROOT'] . "/inc/inc-photo-table-loop.inc.php");
  }
?>

What is the actual field relationship between the homepage_categories table and the categories table (or what does showme actually do)? Is there are better way to build this so I'm keying photo category on discrete fields in the homepage_categories table?

When I open up the DB table view I see that the photos_categories values are all being stored in a single field. Maybe this is ok but it seems like it would make a join on this field difficult.

Thanks again!