Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.

$-5
Custom Sql and code for category listed in hierarchy tree

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

gino naya | 05/19/11 at 11:34am Edit

(3) Possible Answers Submitted...

Note: gino naya felt their question was unanswered, so we granted them a refund.

Note: gino naya requested a refund. They offered this explanation:

"Unfortuantely I didn't receive the answer I needed.

Rather than working on what I asked (custom sql and code), experts only proposed to find pre-made alternatives which I was able to find with google before asking here (ex: http://snipplr.com/view/23987/wordpress-show-category-with-checkbox/)

This is not what I need and I ended up solving it by myself.

Thank you anyway for your efforts.

Paolo
"

If no one challenges a refund request, then they are automatically granted and proccessed after 48 hours. Admins of this site only review refund requests if someone challenges the request. If you are curious about how we handled previous refund requests, you may read over all refund requests and their challenges.

This question had 2 challenges to its refund request.

See a chronological view of answers?

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

Last edited:
05/19/11
11:39am

Michael Fields says:

Any reason why wp_category_checklist() will not work?

05/19/11 11:41am

gino naya says:

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)";	



}

05/19/11 12:00pm

Michael Fields says:
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?
05/19/11 12:18pm

gino naya says:
please refer to this for more details...
05/19/11 12:20pm

Michael Fields says:
There is nothing there to refer to?

05/19/11 12:20pm

gino naya says:

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

Last edited:
05/19/11
11:42am

AdamGold says:

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();

Last edited:
05/19/11
8:36pm
Denzel Chia says:
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@denzeldesigns.com , perhaps I can login to your testing server to take a look and understand more.

Thanks.
Denzel

Previous versions of this answer: 05/19/11 at 8:36pm

This question has expired.

gino naya had additional discourse to offer.

Current status of this question: Refunded

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.

Ask your WordPress questions! Pay money and get answers fast! (more info)

$-5 Custom Sql and code for category listed in hierarchy tree

(3) Possible Answers Submitted...

$-5
Custom Sql and code for category listed in hierarchy tree