MySQL, phpBB2 phpbb_users mass editing

[X11]

Hey there,

I know bugger all about writing code in PHP for MySQL stuff, but basically I want to be able to change certain elements of every user at once.

Like if I wanted to change everyones user_lang for an april fools joke quickily and effectivly?

Thanks in advance,
John.

[ZiaTioN]

well not sure about php but it would be pretty simple to do in perl. However I do not run phpBB, I use IkonBoard. Therefore I am not familiar with the way the database is setup with phpBB.

Maybe Void can help you out with the php if that is what you really want.

[Void Main]

Actually it wouldn't require any PHP or Perl at all. A single SQL command in the mysql client would be more than enough. Here's a little shell script I wrote and run nightly from cron to delete users that have signed up at least 1 day ago and have not yet posted a public or private message. These sorts of users are usually only signed up to advertise or for other less than honorable purposes (sorry to the honorable ones I have deleted):

cleanup_users:

Code: Select all

#!/bin/bash
echo "Deleting the following users on `date`:"
/usr/bin/mysql -t forums <<END
select from_unixtime(user_regdate),username,user_email,user_website from phpbb_users where user_posts = 0 and user_last_privmsg = 0 and user_regdate < unix_timestamp() - 86400 order by user_regdate;
delete from phpbb_users where user_posts = 0 and user_last_privmsg = 0 and user_regdate < unix_timestamp() - 86400;
\q
END


You may need to add the --user=someuser --password=somepassword to the mysql command. I redirect the output of this to a log which looks something like this:

Code: Select all

Deleting the following users on Tue Mar  1 02:00:02 CST 2005:
Deleting the following users on Wed Mar  2 02:00:02 CST 2005:
Deleting the following users on Thu Mar  3 02:00:02 CST 2005:
Deleting the following users on Fri Mar  4 02:00:01 CST 2005:
+-----------------------------+-------------+------------------------+--------------------------+
| from_unixtime(user_regdate) | username    | user_email             | user_website             |
+-----------------------------+-------------+------------------------+--------------------------+
| 2005-03-02 06:06:29         | stfu_sharon | sharon@software.com.ru | http://www.sftwar.com.ru |
+-----------------------------+-------------+------------------------+--------------------------+
Deleting the following users on Sat Mar  5 02:00:01 CST 2005:
Deleting the following users on Sun Mar  6 02:00:02 CST 2005:
Deleting the following users on Mon Mar  7 02:00:02 CST 2005:
Deleting the following users on Tue Mar  8 02:00:02 CST 2005:
+-----------------------------+----------+------------------+--------------+
| from_unixtime(user_regdate) | username | user_email       | user_website |
+-----------------------------+----------+------------------+--------------+
| 2005-03-06 06:50:23         | ZYV      | zaytsev@sandy.ru |              |
+-----------------------------+----------+------------------+--------------+


If you connect to your phpbb database (db name is "forums" in my examples) and describe the user table you can see what fields there are:

Code: Select all

$ mysql forums
mysql> describe phpbb_users;
+-----------------------+-----------------------+------+-----+-----------+-------+
| Field                 | Type                  | Null | Key | Default   | Extra |
+-----------------------+-----------------------+------+-----+-----------+-------+
| user_id               | mediumint(8)          |      | PRI | 0         |       |
| user_active           | tinyint(1)            | YES  |     | 1         |       |
| username              | varchar(25)           |      |     |           |       |
| user_password         | varchar(32)           |      |     |           |       |
| user_session_time     | int(11)               |      | MUL | 0         |       |
| user_session_page     | smallint(5)           |      |     | 0         |       |
| user_lastvisit        | int(11)               |      |     | 0         |       |
| user_regdate          | int(11)               |      |     | 0         |       |
| user_level            | tinyint(4)            | YES  |     | 0         |       |
| user_posts            | mediumint(8) unsigned |      |     | 0         |       |
| user_timezone         | decimal(5,2)          |      |     | 0.00      |       |
| user_style            | tinyint(4)            | YES  |     | NULL      |       |
| user_lang             | varchar(255)          | YES  |     | NULL      |       |
| user_dateformat       | varchar(14)           |      |     | d M Y H:i |       |
| user_new_privmsg      | smallint(5) unsigned  |      |     | 0         |       |
| user_unread_privmsg   | smallint(5) unsigned  |      |     | 0         |       |
| user_last_privmsg     | int(11)               |      |     | 0         |       |
| user_emailtime        | int(11)               | YES  |     | NULL      |       |
| user_viewemail        | tinyint(1)            | YES  |     | NULL      |       |
| user_attachsig        | tinyint(1)            | YES  |     | NULL      |       |
| user_allowhtml        | tinyint(1)            | YES  |     | 1         |       |
| user_allowbbcode      | tinyint(1)            | YES  |     | 1         |       |
| user_allowsmile       | tinyint(1)            | YES  |     | 1         |       |
| user_allowavatar      | tinyint(1)            |      |     | 1         |       |
| user_allow_pm         | tinyint(1)            |      |     | 1         |       |
| user_allow_viewonline | tinyint(1)            |      |     | 1         |       |
| user_notify           | tinyint(1)            |      |     | 0         |       |
| user_notify_pm        | tinyint(1)            |      |     | 1         |       |
| user_popup_pm         | tinyint(1)            |      |     | 0         |       |
| user_rank             | int(11)               | YES  |     | 0         |       |
| user_avatar           | varchar(100)          | YES  |     | NULL      |       |
| user_avatar_type      | tinyint(4)            |      |     | 0         |       |
| user_email            | varchar(255)          | YES  |     | NULL      |       |
| user_icq              | varchar(15)           | YES  |     | NULL      |       |
| user_website          | varchar(100)          | YES  |     | NULL      |       |
| user_from             | varchar(100)          | YES  |     | NULL      |       |
| user_sig              | text                  | YES  |     | NULL      |       |
| user_sig_bbcode_uid   | varchar(10)           | YES  |     | NULL      |       |
| user_aim              | varchar(255)          | YES  |     | NULL      |       |
| user_yim              | varchar(255)          | YES  |     | NULL      |       |
| user_msnm             | varchar(255)          | YES  |     | NULL      |       |
| user_occ              | varchar(100)          | YES  |     | NULL      |       |
| user_interests        | varchar(255)          | YES  |     | NULL      |       |
| user_actkey           | varchar(32)           | YES  |     | NULL      |       |
| user_newpasswd        | varchar(32)           | YES  |     | NULL      |       |
+-----------------------+-----------------------+------+-----+-----------+-------+
45 rows in set (0.00 sec)


