Skip to content

From Magic Fields 1.x to ACF, Part 2

Other posts in this series: Part 1; Part 2 (this post); Part 3; Part 4; Part 5; Part 6; Part 7; Part 8; Part 9

We left off at the end of Part 1 with a query that could be run from your database’s GUI interface:

UPDATE `prefix_posts` SET post_type='musical_work' WHERE ID IN (
SELECT * FROM (
SELECT ID FROM `prefix_posts`
LEFT JOIN `prefix_postmeta` ON `prefix_posts`.ID=prefix_postmeta.post_id
WHERE meta_key = '_mf_write_panel_id' AND meta_value = 1)
as t )

That changed all of the posts that used the Magic Fields Write Panel ID 1 to a custom post type of ‘musical_work’. I ran the same query three more times to change meta_value = 2 to ‘program’. The musical_work and performer custom post types are easy enough to flip to ACF because they re-use the same postmeta data structure as MF — a single entry; a single value.

And that converted them all here are screen caps from the old MF site (left) and the new ACF site (right).

Before I run meta_value = 3 to ‘performer’, and meta_value = 4 to ‘season’ I need to set up those custom post types in Custom Post Type UI. You can refer to Part I for information on how that’s done. The settings for these last two post types are pretty much identical to the first two, only the names have been changed.

Also, programs and seasons are harder. A program has multiple performers — at least a conductor and a soloist — and a season will have several programs and a collection of soloists. Here’s my test code for a program details page:


$date = get_field('basic_info_date');
$time = get_field('basic_info_time');
$location = get_field('basic_info_location');
$works = get_field('program_title');
$artists = get_field('artist_artist');


echo "Date is $date<br>";
echo "Time is $time<r>";
echo "Location is $location<br>";
echo "Works:";
print_r($works);
echo "Artists:";
print_r($artists);

Which generates this:

HTML code
Only the first values get captured.

As you can see, the “program_title” and “artist_artist” get_field calls are only returning the first item, whereas there are actually several of each.

The problem is that after you’ve changed the post type, you still have to cook up the meta values into a format that ACF understanfds. Because ACF multi-selections don’t use the same field name as with MF. MF creates separate lines in the postmeta table for each of the multiselected fields (left), but ACF creates just one entry with a serialized string (right).

So the function call above has an implicit “LIMIT 1” attached to it. Maybe it’s explicit, I don’t know; but that’s what it does.

This can lead to another potential problem if you try to change all of the entries with “WHERE meta_key = program_title AND post_id = 1234”.

In the previous recipe I suggested keeping the original custom field names. But here, I’m proposing to create a new field name to replace “program_title” with “program_titles” (and similarly for “program_dates” etc.) so that I can purge the database of unnecessary fields when I have time. Otherwise I’d need to do a test to skip duplicates, not try to seamlessly cross over between strings and arrays, and so on.

Here’s another go at the PHP template part:

$date = get_field('basic_info_date');
$time = get_field('basic_info_time');
$location = get_field('basic_info_location');
$works = get_field('program_titles');
$artists = get_field('artist_artists');


echo "Date is $date<br>";
echo "Time is $time<br>";
echo "Location is $location<br>";
echo "Works:";
print_r($works);
echo "\nArtists:";
print_r($artists);
echo "\n";

And when I pull up the page I get a couple of sensible arrays to work with. I’ll show the source code here for readability:


Date is February 6, 2011
Time is 2:00 pm
Location is The Broadway Presbyterian Church
114th and Broadway, New York City
Works:Array
(
[0] => 7
[1] => 13
[2] => 11
)

Artists:Array
(
[0] => 18
[1] => 22
)

So now I can attach some sensible code to all of these. For example this block:

echo "<p><strong>Date:</strong> $date<br>";
echo "<strong>Time:</strong> $time<br>";
echo "<strong>Location:</strong> $location</p>";


