Need help with only loading records that contain uploaded image
5 posts by 2 authors in: Forums > CMS Builder
Last Post: February 11, 2013 (RSS)
I'm trying to only load records that contain an uploaded image using a "where" statement. I tried adding the below if statement to the code, but, this leaves blank spaces for records without images. For example if two records have images and 4 do not, only two are displayed because the limit is 6. I need it so that it displays 6 records with images and believe this can only be done via the "where". Any help would be appreciated
<?php
list($sampleRecords, $sampleMetaData) = getRecords(array(
'tableName' => 'sample',
'limit' => '6',
'loadUploads' => true,
'allowSearch' => false,
));
?>
<?php foreach ($sampleRecords as $record): ?>
<?php if (sizeof($record['photos']) >= 1): ?>
<?php foreach ($record['photos'] as $index => $upload): ?>
<img src="<?php echo $upload['thumbUrlPath'] ?>">
<?php endforeach ?>
<?php endforeach ?>
<?php else: ?>
<?php endif ?>
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
PHP Programmer - interactivetools.com
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
PHP Programmer - interactivetools.com