WordPress List Posts by First Title Letter

first-title-letter-query.php

<?php
global $wpdb;

$first_char = esc_attr($_GET[$search_key]);

$postids = $wpdb->get_col($wpdb->prepare("
	SELECT      ID
	FROM        $wpdb->posts
	WHERE       SUBSTR($wpdb->posts.post_title,1,1) = %s
	AND 		$wpdb->posts.post_type = 'product'
	ORDER BY    $wpdb->posts.post_title",$first_char));

if ( $postids ) {
	$args = array(
		'post__in' => $postids,
		'post_type' => 'product',
		'post_status' => 'publish',
		'posts_per_page' => -1,
		'caller_get_posts'=> 1
		);

	$my_query = null;
	$my_query = new WP_Query($args);
	if( $my_query->have_posts() ) {
		echo '<p>List of Posts Titles beginning with the letter <strong>'. $first_char . '<strong></p>';
		$counter = 1;
		while ($my_query->have_posts()) : $my_query->the_post(); ?>
		<p>
			<span><?php echo $counter++; ?></span>
			<a href="<?php the_permalink() ?>" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a>
		</p>
		<?php endwhile;
	}

	wp_reset_query();
}
?>

Hello,
Great solution! I am trying to add 0-9 but can't get it to work. Any ideas?

Thomas

for number change %s to %d

Good snippet but I had to exclude drafts and other unwanted IDs from $postsids query:
I added AND $wpdb->posts.post_status = 'publish'

How is possible use also Y letter?

How efficient is this query anybody tested the time it takes? Please mention number of posts too.

Use paginated query if you have many posts. @Salamander3

I ran across this yesterday looking to solve an identical problem. I thought I would add my solution to the conversation since it uses pieces of the snippet above. The method I ended up with uses the posts_where filter.

add_filter('posts_where', 'first_character_posts_where' );

function first_character_posts_where($where) {
global $wpdb;

//Take in a URL parameter
    $alpha_filter = sanitize_text_field( $_GET['alpha'] );
	
// throw a different regex where at the database depending on if we want numbers or a specific character
if( $alpha_filter !== false && $alpha_filter == 'number' ) {
	$where .= $wpdb->prepare( " $wpdb->posts.post_title REGEXP %s ", '^[0-9]' );
} elseif( $this->alpha_filter !== false ) {
	$where .= $wpdb->prepare( " LOWER($wpdb->posts.post_title) REGEXP %s",'^'.$alpha_filter );
}

return $where;

}

https://bitbucket.org/snippets/jeremiahbratton/Le44KG

This code solve my life, Thank you very match!