Pagination not working using a custom SQL query with a custom type

I've been struggling with this problem for 2 days now. I've read dozens of posts but can't get to the solution.

Note: all the var names are in spanish since this is a spanish website.

I've created a custom type named "promocion", but when listing the archives when I try to go to page 2 I get a 404 error.

The structure I'd like to set up is the following:

• domain.com/promocion/new-promocion -> this works well
• domain.com/promociones -> list of all the promociones, this works well too
• domain.com/promociones/page/2 -> Error 404 - Not Found
• Name of the archive file in my template: archive-promocion.php
• Name of the single page view in my template: single-promocion.php

WordPress version: 3.1

Plugins:

• wp-page-navi
• Posts 2 Posts plugin (http://wordpress.org/extend/plugins/posts-to-posts/), used to create a relation between posts and promociones.

Here's the custom type created in functions.php

 register_post_type('promocion', array(
    'label' => 'Promociones',
    'description' => 'Promociones',
    'public' => true,
    'show_ui' => true,
    'show_in_menu' => true,
    'capability_type' => 'post',
    'hierarchical' => false,
    'rewrite' => array开发者_Go百科('slug' => 'promocion'),
    'query_var' => true,
    'has_archive' => 'promociones',
    'menu_position' => 4,
    'supports' => array('title','editor',),'labels' => array (
      'name' => 'promociones',
      'singular_name' => 'promocion',
      'menu_name' => 'Promociones',
      'add_new' => 'Añadir nueva',
      'add_new_item' => 'Añadir nueva',
      'edit' => 'Editar',
      'edit_item' => 'Editar Promoción',
      'new_item' => 'Nueva Promoción',
      'view' => 'Ver Promoción',
      'view_item' => 'Ver Promoción',
      'search_items' => 'Buscar Promociones',
      'not_found' => 'No se encontraron promociones',
      'not_found_in_trash' => 'No se encontraron promociones en la papelera',
      'parent' => 'Parent Promoción',),) );


    function my_connection_types() {
    if ( !function_exists( 'p2p_register_connection_type' ) )
        return;

    p2p_register_connection_type( array(
        'from' => 'promocion',
        'to' => 'post',
        'reciprocal' => true
    ) );
}
add_action( 'init', 'my_connection_types', 100 );

And here's the beginning of my archive page (archive-promocion.php) where I do a custom SQL query and set the pagination:

   if ( $cat != '' ) {
        $cat_filter = 'wp_term_taxonomy.term_id =  "' . $cat . '" AND';
    } else {
        $cat_filter = '';
    }

 $querystr = '
    SELECT DISTINCT
        promociones.ID,
        promociones.post_title
        FROM
        wp_terms
        Inner Join wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id
        Inner Join wp_term_relationships AS wpr ON wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
        Inner Join wp_posts AS comercios ON comercios.ID = wpr.object_id
        Inner Join wp_p2p ON wp_p2p.p2p_to = comercios.ID
        Inner Join wp_posts AS promociones ON promociones.ID = wp_p2p.p2p_from
        WHERE
        wp_term_taxonomy.taxonomy =  "category" AND
        comercios.post_type =  "post" AND
        ' . $cat_filter . '
        promociones.post_type =  "promocion"
        ORDER BY
        promociones.menu_order ASC
 ';
    $totalposts = $wpdb->get_results($querystr, OBJECT);
    $ppp = 2;
    $wp_query->found_posts = count($totalposts);
    $wp_query->max_num_pages = ceil($wp_query->found_posts / $ppp);
    $on_page = intval(get_query_var('paged'));

    if($on_page == 0){ $on_page = 1; }
    $offset = ($on_page-1) * $ppp;
    $wp_query->request = $querystr . " LIMIT $ppp OFFSET $offset";
    $pageposts = $wpdb->get_results($wp_query->request, OBJECT);

.htaccess file

<IfModule mod_rewrite.c>
RewriteEngine On
RewriteBase /sitiodeloschicos/
RewriteRule ^index\.php$ - [L]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /sitiodeloschicos/index.php [L]
</IfModule>

Please help me, I'm going insane here and I'm already late with this project. Thanks!

Well, after 1 week, yes, 1 week of pulling my hair, reading every forum on the Internet and asking everybody, I found the solution, which is pretty dumb.

All the code posted above is working perfect, there's nothing wrong with it.

What was messing with the code is that I had setup in the admin panel to display 10 posts per page. In my code I changed it to display only 1 per page for testing purposes, but somehow wordpress was still using the 10 value configured in the admin panel.

So what I did is I changed the value to 1 in the admin panel, and then increased mine in the code. This is a nasty solution but it works.

Is there any way to setup the global value of posts per page by code?

My guess is that this is because you are using custom SQL to get your custom post type. By using the WP_Query class, you can perform the query in a more standard WP way. I would use this to get my custom posts:

$query = new WP_Query();
$query->query('post_type=promociones');
if($query->have_posts()){
    while($query->have_posts()){
        // Do your loop stuff
    }
}

If you perform your query as such, you will be able to use the built in WP functions for getting the next (http://codex.wordpress.org/Function_Reference/next_post_link) and previous posts (http://codex.wordpress.org/Template_Tags/previous_post_link). Additionally, you could take advantage of this awesome pagination plugin (http://wordpress.org/extend/plugins/wp-paginate/).

Make sure to check out all that you can do with the WP_Query class: http://codex.wordpress.org/Function_Reference/WP_Query.

If you're getting a 404 error with pretty permalinks, it's because you haven't created a custom post type archive template file. Custom post types don't pull default template files for them. So you have to create archive-post_type_name.php, single-post_type_name.php, etc. to avoid the 404's.

If you want categories associated with your custom post type, then you need to register_taxonomy and add categories that are meant solely for your custom post type. ( http://codex.wordpress.org/Function_Reference/register_taxonomy and http://justintadlock.com/archives/2010/06/10/a-refresher-on-custom-taxonomies)

Then you can use the regular WP_Query.

In any case, when you query_posts, you're removing all capabilities for pagination. You have to add it in. i.e.:

$cat = get_query_var('cat');
// this "activates" the pagination
$paged = (get_query_var('paged')) ? get_query_var('paged') : 1; 
query_posts(array('posts_per_page' => '9', 'cat' => $cat, 'paged' => $paged)));