Query help

[H]

Could someone please provide a query that will lock normal (not stickys) topics that are six-months old?

[B Patterson]

Something like

Code Select Expand

UPDATE smf_topics
SET smf_topics.locked='1'
WHERE isStickey<>'1'
  AND ((SELECT smf_messages.posterTime FROM smf_messages WHERE smf_messages.ID_MSG=smf_topics.ID_FIRST_MSG)-UNIX_TIMESTAMP(NOW()) > (60*60*24*180))

Something like that... it's a little sloppy, I know...
Maybe a better version:

Code Select Expand

UPDATE smf_topics a
SET a.locked='1'
WHERE a.isStickey<>'1'
  AND UNIX_TIMESTAMP(SUBDATE(NOW(), INTERVAL 6 MONTHS)) > (SELECT b.posterTime FROM smf_messages b WHERE a.ID_FIRST_MSG=b.ID_MSG)

Either of those should work on 1.1RC2

And if you wanted it so that the thread hasn't been active in the last 6 months, just change it from ID_FIRST_MSG to ID_LAST_MSG

[hobox]

Does this work for 1.1.5?

[H]

I believe it should do

[spearfish]

Yes it will.  Nothing has changed about the organizational structure of locked / sticky topics

[And yes, it should also work for 2.0]

[hobox]

Correct me if I'm wrong but will not

UPDATE smf_topics a SET a.locked='1' WHERE a.isStickey<>'1'  AND UNIX_TIMESTAMP(SUBDATE(NOW(), INTERVAL 6 MONTHS)) > (SELECT b.posterTime FROM smf_messages b WHERE a.ID_FIRST_MSG=b.ID_MSG)

lock all topics that are started 6 months ago?

How can I change the above to only lock topics that haven't had any reply for 6 months?

[niko]

Correct me if I'm wrong but will not

UPDATE smf_topics a SET a.locked='1' WHERE a.isStickey<>'1'  AND UNIX_TIMESTAMP(SUBDATE(NOW(), INTERVAL 6 MONTHS)) > (SELECT b.posterTime FROM smf_messages b WHERE a.ID_FIRST_MSG=b.ID_MSG)

lock all topics that are started 6 months ago?

How can I change the above to only lock topics that haven't had any reply for 6 months?

And if you wanted it so that the thread hasn't been active in the last 6 months, just change it from ID_FIRST_MSG to ID_LAST_MSG

[hobox]

I'm not that good at MySQL but when I ran the query above I got this

Error
SQL query: 

UPDATE smf_topics a SET a.locked = '1' WHERE a.isStickey <> '1' AND UNIX_TIMESTAMP( SUBDATE( NOW( ) , INTERVAL 6 MONTHS ) ) > ( SELECT b.posterTime
FROM smf_messages b
WHERE a.ID_LAST_MSG = b.ID_MSG )

MySQL said: 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MONTHS)) > (SELECT b.posterTime FROM smf_messages b WHERE a.ID_LAST_MSG=b.ID_MSG' at line 1

[hobox]

Finally solved this one on my own. The correct query should have been the following

Code Select Expand

UPDATE smf_topics a SET a.locked = '1' WHERE a.isSticky <> '1' AND UNIX_TIMESTAMP( SUBDATE( NOW( ) , INTERVAL 6 MONTH ) ) > ( SELECT b.posterTime FROM smf_messages b WHERE a.ID_LAST_MSG = b.ID_MSG )

[hobox]

Any help on how I can put this in a cron job?

I use Cpanel.

[MrMike]

Use the CPanel settings to create an event that runs at 3am each day. This is the definition for the cron job:

* 1 * * * php /path/to/some/directory/locktopics.php

This runs the "locktopics.php" file every day at 3am. You may have to include the full path to PHP- some servers require it.


This is the contents of locktopics.php (crude, but should work). Create this file and place it in the directory that you specified in the cron job definition.

Code Select Expand

<?php

// change this to the server path to your Settings.php file
include_once('/path/to/Settings.php');

$db_connection = mysql_connect($db_server, $db_user, $db_passwd);
mysql_select_db($db_name, $db_connection);

$lock_sql = "UPDATE smf_topics a SET a.locked = '1' WHERE a.isSticky <> '1' AND UNIX_TIMESTAMP( SUBDATE( NOW( ) , INTERVAL 6 MONTH ) ) > ( SELECT b.posterTime FROM smf_messages b WHERE a.ID_LAST_MSG = b.ID_MSG )";

$lock_result = mysql_query($lock_sql);

?>

[hobox]

Thanks! Works great

[marcbo]

and how can we lock threads in only one or more boards instead of all the boards?

[gh0stw4lk3r]

same question for me,

i would like to lock all non sticky topics in a specific board which have been started >30 days ago