random select records where upload field are true

3 posts by 2 authors in: Forums > CMS Builder
Last Post: January 31, 2011   (RSS)

By ht1080z - January 30, 2011

Hi,

I try to select from table "used_items" records where specified upload field "photos" are exist.

list($uirandomRecords, $uirandomMetaData) = getRecords(array(
'tableName' => 'used_items',
'orderBy' => "RAND()",
'where' => 'xxx'
'limit' => '5',
'loadUploads' => '1',
'allowSearch' => '0',
'debugSql' => true,
));
showme($uirandomRecords);

how can i request in the 'where' that only select records which have upload images in the photos field?

Any suggestion would appreciated.

Re: [ht1080z] random select records where upload field are true

By Jason - January 31, 2011

Hi,

In CMS Builder, all uploads are actually stored in a separate table called "uploads". The best solution here would be to first query the uploads table to find out which records in your "used_items" table have uploads. We can then use that list to randomly order those records.

Try this:

$imageQuery = "SELECT * FROM `{$TABLE_PREFIX}uploads` WHERE tableName = 'used_items' AND fieldName='photos' GROUP BY recordNum";
$imageRecords = mysql_query_fetch_all_assoc($imageQuery);

$recordNumList = join(",",array_pluck($imageRecords,'recordNum'));

if(!$recordNumList){$recordNumList=0;}

list($uirandomRecords, $uirandomMetaData) = getRecords(array(
'tableName' => 'used_items',
'orderBy' => "RAND()",
'where' => "num IN ($recordNumList)",
'limit' => '5',
'loadUploads' => '1',
'allowSearch' => '0',
'debugSql' => true,
));

Hope this helps
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/