how to page custom database query (to sort by custom field)?

You need to add a LIMIT to the SQL and increment that with an offset that you pass between pages.

Say $page=1,2,3, etc…
$posts_per_page = 10;
$offset = ($page-1) * $posts_per_page
Now do your sql but add “LIMIT $offset, $posts_per_page” to the query.

Thanks for the guidance, Otto.

It’s what I thought might be required. I’ll tackle it when I have more time and report back with any success.

One question right away, though: How to pass the page information? I.e., how to tell each query which page number it’s on?

Another challenge I see is that I’d want to get the number of posts so I know when I’m on the last page, but that means a query has to be done once to get the number of posts and then again with the offset and posts_per_page. So it would seem a better choice to offset and limit the While loop perhaps (which I’m not sure how to do: can each $post be called by $pageposts[n]?)?

This is working:

<?php
$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
$postsperpage = 30;
$pageposts = $wpdb->get_results("SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id
AND wpostmeta.meta_key = 'Author'
AND wposts.post_status = 'publish'
ORDER BY wpostmeta.meta_value ASC", OBJECT);

$numposts = count($pageposts);
$numpages = $numposts/$postsperpage;
if ($numposts%$postsperpage) $numpages=$numpages+1; ?>

    (navigation stuff)

<?php
if ($pageposts):
$i=$postsperpage*($paged-1);
while ($i<$postsperpage*$paged):
$post=$pageposts[$i];
setup_postdata($post); ?>

    (loop stuff)

<?php
$i++;
endwhile; endif; ?>

https://www.wind-watch.org/documents/authors/

I’ve run into a problem farther down the page, now.

The following is an attempt to create prev/next navigation right after the end of the loop above.

<?php
$precpage=$paged-1;
$nextpage=$paged+1;
if ($paged>1) {
if ($precpage==1)
	{ echo ('<a href="../../">« Home</a>'); }
	else
	{ echo ('<a href="../' . $precpage . '/">« Preceding Page</a> • <a href="../../">Home</a>'); }
}
if ($paged<$numpages)
	{ if ($paged==1)
		{ echo (' • <a href="./page/'); }
		else
		{ echo (' • <a href="../'); }
	echo ($nextpage . '/">Next Page »</a>'); }
?>

The “home” link is provided only if it’s not page 1, and the “preceding” link only if it’s page 3 or more. The “next” link is provided as long as the current page is less than the total number of pages (as determined earlier).

That “next” link isn’t working, though, because the value for $numpages has apparently changed to 1. (If I remove the “if($paged<$numpages)” condition, the link is created — but the last page would then point to a nonexisting page.)

What happened to $numpages?

What happened to $numpages?

Are you doing this in the theme itself, or are you using some kind of plugin like ExecPHP or something? Different files in the theme?

Different bits of code can have different scope, I need to know where you are placing this code to explain the scope to you properly.

Also, your main code is an incredibly bad idea. You’re basically pulling every single post from the database, then only displaying a small fraction of them. You really, really should use a LIMIT in your selection instead. It will be massively faster, and your code will be much simpler.

Example:

<?php
$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
$postsperpage = 30;
$offset = ($paged-1) * $postsperpage
$pageposts = $wpdb->get_results("SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE ... blah ...
LIMIT $offset, $posts_per_page ", OBJECT);
...
foreach ($posts as $post) :
setup_postdata($post); ?>
    (loop stuff)
<?php endforeach; ?>

Thanks for the feedback.

All of the code I’ve shown is in the Page template. Other variables, but not $numpages, seem to be usable throughout the template script (in fact, for now, I’m just recalculating $numpages at the bottom of the page from those other variables).

The question I had about using LIMIT in the query is knowing when I’m on the last page, which would usually be when the number of posts is less than the $postsperpage — but what if it’s equal to that number but it’s the last page? So I’d still want to do a query to find out how many posts there are in total. And I’d want to be able to know how many pages there are in total so I can have a navigation link for each page on all of the pages.

I’ll re-do this with an initial query to count the number of posts with “Author” metadata, which I guess would be much faster each time than getting all of the posts.

(By the way, this is not a blog with tens of thousands of posts, but more a CMS-type reference library — after more than 2 years, there are only just over 360 posts.)

I’m having trouble getting the result of this query via the $wpdb query:

SELECT COUNT(*)
FROM wp_postmeta, wp_posts
WHERE wp_postmeta.meta_key = 'Author'
AND wp_posts.ID = wp_postmeta.post_id
AND wp_posts.post_status = 'publish'

That works as expected in MySQL (returning the count of published posts with the “Author” custom field), but the following returns the string “Array” in the Page template:

$numpostss = $wpdb->get_results("SELECT COUNT(*)
FROM $wpdb->postmeta wpostmeta, $wpdb->posts wposts
WHERE wpostmeta.meta_key = 'Author'
AND wposts.ID = wpostmeta.post_id
AND wposts.post_status = 'publish'", ARRAY_N);
echo $numposts = $numpostss[0];

Never mind: It needed to go one down one more level:

$numposts = $numpostss[0][0];

OK — here’s the new code in the custom Page template:

<?php
$numpostss = $wpdb->get_results("SELECT COUNT(*)
FROM $wpdb->postmeta wpostmeta, $wpdb->posts wposts
WHERE wpostmeta.meta_key = 'Author'
AND wposts.ID = wpostmeta.post_id
AND wposts.post_status = 'publish'", ARRAY_N);

$numposts = $numpostss[0][0];
$postsperpage = 30;
$numpages = intval($numposts/$postsperpage);
if ($numposts%$postsperpage) { $numpages=$numpages+1; };
$lastpageposts = $numposts-(($numpages-1)*$postsperpage);
$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;

   ... navigation stuff ...

$offset = ($paged-1) * $postsperpage;
if ($paged==$numpages) { $postsperpage=$lastpageposts; }

$posts = $wpdb->get_results("SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id
AND wpostmeta.meta_key = 'Author' AND
wposts.post_status = 'publish'
ORDER BY wpostmeta.meta_value ASC
LIMIT $offset, $postsperpage", OBJECT);

foreach ($posts as $post) :
setup_postdata($post); ?>

   ... loop stuff ...

endforeach;
?>

   ... navigation stuff ...

I still have to recreate $numpages (but not any of the other variables) for that second navigation section.

Thanks again for the help.

It turns out that $numpages is a WordPress-defined variable. I renamed my variable to $numofpages and it all works as expected.

Hah. Yes, I didn’t notice that one.

WordPress uses a lot of global variables like that. I highly recommend always prefixing your own variables. Instead of $numpages, something like $otto_numpages and $otto_posts and things like that.

Plugins should do this too, in general.

Good advice!

Please Ericr23, copy all code for view the example.

Tks! ??

What code other than what is posted above (remembering to change $numposts to $numofposts or, better, to $xx_numposts) do you want to see?