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 Daryl - June 7, 2013

The "photos_categories" field of your "homepage_categories" table is the tab separated values of the photo category num that you included in a homepage_categories record. That's how they are related.

You can explode it to make it an array to be use in a foreach loop. For example:

$categoryArray = explode("\t", trim($homepage_categoriesRecord['included_categories'], "\t"));
showme($categoryArray);

You can also get this field values into an array automatically by using:

$categoryArray = $homepage_categoriesRecord['included_categories:values']; // add ":values" to get values(num), ":labels" for labels(category name)
showme($categoryArray);

Showme() function is use to display an array. Sorry if that confused you in my previous example.

Let us know if you still have any question.

Thanks,

Daryl

Daryl Maximo
PHP Programmer - interactivetools.com