ACFs are a great (legacy?) way of storing additional data in a ClassicPress/Wordpress installtion. But things can get a but ugly when you have thousands of posts, each with ACFs maybe nested Repeater ACFs. Getting data out of larger installation with get_posts and WP_Query can quickly hit the limits of the DB installation and fail.

The solution? A raw, hand-crafted MySQL query:

Getting a Single Custom Field

SELECT wp_postmeta.meta_value             // return the meta_value
  FROM wp_posts, wp_postmeta
  WHERE wp_posts.post_status = 'publish'    // publish posts only
   AND wp_posts.ID = wp_postmeta.post_id
   AND wp_postmeta.meta_key = 'my_key'       // get value from 'my_key'

Getting the results with $wpdb->get_results().

$results = $wpdb->get_results($query);

Example Output

Array
(
    [0] => stdClass Object
        (
            [meta_value ] => 123
        )
    [1] => stdClass Object
        (
            [meta_value ] => 456
        )
)

That works great, if you want to get one Custom Field, but how about multiple Custom Fields?

Getting Multiple Custom Fields

Custom fields are stored in the wp_postmeta table and defined by the meta_key and meta_value column.

SELECT p.ID, m1.meta_value as v1, m2.meta_value as v2 
FROM wp_posts p
LEFT JOIN wp_postmeta m1 ON p.ID = m1.post_id
LEFT JOIN wp_postmeta m2 ON p.ID = m2.post_id
WHERE p.post_status='publish'
AND m1.meta_key = 'my_key'
AND m2.meta_key = 'my_other_key'

Is it important to note - and slightly unintuitive - that the select line also declares aliases which will be used in the rest of the query:

1. The p in p.ID is a shortcut for wp_posts
2. The m1 in m1.meta_value is a shortcut for wp_postmeta
3. Same as m2, but we want to select and JOIN different meta key we also need to have two shortcuts for wp_postmeta.
4. meta_value as v1 is also important. If we would not use ... as v1 then the return array would include meta_value as a key and m2 would overwrite m1. Does not have to be v1 and v2, use whatever you like.

Example Output

Array
(
    [0] => stdClass Object
        (
            [ID] => 123
            [v1] => 33
            [v2] => 20130402
        )
    [1] => stdClass Object
        (
            [ID] => 456
            [v1] => 22
            [v2] => 20130404
        )
)

Getting Multiple Custom Fields including ACF Repeater

The ACF Repeater fields also stores its values in wp_postmeta, following this schema: repeatername_nr_fieldname. Let's say we have a repeater field called videos and a sub-field called video, the meta_keys in wp_postmeta would look like this:

videos_0_video
videos_1_video
videos_2_video
videos_3_video
...

That means we need to modify the previous query, because the we can't be sure how many meta_key we have.

SELECT p.ID, m1.meta_value as v1, m2.meta_value as v2 
FROM wp_posts p
LEFT JOIN wp_postmeta m1 ON p.ID = m1.post_id
LEFT JOIN wp_postmeta m2 ON p.ID = m2.post_id
WHERE p.post_status='publish'
AND m1.meta_key REGEXP '[[:<:]]videos_[0-9]*_video[[:>:]]' 
AND m1.meta_value > 0
AND m2.meta_key = 'my_key'

AND m1.meta_key REGEXP '[[:<:]]videos_[0-9]*_video[[:>:]]' is a regular expression with some MySQL-specific syntax: [[:<:]] means beginning of string and [[:>:]] stands for end of string.

Sources

• https://wordpress.stackexchange.com/questions/237957/get-post-with-multiple-meta-keys-and-value
• https://wordpress.stackexchange.com/questions/358962/get-2-meta-values-from-meta-key-column

Working with XAMPP and and phpMyAdmin makes dealing with databases more visual, but importing large db dumb files can fail/take a long time.

Importing from the command line is faster and more stable.

Use the XAMPP mysql, if there is not a global mysql

cd /Applications/XAMPP/xamppfiles/bin 

Import on Local Server

mysql -u username -p db_name < ~/path/to/db/file.sql

Import with specified Server

mysql -u username -p -h hostname db_name  < ~/path/to/db/file.sql

Checking Status of MySQL Server

mysqladmin -u username -p -h hostname status

• -p asks for the password on execution
• Importing a ca. 300MB file takes ca. 5 seconds.
• Make sure the DB "db_name" already exists.

Troubleshooting

I exported all the DB from one installatino as one, big localhost.sql and then tried to import it in another one. That creates the following error:

ERROR 2006 (HY000) at line 127571: MySQL server has gone away

Some DBs have been imported, but not all.

Workaround: Export/Import DBs individually.

I am surprised that there is not a simple import function in mysql or phpMyAdmin, I did the following:

• In SQLite3, export tables as CSV
• In phpMyAdmin, create DB, and import table as CSV

Make sure you check the box that converst the first line into the table structure.

Table Size/Execution Time Limit

When importing a larger table (in my case ~300M), the phpMyAdmin was complaining about memory (which I fixed), but then the script time out.

Solution? Import directly from mysql

Open the XAMPP mysql binary:

cd /Applications/XAMPP/xamppfiles/bin 
./mysql -u root -p

LOAD DATA LOCAL INFILE '/path/to/table.csv' 
INTO TABLE mydb.mytable FIELDS TERMINATED 
BY ',' ENCLOSED BY '"' LINES TERMINATED 
BY '\n' IGNORE 1 LINES;

(Line breaks are added for legibility.)

• mydb.mytable are mydb name and mytable table
• IGNORE 1 LINES ignores the first line with the headers

I encountered a situation where my dev MySQL was 5.6 and running with a utf8mb4_unicode_ci Server Connection Collation, and the remote MySQL with 5.5 and a utf8mb4_general_ci collation.

Needless to say, the remote DB did not like the dumps from my dev DB.

Quick and Dirty Solution:

replace('utf8mb4_unicode_520_ci', 'utf8mb4_general_ci') replace('utf8mb4_unicode_ci', 'utf8mb4_general_ci')

If your are using Wordpress you might have run into the following situation. Let's say the main usage of this particular Wordpress installation is not so much blogging (articles), but as light-weight CMS (pages). Wordpress' Page and Subpage system works quite well in this respect, but I am no happy with one thing: Numbers as Page Slugs.

While it's possible to create a new page, name it with a number "2012", the page slug automatically turns to 2012-2. Manually editing the page slug back to "2012" and publishing it should solve that. Unfortunately not. Back to "2012-2".

In most cases, when the page slug is not a number, the presence of a "-2" and the end of the slug suggests, that the page already exists in the database. Make sure it's not in the Trash, empty the trash, if it is.

trembl.org/2012/

It does not seem to be a huge problem, because even when the page slug is "2012-2" and you ask for a page named trembl.org/2012/, Wordpress will happily forward (redirect?) you to trembl.org/2012-2/. So... Working: Yes? Nice solution: No!.

No other choice than to have a look at the MySQL itself. If you are not comfortable doing it on the CL, have a look at phpMyAdmin. Actually, if you are not comfortable editing MySQL, don't do it at all. Messing up the MySQL will mess up you Wordpress installation! And I assume you have a recent backup.

So, with that out of the way, open your Wordpress MySQL DB in phpMyAdmin. The wp_posts table holds all your posts, pages, as well as all the revisions to them. Look for the one you want to edit, in this case "2012-2". Open the entry for editing (the small crayon on the left). You'll want to edit "post_name". Change it to whatever you live ("2012"), save (it's called "Go") and you're done.

Wordpress Version: 3.1.3