WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Custom sql for subcategories - for use in pagination (18 posts)

  1. irkevin
    Member
    Posted 4 years ago #

    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

  2. MichaelH
    Member
    Posted 4 years ago #

    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> ';
      }
    	?>
  3. MichaelH
    Member
    Posted 4 years ago #

    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);
  4. irkevin
    Member
    Posted 4 years ago #

    Thnx a lot MichaelH,

    I will try those and come back to you

  5. MichaelH
    Member
    Posted 4 years ago #

    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]

  6. irkevin
    Member
    Posted 4 years ago #

    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.

  7. MichaelH
    Member
    Posted 4 years ago #

    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>";
    ?>
  8. irkevin
    Member
    Posted 4 years ago #

    The query is working just fine!

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

  9. irkevin
    Member
    Posted 4 years ago #

    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?

  10. MichaelH
    Member
    Posted 4 years ago #

    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.

  11. irkevin
    Member
    Posted 4 years ago #

    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

  12. irkevin
    Member
    Posted 4 years ago #

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

  13. irkevin
    Member
    Posted 4 years ago #

    Huh nobody can help on this?

  14. irkevin
    Member
    Posted 4 years ago #

    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>
  15. irkevin
    Member
    Posted 4 years ago #

    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?

  16. irkevin
    Member
    Posted 4 years ago #

    Ok, finally, I succeeded in my quest :P

    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 :D 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.

  17. bevi
    Member
    Posted 4 years ago #

    Amazing!!!!! it works, right now I can't do work with permalinks but I try later, I don't know why don't do now...

    I don't know if can be show the list in cols, I find some code than put the list in cols but don't have too much knowledge of php to put both together, maybe someone can help me here...

    This is the code to show the list of categories in two cols:

    $cats = explode("<br />",wp_list_categories('child_of=1&echo=0&hide_empty=0&orderby=ID&style=none&title_li='));
    $cat_n = count($cats) - 1;
    for ($i=0;$i<$cat_n;$i++):
    if ($i<$cat_n/2):
    $cat_left = $cat_left.'<li>'.$cats[$i].'</li>';
    elseif ($i>=$cat_n/2):
    $cat_right = $cat_right.'<li>'.$cats[$i].'</li>';
    endif;
    endfor;
    
    echo '<ul class="left">';
    echo $cat_left;
    echo '</ul>';
    echo '<ul class="right">';
    echo $cat_right;
    echo '</ul>';
  18. gward1
    Member
    Posted 4 years ago #

    I have this working however due to the huge number of subcategories that I have it, and it does not use the same logic as the plugin (only listing so many pages, instead you can view them all which goes out of the design). You can see what I mean here: http://www.songlyricsx.com/lyrics/a/

    Anyone have a solution for that?

Topic Closed

This topic has been closed to new replies.

About this Topic