echo "<h3>Works on the Program</h3>";
foreach ($works as $work) {
# code...
echo '<p>' . get_the_title($work) . '</strong><br>';
echo get_the_excerpt($work) . '</p>';
}
echo "<h3>Guest Artists</h3>";
foreach ($artists as $artist) {
# code...
echo '<p>' . get_the_title($artist) . '</strong><br>';
echo get_the_excerpt($artist) . '</p>';
}

Yields this:

Live Page
The result of the PHP code above.

Plus now that I’ve got these post IDs stored ($work, $artist) I can use ACF and built-in WordPress functions to continue on to get the hyperlinks, add images to the performer’s blocks, and a few other things.

But What About All That Existing Content?

I’m glad you asked. Now we’re going to run some queries through a PHP script to generate new queries, queries that should create the postmeta content that we really need. This is the part where we get into the weeds a little bit, and if you’re not used to back-end coding — well, sorry. The first thing we do is set up a mysqli object, at least in my usual way. I have PHP installed on my laptop, and I’ve got remote SQL authorized on the host database so that I can make calls to it from here.

Warning, Code Ahead


<?php
$link = new mysqli('host.com:3306', $username, $password);


if ($link->connect_errno)
{
echo "Failed to connect to MySQL: " . $link->connect_error;
}


if (! $link->select_db($database) ) echo $link->errno;

If you’ve followed this far along, you know that $link is the database object which we can throw queries against. You could put this into a test.php file on your laptop running php and do something like ‘php c:/directory/test.php’. If you get no errors, you’re good.

The query I want to run to get this next chunk done is this, line by line:

$query = "SELECT ID, post_title, post_content, post_type, meta_key, meta_value FROM prefix_posts
From the list of posts, get the post ID, post title, content, type etc.
LEFT JOIN prefix_term_relationships ON ID=object_id
Join the term_relationships table’s object_id to the post ID
LEFT JOIN prefix_postmeta ON ID=post_id
Join the postmeta table’s post_id to the post ID
WHERE term_taxonomy_id=7
Only select those where the term_taxonomy_id is 7, corresponding to the line in the prefix_terms table where term_id = 7 and name = Performances.
AND (meta_key='artist_artist' OR meta_key='program_title')
Of course we’re only interested here in the artists and titles
ORDER BY ID ASC ";
Just for sanity’s sake.
$view = mysqli_query($link, $query);
Aaaand run the query, storing it in $view.

And what does that give you?

while ( $view_row = @mysqli_fetch_array( $view ) ) {
Read a line, put it into $view_row. It will be an indexed array.
$index = strval($view_row[0]);
We’ll use the value of ID (the post ID from the original query above) as the index.
if ( 'program_title' == $view_row[4] ) {
If the value of ‘meta_key’ is ‘program_title’, then…
$programs[$index]['works'][] = strval($view_row[5]);
}

We’re making an array of arrays here. $programs is the top-level array; $index is a sub-array of that. Then we’ll drop into the sub-array we’re (arbitrarily) calling ‘works’ the string value of meta_value (which would have been an integer). Those represent the post IDs of the individual works.
else if ( 'artist_artist' == $view_row[4] ) {
$programs[$index]['performers'][] = strval($view_row[5]);
}

At the same time make a sub-array of $programs[$index] called ‘performers’, and push in the meta_value (or post ID) of each performer.
}

Then we’re going to take that big array — which consists of the post IDs of programs — containing arrays of works and performers, and go through it item by item.

