Ask your WordPress questions! Pay money and get answers fast! Comodo Trusted Site Seal
Official PayPal Seal

Custom Sql and code for category listed in hierarchy tree WordPress

  • REFUNDED

I need to modify a theme where members can post articles from a submit page.

In this page members can select categories through a checkbox list. Right now this page lists categories ordered alphabetically without taking parents and childs into consideration.

cat1
cat2
cat3
subcat1
subcat2
subcat3

What I'd like to achieve is simple:

cat1
- subcat1
cat2
- subcat2
cat3
- subcat3

This is the orginal code :



<?php
global $wpdb;
$blog_cat = get_option('ptthemes_blogcategory');
if(is_array($blog_cat) && $blog_cat[0]!='')
{
$blog_cat = get_blog_sub_cats_str($type='string');
}else
{
$blog_cat = '';
}
if($blog_cat)
{
$blog_cat .= ",1";
}else
{
$blog_cat .= "1";
}
global $price_db_table_name;
$package_cats = $wpdb->get_var("select group_concat(cat) from $price_db_table_name where cat>0 and amount>0");
if($package_cats)
{
if($blog_cat){
$blog_cat .= ",".$package_cats;
}else
{
$blog_cat .= $package_cats;
}
}
if($blog_cat)
{
$substr = " and c.term_id not in ($blog_cat)";
}
$catsql = "select c.term_id, c.name from $wpdb->terms c,$wpdb->term_taxonomy tt where tt.term_id=c.term_id and tt.taxonomy='category' $substr order by c.name";
$catinfo = $wpdb->get_results($catsql);
global $cat_array;
if($catinfo)
{
$counter = 0;
foreach($catinfo as $catinfo_obj)
{
$counter++;
$termid = $catinfo_obj->term_id;
$name = $catinfo_obj->name;
?>
<div class="form_cat" ><label><input type="checkbox" name="category[]" id="category_<?php echo $counter;?>" value="<?php echo $termid; ?>" class="checkbox" <?php if(isset($cat_array) && in_array($termid,$cat_array)){echo 'checked="checked"'; }?> /> <?php echo $name; ?></label></div>
<?php }
}
?>


I was trying to modify the sql and add a second query for childs... something like:

$catsql = "select c.term_id, c.name, tt.parent from $wpdb->terms c,$wpdb->term_taxonomy tt where tt.term_id=c.term_id and tt.taxonomy='category' $substr order by c.name";
$catinfo = $wpdb->get_results($catsql);

$parentsql = "select c.term_id, c.name, tt.parent from $wpdb->terms c,$wpdb->term_taxonomy tt where tt.term_id=c.term_id and tt.taxonomy='category' $substr order by tt.parent, c.name";
$parentinfo = $wpdb->get_results($parentsql);

which is a think the way to go, but then I could go beyond that....

2011-05-19 by gino naya

Answers (3)

2011-05-19

Michael Fields answers:

Any reason why wp_category_checklist() will not work?

gino naya comments:

because of this...

if(is_array($blog_cat) && $blog_cat[0]!='')

{

$blog_cat = get_blog_sub_cats_str($type='string');

}else

{

$blog_cat = '';

}

if($blog_cat)

{

$blog_cat .= ",1";

}else

{

$blog_cat .= "1";

}

global $price_db_table_name;

$package_cats = $wpdb->get_var("select group_concat(cat) from $price_db_table_name where cat>0 and amount>0");

if($package_cats)

{

if($blog_cat){

$blog_cat .= ",".$package_cats;

}else

{

$blog_cat .= $package_cats;

}

}

if($blog_cat)

{

$substr = " and c.term_id not in ($blog_cat)";

}

Michael Fields comments:

It looks like these "Package Cats" are not categories at all, but rather custom pricing data that is stored in a non-WordPress table in the database. Is this true? If so are you looking to mix these special "Package Cats" with normal WordPress categories?

gino naya comments:

please refer to this for more details...

[[LINK href="http://wpquestions.com/user/discourseShow/id/3997/discourse_id/209"]][[/LINK]]

Michael Fields comments:

There is nothing there to refer to?

gino naya comments:

Ok let me clarify...
the same theme has another setting to display category in submit page other than checkboxes, which is "select".

This is the original code :



<?php

global $wpdb;

$blog_cat = get_option('ptthemes_blogcategory');

if(is_array($blog_cat) && $blog_cat[0]!='')

{

$blog_cat = get_blog_sub_cats_str($type='string');

}else

{

$blog_cat = '';

}

if($blog_cat)

{

$blog_cat .= ",1";

}else

{

$blog_cat .= "1";

}

global $price_db_table_name;

$package_cats = $wpdb->get_var("select group_concat(cat) from $price_db_table_name where cat>0 and amount>0");

if($package_cats)

{

if($blog_cat){

$blog_cat .= ",".$package_cats;

}else

{

$blog_cat .= $package_cats;

}

}

if($blog_cat)

{

$substr = " and c.term_id not in ($blog_cat)";

}

$catsql = "select c.term_id, c.name from $wpdb->terms c,$wpdb->term_taxonomy tt where tt.term_id=c.term_id and tt.taxonomy='category' $substr order by c.name";

$catinfo = $wpdb->get_results($catsql);

global $cat_array;

if($catinfo)

{

$counter = 0;

?>

<div class="form_cat" >

<select name="category[]" id="category_<?php echo $counter;?>" class="textfield" >

<?php

foreach($catinfo as $catinfo_obj)

{

$counter++;

$termid = $catinfo_obj->term_id;

$name = $catinfo_obj->name;

?>

<option <?php if(isset($cat_array) && in_array($termid,$cat_array)){echo 'selected="selected"'; }?> value="<?php echo $termid; ?>"><?php echo $name; ?></option>

<?php

}?>

</select></div>

<?php }

?>



Modifying that into this :

<?php





function category_select_value($array, $parentid, $cat_array, $count) {

$count++;

foreach ($array[$parentid] as $cat) {





if(isset($cat_array) && in_array($cat->term_id, $cat_array)) {



echo "<option value=\"".$cat->term_id."\" checked=\"checked\" selected>";

} else {

echo "<option value=\"".$cat->term_id."\">";

}



for ($i=0;$i<$count;$i++)

echo "&nbsp;&nbsp;";



echo $cat->name."</option>";

//echo $cat->name."<br>";

if (array_key_exists ($cat->term_id, $array))

category_select_value($array, $cat->term_id, $cat_array, $count);

}

}



global $wpdb;



$blog_cat = get_option('ptthemes_blogcategory');



if(is_array($blog_cat) && $blog_cat[0]!=''){

$blog_cat = get_blog_sub_cats_str($type='string');

}else{

$blog_cat = '';

}

if($blog_cat)

{

$blog_cat .= ",1";

}else

{

$blog_cat .= "1";

}

global $price_db_table_name;

$package_cats = $wpdb->get_var("select group_concat(cat) from $price_db_table_name where cat>0 and amount>0");

if($package_cats)

{

if($blog_cat){

$blog_cat .= ",".$package_cats;

}else

{

$blog_cat .= $package_cats;

}

}

if($blog_cat)

{

$substr = " and c.term_id not in ($blog_cat)";

}



$catsql = "select c.term_id, c.name, tt.parent from $wpdb->terms c,$wpdb->term_taxonomy tt where tt.term_id=c.term_id and tt.taxonomy='category' $substr order by c.name";

$catinfo = $wpdb->get_results($catsql);



$parentsql = "select c.term_id, c.name, tt.parent from $wpdb->terms c,$wpdb->term_taxonomy tt where tt.term_id=c.term_id and tt.taxonomy='category' $substr order by tt.parent, c.name";

$parentinfo = $wpdb->get_results($parentsql);



$categories=array();



foreach ($parentinfo as $key=>$info) {

$categories[$info->parent][$info->term_id]=$info;

}



global $cat_array;

if($catinfo) {

//$cat_display=get_option('ptthemes_category_dislay');

//if($cat_display==''){$cat_display='checkbox';}

$counter = 0;

?>

<div class="form_cat" >

<select name="category[]" id="category_<?php echo $counter;?>" class="textfield" >

<?php

category_select_value ($categories, 0, $cat_array, 0);

foreach($catinfo as $catinfo_obj) {

$counter++;

$termid = $catinfo_obj->term_id;

$name = $catinfo_obj->name;

continue;?>

<option <?php if(isset($cat_array) && in_array($termid,$cat_array)){

echo 'selected="selected"'; }?> value="<?php echo $termid; ?>">

<?php echo "$name"; ?></option>

<?php

}

?>

</select></div>

<?php }

?>



It worked perfectly and I want to achieve the same with the checkbox list...

Thanks

2011-05-19

AdamGold answers:

Don't know exactly how to do it with SQL queries, but WP built-in functions are much more useful:
function get_categories_checkboxes( $selected_cats = null ) {
$all_categories = get_categories();
$o = '<ul style="margin-left:12px">';
foreach($all_categories as $key => $cat) {
if($cat->parent == "0") $o .= __show_category($cat, $selected_cats);
}
return $o . '</ul>';
}
function __show_category($cat_object, $selected_cats = null) {
$checked = "";
if(!is_null($selected_cats) && is_array($selected_cats)) {
$checked = (in_array($cat_object->cat_ID, $selected_cats)) ? 'checked="checked"' : "";
}
$ou = '<li><label><input ' . $checked .' type="checkbox" name="cats[]" value="'. $cat_object->cat_ID .'" /> ' . $cat_object->cat_name . '</label>';
$childs = get_categories('parent=' . $cat_object->cat_ID);
foreach($childs as $key => $cat) {
$ou .= '<ul style="margin-left:12px">' . __show_category($cat, $selected_cats) . '</ul>';
}
$ou .= '</li>';
return $ou;
}

Usage:
get_categories_checkboxes();

2011-05-20

Denzel Chia answers:

Hi gino,

With reference to what you posted on discourse column. You said your original code works and you want modify it into a checkbox list. But your function category_select_value() contains codes of a select box and not checklist?

So do you want to modify the select list part of your original codes to a checklist and leaving other parts intact, or do you want to modify your original codes to a function that has variables to control the variables in the original codes and have it output in a checklist? If what you want is a function, can you also specify what you want to be able to control?

I do not understand how you came up with your modify codes, perhaps you want me to create a new one for you?

If you want to communicate via email for more details, you can contact me at [email protected] , perhaps I can login to your testing server to take a look and understand more.

Thanks.
Denzel