Blog

Recently, I helped a client import a large set of addresses into a location plugin for WordPress. The import mainly went smoothly, but we noticed some issues when searching in areas with zip codes leading with one or two zeroes. The addresses weren’t coming up as they should.

After examining some of the imported addresses, we realized that all of the leading zeroes were being stripped, and we could no longer search by those zip codes. I’m going to give a brief overview of why this happened, and how I solved it. Hopefully it helps if you need to make this kind of update to a WordPress database too!

Why is this happening?

Some programs “helpfully” strip leading zeroes from numbered cells, including Excel, Numbers, and Google Sheets. This means that 04102 in Portland, Maine becomes 4102, which isn’t a zip code in the US.

The same could happen upon import into the database, depending on how the import is done. In either case, I’m working with an import that’s already complete, as opposed to having caught this issue before the addresses were added to the site. I don’t want to remove all other relevant content just to import again and fix these zip codes, so I’m going to go directly to the database to solve the problem.

How to fix the missing zeroes

There are several ways to add the zeroes back, but most places that you search will suggest changing the datatype of the zip code column, which doesn’t help when it’s in WordPress where we can’t modify that when there is other info stored in the same place. Plus some zip codes have the full nine digit route number depending on where the data was taken from, and some are postal codes from Canada and other countries that don’t follow the same pattern.