ksort( $programs );
foreach ( $programs as $programs=>$details ) {

Sort the array numerically just so things are easier to find. Then take each program’s array of details as $details.


echo "\n" . strval($programs) . "\t";
foreach ( $details['performers'] as $performer ) {
echo $performer . "\t";
}

I know it helps me to put in some sanity checks along the way so I can make sure I’m getting what I think I’m getting.

Here’s the heart of the matter. We’re going to build a mysqli query that we can run:

$insert_query = "INSERT INTO `prefix_postmeta` (post_id, meta_key, meta_value) VALUES ('$programs', 'artist_artists', '";
I really should be doing this as a prepared statement, but this is maybe more illustrative. We’re going to create a line in the postmeta table assigning values to the columns post_id, meta_key, meta_value:
$insert_query .= serialize($details['performers']);
As mentioned above, serialized data is how ACF understands multiple selections. Again, each program will have multiple performers and multiple musical works.
$insert_query .= "')";
Simply close the string and end the line.
echo "\n$insert_query\n";
Not just a sanity check, but when you run this program you can copy queries off of the terminal and paste them into your mysql GUI app.
$queries[$programs]['performers'] = $insert_query;
We’re building a multidimensional table called $queries, because it holds a bunch of queries. Then using the $programs value is a key, set the value of ‘performers’ to the query we just built.

Then do the same thing for the musical works, here keyed as ‘titles’:

$insert_query2 = "INSERT INTO `prefix_postmeta` (post_id, meta_key, meta_value) VALUES ('$programs', 'program_titles', '";
Same as above but the meta_key is ‘program_titles’.
$insert_query2 .= serialize($details['works']);
Serialize the list of several works.
$insert_query2 .= "')";
Close the string and the statement.
echo "\n$insert_query2\n";
Check your work.
$queries[$programs]['titles'] = $insert_query2;
}

Then push it onto the subarray as ‘titles’.

I echo this stuff out because I can see the queries on the command-line screen, or I can do something like php c:\script.php > myoutput.txt.

One could, if one were feeling daring, proceed to actually run these queries against the database. I’ll do a few at a time to make sure.

Here’s a sample of the output I get:

6217:
Autumn 2018 Concert
INSERT INTO `prefix_postmeta` (post_id, meta_key, meta_value) VALUES ('6217', 'program_titles', 'a:3:{i:0;s:4:"6133";i:1;s:4:"6137";i:2;s:4:"6127";}')
INSERT INTO `a234sI4GDe_postmeta` (post_id, meta_key, meta_value) VALUES ('6217', 'artist_artists', 'a:2:{i:0;s:4:"3344";i:1;s:2:"39";}')
6219:
Winter 2019 Concert
INSERT INTO `prefix_postmeta` (post_id, meta_key, meta_value) VALUES ('6219', 'program_titles', 'a:4:{i:0;s:4:"6141";i:1;s:4:"6147";i:2;s:4:"6152";i:3;s:4:"6156";}')
INSERT INTO `prefix_postmeta` (post_id, meta_key, meta_value) VALUES ('6219', 'artist_artists', 'a:2:{i:0;s:2:"39";i:1;s:4:"6186";}')
6221:
Spring 2019 Concert
INSERT INTO `prefix_postmeta` (post_id, meta_key, meta_value) VALUES ('6221', 'program_titles', 'a:4:{i:0;s:4:"6160";i:1;s:4:"6164";i:2;s:4:"6167";i:3;s:4:"6171";}')
INSERT INTO `prefix_postmeta` (post_id, meta_key, meta_value) VALUES ('6221', 'artist_artists', 'a:2:{i:0;s:2:"39";i:1;s:4:"6183";}')

Each of these INSERT INTO statements can be run separately on the database. Let’s see what happens.

Fingers crossed.

Here’s the page before running the query:

Before. Because we’re trying to parse non-existent arrays we’re getting a warning. The other fields (date, time, location) come up fine, as does the post body.

This, by the way, is the live page for the concert. The formatting and the information locks I’m showing are different, but it’s where we want to be eventually.

The actual page from www.broadwaybach.org, using the Magic Fields data and templates.

OK, I’ve run the two queries my script generated for me using phpMyAdmin. What do I get?

Displaying the correct musical works, and the correct soloists!

I can’t tell you how happy I am

That this worked the first time!

I’m going to spend the next day or two running these queries. I could just copy-paste them into the SQL window of the phpMyAdmin web page, line-by-line, with a semi-colon at the end of each. It should work.

Watch for the next installment where I go one level up and create concert seasons full of individual programs and soloists.

    Leave a Reply

    Your email address will not be published. Required fields are marked *