Advertisement:

Author Topic: a quick question about deleting a member via the database  (Read 510 times)

Offline aegersz

  • SMF Hero
  • ******
  • Posts: 1,490
  • Gender: Male
  • "mods" junkie
    • dopetalk
a quick question about deleting a member via the database
« on: September 08, 2018, 01:59:03 AM »
Hi, i am culling my list of members, starting with those where never logged in.

I have already emailed them with my php script (that runs outside SMF) but if i wanted to then delete them can i simply delete the member's row ?

The configuration of my Linux VPS (SMF 2.0 with 145 mods & some assorted manual tweaks) can be found here and notes on my mods can be found here (warning: those links will take you to a drug related forum)

Offline aegersz

  • SMF Hero
  • ******
  • Posts: 1,490
  • Gender: Male
  • "mods" junkie
    • dopetalk
Re: a quick question about deleting a member via the database
« Reply #1 on: September 08, 2018, 03:11:30 AM »
... and if i delete the row BUT the member has posts (which i want to keep), will i maintain integrity ?

sorry about these basic questions. I have tried this in my test environment but i want to be absolutely sure that i don't mess up.
The configuration of my Linux VPS (SMF 2.0 with 145 mods & some assorted manual tweaks) can be found here and notes on my mods can be found here (warning: those links will take you to a drug related forum)

Offline aegersz

  • SMF Hero
  • ******
  • Posts: 1,490
  • Gender: Male
  • "mods" junkie
    • dopetalk
Re: a quick question about deleting a member via the database
« Reply #2 on: September 08, 2018, 06:01:04 AM »
too late, i have now removed everybody that never logged in AND that had invalid email addresses.

sorry to bother.

The configuration of my Linux VPS (SMF 2.0 with 145 mods & some assorted manual tweaks) can be found here and notes on my mods can be found here (warning: those links will take you to a drug related forum)

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 58,435
  • Gender: Male
    • Kindred-999 on GitHub
Re: a quick question about deleting a member via the database
« Reply #3 on: September 08, 2018, 06:56:21 AM »
DO NOT just delete the row from the members table.
You should rarely, if ever, edit the database directly....  and practically never if it involves a delete or an add...  since both actions have interactions between multiple tables.

If you have manually deleted a member from MySQL directly, then your system now has extraneous, bad and disconnected records scattered throughout your database.
Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

Offline aegersz

  • SMF Hero
  • ******
  • Posts: 1,490
  • Gender: Male
  • "mods" junkie
    • dopetalk
Re: a quick question about deleting a member via the database
« Reply #4 on: September 08, 2018, 09:05:13 AM »
oh dear. i removed 45 members BUT does it really matter even though they have never logged in ?

what is the proper protocol to delete members via an automated/batch procedure ?

I ran Admin > Maintenance > Forum > Recount all forum totals and statistics and it corrected the member count.

should i do anything else at this stage ?

« Last Edit: September 08, 2018, 09:41:46 AM by aegersz »
The configuration of my Linux VPS (SMF 2.0 with 145 mods & some assorted manual tweaks) can be found here and notes on my mods can be found here (warning: those links will take you to a drug related forum)

Offline aegersz

  • SMF Hero
  • ******
  • Posts: 1,490
  • Gender: Male
  • "mods" junkie
    • dopetalk
Re: a quick question about deleting a member via the database
« Reply #5 on: September 08, 2018, 10:09:15 AM »
i went ahead and am restoring the DB to before my antics ... thanks for the warning anyway.
The configuration of my Linux VPS (SMF 2.0 with 145 mods & some assorted manual tweaks) can be found here and notes on my mods can be found here (warning: those links will take you to a drug related forum)

Offline SychO

  • Lead Customizer
  • SMF Hero
  • *
  • Posts: 1,467
  • Gender: Male
    • SychO9 on GitHub
    • SychO
Re: a quick question about deleting a member via the database
« Reply #6 on: September 08, 2018, 10:13:18 AM »
Why don't you just delete them from the forum options
Additionally take a look at Admin>Forum Maintenance>Members>Remove Inactive Members

Imazighen

Offline aegersz

  • SMF Hero
  • ******
  • Posts: 1,490
  • Gender: Male
  • "mods" junkie
    • dopetalk
Re: a quick question about deleting a member via the database
« Reply #7 on: September 08, 2018, 10:32:38 AM »
That's probably a better idea ... my thinking was that I wanted to automate it and also couldn't find a specific option for members NEVER having been logged in.

I also got that vanilla function of SMF's confused with the "email Inactive Members" mod and thought that it was a part of it ! my bad, whoops.

anyway, it's back to normal since i do keep backups (thankfully).

FYI, the best integrity test is Admin>Forum Maintenance>Routine>Find and repair any errors (should be none).

at least my php skills are improving and if i try something like this again then i will read the existing code first.

