Resetting Field Values
9 posts by 2 authors in: Forums > CMS Builder
Last Post: May 23, 2011 (RSS)
By gkornbluth - May 14, 2011
As a first step in automating the setting of an expiryDate to 1 year after a member signs up for an account, I need to update the existing account Expiry dates to one year after each account’s createdDate.
Here’s the code that I’m trying to use in the fieldResetter plugin, http://www.interactivetools.com/iforum/gforum.cgi?post=75874 but there’s a problem with the syntax in the area in red, and I could use some guidance in getting it to work.
Thanks,
Jerry Kornbluth
$GLOBALS['FIELD_RESET_FIELDS'] = array(
array('tableName' => 'accounts', 'fieldName' => 'expiryDate', 'resetValue' => (createdDate + INTERVAL 1 year) )
);Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Re: [gkornbluth] Resetting Field Values
By Dave - May 14, 2011
I haven't use that plugin or double-checked this code, but try adding quotes:
$GLOBALS['FIELD_RESET_FIELDS'] = array(
array('tableName' => 'accounts',
'fieldName' => 'expiryDate',
'resetValue' => "(createdDate + INTERVAL 1 year) )",
);Hope that helps! Let me know if that works. :)
interactivetools.com
Re: [Dave] Resetting Field Values
By gkornbluth - May 14, 2011
As always, it's so much easier when you know what you're doing.
I'll give it a try.
Thanks,
Jerry Kornbluth
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Re: [Dave] Resetting Field Values
By gkornbluth - May 18, 2011
Sorry it took a while to get back to this.
I just set up a test section using the code you suggested:
$GLOBALS['FIELD_RESET_FIELDS'] = array(
array('tableName' => 'reset_date',
'fieldName' => 'dateTest',
'resetValue' => "(dateTest + INTERVAL 1 year)" ),
);The syntax error has disappeared, but when I apply the plugin, instead of adding a year to the date, it resets the date to blank fields.
I'm sure that it has something to do with the fact that date fields are constructed differently then check box fields, but I guess I could use some more guidance.
I've included the code from the plugin that Carl wrote, just in case something pops out at you.
Thanks,
Jerry Kornbluth
<?php
// Add the names of tables and fields, and the value to reset them to, to the following array.
// For example, to add a command to set the field 'available' to '0' on the table 'products'
// would look like this:
// $GLOBALS['FIELD_RESET_FIELDS'] = array(
// array( 'tableName' => 'products', 'fieldName' => 'available', 'resetValue' => 0 )
// );
$GLOBALS['FIELD_RESET_FIELDS'] = array(
array('tableName' => 'reset_date',
'fieldName' => 'dateTest',
'resetValue' => "(dateTest + INTERVAL 1 year)" ),
);
// DON'T UPDATE ANYTHING BELOW THIS LINE
addFilter( 'list_advancedCommands', '_fieldReset_addCommands', null, 1 );
addAction( 'section_unknownAction', '_fieldReset_handleReset', null, 2 );
addAction( 'init_complete', '_fieldReset_modal', null, null );
function _fieldReset_addCommands( $labelsToValues ) {
global $CURRENT_USER;
if ( !$CURRENT_USER['isAdmin'] ) return;
$tableName = $_REQUEST['menu'];
$i=0;
foreach( $GLOBALS['FIELD_RESET_FIELDS'] as $field ) {
if ( $field['tableName'] == $tableName ) {
$label = "Set '{$field['fieldName']}' on all records to {$field['resetValue']}";
$labelsToValues[ $label ] = "reset_" . $i;
}
$i++;
}
return $labelsToValues;
}
function _fieldReset_handleReset( $tableName, $action ) {
if ( strpos( $action, 'reset' ) !== 0 ) return;
$parts = preg_split( '!_!', $action );
$field = $GLOBALS['FIELD_RESET_FIELDS'][ (int)$parts[1] ];
global $TABLE_PREFIX;
$query = "UPDATE {$TABLE_PREFIX}{$field['tableName']} SET {$field['fieldName']} = '" . mysql_escape( $field['resetValue'] ) . "'";
mysql_query( $query ) or die( mysql_error() );
header( 'Location: admin.php?_fieldReset=' . $field['fieldName'] . '&menu=' . $tableName );
}
function _fieldReset_modal() {
if ( @$_REQUEST['_fieldReset'] ) {
global $APP;
$APP['notices'] = 'All ' . $_REQUEST['_fieldReset'] . ' values reset';
}
}
?> Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Re: [gkornbluth] Resetting Field Values
By Dave - May 19, 2011
It looks like that plugin only works with text values, you can't pass it SQL values.
Would it work to use the "MySQL Console" plugin and a query such as this:
UPDATE cms_accounts SET expiryDate = (createdDate + INTERVAL 1 year)Let me know, thanks
interactivetools.com
Re: [Dave] Resetting Field Values
By gkornbluth - May 20, 2011
I sort of figured that it might be something like that.
Jerry Kornbluth
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Re: [Dave] Resetting Field Values
By gkornbluth - May 22, 2011 - edited: May 22, 2011
That was perfect advice.
One quick question.
I'm also resetting the neverExpire field to '0' using this same approach, and would like to not affect any admin records.
I tried adding something like:
'where' => !$CURRENT_USER['isAdmin']to
UPDATE cms_accounts SET neverExpires = ‘0'But obviously that's not the correct syntax and I'm afraid that if I muck around long enough, I'll break something.
Thanks,
Jerry
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Re: [gkornbluth] Resetting Field Values
By Dave - May 23, 2011
Be sure to make a backup before trying any MySQL. Then try this:
UPDATE cms_accounts SET neverExpires = '0' WHERE isAdmin = '0'
Also note the opening quote on your zero was a backtick (by the esc key), those are different from single-quotes so watch out not to get them mixed up.
Let me know if that works for you.
interactivetools.com
Re: [Dave] Resetting Field Values
By gkornbluth - May 23, 2011
Jerry
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php