WordPress.org

1. DrLightman
   Member
   Posted 2 years ago #

   Hello,

   I'm using a template page as a frontpage. In that template page I want to loop items from a table containing a lot of items, too many to be managed by wp posts so I decided to put into a separate table. I want to be able to paginate with /page/x like WP does.

   I've setup frontpage succesfully through Settings>Reading and I can see the first 5 items, but if I navigate to /page/2, I get 404.

   The code on top of the template page is the classic pagination code:

   global $wpdb;
   $paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
   $offset = ($paged-1)*5;
   $rows = $wpdb->get_results("
   	select *
   	from {$wpdb->prefix}companies
   	limit $offset,5
   ");

   How can I get pagination working on a custom frontpage that is not the classic post loop?

   Thank you.

2. vtxyzzy
   Member
   Posted 2 years ago #

   I don't think you need to use anything other than a normal query_posts, because WP will insert the limit into a paged query. Check it out by doing a print_r($wp_query->request); after your query_posts().

3. DrLightman
   Member
   Posted 2 years ago #

   Hi, as I described before, I'm not querying posts (from wp_posts) but companies from a custom table "companies", so I'm using the generic $wpdb->get_results("select...") method.

   But I think the problem is that the frontpage cannot be paginated if set to a static page.

4. DrLightman
   Member
   Posted 2 years ago #

   You may follow these steps to replicate the "bug"/"misbehaviour":

   1. create a page "foo"
   2. settings>reading, Front page displays as "A static page" and select "foo".
   3. permalinks settings I use the typical "/%year%/%monthnum%/%day%/%postname%/"
   4. go to yourdomain/ and u see the front page as static page (OK)
   5. go to yourdomain/page/2/ and u should see a 404 (BUG?)

5. blondishnet
   Member
   Posted 2 years ago #

   What what are you trying to call in your custom query... a certain category?

   Did You select what page your blog would be on... you have to have a page for that too.

6. DrLightman
   Member
   Posted 2 years ago #

   What what are you trying to call in your custom query... a certain category?

   $wpdb->get_results("select * from {$wpdb->prefix}companies limit $offset,5");

   No WP stuff at all, only a listing of rows from a table with a lots of entries.

   Did You select what page your blog would be on... you have to have a page for that too.

   I didn't at first, because I don't know yet if I'm gonna write posts for this particular blog. But I added just to test it and I keep getting 404.

   I looked into wp_options and the rewrite rule seems to be in effect:

   [page/?([0-9]{1,})/?$] => index.php?&paged=$matches[1]

   404 keeps popping.

   If I cannot find a wp solution I have to install the "redirection plugin" and set up a pass-thourgh for a custom query var for pagination (/browse/x/ -> /)

7. vtxyzzy
   Member
   Posted 2 years ago #

   You can paginate any array by using your own pagination code. See the example here in the pastebin.

8. DrLightman
   Member
   Posted 2 years ago #

   I know I know, but it's better doing it using MySQL "select ... limit offset,count" clause to fetch only the required amount of rows from the database, while the total rows be over 3 millions in my case and it's not praticable.

   BUT, the problem is that I cannot pass the page number information from page to page because /page/<x>/ links don't work, they give 404.

9. vtxyzzy
   Member
   Posted 2 years ago #

   It is not possible to paginate unless you know the total number of entries that would be retrieved if the limit were not in effect.

   So, in order to paginate, you will have to use SQL_CALC_FOUND_ROWS, or run a second query to count the rows. If you have an index on the companies table, it will be much faster to use a second query like this (assuming company_id is indexed):

   "SELECT count(company_id) FROM {$wpdb->prefix}companies"

   Now you have all you need to paginate the query.

10. DrLightman
    Member
    Posted 2 years ago #

    I really appreciate your efforts in order to help me, but how pagination is done at low PHP level is not my issue, I'm pretty familiar with it, I always use the SQL_CALC_FOUND_ROWS thing by the way :)

    Regarding this topic, I'll explain again, the issue is that in a WordPress install with the front page set to a static page:

    http://example.org/

    The above WORKS, page number 1 is assumed

    http://example.org/page/2/
    http://example.org/page/3/
    ...
    http://example.org/page/x/ (x being a number)

    The urls above DOES NOT WORK, they all give "404 page not found"

11. Mark / t31os
    Moderator
    Posted 2 years ago #

    I think that's down to the intended use of the Front Page setting, ordinarily you'd have a list of posts on the main page, when you set the Front Page, you're basically saying show this instead.

    I don't believe it was ever intended for the Front Page to be paged, ordinarily it would be a list of posts, with paging (now it's obviously a single page, the one you selected as the front page).

    In theory it should work, but i'm sure i vaguely remember this question cropping up before without solution. I'd guess this page also has a page template attached, with the code shown above included right?

    : Thinking out loud :

    In terms of example.com/page/2 , WP will be looking at the main query for results, if you're writing your own query, then the main query doesn't have a result set for page 2(the main query will likely contain a single result - the page you set as a front page).

    In such a case, as has been suggested already, you may need to create your own paging code.

    Also example.com/page/2 in terms of WP's rewrite rules will map to something like.. index.php?p=X&paged=X or index.php?page_id=X&paged=X, again this ties in with WordPress's main query (not your custom one)..

12. vtxyzzy
    Member
    Posted 2 years ago #

    You know what? The module I posted WILL work because it does not use the example.com/page/2 form of pagination. Rather it uses a special URL variable 'mypage' to pass the page number. I developed the module to get around just precisely the problem that you are having - pagination of a static home page with pretty permalinks.

    You should time a query using SQL_CALC_FOUND_ROWS versus two queries where one uses a LIMIT and another which counts an indexed field. The calc_found has to retrieve all rows from the table while the count only has to read the index. Since the index records are usually much smaller than the table records, you can dramatically cut the time.

    Of course, you can't use the count method if you have a join or where condition that will result in some rows not being selected.

13. DrLightman
    Member
    Posted 2 years ago #

    The pagination code is not big issue, if /page/x/ would have been recognized by WP on the front-static-page (with the own template, yes), I had already set up the code, because I assumed it would work:

    <?php
    /*
    Template Name: companies listing
    */
    get_header();
    global $wpdb;
    $paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
    $offset = ($paged-1)*5;
    $rows = $wpdb->get_results("
    	select *
    	from {$wpdb->prefix}companies
    	limit $offset,5
    ");
    ...

    (regarding the total number of items of the table, I could even hardcode it because the table will not be modified by any backend)

    Since the above doesn't work, I'm on the way of RESOLVING by using the awesome redirection plugin. After installing I create this redirection:

    http://img375.imageshack.us/img375/2557/redirpagefrontpage.jpg

    /page/([0-9]+)/? -> /?mypage=x

    So I can read myself the number in the frontpage template page:

    $mypage = isset($_GET['mypage']) ? $_GET['mypage'] : 1;

    The rest of the code will be similar to the above at the beginning of the post.

14. vtxyzzy
    Member
    Posted 2 years ago #

    Please post more of the code when you get it working. I am really curious to see how pagination works without knowing the total number of rows.

15. DrLightman
    Member
    Posted 2 years ago #

    The total number of rows is always required, where did I tell otherwise?

    In my case, I think I'm going to hardcode it in a variable in the template, because the table is pretty much static, I'd have to update it every six months or more through ssh or phpmyadmin.

    $rows_count = 3171303;

    But you can use the SQL_CALC_FOUND_ROWS clause in the sql in the generic case when the data is dynamic, but it's pretty much off topic to the issue I described here :)

16. vtxyzzy
    Member
    Posted 2 years ago #

    Sorry, because you didn't mention the number of rows, I assumed (incorrectly!) that you did not have it. My apologies.

17. DrLightman
    Member
    Posted 2 years ago #

    erhm, you also are right, my code was quite uncomplete :)

Topic Closed

This topic has been closed to new replies.