Trying to code autodelete inactive users

Question

I'm not an PHP expert but I tried to code an autodelete function for users that have never posted and are 1 month inactive. I took the delete user code from, \qa-include\db\users.php

Added the code below at the end of file, \qa-include\db\users.php

Can some one help me out with the code I came up with, but is obviously not working.

---

/**
 * Delete inactive users from the database, along with everything they have ever done (to the extent that it's possible)
 * @param $userid
 */
$result = qa_db_read_all_values(qa_db_query_sub('SELECT userid FROM ^users WHERE loggedin  < '.(now() - interval 1 month).' AND written = NULL'));
    while ($userid = qa_db_read_all_values($result))
{
    qa_db_query_sub('UPDATE ^posts SET lastuserid=NULL WHERE lastuserid=$', $userid);
    qa_db_query_sub('DELETE FROM ^userpoints WHERE userid=$', $userid);
    qa_db_query_sub('DELETE FROM ^blobs WHERE blobid=(SELECT avatarblobid FROM ^users WHERE userid=$)', $userid);
    qa_db_query_sub('DELETE FROM ^users WHERE userid=$', $userid);

    // All the queries below should be superfluous due to foreign key constraints, but just in case the user switched to MyISAM.
    // Note also that private messages to/from that user are kept since we don't have all the keys we need to delete efficiently.

    qa_db_query_sub('UPDATE ^posts SET userid=NULL WHERE userid=$', $userid);
    qa_db_query_sub('DELETE FROM ^userlogins WHERE userid=$', $userid);
    qa_db_query_sub('DELETE FROM ^userprofile WHERE userid=$', $userid);
    qa_db_query_sub('DELETE FROM ^userfavorites WHERE userid=$ OR entitytype=$ AND entityid=$', $userid, QA_ENTITY_USER, $userid);
    qa_db_query_sub('DELETE FROM ^userevents WHERE userid=$', $userid);
    qa_db_query_sub('DELETE FROM ^uservotes WHERE userid=$', $userid);
    qa_db_query_sub('DELETE FROM ^userlimits WHERE userid=$', $userid);
}
 

Answer 1

What you did is not of much use. You need to create a PHP script following this guidelines. You just need to:

 1. Fetch the user IDs with qa_db_read_all_values(). Make sure you filter by the appropriate date field. I think the last write (written) is much better than the last login (loggedin). People might login once and keep being logged in for months. Also make sure they don't have visible posts checking qcount, acount and ccount. You can get them by joining with the ^userpoints table. There are other fields in there you can check to be 0, as well.

 2. Iterate over the resulting array and call qa_delete_user($userId).

 3. Create a CRON to call that script and that's it.

Comments

Thanks for the guidelines and the steps to follow up. I'll struggle to come up with something working.

---

Perhaps you can use this as a blueprint for fetching the records and deleting the users: https://www.question2answer.org/qa/95827/clean-up-unconfirmed-users#a95828 (you'll have to adjust the query, of course)

First and foremost you need to fix an error in your SELECT query, though. You can't use the "=" operator to check if a value is NULL. You MUST use the keyword "IS" for that ("... AND written IS NULL").