Fix Missing Leading Zeroes in WordPress Zip Codes

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!

Setting up a Custom Palette in Gutenberg

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!

Powering Up Homebrew on Mac with Alfred

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!

Fixing Style Issues While Editing Beaver Builder

I’ve started using Beaver Builder with a few clients after having played with it a bit and hearing lots of great reviews. I’ve looked into multiple WordPress Page Builders, and have had experience with quite a few of them through my work offering WordPress maintenance service.

I’ve found that Beaver Builder is able to handle a lot of the customizations that my clients may want to make, but there are still a few things that I have to setup externally to get a feature that they want. As an example, a client wanted to use the callout module to make an entire box clickable, not just a button after text and images.

Doing the above was fairly straightforward for this use-case: I set the entire callout link to be relatively positioned in CSS, so that I could absolutely position the anchor tag within the link to be the full height and width of that box. Finally, I added a hover and focus state to the button so that when hovering with the mouse or focusing with the keyboard there would be a visual indication that it was clickable, besides the cursor icon that was already set.

.callout-link {
	position: relative;
}

.callout-link a {
    position: absolute;
    width: 100%;
    height: 100%;
    top: 0;
    left: 0;
}

.fl-callout-button a:hover,
.fl-callout-button a:focus {
	box-shadow: 1px 1px 3px 0 #315E7D;
}

So what’s the issue?

That looked like a simple solution to the problem that we had, but like many bits of code, I inadvertently created a new issue.

Beaver Builder is a front-end content editor, which means that it uses the same HTML and CSS structure to display the content while editing. While this is normally a good thing, it means that you need to pay attention to custom code that you’ve added to modify Beaver Builder.

Since I changed the layout of links in the callout module, I changed the layout of links for the editor of that module. Additionally, I’d styled unordered list bullets with pseudo-elements, which also caused a display issue. This is what the editor looked like when I tried to modify those links:

Broken Beaver Builder editor CSS
This is what happens when you let me touch code!

After I determined that I was the cause of the issue, I set about to fix it. Thankfully, Beaver Builder adds several body classes while the page editor is open, including the class fl-builder-edit which I used to fix this particular issue. I hid the li::before pseudo-elements, and restored the link anchor to relative positioning.

/* Beaver Builder Editor Fixes */
.fl-builder-edit .entry-content ul li::before,
.fl-builder-edit .fl-builder-content ul li::before {
	display: none;
}

.fl-builder-edit .callout-link a {
    position: relative;
}

With that code in place, the editor layout looked as it should before I mangled it.

Fixed Beaver Builder editor settings
That’s a lot better and actually usable!

Check for unintended consequences of your code.

This broken CSS wasn’t a major problem, and was thankfully easy to fix. But it did bring up a good reminder: when you make one change to your code, you may change something else that you didn’t mean to. It’s always good to review every time that you make a change. Having some version control in place that you use regularly doesn’t hurt either!

Display a Notice for New WordPress Posts

If you’re like me, it might not always be easy to get new posts out to your blog. I’m trying to keep a new tech-tip going every regular weekday for a while to see how I keep up with that.

Since my content might not always be the newest, I may want to highlight when something was recently published.

Calculate posts published in the last two weeks

In the following example, I’m going to check to see if a post was published within the past two weeks. If so, I’m going to attach a notice to the title of the post. I’m assuming that the following code is going to go into a loop of posts, or somewhere that we’re already using the correct post ID.

$post_title = get_the_title();
if ( get_the_date( 'U' ) >= date( 'U', strtotime( '-2 weeks' ) ) ) {
    $post_title .= ' — New Post!';
}
echo $post_title;

First, on line one, we’re creating a variable in PHP called $post_title. This will hold the title of the post, which we get with the built-in WordPress function get_the_title(). Again, I’m assuming that we’re already in a loop for a specific post, but if not you can pass the ID of the post as an argument in that function.

Next, line two is going to get the date that the post was published in Unix Timestamp format. I’ve put it into that format to make it easy to compare. I am grabbing the date instead of the exact time since it doesn’t really matter to me if it was exactly within two weeks down to the second, just generally two weeks by day count.

The post publish date is compared to the current time minus two weeks, also in Unix Timestamp format. The PHP function strtotime() allows you to use human readable formats for time conversions, which we’re using to say “give me the time in Unix seconds for two weeks ago”.

If that comparison is true and the post was published less than two weeks ago, we’re going to append the text ” — New Post!” to the post title. By using a period followed by the equals sign, we’re saying that we want to concatenate, or add the new value to the existing variable.

Finally, on line five we’re echoing out the value of $post_title, meaning we’re printing it to the screen. So if I were to use the above code to display titles for this site and this post was published less than two weeks ago, the title would display as Display a Notice for New WordPress Posts — New Post!

How else could this be used?

One way that I use this code is for a custom post type that displays properties for sale for a client. They wanted to highlight some recent listings, and using this code along with some CSS let me put a fancy ribbon on the corner of property listings, as well as list the number of days that the home has been on the market.

Property Listing with new listing notice and number of days on the market

If you have the need to calculate WordPress post publish date compared to the current date, I hope the above snippet has been a good place to start!