Insert multiple static values for multiple variable IDs

Question

Some back story (not directly relevant to my question, but maybe someone else can use my method)

I'm working in WordPress v3.9.1 using the plugin Advanced Custom Fields. I've imported a CSV file of custom values (using WP Ultimate CSV Importer plugin, free version) from the old database formatted as I need in WordPress with one exception - ACF Repeater Fields. The plugin is great, but there's not a good method of importing loads of data, yet.

Repeater fields are stored in the database as follows:

meta_id  post_id meta_key           meta_value
3894       4697    beds               2
3895       4697    _beds              field_53bcfe244a98d
4051       4697    _beds_0_other      field_53c2273053218
4050       4697    beds_0_other       1
4051       4697    _beds_1_other      field_53c2273053218
4050       4697    beds_1_other       3

5894       4698    beds               2
5895       4698    _beds              field_53bcfe244a98d
5051       4698    _beds_0_other      field_53c2273053218
5050       4698    beds_0_other       1
5051       4698    _beds_1_other      field_53c2273053218
5050       4698    beds_1_other       3

That is; for each post_id there is one Repeater field called "beds". "In" the repeater field is 1 field, repeated twice. Each field has 2 database entries - a field reference (used for managing saving fields - always the same per field) and a value. Not as intuitive of a setup as it could be, but it's designed around WordPress' default table system.

Actual question

Right now, I have fields imported from my old database that look like this:

meta_id  post_id meta_key           meta_value
####       4697    beds               2
####       4697    beds_0_other       1
####       4697    beds_1_other       3

####       4698    beds               2
####       4698    beds_0_other       1
####       4698    beds_1_other       3

I need to add

meta_id  post_id meta_key           meta_value
####       4697    _beds              field_53bcfe244a98d
####       4697    _beds_1_other      field_53c2273053218
####       4697    _beds_0_other      field_53c2273053218

####       4698    _beds              field_53bcfe244a98d
####       4698    _beds_1_other      field_53c2273053218
####       4698    _beds_0_other      field_53c2273053218

meta_key and meta_value are static - they never change (after the field is created it retains the same field_## until deletion). meta_id is auto increment.

The problem is that I have 200+ post_id values, each needing 50 static entries - not wanting to hard code that. I can select the required IDs using the following:

SELECT DISTINCT ID
FROM  `wp_posts`
WHERE post_type =  "community"

// Returns:
post_id
4697
4698

In Short

How can I do the following:

INSERT INTO `table` (`meta_id`, `post_id`, `meta_key`, `meta_value`)
VALUES
// foreach related distinct ID in wp_posts
 (NULL, 'ID', "_beds", "field_53bcfe244a98d"),
 (NULL, 'ID', "_beds_0_other", "field_53c2273053218"),
 (NULL, 'ID', "_beds_1_other", "field_53c2273053218")
// end foreach

** temp solution**

For the time being, I just dumped all the data using PHP & uploaded it in PHPMyAdmin. Could write a PHP loop that inserts, but I'm looking for a MySQL solution I can use without needing to upload a new php file (or sql).

$ids = array("4697", "4698" );

echo '
INSERT INTO `table` (`meta_id`, `post_id`, `meta_value`, `meta_key`)
VALUES<br />';
foreach ($ids as $id){
    echo '
(NULL, "'. $id .'", "1", "beds"),
(NULL, "'. $id .'", "field_53bcfe244a98d", "_beds"),
(NULL, "'. $id .'", "field_53c2273053218", "_beds_0_other"),
<br />';

}

Answer 1

The easiest way to do this would have been to import the hidden custom fields (leading underscore) along with the meta values. If you want to just clean the data up you can use a UNION SELECT as the input to your INSERT - you don't need the meta_id, it will be assigned automatically. Note that this SQL will not check to see if the data already exists before doing thing insert, so make sure you don't end up with dupes.

You can get the post_id that you need by querying wp_postmeta for all entries with meta_key of "beds". Using this post_id you can hard code the other values and alias them as columns in the SELECT statement, which is then used for the values of the INSERT.

-- modify the wp_ prefix as needed
INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`)
-- get "_beds" key/value for all entries with meta key of "beds"
SELECT post_id, '_beds' AS meta_key, 'field_53bcfe244a98d' AS meta_value
FROM wp_postmeta WHERE meta_key = 'beds'
UNION 
-- get "_beds_0_other" key/value for all entries with meta key of "beds"
SELECT post_id, '_beds_0_other' AS meta_key, 'field_53c2273053218' AS meta_value
FROM wp_postmeta WHERE meta_key = 'beds'
UNION 
-- get "_beds_1_other" key/value for all entries with meta key of "beds"
SELECT post_id, '_beds_1_other' AS meta_key, 'field_53c2273053218' AS meta_value
FROM wp_postmeta WHERE meta_key = 'beds'
-- order results (you can view what will be inserted if you run the SELECT without the INSERT)
ORDER BY post_id

You could also run this as three different INSERT statements without the UNION.