where upload field IS NOT NULL

4 posts by 2 authors in: Forums > CMS Builder
Last Post: November 23, 2009   (RSS)

By Shore - November 20, 2009

Hello,

I'm trying to generate a list limited to 3 random records where the logo upload field is not empty.

----------------------------

list($organizationRecords, $organizationMetaData) = getRecords(array(
'tableName' => 'organization',
'limit' => '3',
'where' => 'logo IS NOT NULL',
'orderBy' => 'RAND()',
));

----------------------------

However, I'm getting this error:

MySQL Error: Unknown column 'logo' in 'where clause'

Any help would be appreciated.

Thanks

Re: [Shore] where upload field IS NOT NULL

By Chris - November 20, 2009

Hi Shore,

Your approach is definitely correct, but it would seem that there's no field called "logo" in your "organization" table. Please check the section editor to make sure you've got the Field Name correct.

The only other thing I can think of is your MySQL Table Prefix. What is listed in Admin -> General Settings -> Database Settings -> Database Settings?
All the best,
Chris

Re: [Shore] where upload field IS NOT NULL

By Chris - November 23, 2009

Hi Shore,

Sorry, you mentioned that it's an upload field. Uploads are not stored in the same table, they're stored in a separate table called cms_uploads.

How about this code?

$recordNums = mysql_query_fetch_all_array("
SELECT p.num
FROM {$TABLE_PREFIX}organization o
LEFT JOIN {$TABLE_PREFIX}uploads u
ON u.tableName = 'organization' AND u.fieldName = 'logo' AND u.recordNum = o.num
GROUP BY o.num
HAVING COUNT(u.num)
ORDER BY RAND()
LIMIT 3
");

list($organizationRecords, $organizationMetaData) = getRecords(array(
'tableName' => 'organization',
'where' => 'num IN (' . join(',', array_pluck($recordNums, '0')) . ')',
'orderBy' => 'RAND()',
));

I hope this helps! Please let me know if you have any questions.
All the best,
Chris