Thanks again.
« Last Edit: September 08, 2018, 10:47:24 AM by aegersz »
The configuration of my Linux VPS (SMF 2.0 with 145 mods & some assorted manual tweaks) can be found here and notes on my mods can be found here (warning: those links will take you to a drug related forum)

Offline aegersz

  • SMF Hero
  • ******
  • Posts: 1,490
  • Gender: Male
  • "mods" junkie
    • dopetalk
Re: a quick question about deleting a member via the database
« Reply #8 on: September 08, 2018, 03:37:19 PM »
I eventually realised i had made a mistake and deleted some active members (as i was also testing for those without valid email addresses) and managed to mess it up.

However, with a restored DB and just the script,  i successfully deleted those NEVER active by removing the only other association i could think of - the 'PMs to new members' "leftovers" (as Kindred warns) by running:   

1. Admin > Maintenance > Forum > Recount all forum totals (corrects member count).

2. Admin>Forum Maintenance>Routine>Find and repair any errors (deletes the PMs).

3. rerun 2. to confirm the absence of new errors, by SMF.

seems ok in my test system after running this batch php script to delete the NEVER active:

Code: [Select]
<?php
$servername 
"localhost";
$username "?";
$password "?";
$dbname "?";
$tbpref "?_";

// SET THESE TOO !
$days_since_registered 30;
$do_email 0//<---  note this setting (run script with both off for testing 
$do_delete 0//<--- care with this setting
// --------

$connect = new mysqli($servername$username$password$dbname);
if (
$connect->connect_error) {
        die(
"Connection failed: " $connect->connect_error);
}

$sql "SELECT * FROM" " " $tbpref "members WHERE last_login = '0'";


$result $connect->query($sql);

if (
$result->num_rows 0) {
        
// output data of each row
        
while($row $result->fetch_assoc()) {
                
$datereg $row["date_registered"];
                
$current_date date("U"/* to have it in microseconds */;
                
$your_month date("m"$datereg);
                
$your_day date("d"$datereg);
                
$your_year =  date("Y"$datereg);
                
$selected_date_stamp mktime(0,0,0,$your_month,$your_day,$your_year);
                
$selected_date date("U",$selected_date_stamp);
                
$difference round (($current_date $selected_date)/(3600*24));
                if (
$difference >= $days_since_registered) {
                        echo 
$row["member_name"] . " " $row["email_address"] . " " "registered" " " $difference " " "days ago" PHP_EOL;
                        
$to_member =  $row["member_name"];
                        
$from_name "SMF Administrator";
                        
$from_email "smfadmin@site-name";
                        
$headers "From: $from_name <$from_email>";
                        
$body "Dear $to_member, \n
You have never logged in to https://site-name since you joined us so I will be removing you within 2 weeks unless you do so. \n
I have already activated your account but both that email and your activation mail probably went to your spam folder. \n
If you have any questions or need any help on this matter then just reply to me. \n
Regards, \n
Server Admin"
;
                        
$subject "Regarding your inactive membership";
                        
$to_email $row["email_address"];

                        if (
$do_email) {
                                if (
mail($to_email$subject$body$headers)) {
                                        echo 
"(email sent)" " ";
                                } else {
                                        echo 
"email failed" " ";
                                }
                        }

                        if (
$do_delete) {
                                
$sql2 "DELETE FROM" " " $tbpref "_members WHERE member_name = '$to_member' ";
                                if (
$connect->query($sql2) === TRUE) {
                                        echo 
"$to_member deleted successfully";
                                } else {
                                        echo 
"Error deleting record: " $connect->error;
                                }
                        }
                }
        }
} else {
        echo 
"0 results";
}

$connect->close();
?>

links to email Inactive Users
« Last Edit: September 08, 2018, 04:19:34 PM by aegersz »
The configuration of my Linux VPS (SMF 2.0 with 145 mods & some assorted manual tweaks) can be found here and notes on my mods can be found here (warning: those links will take you to a drug related forum)

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 58,435
  • Gender: Male
    • Kindred-999 on GitHub
Re: a quick question about deleting a member via the database
« Reply #9 on: September 08, 2018, 11:01:02 PM »
No no no no no no no no no and a thousand times no.

Doing that is essentially the same thing as manually deleting it in the database.

Seriously, what do you have against using the tools that we already built that do things properly?
If you absolutely must do it programmatically, then use the bloody function that is already bully into smf code instead of forcing a delete directly in the database
Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

Offline aegersz

  • SMF Hero
  • ******
  • Posts: 1,490
  • Gender: Male
  • "mods" junkie
    • dopetalk
Re: a quick question about deleting a member via the database
« Reply #10 on: September 09, 2018, 04:04:02 AM »
i agree with you and will use the SM provided tools.

i don't know the built functions yet. no harm done.



The configuration of my Linux VPS (SMF 2.0 with 145 mods & some assorted manual tweaks) can be found here and notes on my mods can be found here (warning: those links will take you to a drug related forum)