Hi-

I currently flag links that haven't been modified by the user in over a year through a custom Links field called 'Needs_Update'. Basically, I run a few SQL commands through SQL Monitor to compare the Mod_Date with the current date. The commands set Needs_Update field to 'Yes' or 'No' for those links that haven't been modified within the last 365 days.

I'm trying to have the following SQL commands execute by putting them in nph-build.cgi so that they are executed on the directory build (rather than me manually running them through the SQL Monitor) but am having trouble getting it into the proper perl format.

The SQL commands that work for me in the SQL Monitor are:

UPDATE linksql_Links SET Needs_Update = 'Yes'
WHERE User_Mod_Date <= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
AND isValidated = 'Yes';

UPDATE linksql_Links SET Needs_Update = 'No'
WHERE User_Mod_Date > DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
AND isValidated = 'Yes';

Does anyone know how to make these work in nph-build.cgi? Something like:

$DB->table('Links')->update ( { Needs_Update => 'Yes' }); ??? how to get the WHERE conditions in here?

$DB->table('Links')->update ( { Needs_Update => 'No' }); ??? how to get the WHERE conditions in here?

I just can't figure out how to get the WHERE conditions incorporated in this so that it works in perl...

--FrankM

Hi,

Below should help

my $yescond = GT::SQL::Condtion->new (
'User_Mod_Date', '<=', \"DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)",
' isValidated', '=','Yes');

$DB->table('Links')->update ( { Needs_Update => 'Yes' },$yescond);

my $nocond = GT::SQL::Condtion->new (
'User_Mod_Date', '>', \"DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)",
' isValidated', '=','Yes');
$DB->table('Links')->update ( { Needs_Update => 'NO'},$nocond );

Cheers,

Cheers,

Dat

Programming and creating plugins and templates
Blog

Thanks! I appreciate it, and will try this code.

--FrankM

Hi,

I have a feeling that you're making this unnecessarily complicated (unless I'm misunderstanding which is always possible).

Why not just have this in your template (or in a global if you use it in several templates):

<%set lastyear = GT::Date::date_get()%>
<%set lastyear = GT::Date::date_sub($lastyear,365)%>

Then you can just use

<%if User_Mod_Date <= $lastyear%>Needs Update<%endif%>

This means you wouldn't be altering any core code so it would survive updates.

Short, simple, and works perfectly... Thanks very much!

--Frank