MySQL query to delete a Joomla user with the same first and last name

Question

I know this is not true, but this is basically what I want to do:

delete FROM jos_users WHERE name like '<firstname>=<lastname>' 

In Joomla, the name field is one entry, and there are many spam accounts that have the same first and last name, such as Uuzuzaio Uzhuzaio, which I would like to delete.

So I need a query that will remove users from jos_users , where the name field contains 2 of the same word

Answer 1

You can use SUBSTRING_INDEX , which returns a substring before or after a certain number of separator occurrences.
In the example below, the space is a separator, and a number of 1 returns everything to the left of the first space and a score of -1 is everything to the right of the first space.

 DELETE FROM jos_users WHERE SUBSTRING_INDEX(name, " ", 1)=SUBSTRING_INDEX(name, " ", -1) 

I would first execute a SELECT to make sure there is no legit user (rarely, but this can happen)

Important note: this only works if the name is in the form <name> <surname> . The problem is with several places. In the case of several spaces (for example, <composite name> <composite name> ), you should use different account values ​​( 2;-2 3;-3 , etc.).

Answer 2

I think that

 DELETE FROM jos_users WHERE SUBSTRING_INDEX(name, '=', 1 ) = SUBSTRING_INDEX(name, '=', -1 ) 

should do the trick, have you tried?

Answer 3

Joomla by default does not have separate fields for the first and last name. Most likely, you separated them with some kind of symbol. I would recommend defining it and writing simple PHP code. At least that's what I would do.