Filtering a Query by custom field first letter - Wordpress

Question

I'm working on a directory staff listing. I went to a custom solution because plugins for this purpose were too complex or too simple for what I was looking for. So I created this custom post type "staff" with custom taxonomies attached as well to it(Science, Mathematics, Administration, etc) so I have a query where I get all those posts listed in a table, my code looks as follows.

<table class="directorytable">
   <thead>
      <th class="first-th">Portrait</th>
      <th>Name</th>
      <th>Position</th>
      <th>Phone</th>
      <th style="width:60px !important;text-align:center;">Email&nbsp;&nbsp;&nbsp;&nbsp;</th>
      <th style="width:60px !important;text-align:center;">Website&nbsp;&nbsp;</th>
      <th style="width:60px !important;text-align:center;">Details&nbsp;&nbsp;</th>  
   </thead>
<?php query_posts(array('post_type'=>'staff','orderby'=>'meta_value','meta_key'=>'staff_last_name','order'=>'asc'))?>
<?php while(have_posts()): the_post();?>

<tr>
    <td class="first-td"><a rel="lightbox" href="<?php the_permalink(); ?>"/><img src="<?php the_field('staff_portrait');?>" style="border-radius:2px; width:35px;"/></a></td>
<td><a rel="lightbox[1]" href="<?php the_permalink();?>"><?php the_field('staff_name');?> <?php the_field('staff_last_name');?></a> </td>
<td><?php the_field('staff_position'); ?></td>
<td><?php the_field('staff_phone');?> | <strong>Ext: </strong><?php the_field('staff_extension')?></td>
<td style="width:60px; text-align:center;"><a href="mailto:<?php the_field('staff_email');?>"><img src="/images/directory/mail.png"/></a></td>
<td style="width:60px; text-align:center;"><?php if(get_post_meta($post->ID,'staff_website', true)){?><a target="_blank" href="<?php the_field('staff_website');?>"><img src="/images/directory/document-globe.png"/></a><?php } else {?><?php echo '';?><?php } ?></td>
<td style="width:60px; text-align:center;"><a rel="lightbox[2]" href="<?php the_permalink();?>"><img src="/images/directory/magnifier-zoom.png"/></a></td>
</tr>   
<?php endwhile; ?>
</table>

So this list is working very nicely, but my client requested me to add an alphabet index. So I will need a range a-z listed before results and then on clicking, show only the posts that their "staff_last_name" custom field (I'm using ACF) begins with the chosen letter.

I have tried with some plugins like AZIndex, WP-Snap, etc, but none of those worked for me.

I'd appreciate any recommendation / solution for this matter.

Answer 1

I assume you have the initial you're searching for (i.e. you're generating the link OK, then retrieving the value passed, say via $_GET or by adding a query_var as in this example).

I also assume you've checked it has a valid value (e.g. is a single, alphabetic character).

Assuming the value is stored in a variable called $initial, changing your query to something like

<?php query_posts (
        array(
            'post_type'=> 'staff',
            'orderby'  => 'meta_value',
            'meta_key' => 'staff_last_name',
            'order'    => 'asc',
            'meta_query' => array(
                array(
                    'key' => 'staff_last_name',
                    'value' => $initial,
                    'compare' => 'LIKE'
                )
            )
        )
    );
?>

will generate most of what you need (see the WordPress codex for more details), though unfortunately it'll find $initial anywhere in the last name. That may not matter if the query's case sensitive, and only the first character (and $initial) are upper case.

If it doesn't work as you need, I can think of three options:

1. Use "BETWEEN" instead of "LIKE", and pass a value of the letter you're searching for and the one after (incremented by ASCII value) e.g. if you're searching for names starting 'A', pass 'A,B',
2. Add a get_meta_sql filter with the query above, and replace "'%" in the argument with "'". That could have unexpected side-effects if you have any other LIKE conditions in your query, or
3. Add a posts_where filter to add the required SQL yourself.