Resetting Field Values

9 posts by 2 authors in: Forums > CMS Builder
Last Post: May 23, 2011   (RSS)

By gkornbluth - May 14, 2011

Hi All,

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) )
);
The first CMS Builder reference book is now available on-line!







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

Hi Jerry,

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. :)
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Resetting Field Values

By gkornbluth - May 14, 2011

Dave,

As always, it's so much easier when you know what you're doing.

I'll give it a try.

Thanks,

Jerry Kornbluth
The first CMS Builder reference book is now available on-line!







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

Hi Dave,

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';
}
}
?>
The first CMS Builder reference book is now available on-line!







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 20, 2011

Thanks Dave,

I sort of figured that it might be something like that.

Jerry Kornbluth
The first CMS Builder reference book is now available on-line!







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

Dave,

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
The first CMS Builder reference book is now available on-line!







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

Jerry,

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.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Resetting Field Values

By gkornbluth - May 23, 2011

Thanks again,

Jerry
The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php