Unlimited Plugins, WordPress themes, videos & courses! Unlimited asset downloads! From $16.50/m
  1. Code
  2. WordPress

Create a Simple CRM in WordPress: Extending WordPress Search

Read Time: 6 mins
This post is part of a series called Create a Simple CRM in WordPress.
Create a Simple CRM in WordPress: Adding Columns to WP_List_Table
Create a Simple CRM in WordPress: Restricting / Hiding Menu Items

We’ve been looking at how to create a simple CRM system in WordPress. In the last part of this series, we added code to our plugin which allowed us to display our Advanced Custom Fields in the WordPress List Table (WP_List_Table), and sort our data alphabetically by those new fields.

Today we’ll cover how to extend the search functionality to include the data stored in our Custom Fields.

Search Functionality

Each Post Type that has an Administration Interface comes with a search box:

WordPress Search BoxWordPress Search BoxWordPress Search Box

By default, when a user searches for Post(s) in the WordPress Administration interface, WordPress will search the wp_posts table to find any partial content matches across the following fields:

  • Title
  • Content
  • Excerpt

If we try to search for a partial phone number, we'll see that no results appear:

Searching our Advanced Custom Fields Data doesnt work Searching our Advanced Custom Fields Data doesnt work Searching our Advanced Custom Fields Data doesnt work

For our Contact Custom Post Type, this isn't very useful if we want to find a contact by phone number or email address!

Advanced Custom Fields stores its data in the wp_postmeta table, which WordPress doesn't search by default. We need to use two of WordPress's provided filters to allow WordPress's search functionality to also search Advanced Custom Fields data. These filters will:

  1. perform an SQL JOIN between the WordPress Post Meta table and the WordPress Posts table
  2. append an SQL WHERE clause to the WordPress Post Query to search our WordPress Posts Meta table

Performing an SQL JOIN

Let's start by adding the posts_join filter to our plugin class's construct to join the WordPress:

We also need to define our search_meta_data_join() function, which tells WordPress which table we want to join to the main WordPress Posts table:

get_query_var() is a function which returns the appropriate query variable stored in the WP_Query class. WP_Query is a WordPress class which provides:

... information defining the current request... what type of query it's dealing with (possibly a category archive, dated archive, feed, or search), and fetches the requested posts. It retains a lot of information on the request, which can be pulled at a later date.

get_query_var() is the magic that lets us 'pull' that information. In this case, we check the query variable 's', telling us what search term (if any) the user has requested. We also use this same function to check which Post Type(s) the user is requesting—we only want to extend our search if the user is looking at the Contact Custom Post Type.

If these conditions are met, we join the wp_postmeta table to the main wp_posts table.

$wpdb is also used here, and it's a defined class which:

... contains a set of functions used to interact with a database. Its primary purpose is to provide an interface with the WordPress database, but can be used to communicate with any other appropriate database.

In short, $wpdb lets us access the MySQL database, get configuration settings and perform SQL queries.

In this case, we use $wpdb to get the names of the Post and Post Meta tables, because these can be modified by each WordPress installation. For example, one installation might set its table name prefix to wp_ (which is the default), whilst another installation might set it to my_awesome_site_. We can't hard code table names, as we can't guarantee they'll always be wp_posts and wp_postmeta, so we use $wpdb->posts and $wpdb->postmeta, which contain the actual table names specific to that WordPress installation.

Appending to the SQL WHERE Clause

With our SQL JOIN complete, we now need to tell WordPress to search the joined Post Meta table.

Go back to the plugin's __construct(), and add a new function to the posts_where filter:

We also need to define our search_meta_data_where() function, which tells WordPress to search our Post Meta data:

In the same way as we did in search_meta_data_join(), we again check that the WordPress Query is a search on the Contacts Custom Post Type. If it isn't, we return the $while clause without modification.

If we need to modify the $while clause, we do this by:

  • getting the start of the WHERE clause: ' AND(('
  • getting the remainder of the WHERE clause
  • injecting our WHERE clause to search the Post Meta table's meta_value column for any instance of our search term
  • adding an OR condition to the end of our WHERE clause, appending the rest of the query to it
  • grouping the results by the Post ID

We need to group the results because there will typically be more than one entry in the Post Meta table for a given Post ID. Because we set up a JOIN between the Posts and their Post Meta, if we didn't group the results, we'd get the same Post repeating in our table.

To check that our JOIN and WHERE clauses have worked, reload your Contacts Table, and try to search for one of your contacts by part of their phone number:

We can now search our Advanced Custom FieldsWe can now search our Advanced Custom FieldsWe can now search our Advanced Custom Fields

If it works, congratulations! You can now search by any Advanced Custom Fields you specify in your CRM system.

Up Next...

In the next article, we're going to restrict and hide WordPress Administration functionality and menu items that we don't need for our CRM.

Did you find this post useful?
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.