Hmmm, I'll bet the "user_lang" column would contain users' lang settings:

Code: Select all

mysql> select username,user_lang from phpbb_users where username = 'Void Main';
+-----------+-----------+
| username  | user_lang |
+-----------+-----------+
| Void Main | english   |
+-----------+-----------+
1 row in set (0.01 sec)


Yup, sure 'nuff. Now you can change your lang in phpBB to a lang that you have installed other than english and run the above again. If I wanted to set my lang to "spanish" from mysql I would do this:

Code: Select all

mysql> update phpbb_users set user_lang = 'spanish' where username = 'Void Main';


If I wanted to set everyone who's lang is currently "english" to "spanish" I would do this:

Code: Select all

mysql> update phpbb_users set user_lang = 'spanish' where user_lang = 'english';


I'm sure you can figure out how to set it back.

To see your other tables you can either do this:

Code: Select all

mysql> show tables;


or on the command line:

Code: Select all

$ mysqlshow forums
Database: forums
+------------------------+
|         Tables         |
+------------------------+
| phpbb_anti_robotic_reg |
| phpbb_auth_access      |
| phpbb_banlist          |
| phpbb_categories       |
| phpbb_config           |
| phpbb_confirm          |
| phpbb_disallow         |
| phpbb_forum_prune      |
| phpbb_forums           |
| phpbb_groups           |
| phpbb_posts            |
| phpbb_posts_text       |
| phpbb_privmsgs         |
| phpbb_privmsgs_text    |
| phpbb_ranks            |
| phpbb_search_results   |
| phpbb_search_wordlist  |
| phpbb_search_wordmatch |
| phpbb_sessions         |
| phpbb_smilies          |
| phpbb_themes           |
| phpbb_themes_name      |
| phpbb_topics           |
| phpbb_topics_watch     |
| phpbb_user_group       |
| phpbb_users            |
| phpbb_vote_desc        |
| phpbb_vote_results     |
| phpbb_vote_voters      |
| phpbb_words            |
+------------------------+


You might want to do things like:

Code: Select all

mysql> describe phpbb_config;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| config_name  | varchar(255) |      | PRI |         |       |
| config_value | varchar(255) |      |     |         |       |
+--------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from phpbb_config;
+----------------------+--------------------------------------------+
| config_name          | config_value                               |
+----------------------+--------------------------------------------+
| config_id            | 1                                          |
| board_disable        | 0                                          |
| sitename             | Void's Forums                              |
| site_desc            | <a href="/">Void Main's Beautiful Site</a> |
| cookie_name          | phpbb2mysql                                |
| cookie_path          | /                                          |
| cookie_domain        | voidmain.is-a-geek.net                     |
| cookie_secure        | 0                                          |
| session_length       | 3600                                       |
| allow_html           | 0                                          |
| allow_html_tags      | b,i,u,pre                                  |
| allow_bbcode         | 1                                          |
| allow_smilies        | 1                                          |
| allow_sig            | 0                                          |
| allow_namechange     | 0                                          |
| allow_theme_create   | 0                                          |
| allow_avatar_local   | 0                                          |
| allow_avatar_remote  | 0                                          |
| allow_avatar_upload  | 0                                          |
| override_user_style  | 0                                          |
| posts_per_page       | 15                                         |
| topics_per_page      | 50                                         |
| hot_threshold        | 25                                         |
| max_poll_options     | 10                                         |
| max_sig_chars        | 255                                        |
| max_inbox_privmsgs   | 50                                         |
| max_sentbox_privmsgs | 25                                         |
| max_savebox_privmsgs | 50                                         |
| board_email_sig      | Thanks, Void Main                          |
| board_email          | voidsageek@yahoo.com                 |
| smtp_delivery        | 0                                          |
| smtp_host            |                                            |
| smtp_username        |                                            |
| smtp_password        |                                            |
| require_activation   | 1                                          |
| flood_interval       | 15                                         |
| board_email_form     | 0                                          |
| avatar_filesize      | 6144                                       |
| avatar_max_width     | 80                                         |
| avatar_max_height    | 80                                         |
| avatar_path          | images/avatars                             |
| avatar_gallery_path  | images/avatars/gallery                     |
| smilies_path         | images/smiles                              |
| default_style        | 11                                         |
| default_dateformat   | D M d, Y g:i a                             |
| board_timezone       | -6                                         |
| prune_enable         | 1                                          |
| privmsg_disable      | 0                                          |
| gzip_compress        | 0                                          |
| coppa_fax            |                                            |
| coppa_mail           |                                            |
| record_online_users  | 29                                         |
| record_online_date   | 1103873831                                 |
| server_name          | voidmain.is-a-geek.net                     |
| server_port          | 80                                         |
| script_path          | /forums/                                   |
| version              | .0.13                                      |
| board_startdate      | 1042025080                                 |
| default_lang         | english                                    |
| enable_confirm       | 1                                          |
| sendmail_fix         | 1                                          |
+----------------------+--------------------------------------------+
61 rows in set (0.00 sec)


Hey, the default lang is "english" (for new users). You might find it amusing to poke around. I am sure the list of available langs are stored somewhere in there.

EDIT: I just looked at the source (phpbb/includes/functions_selects.php, function language_select()) and the list of languages is not actually stored in the database but the language directory is read where the languages that you have installed reside to determine which languages are available. So all the directories that start with "lang_" in your phpbb/languages directory can be used. For instance you can use "english" if you have a "lang_english", "spanish" if you have a "lang_spanish", etc. You can also install other languages.

[X11]

Thank-you.

Thank you very much, this will be most useful.

I will also have to get some docs on MySQL and read them.

Maybe even buy a book for the hell of it.

[ZiaTioN]

yeah a php or perl script would help if you wanted to make it more dynamic. Like prompting for which table to edit and what to edit in that table, etc..

[X11]

Maybe another day when I have had more sleep, the php mysql functions are probably very similar in syntax (probably the same) to the mysql stuff from the commandline.

[ZiaTioN]

Both languages have a database driver module that makes the interfacing with the database pretty harmless. I could not tell you what the php version is but the perl driver is called DBI. It is actually pretty versatile. It allows you to use the exact same commands to fully operate and maintain most major databases out today. Some of those of course are Oracle, Informix, MS SQL, DB2, Sybase, MySQL 4, DBM and actually a few more I think.

Quote from Tim Bunce (creator of DBI)

The DBI is the standard database interface module for Perl. It defines a set of methods, variables and conventions that provide a consistent database interface independent of the actual database being used.

Sorry PHP lovers, I am just a perl pusher at heart!

[Void Main]

I probably program about the same about in both Perl and PHP. They each have their strengths and I'll use one or the other depending on what I am working on. For web front-end stuff I like PHP. For back-end work I like Perl although each can be used for either. I like Perl more for system level programming. I think parsing files and regular expressions are a little easier and more powerful in Perl but you can use Perl regular expressions in PHP as well so...

There certainly are a huge number of modules available for Perl although I personally try to not make my Perl programs dependant on exotic modules (of course DBI is not an exotic module). I think database programming is about equal in both although Perl can probably talk to more databases. I regularly use both PHP and Perl for MySQL, PostgreSQL and Oracle. Although for what X11 wanted I wouldn't have used Perl or PHP, I just would have done the single SQL statement right in a mysql command line.

[ZiaTioN]

True, unless he does not have command line access to his web server. Then a script loadable by a browser would be required to even chat with the database.

I recently (few months ago) started to contirbute to development of the message board IkonBoard and since have realized that I am pretty much the only one doing development work for it, lol.. Anyway since I have started doing this I have come to find out that a lot of people only have ftp access to their webservers and are unable to run commands via shell prompt on their webservers. I was really amazed on how many people are in this situation.

This is one of the main reasons why IkonBoard tries to catch all it's errors before letting them propogate to the system and then displays them in the browser so the user can see what the error is instead of having to beg their webhost to sift through the web logs for any errors that might give them a hint as to what the problem is.

[Void Main]

That is true, but in this case I know that x11's server is in his bedroom and that he has complete control over it (well, he and a few script kiddies have complete control over it). :)

[X11]

Nope, no script kiddies - just me

It runs Trustix 2.2 which I have found to be a really good server distro.

I have been reading the MySQL manual and stuff, just making it a habit to avoid webmin to do mysql stuff so I actually learn it properly.

[Void Main]

That's good. Webmin is for wussies. :)