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 />';
}
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 yourINSERT
– you don’t need themeta_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 queryingwp_postmeta
for all entries withmeta_key
of “beds”. Using thispost_id
you can hard code the other values and alias them as columns in theSELECT
statement, which is then used for the values of theINSERT
.You could also run this as three different
INSERT
statements without theUNION
.