leftJoin (with images and multiple "joins" and multi-selects)

Hey Tim, 

Getting Left joined Images

The getRecords left join system doesn't get upload fields for the left joined records, but you can manually fetch the uploads using the following system:

  
  // load viewer library
  $libraryPath = 'cmsb/lib/viewer_functions.php';
  $dirsToCheck = array('C:/wamp/www/','','../','../../','../../../');
  foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
  if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

    // load records from 'blog'
  list($blogRecords, $blogMetaData) = getRecords(array(
    'tableName'   => 'blog',
    'loadUploads' => true,
    'allowSearch' => false,
    'leftJoin'    => array('blog_categories' => 'category')
  ));

  foreach($blogRecords as $key => $blog){
    $where   = "`tableName` = 'blog_categories' AND `fieldName` = 'image' AND `recordNum` = '".intval($blog['blog_categories.num'])."'";
    $uploads = mysql_select('uploads', $where);
    $blogRecords[$key]['blog_categories.image'] = $uploads;
  }

So my sections are set up so that each blog post has a category which comes from the blog_categories table, and each blog_categories has an image field called image. To add the image upload field for each blog post you have to loop through the records and use mysql_select to get the appropriate images from the upload table. Note: These fields won't have the upload directory prefix on them, so when displaying the records you'll need to add it using the $SETTINGS['uploadUrl'] variable. 

Getting Multiple Left Joins

You can create one leftJoin array item, and add multiple sections it like this:

'leftJoin'    => array('author' => 'authors', 'photographer' => 'photographers'),


Left Joining on a multi-select

Mutli-selects are more difficult, as we're returning more than one record for each item in the getRecords array. Here is how you could return those items for each item under the field category:records. In this example each blog post can have multiple categories. 

  // load records from 'blog'
  list($blogRecords, $blogMetaData) = getRecords(array(
    'tableName'   => 'blog',
    'loadUploads' => true,
    'allowSearch' => false,
  ));

  foreach($blogRecords as $key => $blog){
    $categoryNums = implode(", ", $blog['category:values']);
    if($categoryNums){
      $blogRecords[$key]['category:records'] = mysql_select('blog_categories', "`num` IN ($categoryNums)");
    }
  }


Let me know if you have any questions.

Cheers!

Greg

Hey Tim, 

Getting Left joined Images

The getRecords left join system doesn't get upload fields for the left joined records, but you can manually fetch the uploads using the following system:

  
  // load viewer library
  $libraryPath = 'cmsb/lib/viewer_functions.php';
  $dirsToCheck = array('C:/wamp/www/','','../','../../','../../../');
  foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
  if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

    // load records from 'blog'
  list($blogRecords, $blogMetaData) = getRecords(array(
    'tableName'   => 'blog',
    'loadUploads' => true,
    'allowSearch' => false,
    'leftJoin'    => array('blog_categories' => 'category')
  ));

  foreach($blogRecords as $key => $blog){
    $where   = "`tableName` = 'blog_categories' AND `fieldName` = 'image' AND `recordNum` = '".intval($blog['blog_categories.num'])."'";
    $uploads = mysql_select('uploads', $where);
    $blogRecords[$key]['blog_categories.image'] = $uploads;
  }

So my sections are set up so that each blog post has a category which comes from the blog_categories table, and each blog_categories has an image field called image. To add the image upload field for each blog post you have to loop through the records and use mysql_select to get the appropriate images from the upload table. Note: These fields won't have the upload directory prefix on them, so when displaying the records you'll need to add it using the $SETTINGS['uploadUrl'] variable. 

Getting Multiple Left Joins

You can create one leftJoin array item, and add multiple sections it like this:

'leftJoin'    => array('author' => 'authors', 'photographer' => 'photographers'),


Left Joining on a multi-select

Mutli-selects are more difficult, as we're returning more than one record for each item in the getRecords array. Here is how you could return those items for each item under the field category:records. In this example each blog post can have multiple categories. 

  // load records from 'blog'
  list($blogRecords, $blogMetaData) = getRecords(array(
    'tableName'   => 'blog',
    'loadUploads' => true,
    'allowSearch' => false,
  ));

  foreach($blogRecords as $key => $blog){
    $categoryNums = implode(", ", $blog['category:values']);
    if($categoryNums){
      $blogRecords[$key]['category:records'] = mysql_select('blog_categories', "`num` IN ($categoryNums)");
    }
  }


Let me know if you have any questions.

Cheers!

Greg

This is great Greg.

With displaying the images, what do I use as the for-each loop?

<?php foreach ($blogRecords as $record): ?>

<h1><?php echo htmlencode($record['title']) ?></h1>

<?php foreach ($record['upload'] as $index => $upload): ?>
  <img src="<?php echo $SETTINGS['uploadUrl'] ?>/<?php echo htmlencode($upload['urlPath']) ?>" alt="<?php echo htmlencode($record['title']) ?>" class="width"/>
<?php endforeach ?>


<?php endforeach ?>

Hey Tim,

That code looks good to me, if you were using the code below to generate the uploads:

  // load records from 'blog'
  list($blogRecords, $blogMetaData) = getRecords(array(
    'tableName'   => 'blog',
    'loadUploads' => true,
    'allowSearch' => false,
    'leftJoin'    => array('blog_categories' => 'category')
  ));

  foreach($blogRecords as $key => $blog){
    $where   = "`tableName` = 'blog_categories' AND `fieldName` = 'image' AND `recordNum` = '".intval($blog['blog_categories.num'])."'";
    $uploads = mysql_select('uploads', $where);
    $blogRecords[$key]['blog_categories.image'] = $uploads;
  }

Then the code you'd use to display the images would be:

<?php foreach ($blogRecords as $record): ?>
  <h1><?php echo htmlencode($record['title']) ?></h1>
  <?php foreach ($record['blog_categories.image'] as $index => $upload): ?>
    <img src="<?php echo $SETTINGS['uploadUrl'] ?>/<?php echo htmlencode($upload['urlPath']) ?>" alt="<?php echo htmlencode($record['title']) ?>" class="width"/>
  <?php endforeach ?>
<?php endforeach ?>

So the field highlighted in orange in the code above shows the field name that is generated for the upload records. You can change this to whatever works best for you. 

Cheers,

Greg