Title: Custom sql for subcategories – for use in pagination
Last modified: August 19, 2016

---

# Custom sql for subcategories – for use in pagination

 *  Resolved [irkevin](https://wordpress.org/support/users/irkevin/)
 * (@irkevin)
 * [16 years, 5 months ago](https://wordpress.org/support/topic/custom-sql-for-subcategories/)
 * Hi all,
 * Can anyone show me how to make an sql to fetch subcategories?
 * I don’t want to use wp_list_categories(“child_of=<id>”)
 * I have to create an SQL to fetch the subcategories if i want to paginate them.
 * I know SQL, but the way wordpress DB is setup makes it hard for me
 * How should write the SQL?
 * Help plz

Viewing 15 replies - 1 through 15 (of 17 total)

1 [2](https://wordpress.org/support/topic/custom-sql-for-subcategories/page/2/?output_format=md)
[→](https://wordpress.org/support/topic/custom-sql-for-subcategories/page/2/?output_format=md)

 *  [MichaelH](https://wordpress.org/support/users/michaelh/)
 * (@michaelh)
 * [16 years, 5 months ago](https://wordpress.org/support/topic/custom-sql-for-subcategories/#post-1291326)
 * 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> ';
         }
       	?>
       ```
   
 *  [MichaelH](https://wordpress.org/support/users/michaelh/)
 * (@michaelh)
 * [16 years, 5 months ago](https://wordpress.org/support/topic/custom-sql-for-subcategories/#post-1291327)
 * 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);
       ```
   
 *  Thread Starter [irkevin](https://wordpress.org/support/users/irkevin/)
 * (@irkevin)
 * [16 years, 5 months ago](https://wordpress.org/support/topic/custom-sql-for-subcategories/#post-1291339)
 * Thnx a lot MichaelH,
 * I will try those and come back to you
 *  [MichaelH](https://wordpress.org/support/users/michaelh/)
 * (@michaelh)
 * [16 years, 5 months ago](https://wordpress.org/support/topic/custom-sql-for-subcategories/#post-1291342)
 * 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]
 *  Thread Starter [irkevin](https://wordpress.org/support/users/irkevin/)
 * (@irkevin)
 * [16 years, 5 months ago](https://wordpress.org/support/topic/custom-sql-for-subcategories/#post-1291347)
 * 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.
 *  [MichaelH](https://wordpress.org/support/users/michaelh/)
 * (@michaelh)
 * [16 years, 5 months ago](https://wordpress.org/support/topic/custom-sql-for-subcategories/#post-1291349)
 * 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>";
       ?>
       ```
   
 *  Thread Starter [irkevin](https://wordpress.org/support/users/irkevin/)
 * (@irkevin)
 * [16 years, 5 months ago](https://wordpress.org/support/topic/custom-sql-for-subcategories/#post-1291357)
 * The query is working just fine!
 * Let me fetch some beers and attack the pagination part. Lol
 *  Thread Starter [irkevin](https://wordpress.org/support/users/irkevin/)
 * (@irkevin)
 * [16 years, 5 months ago](https://wordpress.org/support/topic/custom-sql-for-subcategories/#post-1291372)
 * 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
 * [http://www.mu-anime.com/new/category/animes](http://www.mu-anime.com/new/category/animes)
 * When I click on next, the URL goes like this
 * [http://www.mu-anime.com/new/category/animes?current_page=2](http://www.mu-anime.com/new/category/animes?current_page=2)
 * Is there a way to make the url look nice and clean?
 *  [MichaelH](https://wordpress.org/support/users/michaelh/)
 * (@michaelh)
 * [16 years, 5 months ago](https://wordpress.org/support/topic/custom-sql-for-subcategories/#post-1291376)
 * 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](http://codex.wordpress.org/Template_Tags),
   [previous_posts_link()](http://codex.wordpress.org/Template_Tags/previous_posts_link)
   and [next_posts_link()](http://codex.wordpress.org/Template_Tags/next_posts_link)
   in a [Category Template](http://codex.wordpress.org/Category_Templates).
 * But, resolving the URL for pagination of categories is part of the problem that
   I’ve seen no good resolution.
 *  Thread Starter [irkevin](https://wordpress.org/support/users/irkevin/)
 * (@irkevin)
 * [16 years, 5 months ago](https://wordpress.org/support/topic/custom-sql-for-subcategories/#post-1291380)
 * 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
 *  Thread Starter [irkevin](https://wordpress.org/support/users/irkevin/)
 * (@irkevin)
 * [16 years, 5 months ago](https://wordpress.org/support/topic/custom-sql-for-subcategories/#post-1291407)
 * Well, i dont have any clue how to fix the link :S
 *  Thread Starter [irkevin](https://wordpress.org/support/users/irkevin/)
 * (@irkevin)
 * [16 years, 5 months ago](https://wordpress.org/support/topic/custom-sql-for-subcategories/#post-1291488)
 * Huh nobody can help on this?
 *  Thread Starter [irkevin](https://wordpress.org/support/users/irkevin/)
 * (@irkevin)
 * [16 years, 5 months ago](https://wordpress.org/support/topic/custom-sql-for-subcategories/#post-1291497)
 * 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
 * [http://www.mu-anime.com/code/pagination.txt](http://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>
       ```
   
 *  Thread Starter [irkevin](https://wordpress.org/support/users/irkevin/)
 * (@irkevin)
 * [16 years, 5 months ago](https://wordpress.org/support/topic/custom-sql-for-subcategories/#post-1291500)
 * 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?
 *  Thread Starter [irkevin](https://wordpress.org/support/users/irkevin/)
 * (@irkevin)
 * [16 years, 5 months ago](https://wordpress.org/support/topic/custom-sql-for-subcategories/#post-1291503)
 * 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:
    [http://www.mu-anime.com/code/code.txt](http://www.mu-anime.com/code/code.txt)
 * The pagination class
    [http://www.mu-anime.com/code/pagination.txt](http://www.mu-anime.com/code/pagination.txt)
 * Hope it works if you need it!
 * An example can be found there:
 * [http://www.mu-anime.com/new/category/animes](http://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.

Viewing 15 replies - 1 through 15 (of 17 total)

1 [2](https://wordpress.org/support/topic/custom-sql-for-subcategories/page/2/?output_format=md)
[→](https://wordpress.org/support/topic/custom-sql-for-subcategories/page/2/?output_format=md)

The topic ‘Custom sql for subcategories – for use in pagination’ is closed to new
replies.

## Tags

 * [meh_code](https://wordpress.org/support/topic-tag/meh_code/)
 * [pagination](https://wordpress.org/support/topic-tag/pagination/)

 * In: [Fixing WordPress](https://wordpress.org/support/forum/how-to-and-troubleshooting/)
 * 17 replies
 * 4 participants
 * Last reply from: [gward1](https://wordpress.org/support/users/gward1/)
 * Last activity: [15 years, 10 months ago](https://wordpress.org/support/topic/custom-sql-for-subcategories/page/2/#post-1291625)
 * Status: resolved

## Topics

### Topics with no replies

### Non-support topics

### Resolved topics

### Unresolved topics

### All topics