Need help with only loading records that contain uploaded image

Hi,

Because of the way CMS Builder stores uploads, you have to use a mysql_select statement to select the correct records from the uploads table first:

  //Get all records with images in the blog section from the uploads table.
  $blogImages = mysql_select('uploads', "tableName = 'blog'");

  //Get the unique record nums for the blog table and put them into a comma separated string. 
  $blogImages = implode(',',array_unique(array_pluck($blogImages, 'recordNum')));

  // load records from 'blog'
  list($blogRecords, $blogMetaData) = getRecords(array(
    'tableName'   => 'blog',
    'where'       => "num IN ($blogImages)",
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => 6
  ));

  ?>
  
<?php foreach ($blogRecords as $record): ?>
  <?php if (sizeof($record['uploads']) >= 1): ?>       
       <?php foreach ($record['uploads'] as $index => $upload): ?>
        <img src="<?php echo $upload['thumbUrlPath'] ?>">     
       <?php endforeach ?>
      <?php endif; ?>
<?php endforeach ?>  

This is example code, so you'll have to change the variable names to work with your sections. 

First select all of the records from the uploads section that are used in the blog section using the mysql_select function. Then get all of the unique record num values from the recordNum field of the returned array, and create a comma separated string from them.

Finally a where statement is used to only return items with a num value that is in the $blogImages comma separated string using the MySQL IN function.

Let me know if you have any questions.

Thanks!

Greg

Hi Greg,

Thanks for the quick response.  I placed all my variable names to your code including changing the upload table to "team_photo" and receive a the following error:

MySQL Error: Table 'ylssocce_cms.cms_team_photo' doesn't exist (at old_alias_functions.php line 142 in function mysql_select_query)

Here is what i have:

<?php

  //Get all records with images in the blog section from the uploads table.
  $blogImages = mysql_select('team_photo', "tableName = 'player'");

  //Get the unique record nums for the blog table and put them into a comma separated string.
  $blogImages = implode(',',array_unique(array_pluck($blogImages, 'recordNum')));

  // load records from 'blog'
  list($blogRecords, $blogMetaData) = getRecords(array(
    'tableName'   => 'player',
    'where'       => "num IN ($blogImages)",
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => 6
  ));

  ?>
 
<?php foreach ($blogRecords as $record): ?>
  <?php if (sizeof($record['team_photo']) >= 1): ?>       
       <?php foreach ($record['team_photo'] as $index => $upload): ?>
        <img src="<?php echo $upload['thumbUrlPath'] ?>">     
       <?php endforeach ?>
      <?php endif; ?>
<?php endforeach ?> 

Dan

Hi Dan,

I think I should have used some slightly clearer variable names in my example code. :-) The problem is with the mysql_select function, the table you need to look in is the uploads table. This should work:

<?php 

  //Get all records with images in the blog section from the uploads table.
  $blogImages = mysql_select('uploads', "tableName = 'player'");

  //Get the unique record nums for the blog table and put them into a comma separated string. 
  $blogImages = implode(',',array_unique(array_pluck($blogImages, 'recordNum')));

  // load records from 'blog'
  list($blogRecords, $blogMetaData) = getRecords(array(
    'tableName'   => 'player',
    'where'       => "num IN ($blogImages)",
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => 6
  ));

  ?>
  
<?php foreach ($blogRecords as $record): ?>
  <?php if (sizeof($record['team_photo']) >= 1): ?>       
       <?php foreach ($record['team_photo'] as $index => $upload): ?>
        <img src="<?php echo $upload['thumbUrlPath'] ?>">     
       <?php endforeach ?>
      <?php endif; ?>
<?php endforeach ?> 

Thanks!

Greg