[Tutorial] Common SQL queries and how to run them

[jesuite]

I have an installation of MyBB that has actually been online for a long time, but since I've been working on the site and getting ready to launch it, it has been totally hidden from the public. I'm getting ready to launch it very soon and I was wondering if there was some way I could reset the stats on the board with an SQL Query so that when I do finally open the site, it looks brand new. Any help or suggestions would be appreciated!

[MMFB]

how can i run query for only members of a particular group? like i want to set a default value of my custom profile field to all the already registered users of a particular group?.

[Predatorz]

Can anyone help me making a query?
I have recently installed newpoints on my forum and I want to count current posts and threads as points so I would need a query that looked at a user's postnumber x 2 + threadnumber x 3 and then set the result as their newpoint field. Is this even possible?

[kateile]

Is this SQL Query correct?
ALTER TABLE mybb_users MODIFY views int NOT NULL DEFAULT '0'

somebody gave me to fix this error

MyBB SQL Error
MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
1364 - Field 'myforumicons_icon' doesn't have a default value
Query:
INSERT INTO mybbcn_forums (name,description,linkto,type,pid,parentlist,disporder,active,open,allowhtml,allowmycode,allowsmilies,allowimgcode,allowvideocode,allowpicons,allowtratings,usepostcounts,usethreadcounts,requireprefix,password,showinjump,style,overridestyle,rulestype,rulestitle,rules,defaultdatecut,defaultsortby,defaultsortorder) VALUES ('special zones','','','c',0,'',1,1,1,0,1,1,1,1,1,1,1,1,0,'',1,0,0,0,'','',0,'','')

[])RAG()N]

Set all people to recieve admin emails (requires template changes to remove option to change again):

UPDATE `mybb_users` SET `allownotices` = '1' WHERE `allownotices` = '0'

Set all people to recieve PMs (requires template changes to remove option to change again):

UPDATE `mybb_users` SET `receivepms` = '1' WHERE `receivepms` = '0'

Where do I change and what do I change in the template ?

[thelovelyone]

Find the specific lines u want to disable in usercp_options template and usercp_options_pm
and change the input type to "hidden", also make sure the default "values" are as u want.

Important: Don't remove them completely!

[])RAG()N]

Find the specific lines u want to disable in usercp_options template and usercp_options_pm
and change the input type to "hidden", also make sure the default "values" are as u want.

Important: Don't remove them completely!

Thanks for the help

I don't have a "usercp_options_pm"  

I'm running the Duende-V2 theme if that helps

[salebre]

Great thread,many thanks !!!

my issue
Im trying to mass check the box "Notify by email when new private message is received"  through an mysql query for all existing user with this :

UPDATE mybb_users SET pmnotify = 'yes';

and im getting this message:

Warning: #1366 Incorrect integer value: 'yes' for column 'pmnotify' at row 1

Im not so skilled and need help how to do it...

best regards

[Matt]

UPDATE mybb_users SET pmnotify = 1;

It's an integer column so won't accept a string.

[salebre]

UPDATE mybb_users SET pmnotify = 1;

It's an integer column so won't accept a string.

Like a charm,perfect as always !!!

Many thanks Sir and happy holidays

br