News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

a quick question about deleting a member via the database

Started by aegersz, September 08, 2018, 01:59:03 AM

Previous topic - Next topic

aegersz

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 160+ 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). My (House) music DJ dedication page is here

aegersz

... 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 160+ 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). My (House) music DJ dedication page is here

aegersz

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 160+ 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). My (House) music DJ dedication page is here

Kindred

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.
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

aegersz

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 ?

The configuration of my Linux VPS (SMF 2.0 with 160+ 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). My (House) music DJ dedication page is here

aegersz

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 160+ 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). My (House) music DJ dedication page is here

SychO

Why don't you just delete them from the forum options
Additionally take a look at Admin>Forum Maintenance>Members>Remove Inactive Members
Checkout My Themes:
-

Potato  •  Ackerman  •  SunRise  •  NightBreeze

aegersz

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.
The configuration of my Linux VPS (SMF 2.0 with 160+ 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). My (House) music DJ dedication page is here

aegersz

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:

<?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
The configuration of my Linux VPS (SMF 2.0 with 160+ 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). My (House) music DJ dedication page is here

Kindred

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
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

aegersz

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 160+ 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). My (House) music DJ dedication page is here

Advertisement: