Gallery - Uploads Table

7 posts by 3 authors in: Forums > CMS Builder
Last Post: November 8, 2014   (RSS)

By Perchpole - October 25, 2014

Hi, Everyone -

I'm trying to create a gallery system which pulls images directly from the Uploads table. The getRecords call is fairly basic:

list($gallery, $galleryMetaData) = getRecords(array(
    'tableName'   => 'uploads',
    'where' => " tableName = 'gallery' ",
    'perPage' => 20,
));


By using this method I can load the uploads directly without first having to load the records to which they are attached.

The only downside is how can I filter the results using data from the parent records? For example, how would I create a "where" clause which only loads the uploads created by a particular user?

:0/

Perch

By gkornbluth - October 26, 2014

Hi Perch,

There might be an idea in this where clause that you can use to sort it out.

'where' => " createdByUserNum IN (SELECT num FROM cms_accounts WHERE (account_status LIKE '%1%' AND membership_level LIKE '%2%') ) AND  updatedDate > '$listing_age' OR (createdByUserNum IN (SELECT num FROM cms_accounts WHERE (account_status LIKE '%1%' AND (membership_level LIKE '%1%'  OR membership_level LIKE '%3%' OR membership_level LIKE '%4%' OR membership_level LIKE '%5%' OR membership_level LIKE '%6%')) ) ) ",

Best,

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By Perchpole - October 27, 2014

Hi, Jerry -

Thanks for your input. I never knew we could do so much with the "Where" clause! However, the uploads table does not have a createdByUserNum column - so I need to think of a different approach.

:0/

Perch

By gregThomas - November 5, 2014

Hi Perch,

It would probably be easier to access the gallery table and add you're where statement there. Then loop through the gallery uploads:

  // load record from 'accounts'
  list($galleries, $galleriesMeta) = getRecords(array(
    'tableName'   => 'gallery',
    'where'       => "`createdByUserNum` = '1'",
    'loadUploads' => true,
    'allowSearch' => false,
  ));

  foreach($galleries as $gallery){
    foreach($gallery['upload_field_name'] as $upload){
      echo "<img src='{$upload['urlPath']}' alt='{$upload['info1']}' />";
    }
  }

If you'd rather access the uploads table directly, you could use a leftJoin to link the recordNum field to the gallery table:

  // load record from 'accounts'
  list($galleries, $galleriesMeta) = getRecords(array(
    'tableName'   => 'uploads',
    'where' => " tableName = 'gallery' ",
    'loadUploads' => false,
    'allowSearch' => false,
    'leftJoin'    => array('gallery' => 'recordNum'),
  ));

Although as getRecords wasn't designed to access the uploads table directly, I'm not 100% sure if this method will work.

Thanks,

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By Perchpole - November 7, 2014

Hi, Greg -

Thanks for this. I've never used the leftJoin in a getRecords call before - but I'm now realising just how useful it might be!

In this instance, however, it doesn't quite work. The join pulls in all of the columns from the gallery table - except: createdDate, createdByUserNum, updatedDate and updatedByUserNum.

Can you explain why this happens (or doesn't happen)?

Thanks,

:0)

Perchpole

By gregThomas - November 7, 2014

Hey Perch,

This happens because the leftJoin is designed to only return fields with a type, it will only return the following field types:

  • textfield
  • textbox
  • wysiwyg
  • date
  • list
  • checkbox

But as the createdDate, updatedDate, etc are internal fields, they don't have a type set on them. 

Even though you can't view these fields in the returned data, you can still filter on them in your where statement.

Thanks!

Greg

Greg Thomas







PHP Programmer - interactivetools.com