Reducing server load
14 posts by 2 authors in: Forums > CMS Builder
Last Post: December 19, 2013 (RSS)
Hi Dave,
Sorry to bother you again with this, but I’m now trying to filter the results of the mySQL query to only include those uploads where there’s also a checkbox field in the user account records called use_large_banners = 1
The closest that I could come without throwing a mySQL error is below but that doesn’t pull any records at all.
Any suggestions appreciated.
Jerry Kornbluth
<?php // load uploads from accounts
$uploadRecords = mysql_select('uploads', " tableName = 'accounts' AND fieldName = 'large_banners' AND 'use_large-banners' = '1'");
?>
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
By Dave - December 18, 2013
Hi Jerry,
In MySQL single or double quotes means a string or text value to compare to. If a fieldname needs to be quoted these use `backticks` for that.
So try this instead:
`use_large-banners`
You were probably getting errors because the column name contains a - which MySQL doesn't know isn't a subtraction operator.
Let me know if that works for you.
interactivetools.com
Hi Dave,
Sorry the - was a typo in the post only,
Anyway I must be gaining on it because I get a different error now: (and I know that there is a checkbox field named use_large_banners in the accounts table
MySQL Error: Unknown column 'use_large_banners' in 'where clause' (at old_alias_functions.php line 142 in function mysql_select_query)
With the code:
<?php // load uploads from accounts
$uploadRecords = mysql_select('uploads', " tableName = 'accounts' AND fieldName = 'large_banners' AND `use_large_banners` = '1'");
?>
Jerry Kornbluth
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
By Dave - December 19, 2013
Hi Jerry,
Ahh, ok, I see where you're going with this. You're pretty much working with MySQL at this point to get the data you want out of the database. Try this:
$accountsSubquery = "SELECT num FROM {$TABLE_PREFIX}accounts WHERE `use_large_banners` = '1' ";
$uploadsWhere = " tableName = 'accounts' AND fieldName = 'large_banners' AND recordNum IN ($accountsSubquery)";
$records = mysql_select('uploads', $uploadsWhere);
// debug - show output
showme($records);
print "Done";
exit;
If you were going to write that as a straight MySQL query it would look something like this:
SELECT * FROM cms_uploads
WHERE tableName = 'accounts'
AND fieldName = 'large_banners'
AND recordNum IN (SELECT num FROM cms_accounts WHERE `use_large_banners` = '1')
Let me know if that works for you or if you have any questions about how it works. Cheers!
interactivetools.com