Custom sql for subcategories – for use in pagination

Why not just use get_categories and put them in an array?

<?php
	$categories = get_categories('orderby=count&order=ASC');
	foreach ( $categories as $cat ) {
	//echo 'category ' . $cat->term_id;
  echo '<p> category ' . '<a href="' . get_category_link( $cat->cat_ID, 'post_tag' ) . '" title="' . sprintf( __( "View all posts in %s" ), $cat->name ) . '" ' . '>' . $cat->cat_name.'</a> has ' . $cat->count . ' post(s). </p> ';
  }
	?>

But in case you want the SQL, this from the wp-includes/taxonomy.php should give you an idea:

//the variables would be something like:
$parent = 34;// your category parent ID
$where = " tt.parent = '$parent'";
$in_taxonomies = 'category';
$orderby = 't.term_id';
$order = 'ASC';
$limit = '0,100';

$query = "SELECT $select_this FROM $wpdb->terms AS t INNER JOIN $wpdb->term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ($in_taxonomies) $where ORDER BY $orderby $order $limit";

$terms = $wpdb->get_results($query);

Thnx a lot MichaelH,

I will try those and come back to you

Okay irkevin–be interested in seeing your pagination solution as there haven’t seemed to be any easy answers to this topic.

[Note edit topic to include pagination]

It’s not showing anything.

I’m using this

<?php
//the variables would be something like:
$parent = 9;// your category parent ID
$where = " tt.parent = '$parent'";
$in_taxonomies = 'category';
$orderby = 't.term_id';
$order = 'ASC';
$limit = '0,100';

$query = "SELECT name FROM $wpdb->terms AS t INNER JOIN $wpdb->term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ($in_taxonomies) $where ORDER BY $orderby $order $limit";

$terms = $wpdb->get_results($query);

echo "<pre>";
print_r($terms);
echo "</pre>";
?>

What I want, is create a query to select all Subcategories in the Category 9.

Okay, had to play with it a little…

<?php
//the variables would be something like:
$parent = 9;// your category parent ID
$where = "AND tt.parent = '$parent'";
$in_taxonomies = "'category'";
$orderby = 't.term_id';
$order = 'ASC';
$limit = 'LIMIT 0,100';

$query = "SELECT t.name FROM $wpdb->terms AS t INNER JOIN $wpdb->term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ($in_taxonomies) $where ORDER BY $orderby $order $limit";

$terms = $wpdb->get_results($query);

echo 'query ' . $query;
echo "<pre>"; print_r($terms); echo "</pre>";
?>

The query is working just fine!

Let me fetch some beers and attack the pagination part. Lol

Ohhh it’s working. But now there’s something i would like to modify.

Here is the link to check the Subcategories Pagination in action

https://www.mu-anime.com/new/category/animes

When I click on next, the URL goes like this

https://www.mu-anime.com/new/category/animes?current_page=2

Is there a way to make the url look nice and clean?

I’m a bit confused as I don’t see that you have that many categories to paginate. Are you talking about the pagination of posts in a given category? If so then you should be able to use the template tag, previous_posts_link() and next_posts_link() in a Category Template.

But, resolving the URL for pagination of categories is part of the problem that I’ve seen no good resolution.

Well my point was .. more explanation below

I have a category called anime, in anime there will be more subcategories..

Imagine i have 500 subcategories in the Categories Anime, it’s much better to paginate those 500 Subcats.

Right now i have 3, but I’m going to add more.. The website is not finished XD

Now my only problem is about the link

Well, i dont have any clue how to fix the link :S

Huh nobody can help on this?

Ok, nevermind I made it.

I made it so that when permalink is ON, use a nice url, and when it’s OFF, use the query string instead.

I will explain how I made it.

I used a pagination class I wrote a while back.

It can be found here

https://www.mu-anime.com/code/pagination.txt

In the page where you want the subcategories to show, use the code below

