Ever need to do a bulk settings change to an established site in Joomla? Perhaps you forgot to make the changes as you were building the site?

We recently worked on a development which already had a complex hierarchy of menu links set-up on the system (approx. 200 links), but realised later in the development that the "SSL Enabled" flag in the menu settings needed to be set to the "Off" setting in order to force the majority of the site to run in non-SSL mode:

The obvious solution is to go through each menu item one at a time at set this manually – but there's a risk that some may be missed, and of course, the time it would take to do this to 200+ links would be considerable.

So, instead, we set about using simple find & replace commands, we can run a query directly on the database to do this for us in one step. The query we will be using has this structure:

NOTE: Please ensure you make a full backup of your database before proceeding – if there's a problem, you will easily be able to restore the database and try again.

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, 'string to find', 'new string')

Use a program such as PHPMyAdmin to easily make edits to your database. Most hosting providers give easy access to this via your hosting control panel – this will allow you to find the table/field names and run the query easily.

So, in our case, it's fairly obvious we'll find the setting we need to adjust in the jos_menu table, so, browse the table, then click the edit icon to open up the contents of one of the rows:

You'll see I've indicated the bit we need to change on the image above – it's in the field named params. So, we now know the name of the table, and the field that we need to modify. The text to search for (in this case) will be "secure=0" (so, any menu SSL settings that are set to "ignore"), and the setting we need to change to (like on the diagram) is "secure=-1" ("Off). If you're in any doubt as to the setting that's needed, just change it in Joomla and view the string in PHPMyAdmin.

This now allows us to construct the final query that we will run on the database:

update jos_menu set params = replace(params, 'secure=0', 'secure=-1')

Simple, isn't it?

To run the query, simply click the "SQL" tab in PHPMyAdmin, paste your query into the box, and click go. Once it's run successfully, log into the Joomla administrator and check that everything was changed as expected.

There are 3 awesome comments...

1. 

   joomla tutorial - January 31, 2010 at 7:15 pm

   Very great information.
2. 

   omterter - October 23, 2010 at 2:51 am

   thanks for sharing the tip
3. 

   cheryl - May 12, 2017 at 2:24 pm

   thanks for posting this, it helped me get the code i needed to mass update one param

   update xxx_menu set params = replace(params,'”show_page_heading”:”1″‘,'”show_page_heading”:”0″‘) where params like ‘%”show_page_heading”:”1″%’

Leave a comment: