If I update username thru sql, what tables would be affected ?

Need some custom code changes to the phpBB core simple enough that you feel doesn't require an extension? Then post your request here so that community members can provide some assistance.

NOTE: NO OFFICIAL SUPPORT IS PROVIDED IN THIS SUB-FORUM
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTE: NO OFFICIAL SUPPORT IS PROVIDED IN THIS SUB-FORUM
exemplary1
Registered User
Posts: 109
Joined: Mon Feb 05, 2024 11:41 am

If I update username thru sql, what tables would be affected ?

Post by exemplary1 »

If I want to update username name through sql query, how many tables need to be updated ?
Let's say there's username 'abc1', and I want to update it to 'abc'. Are updating username & username_clean in table phpbb_users enough ?
User avatar
Mick
Support Team Member
Support Team Member
Posts: 26681
Joined: Fri Aug 29, 2008 9:49 am

Re: If I update username thru sql, what tables would be affected ?

Post by Mick »

You can do that in the ACP> Users and groups> Manage users> User administration without messing with the database.
  • "The more connected we get the more alone we become" - Kyle Broflovski©
  • "The good news is hell is just the product of a morbid human imagination.
    The bad news is, whatever humans can imagine, they can usually create.
    " - Harmony Cobel©
🇬🇧
exemplary1
Registered User
Posts: 109
Joined: Mon Feb 05, 2024 11:41 am

Re: If I update username thru sql, what tables would be affected ?

Post by exemplary1 »

But I want to mass update bunch of usernames.
And I am not doing it on live forum.
User avatar
AmigoJack
Registered User
Posts: 6115
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン

Re: If I update username thru sql, what tables would be affected ?

Post by AmigoJack »

exemplary1 wrote: Sat Feb 10, 2024 12:24 pmAre updating username & username_clean in table phpbb_users enough ?
My post here demonstrates that your username now resides in my post text, too. Would you now consider that by far much more columns/tables would need to be addressed?

When doing it per ACP, phpBB executes mostly \includes\functions_user.php's function user_update_name(), so just call that in a loop if you want to code. Updating textual usernames in post texts and private message texts is more difficult. Wiping all traces of a previous username needs talent.
  • "The problem is probably not my English but you do not want to understand correctly. ... We will not come anybody anyway, nevertheless, it's best to shit this." Affin, 2018-11-20
  • "But this shit is not here for you. You can follow with your. Maybe the question, instead, was for you, who know, so you shoved us how you are." axe70, 2020-10-10
  • "My reaction is not to everyone, especially to you." Raptiye, 2021-02-28
User avatar
Brf
Support Team Member
Support Team Member
Posts: 53468
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}

Re: If I update username thru sql, what tables would be affected ?

Post by Brf »

AmigoJack wrote: Thu Feb 15, 2024 4:14 pm
exemplary1 wrote: Sat Feb 10, 2024 12:24 pmAre updating username & username_clean in table phpbb_users enough ?
My post here demonstrates that your username now resides in my post text, too.
No it doesn't. Only the userid is there. The only table that would have be changed is the users table. Everything else looks up the username by id from there.

Hmmm... Actually, first_poster and last_poster are stored by name in the topic and forums table.
User avatar
axe70
Registered User
Posts: 752
Joined: Sun Nov 17, 2002 10:55 am
Location: Italy
Name: Alessio

Re: If I update username thru sql, what tables would be affected ?

Post by axe70 »

exemplary1 wrote: Sat Feb 10, 2024 12:24 pm If I want to update username name through sql query, how many tables need to be updated ?
Let's say there's username 'abc1', and I want to update it to 'abc'. Are updating username & username_clean in table phpbb_users enough ?
Yes, if there are no extension that can require the change also, but normally it should not be, or the extension is a fake because should manage users by ids maybe. But it depend, maybe.

Anyway the answer is Yes
Do not take me too serious
Anyway i do not like Discourse
User avatar
Brf
Support Team Member
Support Team Member
Posts: 53468
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}

Re: If I update username thru sql, what tables would be affected ?

Post by Brf »

axe70 wrote: Fri Feb 16, 2024 2:05 pm Anyway the answer is Yes
No.
Brf wrote: Thu Feb 15, 2024 4:50 pm first_poster and last_poster are stored by name in the topic and forums table.
User avatar
axe70
Registered User
Posts: 752
Joined: Sun Nov 17, 2002 10:55 am
Location: Italy
Name: Alessio

Re: If I update username thru sql, what tables would be affected ?

Post by axe70 »

Ok: Brf is right, so

Code: Select all

mb_strtolower($anusername,'UTF-8');
as value for the username_clean (lowercase)
then perform a query for each you'll change, changing in accord
with this

Code: Select all

first_poster and last_poster are stored by name in the topic and forums table. 
should not so hard for you as i intended.

The query need to search for the name and update it in accord with the new
Do not take me too serious
Anyway i do not like Discourse
User avatar
axe70
Registered User
Posts: 752
Joined: Sun Nov 17, 2002 10:55 am
Location: Italy
Name: Alessio

Re: If I update username thru sql, what tables would be affected ?

Post by axe70 »

Ps. no Brf is wrong (if i am not wrong)
Because fields he say, are filled ONLY when it is a guest to post, while if it is a registered user, the post is retrieved and processed via
poster_id field!

Sorry for the bump :D

so just beware that anyway the username_clean field need to be:

Code: Select all

mb_strtolower($anusername,'UTF-8');
as value for the username_clean (lowercase)
Do not take me too serious
Anyway i do not like Discourse
exemplary1
Registered User
Posts: 109
Joined: Mon Feb 05, 2024 11:41 am

Re: If I update username thru sql, what tables would be affected ?

Post by exemplary1 »

Thank you for the inputs.
axe70 wrote: Fri Feb 16, 2024 2:22 pm
so just beware that anyway the username_clean field need to be:

Code: Select all

mb_strtolower($anusername,'UTF-8');
as value for the username_clean (lowercase)
I just asked about difference between username and username_clean , but I think my reply got lost somewhere. Thank you.
User avatar
Brf
Support Team Member
Support Team Member
Posts: 53468
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}

Re: If I update username thru sql, what tables would be affected ?

Post by Brf »

No. Those last_poster and first_poster names are always filled in, to make displaying the forumlist and topiclist easier, without having to join in the post data.
User avatar
axe70
Registered User
Posts: 752
Joined: Sun Nov 17, 2002 10:55 am
Location: Italy
Name: Alessio

Re: If I update username thru sql, what tables would be affected ?

Post by axe70 »

I checked the database table and it is not (and for what i know because i did the same in the past)
I see the field filled only when the user is a registered user and not when it is a guest user (into posts table)
While into the topics table, there is the topic_first_poster_name yes,
but it can be omitted to be changed because lead to no problems at all-

the unique that he have to do, is to change the username and the username_clean into the users table.

What phpBB will use, are ids, not usernames, for the posts/topics processes and the fact that the topic_first_poster_name will not match for some topic, is not a problem (and can also be resolved with easy, but i do not advice to do it, because it is not really needed maybe)

[EDITED]
Do not take me too serious
Anyway i do not like Discourse
User avatar
Brf
Support Team Member
Support Team Member
Posts: 53468
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}

Re: If I update username thru sql, what tables would be affected ?

Post by Brf »

I do not know what version of phpBB you might be looking at, but forum_last_poster_name is definitely filled in for every forum, whether the last poster is a registered user or a guest. The same is true for topic_last_poster_name and topic_first_poster_name. If those are not corrected, then the forumlist and topiclist would show the wrong names.

just to prove it to you, I went into my Forums and topics tables and put the wrong names into to those fields:
You do not have the required permissions to view the files attached to this post.
User avatar
axe70
Registered User
Posts: 752
Joined: Sun Nov 17, 2002 10:55 am
Location: Italy
Name: Alessio

Re: If I update username thru sql, what tables would be affected ?

Post by axe70 »

Hi Brf, but this is not significant because
how much time the info you say stay into the index page? until a new post inserted.
And

Code: Select all

https://www.......com/w3/forums/memberlist.php?mode=viewprofile&u=48&sid=
while the name is wrong, for the little time it will be until a new post inserted, the id is right and will return the correct result if you'll click into, leading you to the right user.
And as suggested, because it is very easy but i do not advice because maybe not so useful, he can perform a query to substitute the field into topics table, with the new username
Do not take me too serious
Anyway i do not like Discourse
User avatar
AmigoJack
Registered User
Posts: 6115
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン

Re: If I update username thru sql, what tables would be affected ?

Post by AmigoJack »

Brf wrote: Thu Feb 15, 2024 4:50 pm
AmigoJack wrote: Thu Feb 15, 2024 4:14 pm My post here demonstrates that your username now resides in my post text, too.
No it doesn't. Only the userid is there.
That's also not true. And never has been. Otherwise I wouldn't be able to search for the keyword exemplary1 and get my own post as result. One doesn't quote with the username and phpBB then magically eliminates that written username to make it an ID. Go try it yourself, go inspect the database table content.
  • "The problem is probably not my English but you do not want to understand correctly. ... We will not come anybody anyway, nevertheless, it's best to shit this." Affin, 2018-11-20
  • "But this shit is not here for you. You can follow with your. Maybe the question, instead, was for you, who know, so you shoved us how you are." axe70, 2020-10-10
  • "My reaction is not to everyone, especially to you." Raptiye, 2021-02-28

Return to “phpBB Custom Coding”