2

I've got a site with few hundreds menu items. I need to change one parameter in all of them. Right now in most of the pages "Show page heading" paremeter is set to "No". I need it to be set to "Use global" in all menu items.

I don't think it's possible from backend so I went to database and found 'menu' table containing all the menu items. Unfortunatelly parameter "show_page_heading" is inside large JSON data structure. All of the settings stored there must be kept except for the "show_page_heading", which must be changed from 0 or 1 to "" (empty string) .

Is there a way to do that?

6

I believe you'll need to use SQL statements such as below. But be CAREFUL - BACKUP THE TABLE FIRST to be sure that you can restore it if there are more considerations than you've realized at this point.

This could be combined into a single SQL statement, but for clarity of what you're doing, run each of them

UPDATE xxx_menu 
SET params = REPLACE(
    params,
    'show_page_heading":"1"',
    'show_page_heading":""'
  ) 
WHERE params LIKE '%show_page_heading":"1"%' 

UPDATE xxx_menu 
SET params = REPLACE(
    params,
    'show_page_heading":"0"',
    'show_page_heading":""'
  ) 
WHERE params LIKE '%show_page_heading":"0"%' 

There is/was a pending edit about that very last line of the 2nd SQL statement. I've reject the edit because I want to retain the clarity that the 1st statement is changing the records that have "1" as a value, and the 2nd statement is changing the records that have a "0" as a value. Both statements set the database value to "".

It's debatable which is "correct", but separating and explaining both should remain for clarity of what is happening, and allow the user to alter as necessary for the particular circumstance. I had records with "" and "0" in the database, so don't want to presume that my settings should be the same as everybody elses.

  • At first it didn't affect any records. I just needed to get rid of double quotes around 0s and 1s, since these values are stored as a json integer, not a string. After that it worked like a charm! Thank you! Please update your answer and I'll happily check it as accepted. – zorza Mar 18 '15 at 14:36
  • @zorza - is your pending edit to this answer what worked for you in the end? – Lodder Mar 18 '15 at 15:55
  • Interesting....making that change doesn't work for me...I'm going to fallback to my clarification about the SQL and let others adjust accordingly as you have since that change doesn't work on my end, and I don't wish to post SQL that I can't verify works as I expect it to. If that makes the answer unacceptable to you, so be it. – GDP Mar 18 '15 at 18:20
  • @zorza -1 for not accepting an answer that he took the time to write and test for you, which then solved your problem, but you had to adjust for your own database. – Al Knight Mar 19 '15 at 4:35
  • @GDP your answer is absolutely great. I upvoted it the moment I saw it, but I was just waiting for the edit so it would be copy-paste solution for everyone who come accross this problem in the future. But stating that double quotes issue is dependent on database is enough and I've accepted this answer. I just don't like when people accept solutions that don't quite work and need further digging in when fix is two clicks away. If it was just for me, I'd accept the answer right away. Anyway, thanks again for your help, your answer helped me a lot! – zorza Mar 19 '15 at 16:51
0

@zorza Thank you. Removing the quotes was what I needed. I had many menus that were set to not show the menu heading and I wanted them to use the global setting instead. This modification worked for me:

UPDATE `xxx_menu` 
SET params = REPLACE(
        params,
        '"show_page_heading":0',
        '"show_page_heading":""'
) 
WHERE params LIKE '%"show_page_heading":0%'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.