phpBB SnippetsSQL Queries

Post Reply
User avatar
Topic Starter
Restless Rancor
Great Britain

Site Admin
New Member
Posts: 48
Joined: October 15th, 2018, 3:14 pm
Age: 25
Location: Hampshire, England
Contact:

SQL Queries

#1

Post by Restless Rancor » October 16th, 2018, 7:16 am


The purpose of this topic is to detail some of the SQL Queries I've needed in the past. If you have a broad knowledge of MySQL you probably won't need this topic.

Note: Make a backup of your database if you're not confident in this process. SQL Queries directly alter the database.
Jump To...
  • Reset Captcha [Go to...]
  • Reset Login Attempts [Go to...]
  • View Subscriptions [Go to...]
  • View Subscriptions per forum [Go to...]
  • Force style change for all members [Go to...]
  • Disable Extension [Go to...]
  • Force 'always attach my signature' [Go to...]
  • Force existing posts to show signature [Go to...]
  • Fetch phpBB version [Go to...]
  • Deactivate all styles except for prosilver [Go to...]
  • Set British English (en) as only language pack [Go to...]

Reset Captcha: [Go to top]

Code: Select all

UPDATE `phpbb_config` SET `config_value` = 'core.captcha.plugins.gd' WHERE `phpbb_config`.`config_name` = 'captcha_plugin';

Reset Login Attempts: [Go to top]

Code: Select all

UPDATE phpbb_users SET `user_login_attempts` = 0 WHERE `username` = 'x';
Replace x with the username.

View Subscriptions: [Go to top]

Code: Select all

SELECT topic_id, user_id FROM phpbb_topics_watch WHERE notify_status = 1;
SELECT forum_id, user_id FROM phpbb_forums_watch WHERE notify_status = 1;

View Subscriptions per forum: [Go to top]

Code: Select all

SELECT w.topic_id, w.user_id, t.topic_title FROM phpbb_topics_watch w, phpbb_topics t WHERE t.topic_id = w.topic_id
SELECT w.forum_id, w.user_id, f.forum_name FROM phpbb_forums_watch w, phpbb_forums f WHERE w.forum_id = f.forum_id

Force style change for all members: [Go to top]

Code: Select all

UPDATE phpbb_users SET user_style = X WHERE user_type <> x
Change x to the style ID you want to force

Disable Extension: [Go to top]

Code: Select all

update phpbb_ext set ext_active = 0 where ext_name like 'ext_name';
Change ext_name to the extension name as displayed in the database. EG. phpbb/pages
Alternatively manually change ext_active to 0 for the extension you want to disable.


Force 'always attach my signature': [Go to top]

Code: Select all

UPDATE phpbb_users SET user_options = user_options | 64 WHERE user_type != 2

Force existing posts to show signature: [Go to top]

Code: Select all

UPDATE phpbb_posts SET enable_sig = 1

Fetch phpBB version: [Go to top]

Code: Select all

SELECT config_value FROM phpbb_config WHERE config_name = 'version'

Deactivate all styles except for prosilver: [Go to top]

Code: Select all

UPDATE phpbb_styles SET style_active = 0 WHERE style_name != 'prosilver';

Set British English (en) as only language pack: [Go to top]

Code: Select all

DELETE FROM phpbb_lang WHERE lang_iso != 'en'


These aren't the droids you're looking for.

Post Reply

Topic Subscribers

1 user subscribed to this topic: Restless Rancor