Changing custom field names

• elloxar

• March 6, 2020 at 10:59 pm

Hi
I have multiple field groups (e.g. events, venues, suppliers, honeymoons) and each one has multiple custom fields (e.g. slider, capacity, accommodation, budget, capacity, type, region). My problem is they all have the same slug.

I need to change the name of theses custom fields into more suitable ones (e.g. venue_slider, supplier_budget, honeymoon_region, etc.).

I have exported the field groups into a json and looked for the field_* so I can change them in my database.

UPDATE 'wpmx_postmeta' as m 
JOIN 'wpmx_posts' as p ON m.post_id = p.ID 
SET m.meta_key = '_venue_custom_slider' 
WHERE m.meta_value = 'field_582efaa778bfc' 
AND p.post_type = 'post'

What I’m struggling with is how can I change the custom field slug in my database. If I’m using this command, then all my custom fields are changed (events, venues, suppliers & honeymoons) which I don’t want.

UPDATE 'wpmx_postmeta' as m 
JOIN 'wpmx_posts' as p ON m.post_id = p.ID 
SET m.meta_key = 'venue_custom_slider' 
WHERE m.meta_key = 'custom_slider' 
AND p.post_type = 'post'

• John Huebner

• March 15, 2020 at 3:14 am

there isn’t any way to do this with a single SQL query and you’ll need to do this in PHP so that you can do one query to find what needs to be changes and then loop over these results and do a second query on each row to make the update.

First you need to select the correct fields, these are quick sql examples

‘SELECT * FROM wp_postmeta WHERE meta_value = {$field_key}’

Extract the post ID, and meta_key from the results

// get the correct meta key
$field_meta_key = substr($meta_value, 1)

You will need to loop over these results in PHP and update each row

update the field key reference

UPDATE wp_postmeta WHERE post_id = "$post_id" AND meta_key = "$field_meta_key" SET meta_key = "{$new_meta_key}"

update the field
`
UPDATE wp_postmeta WHERE post_id = “$post_id” AND meta_key = “$meta_key” SET meta_key = “_{$new_meta_key}”
‘

Even this would be prone to causing issues

I would not do this, no one sees the field names and I would live with what I built and learn from the experience to use better naming in the future.

• elloxar

• March 15, 2020 at 5:05 am

Hi and thank you for the suggestion, John.

What I did and solved my issue was using the following command:

UPDATE wpy4_postmeta SET meta_key= '_venue_custom_slider' WHERE (post_id = '29057' AND meta_value = 'field_582efaa778bfc' );

How did I do it in bulk? Good question. To get the ‘post_id’ (e.g. 29057) what I did was to:

1. Display all posts in the WP Admin > Posts area (Number of items per page: 999)
2. Filter by category (e.g. Venues)
2. Show Page Source
3. Copy/Paste the source code in the text editor (e.g. Atom)
4. Use the bulk search function (Find All) to search all my post IDs (e.g. ‘https://www.example.com/wp-admin/post.php?post=’ and selected only the string after that, next 5 digits in my case)
5. Paste that into an empty file, one on each row
6. Copy/Paste the mentioned MySQL command before and after the Post ID (using multi editing)

And the ‘meta_value’ I got it from ACF > Custom Fields > Field Groups > Field Keys (this column is hidden by default but can be shown easily from the Screen Options panel).

I hope that I remember the correct SQL command as I did finish the job the same day I posted here.

Cheers