Posted 2 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
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);
Posted 2 years ago #
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]
Posted 2 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.
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>";
?>
Posted 2 years ago #
The query is working just fine!
Let me fetch some beers and attack the pagination part. Lol
Posted 2 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?
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.
Posted 2 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
Posted 2 years ago #
Well, i dont have any clue how to fix the link :S
Posted 2 years ago #
Huh nobody can help on this?
Posted 2 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().'">» Previous</a>';
}else{
echo '<a href="?cat='.$cat_parent->term_id.'¤t_page='.$paginate->previousPage().'">« 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 »</a>';
}else{
echo '<a href="?cat='.$cat_parent->term_id.'¤t_page='.$paginate->nextPage().'">Next »</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>
Posted 2 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?
Posted 2 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.
Posted 1 year 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>';
Posted 1 year 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?
This topic has been closed to new replies.