In this particular case, we know what we’re looking for (postmeta with a key of wpsl_zip, and we know where it’s at (the wp_postmeta table). If you connect to the MySQL database through PHPMyAdmin or an external application you can run the following query to see how many zip codes stored have fewer than five digits:

Important: Always make a backup of your database before doing any of the changes below!

SELECT
    *
FROM
    `wp_postmeta`
WHERE
    `meta_key` = 'wpsl_zip' AND LENGTH(`meta_value`) < 5

What we’ve told the database to do, is to “select all rows from the wp_postmeta table that have a meta_key of ‘wpsl_zip’, and that have a meta_value of less than five characters in length”.

It’s important to ignore rows that already have a value of five or more characters, as LPAD will trim them to fit five characters otherwise. We don’t want that, just the ones that are too short.

The above will return all of the rows that match the query, so that we can review them and confirm that they are indeed the addresses that we want to update.

Now that we’ve identified how many there are (89 in this case), the following MySQL command will update those zipcodes using LPAD to add a left padding of 0’s until the meta_value is five characters. Values that are already five characters or larger are ignored.

UPDATE
    `wp_postmeta`
SET
    `meta_value` = LPAD(`meta_value`, 5, 0)
WHERE
    `meta_key` = 'wpsl_zip' AND LENGTH(`meta_value`) < 5

You’ll see that the WHERE clause is the same, since we already confirmed that we had the right records to change before. What we’ve done differently with this query is to say that we want to make updates to the wp_postmeta table by setting the meta_value of the rows that we selected to have exactly five characters, and that if they have fewer than five characters, to left pad them with 0’s.

Summary

To review, the MySQL function LPAD works like this:

LPAD(
    "cell that we want to change",
    "final cell string length",
    "what to use to left pad the cell if needed"
)

I hope that helps save you spending the same time that it took me to find the problem that I had and to come up with a solution!

I’m happy to announce the release of David v3.1.0!

As with any minor version update, some new, backwards-compatible features have been included since the last minor release. Among these are:

New features are constantly being added, and original developments refined. The timeline for release this year looks promising. I’ll keep you up to date when further information is available.

Thank you all for making the latest release possible, and for your support through the ups and downs of prior release schedules!

I know that I made a commitment earlier this year to post code tips and resources most weekdays. I kind of indicated it there, but will make more clear that I intend to follow that commitment as best as I can, but know that some weeks I will just plain fall short.

This week and last have been two of those weeks already. I can use the excuse of having other projects that take precedence (I do), or that I’m writing in other places with more pressing deadlines (I am). But I did tell myself, and in that last post, all of you reading, that I would be posting more regularly. This week was also shot, and while I have plenty of half done posts, following through seems to be my issue.

Still, the other part of that commitment was that any progress on that goal was better than standing still. For some reason I find that when I take on larger challenges I end up getting more done than if I reduce the scope of those goals. I’ve heard that people who maintain more commitments are happier and more successful over all than people who don’t keep any. I often feel that I’d be liberated by dropping all commitments and starting fresh, but there’s a reason that I keep lists of “maybe someday” projects always nearby.

I can’t say that I’m more motivated now, or that I’m not concerned that this won’t happen again. I can say that though I’ve got a lot of self-imposed deadlines on my plate, I’m glad to be able to make time for this in the first place.

I’ll see you next week!

While there’s been a lot written about the new editing experience that came out with WordPress v5.0 last month, I want to give a reminder of some of the neat features for end users. One of the best things about the new editor is that a theme or plugin can add or remove features from the editor with simple hooks, allowing you to craft an experience that fits your needs.

As an example, I have taken a few client sites that have embraced the new editor, and used their style guides to add their branding colors, fonts, and variants into the page editor. Now, when they want to add a block of content with a colored background or change the color of a button on a page, they have their palette of brand-approved colors already set to use. No need to remember hex codes or anything confusing!

Sounds great! How do I set up a custom color palette?

Default WordPress Editor Color Palette
Notice that the editor will warn you if your background and text colors aren’t high contrast. This makes it a bit easier to keep your content accessible!

By default the editor will have a palette of 11 colors, plus a color picker to get a different color. You can swap to a palette of your own by adding some code to your theme. Place the following in your functions.php file or where appropriate based on your structure. Next, we’ll modify it to fit our needs.

This code came directly from the Gutenberg Theme Support Handbook, a good resource for all WordPress developers.

function mytheme_setup_theme_supported_features() {
    add_theme_support( 'editor-color-palette', array(
        array(
            'name' => __( 'strong magenta', 'themeLangDomain' ),
            'slug' => 'strong-magenta',
            'color' => '#a156b4',
        ),
        array(
            'name' => __( 'light grayish magenta', 'themeLangDomain' ),
            'slug' => 'light-grayish-magenta',
            'color' => '#d0a5db',
        ),
        array(
            'name' => __( 'very light gray', 'themeLangDomain' ),
            'slug' => 'very-light-gray',
            'color' => '#eee',
        ),
        array(
            'name' => __( 'very dark gray', 'themeLangDomain' ),
            'slug' => 'very-dark-gray',
            'color' => '#444',
        ),
    ) );
}

add_action( 'after_setup_theme', 'mytheme_setup_theme_supported_features' );

There’s a lot of code there, but not a lot to break down. First, remember that after_setup_theme is a hook, on which you add the function mytheme_setup_theme_supported_features that you’re creating. In that function we’re using add_theme_support, a built in WordPress function, where we’re using editor-color-palette to set our palette up.

We’re adding an array of colors, and each element of that array is itself an array. Within those nested arrays we have the name of the color, which we’re making translatable with the __() function, and setting the textdomain of our theme. Change themeLangDomain to whatever matches your theme. This name is a descriptor for when you hover over it in the palette.

The slug is a string of how you’ll refer to the color elsewhere in your code. The color is the hexadecimal value of the color that you want in your palette. With the above code, you’ve got a new editor palette with four colors that you’ve set, along with the color picker.

Our custom WordPress editor color palette
Our four custom colors now appear, along with the color picker

Adding to Our Palette

There are a few more features of the editor color palette that I’d like to show off, including targeting blocks in CSS, Customizer set colors, and removing the color picker.

Using our Color Palette Selections in CSS

If you’re editing text with the color palette you shouldn’t have to make any other changes. But what if you want to use the color selection in something a bit more customized, or in your own block type?

The slug that we added to our colors in the example above lets us target for both background and text colors. We don’t even need to use the color set in the editor, but something custom to our needs. For example, you may want a specific background or text color when you use the strong magenta color. In that case, here’s the CSS that can target the classes added when we use that color:

.has-strong-magenta-background-color {
    background-color: #313131;
}

.has-strong-magenta-color {
    color: #f78da7;
}

Setting a Color Palette with the Customizer

The twentynineteen theme that comes with WordPress has a custom palette that includes colors that can be set in the Customizer. This means that you can set your own primary and secondary color from the WordPress dashboard, without changing code!

array(
	'name'  => __( 'Primary', 'twentynineteen' ),
	'slug'  => 'primary',
	'color' => twentynineteen_hsl_hex( 'default' === get_theme_mod( 'primary_color' ) ? 199 : get_theme_mod( 'primary_color_hue', 199 ), 100, 33 ),
),
array(
	'name'  => __( 'Secondary', 'twentynineteen' ),
	'slug'  => 'secondary',
	'color' => twentynineteen_hsl_hex( 'default' === get_theme_mod( 'primary_color' ) ? 199 : get_theme_mod( 'primary_color_hue', 199 ), 100, 23 ),
),

The new color is now set as the output of a function that will get a theme mod, if you’ve modified the color. If not, it’ll return the default, ensuring that there’s always a color set.

The WordPress customizer with a primary color selection

Removing the Color Picker

You can also do things like disable the color picker, to ensure that users can only use the colors that you have preset for them. Doing so requires just one line of code in your functions file:

add_theme_support( 'disable-custom-colors' );

With that single line we’ve made it so the beautiful design that we’ve worked so hard to craft and the branding style guide that we have had to constantly review will always be set the way that we want.

Wrapping Up

As you can see, there’s a lot that you can do to change how users edit content in the Gutenberg editor, without having to add a tremendous amount of code.

This is only the beginning, and even more developer and user friendly features like this already exist or are coming to the editor and the rest of WordPress. I’m excited for the new opportunities this gives to all stakeholders of a site, from designers and developers, to admins and editors, all the way to customers and visitors. Let’s keep making WordPress better for everyone!

I’ve mentioned before on the blog that I use Alfred App for OSX and love it. The app helps me do a lot more things quicker, without having to leave the keyboard.

I also use the Alfred Powerpack, which is currently £39.00 for lifetime updates. In US Dollars, that’s $50, which I was quickly able to determine with a currency exchange workflow 😉

Converting currency from GBP to USD
Lots of quick things can be done via Alfred!

The Powerpack includes quite a few extra features, but I make regular use of clipboard history, snippets, auto-expansion, and running shell commands, as well as styling it with a theme and backing up all of my settings

Using Alfred with Homebrew

I also use Homebrew, which is a package manager for OSX. Basically, it’s a way to install and update applications for your mac via the command line. Since I’ve already written some posts about it in the past (as well as how to create the workflow that I’m discussing today), I’m going to refer you back to those posts instead of reiterating them.

Why write this post again?

I have always gone to the terminal, used brew search (and the now deprecated brew cask search to look for applications that I wanted to install. But this meant opening terminal if it wasn’t already, typing the name that I hoped was there, and seeing what came up while guessing if it was the right app when installing.

I recently discovered an Alfred workflow meant specifically for Homebrew tasks, which allows you to do all of the normal Homebrew commands, including searching packages. You can find Homebrew and Cask for Alfred on Github, and it’s already wrapped up as a workflow to install.

I can still use my existing homebrew workflow to update all existing packages that I’ve installed, as well as cleanup when done. Thanks to some updates since the last post about this, there are even fewer tasks to run.

But now I also have access to the normal homebrew tasks, including install, uninstall, search, update, and all of the flags and various commands for them. Even better, when you search for a formula it includes a link to the Github page for it, meaning I can see what that package actually does and not have to guess. Again, this is without ever having to leave the keyboard.

Alfred Homebrew search
Searching the word lint will give all formulas with lint in the name

Having tools like this allows me to work faster and waste less time on managing applications, as well as keeping them all up to date easier. I already procrastinate enough, and I don’t need searching for apps or waiting for them to update when I open them to help me waste even more time!