Need help sorting out schema and queries

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

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! 

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