WordPress.org

Support

Support » How-To and Troubleshooting » [Resolved] Custom sql for subcategories – for use in pagination

[Resolved] Custom sql for subcategories – for use in pagination

  • 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)
  • 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

    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

    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

    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>

    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:
    http://www.mu-anime.com/code/code.txt

    The pagination class
    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

    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)
  • The topic ‘[Resolved] Custom sql for subcategories – for use in pagination’ is closed to new replies.