Anyone know how to delete inactive users from Joomla database?

Discussion in 'MySQL' started by Beefandy, Nov 22, 2011.

0
  1. #1
    Hi, I want to delete all the spammy users out my database

    I read that this command would do it

    delete from jos_users where lastvisitDate=’0000-00-00 00:00:00′

    However I get this error:

    #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 '00:00:00′' at line 1


    Any help how I can get rid of these spammy inactive users?

    Thanks
     
    Solved! View solution.
    Beefandy, Nov 22, 2011 IP
  2. #2
    Make sure you're not using backticks around the date time, should be a single quote.

    delete from jos_users where lastvisitDate='0000-00-00 00:00:00'

    Also, the "′" might also suggest that there is an incorrect character in the query. Is this a standard english keyboard? My first guess would be leaning toward the backticks vs. single quote.
     
    jestep, Nov 22, 2011 IP
    Beefandy likes this.
  3. Beefandy

    Beefandy Active Member

    Messages:
    773
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    90
    #3
    Thanks that worked fine now!

    Just got rid of 12000 users etc

    Could you suggest any other query I could run to get rid of more. I still have about 200k users registered most are spam..?

    There's so many I cant send a mess mail properly
     
    Beefandy, Nov 22, 2011 IP
  4. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #4
    I don't know the back end enough to be able to give specific advice on purging joomla. If you have the number of logins it may be something you can query based on. My biggest concern would be what is a legitimate user, and what is a user that just doesn't log in often, or recently.
     
    jestep, Nov 22, 2011 IP