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: [chris] where upload field IS NOT NULL

By Shore - November 20, 2009

Hi Chris,

There is definitely an upload field named "logo" in my organization table. I uploaded a screenshot.

Also, the standard cms_ prefix is in the Database Settings.

Thanks
Attachments:

logo-field.jpg 89K

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