Pagination of custom page with custom fields query

Question

Hello I have created a custom php file that I use to show specific posts by value inside the a custom field. So for example I have 100 posts, let's say they all have custom field MyTitle and 10 of them have value "New".

$querystr = "
            SELECT $wpdb->posts.* 
            FROM $wpdb->posts, $wpdb->postmeta
            WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id 
            AND $wpdb->postmeta.meta_key = 'Mytitle' 
            AND $wpdb->postmeta.meta_value LIKE '%".$mytitle."%' 
            AND $wpdb->posts.post_status = 'publish' 
            AND $wpdb->posts.post_type = 'post'
            AND $wpdb->posts.post_date < NOW()
            ORDER BY $wpdb->posts.post_date DESC
         ";

         $pageposts = $wpdb->get_results($querystr, OBJECT);

So I use the code to display those posts, however I wanted to add a pagination.I've done some search here and on the internet, but all the pagination is rather different talking about getting "paged" from the main query.. I don't really understand that part. So can you show me examples which I can use with the code that I've shown here?

Answer 1

I will start with this, never pass raw variables to a sql query! unless you have 100% control over the variable content, and even then I would still not pass it raw.
Always use prepare().

Now going by your need you will need to do something like this

<?php
$paged = (get_query_var( 'paged')) ? get_query_var('paged') : 1;

$posts = new WP_Query([
    'post_type'      => 'post',
    'posts_per_page' => 1,
    'post_status'    => 'publish',
    'meta_key'       => 'Mytitle',
    'meta_value'     => $mytitle,
    'meta_compare'   => 'LIKE',
    'paged'          => $paged
]);
?>

<ul>

<?php if ($posts->have_posts()) : while ($posts->have_posts()) : $posts->the_post(); ?>
<li><?php the_title(); ?></li>
<?php
endwhile; endif;

wp_reset_postdata();
?>

</ul>

<div class="pagination">
    <?php 
        echo paginate_links([
            'base'         => str_replace(999999999, '%#%', esc_url(get_pagenum_link(999999999))),
            'total'        => $posts->max_num_pages,
            'current'      => max( 1, get_query_var('paged')),
            'format'       => '?paged=%#%',
            'show_all'     => false,
            'type'         => 'plain',
            'end_size'     => 2,
            'mid_size'     => 1,
            'prev_next'    => true,
            'prev_text'    => sprintf('<i></i> %1$s', __('Newer Posts', 'text-domain')),
            'next_text'    => sprintf('%1$s <i></i>', __('Older Posts', 'text-domain')),
            'add_args'     => false,
            'add_fragment' => '',
        ]);
    ?>
</div>

This is a very raw example so you will need to do changes based on your needs.

Some resources to get a better idea on how everything works.

1. WP_Query class
2. Looping WP_Query
3. Pagination with WP_Query WPSE
4. Wordpress pagination codex