vb.org Archive - Need help with MySQL Query

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - Need help with MySQL Query (https://vborg.vbsupport.ru/showthread.php?t=163387)

711	11-22-2007 07:22 PM

Need help with MySQL Query

I would like to run a mysql query to copy the exact forum permissions from one usergroup to another. Ideally, when query is complete, the two usergroups will have identical permissions for all forums and subforums.

I am far from a SQL guru but I maybe my feeble attempt can give some idea of what I would like to do:

usergroupid = 2 {source usergroup whose permissions I would like to copy}

usergroupid = 83 {target usergroup whose permissions I would like to overwrite}

Step 1:

PHP Code:


		
			


SELECT forumpermissions FROM forumpermission WHERE usergroupid = 2;

Step 2: This is where I am stuck

PHP Code:


		
			


UPDATE forumpermission SET forumpermissions = {the ones selected above} WHERE usergroupid = 83;

Any help would be greatly appreciated.

Thanks!

WhaLberg

11-22-2007 07:32 PM

PHP Code:


		
			
<?php



$query = $db->query_read("SELECT forumpermissions FROM forumpermission WHERE usergroupid = 2");

$result = $db->fetch_array($query);

$secondquery = $db->query_write("UPDATE forumpermission SET forumpermissions = " . $result['forumpermissions'] . " WHERE usergroupid = 83");



?>

711	11-22-2007 07:38 PM

Sorry, I should have specified, can you give the code in pure SQL format? I will run the query directly from MySQL query Browser, not within a vBulletin or php script.

Thanks for the quick reply!!!!

Analogpoint

11-22-2007 10:32 PM

Something like this should do: (Back up your board first)
[sql]UPDATE forumpermission as permwrite, forumpermission as permread
SET permwrite.forumpermissions = permread.forumpermissions
WHERE permwrite.usergroupid = 83 AND permread.usergroupid = 2[/sql]

That should change usergroup 83's forum permissions to match usergroup 2's.

711	11-22-2007 10:57 PM

That query returns an error.

Even when I try just this:

PHP Code:


		
			
UPDATE forumpermissions as permwrite, forumpermissions as permread ;

or this

PHP Code:


		
			
UPDATE forumpermissions as permwrite;

All return errors.

I am using MySQL 4.1.22, perhaps 'UPDATE ... AS' is not supported in that version?

I couldn't find reference to that contruction anywhere in the mysql online documentation (not even for mysql 5).

Thanks for trying in any case!

Any ideas?

Analogpoint

11-23-2007 01:18 PM

I tested it on MySQL 5.0.21.

Now, what do you want to do, just update forum permissions one usergroup and make it the same as another one, or do you need to do this for many usergroups?

711	11-23-2007 04:27 PM

Quote:

Originally Posted by Analogpoint (Post 1388052)

I tested it on MySQL 5.0.21.

Now, what do you want to do, just update forum permissions one usergroup and make it the same as another one, or do you need to do this for many usergroups?

I would like to copy all forum permissions from one usergroup (usergroupid = 2) to another (usergroupid = 54).

I need to copy those permissions for several usergroups, but if we can get the query to work successfully on group 54, then I can simply run the query again for the others.

Analogpoint

11-23-2007 07:07 PM

The table is actually `forumpermission` (with no s, my bad). Can you try this again?
[sql]UPDATE forumpermission as permwrite, forumpermission as permread
SET permwrite.forumpermissions = permread.forumpermissions
WHERE permwrite.usergroupid = 83 AND permread.usergroupid = 2[/sql]

Eikinskjaldi

11-25-2007 08:44 PM

Quote:

Originally Posted by 711 (Post 1387779)

That query returns an error.

Any ideas?

One idea, please let us know exactly what error it returns. The more detail you provide, the more help we can provide.

All times are GMT. The time now is 01:56 AM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.02014 seconds Memory Usage 1,740KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (5)bbcode_php_printable (2)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (9)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: