Inserting data into MagicFields using mysql queries

Question

PART 01: INTRO Hey Stackexchange Greetings from the Snowy alps of Norway

I have now tried for two full consecutive days to reverse engineer Magic Fields. Why? Because I am a simple man, and want to import data to my wordpress from an exotic database using CSV (and not programming a php routine, which I don't know nor understand).

This has worked for all vanilla Wordpress fields, i.e. post-data, categories and so on. (I used LibreOffice Calc to fine-tune the CSV data, Quest Toad for MySQL import and to create MySQL queries).

My designer has designed this entire thing extensively using the Magic Fields plugin. Therefore I have to deal with Magic Fields for the rest of the data that I need to have imported. It's a movie database, so it's the typical meta-data for movies like "productionyear" "producer" "imdblink" "youtubetrailerlink" and so on.

My question is that I am hoping to get some insights/ideas/support on how to import these data into the magic fields. I have tried and given up the "magic fields importer", it is not documented and does not give any error messages when I try to import various csv formats.

PART 02: Understanding Magic Fields

My question is how can I populate(insert) a magic field with data using a mysql query? I have tried to understand what it is Magic Fields is doing when I put some data into a magic field and press save, in the admin-edit-post-frontend. It does a lot of things that I can't seem to re-create. For one it is not enough to insert data into the two places where the data is referenced. wp_postmeta and wp_mf_post_meta.

This example post is not sufficient to get the data to "work" i.e. display themselves in wordpress neither in the admin-post-editor nor the front-end for the user:

INSERT INTO `wp_postmeta` (`post_id`,`meta_key`,`meta_value`) VALUES ('474','originaltitle','Die Hard 3');

INSERT INTO wp_mf_post_meta ( meta_id, field_name, field_count, group_count, post_id )  VALUES ( 1100083, 'originaltitle' , 1,1 ,474 );

Also with that meta_id number, Magic Fields creates a number series of 10 for each post, like 7000-7010 and then 8000-8010. I don't understand where it takes those numbers from, and whether they have to be sequential/consecutive. I have found that once entered by the admin-post-editor frontend, I can change the meta-id's in wp_postmeta and wp_mf_post_meta and it still works. But again when I try to create them myself with a SQL query, "it just doesn't work (TM)".

After enabling MySQL querylogging and examining the logfile from the MySQL db engine gives and then trying to insert the queries manually (that I can make out from the logs), I still can't make Wordpress "find" the data and display them properly on the page.

I can modify the data with mysql queries after I have populated them using the frontend with no problems.

Also using the query logs, I found that after I push "SAVE" in the Wordpress frontend-post-editor it does no less than ~780 mysql query lines (regardless if I populate 1 or 10 of my magic fields)!

The other thing I have tried is to take a snapshot of the database before and after I have done the post of the magic fields, and then used a diff-tool (various, WinMerge and Notepad++ mainly). I can't understand what it's doing but I THINK it is doing some hidden trickery in wp_term_taxonomy. This is just speculation.

Part 03: Conclusion I don't know programming so any practical solution, would be deeply apprechiated.

the full query I want to do per post (I have about 800 of these) is here: http://pastebin.com/5cZT3AjA

The FULL list of queries that Magic Fields is doing after I push save in the admin-front-end is listed here (which I call the 'robot-at-work'). http://pastebin.com/c2c6qUQt To be sure I have checked three times that it wasn't something extraordinary that it is doing ~780 lines after I push save.

I guess if I could find a way to bulk-edit all of my posts, and then have the "robot" i.e. admin-post-edit frontend do the work to create all these lines in the database. I could then change the fields afterwards... But the bulk-editor doesn't show the magic-field editor either.

I have twisted my head around this problem 10 times now, and can't twist anymore!

Answer 1

I had the same problem but I was successful importing from an self produced xml (for the postmeta).

<wp:postmeta>
<wp:meta_key>telefono</wp:meta_key>
<wp:meta_value><![CDATA[$tel_azienda]]></wp:meta_value>
</wp:postmeta>

The problem was that after that the data were not available to use with MF, I've solved it with this mysql query:

INSERT IGNORE wp_mf_post_meta (`id`, `group_count`, `field_count`, `post_id`, `field_name`, `order_id`) 
SELECT `meta_id`, 1, 1, `post_id`, `meta_key`, 1 FROM `wp_postmeta` WHERE  `meta_key` = 'mini_descrizione' OR  `meta_key` = 'imagen_en_categoria' OR `meta_key` = 'direccion' OR `meta_key` = 'codigo_postal' OR `meta_key` = 'ciudad' OR `meta_key` = 'provincia' OR `meta_key` = 'telefono' OR `meta_key` = 'email'OR `meta_key` = 'web' OR `meta_key` = 'google_map'

It copies the metas from the wp_postmeta table to the wp_mf_post_meta.

Answer 2

Thank you so much buddy. I wish I had seen your post before!

Meta_id 's must be chronological and can't be arbitrary (i.e. random).

data must be inserted into wp_mf_postmetaand wp_postmeta.

these records relate to post_id, meta_id and the key-value stores of the data itself such where key would be "movietitle" and value would be "Die Hard".

I have uploaded the csv I created to do this and create the mysql queries.
I used LibreOffice Calc to finetune the data and Toad for MySQL (by Quest, free) to create the MySQL queries.

These two records need to be modified for the data to be properly inserted:
note that the META_ID must be chronological i.e. 11001, 11002, 11003 per field per post.
so if meta_key is "Movietitle" first then that has meta_id of 11001, if "productionyear" is second then that is 11002 and if "country" is third then that is 11003.

Also note that for the wp_mf_postmeta the fieldname is the same value as meta_key from wp_postmeta , I.e. the meta key.

INSERT INTO `wp_postmeta` (`post_id`,`meta_key`,`meta_value`) VALUES (346,'produksjonsar','18001');

INSERT INTO wp_mf_post_meta ( meta_id, field_name, field_count, group_count, post_id )  VALUES ( 18001, 'produksjonsar' , 1,1 ,346 );

The file I have used for this import is available here, if anyone needs to see an example of how to do this import. Again I used Toad for MySQL to create the SQL Queries by using the IMPORT function.

• http://ge.tt/9Q5h4zC

This also solves this question which I posted on the Wordpress Forums, Stackoverflow and Stackexchange.

http://wordpress.org/support/topic/importing-data-to-custom-fields?replies=2#post-2592976

Inserting data into MagicFields using mysql queries

https://stackoverflow.com/questions/8698631/importing-data-into-wordpress-magic-fields-using-mysql-queries

Massive props to Hameedullah Khan Stackexchange, profile here https://wordpress.stackexchange.com/users/5337/hameedullah-khan , for basically providing the clue needed to find the solution.