SQL query for updating field

[Argos]

I have a BIG site where I need to add an admin to all pages. I don't like to do that manually for each page, but I don't know how to create a SQL query. Can anyone give me a hand?

For all "pages" I want to change the "admin_groups" value from "1" to "2,1". How does that work?

[pcwacht]

NOT TESTED!!

Code:

$query = "UPDATE ".TABLE_PREFIX."pages SET  admin_groups = '2,1'";
$database->query($query);

This should set ALL pages to 2,1

you could allso do:

Code:

$query = "UPDATE ".TABLE_PREFIX."pages SET  admin_groups = '2,1' WHERE admin_groups = '1' ";
$database->query($query);

Look here for explanation : http://www.w3schools.com/php/php_mysql_update.asp

Have fun,
John

[Argos]

Thanks very much John!

[Argos]

Actually I needed just the SQL command to use in phpMyAdmin, but I figured that out from the above:

Code:

UPDATE pages SET admin_groups = '2,1' WHERE admin_groups = '1'

Worked like a charm!

It would be so nice to have a WB admin tool for setting access rights from the backend, for multiple pages at once... The current system is rather clumsy and inefficient. But these kind of SQL statements are a good substitute in the mean time.

[pcwacht]

Remember, an UPDATE without the WHERE clause will change every row in that table!

By Using a WHERE clause you can select one or more rows wich will be affected,
Example:

Code:

UPDATE pages SET admin_groups = '2,1' WHERE parent='6';

will change only those pages where the parent is page_id 6

tip
Copy the tabel yo wish to play on to another new table and experiment


John

[Argos]

Thanks, most useful info for SQL nitwits like me! 

[pcwacht]

I have learned it with a xampp install,
installed WB (I think the pre 2.6 version)
made some pages, newsitems etc

Fired up phpmyadmin and started to play with commands,
there are pretty descent tutorials out there in the g**gle-o-matic.

I still have troubles though doing really complex sql stuff like binding etc, but that is in most cases not even needed.

Have fun,
John