<?php
	//include the paginate class. I put it in the theme folder
	include("paginate.php");

	// This is the SQL Query to get the number of rows I have
	$count = "SELECT COUNT(*) FROM $wpdb->terms AS t
		     INNER JOIN $wpdb->term_taxonomy AS tt
		ON t.term_id = tt.term_id WHERE tt.taxonomy
               IN('category') AND tt.parent = '9'";	

	$number =  mysql_query($count);
	$row = mysql_fetch_array($number);
	$num_rows = array_shift($row);

	// Define some variable to hold our pagination settings
	$page = !empty($_GET['current_page']) ? (int)$_GET['current_page'] : 1;

	$perPage = 2;

	$paginate  =  new sitePagination($page,$perPage,$num_rows);

	//the variables would be something like:
	$parent = 9;// your category parent ID
	$where = " tt.parent = '$parent'";
	$in_taxonomies = 'taxonomy';
	$orderby = 't.term_id';
	$order = 'ASC';

        //This is the actual SQL Query to fetch the Data from Database
        $query = "SELECT * FROM $wpdb->terms AS t
		INNER JOIN $wpdb->term_taxonomy AS tt
		ON t.term_id = tt.term_id WHERE tt.taxonomy
                IN('category')
	       AND tt.parent = '$parent' LIMIT {$perPage} OFFSET {$paginate->offset()}";

	$terms = $wpdb->get_results($query);

        // A foreach loop to output the data nice and clean
	foreach($terms as $term){
	     $cat_parent = get_category($term->parent);

             //Had to use the $cat_parent to build the link
             //if some has a better idea, would be nice
	 echo "<li>
                 <a href='".$cat_parent->slug.'/'.$term->slug."'>". $term->name ."</a>
              </li>";
	}

	// The Fun starts here, all the code below will generate our dynamic page number
       // The container class is the same as WP PAGENAVI
       // I'm using a custom pagination class I created
	echo "<div class='wp-pagenavi'>";

	echo "<span class='pages'>Page {$page} of {$paginate->totalPages()}</span>";
	if($paginate->totalPages() > 1){
		if($paginate->previousPageExists()){
                   if ( get_option('permalink_structure') ){
			echo '<a href="'.$cat_parent->slug.'/page/'.$paginate->previousPage().'">&raquo; Previous</a>';
		}else{
	          echo '<a href="?cat='.$cat_parent->term_id.'&current_page='.$paginate->previousPage().'">&laquo; Previous</a>';
	       }
	   }
       }

	for($i=1;$i < ceil($paginate->totalPages()) + 1;$i++){
		if($page == $i)
			echo '<span class="current">'.$i.'</span>';
		else
                       echo '<a href="'.$cat_parent->slug.'/?current_page='.$i.'">'.$i.'</a>';

	}

	if($paginate->totalPages() > 1){
		if($paginate->nextPageExists()){
                  if ( get_option('permalink_structure') ){
			echo '<a href="'.$cat_parent->slug.'/page/'.$paginate->nextPage().'">Next &raquo;</a>';
		}else{
			echo '<a href="?cat='.$cat_parent->term_id.'&current_page='.$paginate->nextPage().'">Next &raquo;</a>';
		}
	}
}

	echo "</div>";
?>

In the queries, be sure that tt.parent points to your category ID.

Use this in the htaccess, above the #BEGIN wordpress

<ifmodule mod_rewrite.c>
RewriteEngine On
RewriteRule category/animes/page/(.*)$ category/animes/\?current_page=$1[L]
</ifmodule>

Ok almost everything is done now, expect on little problem which I cant seem to fix.

When using permalink, when I click next, the link goes like this

category/animes/page/2

But when I want to go back, the link is messed up like this

animes/page/animes/page/1

Why is it listing it twice?

Ok, finally, I succeeded in my quest ??

Below is the actual code to get the pagination working.. If permalink is ON, it will use a nice link else it will use the default one.

Here is a link to the code

The code:
https://www.mu-anime.com/code/code.txt

The pagination class
https://www.mu-anime.com/code/pagination.txt

Hope it works if you need it!

An example can be found there:

https://www.mu-anime.com/new/category/animes

The subcategories are being displayed and I’m able to paginate them ?? I limited the result to two now since I’m still customizing the site. Couldn’t do it with the PageNavi Plugin since it was paginating only post. I don’t know how to create a plugin, else I would have convert this to a plugin.