Another loop within while (or perhaps a join?)

[bayswater]

Hi, and a merry Christmas to all (if you celebrate that :-)),

I have a table with a number of categories and undercategories, see screendump of database here:
http://img52.imageshack.us/img52/1274/databaseg.png

My SQL looks like this:

$sql="SELECT id, name, parent, path, sortorder FROM ".$prefix."course_categories WHERE visible = '1' ORDER BY path asc";

that gives me a list like this:
1.semester
Underkategori_1_i_1.semester
3.semester
Underkategori_1_i_3.semester
Underkategori_1.1_i_3.semester
Underkategori_2_i_3.semester
5.semester

But I would like for it to show the path (the relation between category and under-/subcategory (see "parent" and "path" in the database (screenshot above)), so that is will display the list like this - INCLUDING the names of parent categories:
1.semester
1.semester/Underkategori_1_i_1.semester
3.semester
3.semester/Underkategori_1_i_3.semester
3.semester/Underkategori_1_i_3.semester/Underkategori_1.1_i_3.semester
3.semester/Underkategori_2_i_3.semester
5.semester

SO - I guess I want my existing display-code:

while($row = mysql_fetch_array($result))
{
<option value="<?php echo $row['id']; ?>"><?php echo $row['name']; ?></option>
}

• to include another loop or something to translate/replace the id's in "path" with the matching "name", so it looks like this:

  while($row = mysql_fetch_array($result))
  {
  <option value="<?php echo $row['id']; ?>"><?php echo $PATH_WITH_NAMES; ?><?php echo $row['name']; ?></option>
  }

I hope you get the idea?! :-)

[savi16]

$sql="SELECT id, path, name FROM ".$prefix."course_categories WHERE visible = '1' ORDER BY path ASC";

/* query here, lets say that your link id is under $link */

while($var = mysql_fetch_array($result)) {
   $path = explode("/", $var["path"]);
   $temp = array_pop($path); //pop the last one as it will always be the deepest subcategory
   echo "<option value=\"".$var["id"]."\">";
   foreach($path as $ids) {
      $nextsql = "SELECT name FROM ".$prefix."course_categories WHERE id = '$ids'";
      $name = mysql_fetch_array(mysql_query($nextsql, $link));
      echo $name["name"].'/';
      }
   echo $var["name"]."</option>";
}

it's not the most efficient way, but should do the job if i understood you right

[savi16]

Dunno why I can't edit my posts... So sorry for spam!

Just noticed your path starts with a '/', so if it will cause $path[0] to be an empty string, use array_shift on it too:

 //...
$path = explode("/", $var["path"]);
$temp = array_pop($path); 
$temp = array_shift($path);
//...

[bayswater]

savi16;10971128 wrote:

$sql="SELECT id, path, name FROM ".$prefix."course_categories WHERE visible = '1' ORDER BY path ASC";

/* query here, lets say that your link id is under $link */

while($var = mysql_fetch_array($result)) {
   $path = explode("/", $var["path"]);
   $temp = array_pop($path); //pop the last one as it will always be the deepest subcategory
   echo "<option value=\"".$var["id"]."\">";
   foreach($path as $ids) {
      $nextsql = "SELECT name FROM ".$prefix."course_categories WHERE id = '$ids'";
      $name = mysql_fetch_array(mysql_query($nextsql, $link));
      echo $name["name"].'/';
      }
   echo $var["name"]."</option>";
}

it's not the most efficient way, but should do the job if i understood you right

Thank you very much for your assistance - I'm unclear about your statement:

/ query here, lets say that your link id is under $link /

If I just copy your code it says "Undefined variable: link" - which I can understand, so what to do with "$link"?

[savi16]

The code between / and / should look like:

<?php
$link = @mysql_connect('your_server', 'db_user', 'db_password');
mysql_select_db('your_database');

Enter your data instead of 'your_server', 'db_user', 'db_password', 'your_database'.

Cheers

[bayswater]

savi16;10971149 wrote:

The code between / and / should look like:

<?php
$link = @mysql_connect('your_server', 'db_user', 'db_password');
mysql_select_db('your_database');

Enter your data instead of 'your_server', 'db_user', 'db_password', 'your_database'.

Cheers

Ah, I had my connection further up the page, and didn't get what you meant - it works beautifully! Thanks a lot - you really made my day! Thank you!

[bayswater]

Thanks for all your help. One small thing though - in the system you can move categories up and down. This is the "sortorder" in the database - this means if I add a new category - for example "2.semester" - it will give give a path matching the new category-ID (sortorder is by default set at "999"). The problem is that this new category is now last in the list - and if I want to move it up the "path" stays the same - only "sortorder" is changed.

New category "2.semester" added:
http://img31.imageshack.us/img31/8266/2semester.png

"2. semester" MOVED up after 1.semester:
http://img507.imageshack.us/img507/2360/2semestermoved.png

But this is moved is not shown in your code, as it sorts by "path" only. Is there way around this?

[bayswater]

Oh, and adding another category I now reach 10 i ID (which sets "path" to "/10":

http://img98.imageshack.us/img98/4111/4semester.png

This makes this new category to be shown at the top of the list:
http://img256.imageshack.us/img256/7797/listmm.png

So I guess sorting by "path" alone is the problem?

[savi16]

yeah, it's sorting by path which causes this behaviour.
Maybe sort by name? But it will mess up the order alltogether then :eek:

it's getting quite complicated there and I think the best way would be recursive query or an iterator, however just altering the code, gave me the below which should also work.

 
<?php
//have your mysql link as $link
$sqla = "SELECT id, name FROM ".$prefix."course_categories WHERE visible = '1' AND parent = '0' ORDER BY name ASC"; //first quering all the parent cats
while($parent = mysql_fetch_array(mysql_query($sqla, $link))) {
echo "<option value=\"".$parent["id"]."\">";
echo $parent["name"];
echo "</option>"; //putting your parent cat as the first position on the list and going through it's children categories
$sqlb="SELECT id, path, name FROM ".$prefix."course_categories WHERE visible = '1' AND path LIKE '/".$parent["id"]."/%' ORDER BY name ASC";
while($var = mysql_fetch_array(mysql_query($sqlb, $link))) {
   $path = explode("/", $var["path"]);
   $temp = array_shift($path); //don't need the parent cat
   $temp = array_pop($path); //don't need the current cat either
   echo "<option value=\"".$var["id"]."\">";
   foreach($path as $ids) {
      $nextsql = "SELECT name FROM ".$prefix."course_categories WHERE id = '$ids'";
      $name = mysql_fetch_array(mysql_query($nextsql, $link));
      echo $name["name"].'/';
      }
   echo $var["name"]."</option>";
} 
}
?>

[bayswater]

savi16;10971167 wrote:

yeah, it's sorting by path which causes this behaviour.
Maybe sort by name? But it will mess up the order alltogether then :eek:

it's getting quite complicated there and I think the best way would be recursive query or an iterator, however just altering the code, gave me the below which should also work.

 
<?php
//have your mysql link as $link
$sqla = "SELECT id, name FROM ".$prefix."course_categories WHERE visible = '1' AND parent = '0' ORDER BY name ASC"; //first quering all the parent cats
while($parent = mysql_fetch_array(mysql_query($sqla, $link))) {
echo "<option value=\"".$parent["id"]."\">";
echo $parent["name"];
echo "</option>"; //putting your parent cat as the first position on the list and going through it's children categories
$sqlb="SELECT id, path, name FROM ".$prefix."course_categories WHERE visible = '1' AND path LIKE '/".$parent["id"]."/%' ORDER BY name ASC";
while($var = mysql_fetch_array(mysql_query($sqlb, $link))) {
   $path = explode("/", $var["path"]);
   $temp = array_shift($path); //don't need the parent cat
   $temp = array_pop($path); //don't need the current cat either
   echo "<option value=\"".$var["id"]."\">";
   foreach($path as $ids) {
      $nextsql = "SELECT name FROM ".$prefix."course_categories WHERE id = '$ids'";
      $name = mysql_fetch_array(mysql_query($nextsql, $link));
      echo $name["name"].'/';
      }
   echo $var["name"]."</option>";
} 
}
?>

First - thank you for all your effort - I REALLY appreciate it. This is way beyond what I can do with PHP, so thank you!

About the code - there seems to be a problem - it loops, I get the following message:
"Fatal error: Maximum execution time of 30 seconds exceeded in line 79"

Line 79: $name = mysql_fetch_array(mysql_query($nextsql, $con));

It produces a list like this:
1.semester
1.semester/Underkategori_1_i_1.semester
1.semester/Underkategori_1_i_1.semester
1.semester/Underkategori_1_i_1.semester
1.semester/Underkategori_1_i_1.semester
... continues with this...

[bayswater]

Oh - an extra note - I have this code from somewhere else, and the first function seems to be similar to your code, but I don't know anything about the adobd-stuff, but maybe you can see some similarities?

[code=php]<?php
require_once('_functions.adodb.php');

// list of course categories, returns full path of each cat,
// array( id => /path/to/category
function categories(){
    global $DB_prefix;
    $sql = "SELECT id, name, path, parent FROM ".$DB_prefix."course_categories 
        ORDER BY sortorder";
    $db = dbConnection();
    if(! $res = $db->Execute($sql))
        return False; 

$category = array();
while ($o = $res->FetchNextObj())
    $category[$o->id] = $o;

// traverse path
// /2/4/5 -> /id2name/id4name/id5name
$return = array();
foreach($category as $id => $obj){
    $return[$id] = str_replace($obj->id,$obj->name,$obj->path);
    $return[$id] = substr($return[$id], 1);//remove leading slash from path
    while ($obj->parent != 0){
        $p = $category[$obj->parent];
        $return[$id] = str_replace($p->id, $p->name,$return[$id]);
        $obj = $p;
    }
}
return $return;
}

// list of courses from  course_categories.id
// return array(category.id_course.id => "name of course")
function courses($cat){
    global $DB_prefix;
    if (!is_numeric($cat))
        return array();

$sql = "SELECT id,fullname,shortname FROM ".$DB_prefix."course 
    WHERE category = $cat";
$db = dbConnection();
if( ! $res = $db->Execute($sql))
    return False;

$courses = array();
while($o = $res->FetchNextObj()){
    $courses[$cat.'_'.$o->id] = $o->fullname;
}

return $courses;
}

$c = @$_GET['cat'];
if(is_numeric($c))
    echo json_encode(courses($c));
else
    echo json_encode(categories());

[/code]

[savi16]

Try this now 🙂
I tested it and it's kinda working fine 🙂 I can't test it exactly how you would as I ain't got your course_categories table structure
<?php
$sqla = "SELECT id, name FROM ".$prefix."course_categories WHERE visible = '1' AND parent = '0' ORDER BY name ASC"; //first quering all the parent cats
$mysqla = mysql_query($sqla, $link);
while($parent = mysql_fetch_array($mysqla)) {
echo "<option value=\"".$parent["id"]."\">";
echo $parent["name"];
echo "</option>"; //putting your parent cat as the first position on the list and going through it's children categories
$sqlb="SELECT id, path, name FROM ".$prefix."course_categories WHERE visible = '1' AND path LIKE '/".$parent["id"]."/%' ORDER BY name ASC";
$mysqlb = mysql_query($sqlb, $link);
while($var = mysql_fetch_array($mysqlb)) {
$path = explode("/", $var["path"]);
echo "<option value=\"".$var["id"]."\">";
if(count($path) > 2) {
$temp = array_shift($path); //don't need the parent cat
$temp = array_pop($path); //don't need the current cat either
foreach($path as $ids) {
$nextsql = "SELECT name FROM ".$prefix."course_categories WHERE id = '$ids'";
$name = mysql_fetch_array(mysql_query($nextsql, $link));
echo $name["name"].'/';
}
}
echo $var["name"]."</option>";

}
}
?>

[bayswater]

savi16;10971171 wrote:

Try this now 🙂
I tested it and it's kinda working fine 🙂 I can't test it exactly how you would as I ain't got your course_categories table structure
<?php
$sqla = "SELECT id, name FROM ".$prefix."course_categories WHERE visible = '1' AND parent = '0' ORDER BY name ASC"; //first quering all the parent cats
$mysqla = mysql_query($sqla, $link);
while($parent = mysql_fetch_array($mysqla)) {
echo "<option value=\"".$parent["id"]."\">";
echo $parent["name"];
echo "</option>"; //putting your parent cat as the first position on the list and going through it's children categories
$sqlb="SELECT id, path, name FROM ".$prefix."course_categories WHERE visible = '1' AND path LIKE '/".$parent["id"]."/%' ORDER BY name ASC";
$mysqlb = mysql_query($sqlb, $link);
while($var = mysql_fetch_array($mysqlb)) {
$path = explode("/", $var["path"]);
echo "<option value=\"".$var["id"]."\">";
if(count($path) > 2) {
$temp = array_shift($path); //don't need the parent cat
$temp = array_pop($path); //don't need the current cat either
foreach($path as $ids) {
$nextsql = "SELECT name FROM ".$prefix."course_categories WHERE id = '$ids'";
$name = mysql_fetch_array(mysql_query($nextsql, $link));
echo $name["name"].'/';
}
}
echo $var["name"]."</option>";

}
}
?>

Thank you. You really are very kind for helping me like this. I really appreciate it. The sorting works fine - only problem is that I sometimes need to show subcategories (without a parent) because I'm trying to make a toggle-tool that toggles "visible" between "0" and "1".

This is my code:

<?php
$host = "...";
$username = "...";
$password =  "...";
$databasename = "...";
$prefix = "mdl_";
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Toggle</title>
<script>
function check_synlige_kategorier() {
if(document.synlige_kategorier["skjul_kategorier[]"].value == "")
{alert('Please pick categories to hide!');
return false; }
return true;
}
function check_skjul_kategorier() {
if(document.skjul_kategorier["vis_kategorier[]"].value == "")
{alert('Please pick categories to show!');
return false; }
return true;
}
</script>
</head>
<body>
<table>
  <tr>
    <td valign=top>
     <b>Visible categories</b>
     <form name="synlige_kategorier" action="" method="post" onSubmit="return check_synlige_kategorier();">
<select name="skjul_kategorier[]" multiple="multiple" size=15 style="width: 500px">
<?php
$con = mysql_connect($host, $username, $password);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db($databasename, $con);
mysql_query("set names 'utf8';");

// HVIS DER SUBMITTES VIS "VENT VENLIGST"
if ((isset($_POST['submit_skjul_kategorier'])) || (isset($_POST['submit_vis_kategorier'])))
{
?>
  <option value="">Please wait...</option>
<?php } else { // ELLERS TRÆK KATEGORIER UD
$sqla = "SELECT id, name FROM ".$prefix."course_categories WHERE visible = '1' AND parent = '0' ORDER BY name ASC"; //first quering all the parent cats
$mysqla = mysql_query($sqla, $con);
   while($parent = mysql_fetch_array($mysqla))
   {
      echo "<option value=\"".$parent["id"]."\">";
      echo $parent["name"];
      echo "</option>"; //putting your parent cat as the first position on the list and going through it's children categories
      $sqlb="SELECT id, path, name FROM ".$prefix."course_categories WHERE visible = '1' AND path LIKE '/".$parent["id"]."/%' ORDER BY name ASC";
      $mysqlb = mysql_query($sqlb, $con);
	 while($var = mysql_fetch_array($mysqlb)) {
	 $path = explode("/", $var["path"]);
	 echo "<option value=\"".$var["id"]."\">";
	 if(count($path) > 2) {
	 $temp = array_shift($path); //don't need the parent cat
	 $temp = array_pop($path); //don't need the current cat either
	 foreach($path as $ids) {
	    $nextsql = "SELECT name FROM ".$prefix."course_categories WHERE id = '$ids'";
	    $name = mysql_fetch_array(mysql_query($nextsql, $con));
	    echo $name["name"].'/';
	    }
	    }
	 echo $var["name"]."</option>";
   }
   }
} // ELSE END
?>

</select><br>
<input type="submit" value="Hide categories &raquo;" name="submit_skjul_kategorier"/> (Subcategories are automatically included)
</form>

<?php
if (isset($_POST['skjul_kategorier']))
{
	$skjul_kategorier=$_POST['skjul_kategorier'];
	if ($skjul_kategorier){
	foreach ($skjul_kategorier as $skjult)
		{
		mysql_query("UPDATE ".$prefix."course_categories SET visible = '0' WHERE id = '$skjult'");
		mysql_query("UPDATE ".$prefix."course_categories SET visible = '0' WHERE path LIKE '%/".$skjult."/%'");
		}
	}
  	echo "Making categories hidden. Please wait...";
	echo "<meta http-equiv='Refresh' content='1.5; URL=_toggle.php'>";
}
?>

</td>
<td width="20">
</td>
<td valign=top>
  <b>Hidden categories</b>
  <form action="" name="skjul_kategorier" method="post" onSubmit="return check_skjul_kategorier();">
<select name="vis_kategorier[]" multiple="multiple" size=15 style="width: 500px">
<?php

if ((isset($_POST['submit_skjul_kategorier'])) || (isset($_POST['submit_vis_kategorier'])))
{

?>
  <option value="">Please wait...</option>

<?php } else { 
$sqlc = "SELECT id, name FROM ".$prefix."course_categories WHERE visible = '0' AND parent = '0' ORDER BY name ASC"; //first quering all the parent cats
$mysqlc = mysql_query($sqlc, $con);
   while($parent = mysql_fetch_array($mysqlc))
   {
      echo "<option value=\"".$parent["id"]."\">";
      echo $parent["name"];
      echo "</option>"; //putting your parent cat as the first position on the list and going through it's children categories
      $sqld="SELECT id, path, name FROM ".$prefix."course_categories WHERE visible = '0' AND path LIKE '/".$parent["id"]."/%' ORDER BY name ASC";
      $mysqld = mysql_query($sqld, $con);
	 while($var = mysql_fetch_array($mysqld)) {
	 $path = explode("/", $var["path"]);
	 echo "<option value=\"".$var["id"]."\">";
	 if(count($path) > 2) {
	 $temp = array_shift($path); //don't need the parent cat
	 $temp = array_pop($path); //don't need the current cat either
	 foreach($path as $ids) {
	    $nextsql = "SELECT name FROM ".$prefix."course_categories WHERE id = '$ids'";
	    $name = mysql_fetch_array(mysql_query($nextsql, $con));
	    echo $name["name"].'/';
	    }
	    }
	 echo $var["name"]."</option>";
   }
   }
} // ELSE END
?>

</select><br>
<input type="submit" value="&laquo; Show categories" name="submit_vis_kategorier"/> (Underkategorier medtages automatisk)
</form>

<?php
if (isset($_POST['vis_kategorier']))
{
	$vis_kategorier=$_POST['vis_kategorier'];
	if ($vis_kategorier){
	foreach ($vis_kategorier as $vist)
		{
		mysql_query("UPDATE ".$prefix."course_categories SET visible = '1' WHERE id = '$vist'");
	 	mysql_query("UPDATE ".$prefix."course_categories SET visible = '1' WHERE path LIKE '%/".$vist."/%'");
		}
	}
  	echo "Making categories visible! Please wait...";
	echo "<meta http-equiv='Refresh' content='1.5; URL=_toggle.php'>";
}
mysql_close($con);
?>      

    </td>
  </tr>
</table>
</body>
</html>

This is the database table (SQL):

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;


CREATE TABLE IF NOT EXISTS `mdl_course_categories` (
  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `description` text COLLATE utf8_unicode_ci,
  `parent` bigint(10) unsigned NOT NULL DEFAULT '0',
  `sortorder` bigint(10) unsigned NOT NULL DEFAULT '0',
  `coursecount` bigint(10) unsigned NOT NULL DEFAULT '0',
  `visible` tinyint(1) NOT NULL DEFAULT '1',
  `timemodified` bigint(10) unsigned NOT NULL DEFAULT '0',
  `depth` bigint(10) unsigned NOT NULL DEFAULT '0',
  `path` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `theme` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `mdl_courcate_par_ix` (`parent`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Course categories' AUTO_INCREMENT=12 ;

INSERT INTO `mdl_course_categories` (`id`, `name`, `description`, `parent`, `sortorder`, `coursecount`, `visible`, `timemodified`, `depth`, `path`, `theme`) VALUES
(5, '5. semester', '', 0, 5, 1, 1, 0, 1, '/5', NULL),
(2, '1.semester', '', 0, 1, 1, 1, 0, 1, '/2', NULL),
(3, '3. semester', '', 0, 3, 3, 1, 0, 1, '/3', NULL),
(4, 'Underkategori_1_i_3.semester', '', 3, 999, 1, 1, 0, 2, '/3/4', NULL),
(6, 'Underkategori_1.1_i_3.semester', '', 4, 999, 0, 1, 0, 3, '/3/4/6', NULL),
(7, 'Underkategori_1_i_1.semester', '', 2, 999, 0, 1, 0, 2, '/2/7', NULL),
(8, 'Underkategori_2_i_3.semester', '', 3, 999, 0, 1, 0, 2, '/3/8', NULL),
(9, '2.semester', '', 0, 2, 0, 1, 0, 1, '/9', NULL),
(10, '4.semester', '', 0, 4, 0, 1, 0, 1, '/10', NULL);

If you would like to see/test it please do :-)

The thing is - if I move (hide) a subcategory it will not show, because of the "AND parent = '0'" in the query. Is there a way around this? Soooo close to the finish line :-) Crossing fingers, hoping for yet another reply from you :-D

[bayswater]

Hi, I'm very close at throwing in the towel... I would like to be able to change the name of the category whilst it keeps the position in the list. If I rename "2.semester" to "Second semester" it obviously sends the category to the bottom of the list because of the "ORDER BY name ASC".

It's not a major deal, but it would be nice, if it was possible. Seems that ORDER BY "sortorder" is the only thing that would do that, but I don't get how, because of all the default "999"s...😕

But I can live with the alphabetical sorting, but the showing of subcategories mentioned above has to work :queasy:

Thank you again!

[bayswater]

bayswater;10971187 wrote:

Hi, I'm very close at throwing in the towel... I would like to be able to change the name of the category whilst it keeps the position in the list. If I rename "2.semester" to "Second semester" it obviously sends the category to the bottom of the list because of the "ORDER BY name ASC".

It's not a major deal, but it would be nice, if it was possible. Seems that ORDER BY "sortorder" is the only thing that would do that, but I don't get how, because of all the default "999"s...😕

But I can live with the alphabetical sorting, but the showing of subcategories mentioned above has to work :queasy:

Thank you again!

Ah, ORDER BY "sortorder" WILL fix this as it lists the parent categories first. But this still leaves the problem of showing subcategories if a parent category is hidden (visible="0"). I hope you understand what I mean... And I hope this is at all possible?!

[savi16]

hi again 🙂

I don't really get what you mean with toggling the parent and subcategories, but it should have the same effect if you get rid of "visible = '1' AND", so the code looks like:

 //...
$sqla = "SELECT id, name FROM ".$prefix."course_categories WHERE  parent = '0' ORDER BY name ASC";
// ...
$sqlb="SELECT id, path, name FROM ".$prefix."course_categories WHERE path LIKE '/".$parent["id"]."/%' ORDER BY name ASC";

Or if it's pretty dynamic create boolean "$zero_visible_show" variable somwhere in the code and make the statement:

if ($zero_visible_show == true) {
$sqla = "SELECT id, name FROM ".$prefix."course_categories WHERE  parent = '0' ORDER BY name ASC";
$sqlb="SELECT id, path, name FROM ".$prefix."course_categories WHERE path LIKE '/".$parent["id"]."/%' ORDER BY name ASC";
}
else {
$sqla = "SELECT id, name FROM ".$prefix."course_categories WHERE visible = '1' AND parent = '0' ORDER BY name ASC";
$sqlb="SELECT id, path, name FROM ".$prefix."course_categories WHERE visible = '1' AND path LIKE '/".$parent["id"]."/%' ORDER BY name ASC";
}

[savi16]

I got it now 🙂

In your hidden categories else do a couple changes. And the same sort of change for the visible categories.

else { //slight change below
$sqlc = "SELECT id, name, visible FROM ".$prefix."course_categories WHERE parent = '0' ORDER BY name ASC"; //first quering all the parent cats
$mysqlc = mysql_query($sqlc, $con);
   while($parent = mysql_fetch_array($mysqlc))
   {
/* this is what i'm adding */
 if($parent["visible"] !== 0) { //you just don't display parent if it's visible, but still go through it's subcats
      echo "<option value=\"".$parent["id"]."\">";
      echo $parent["name"];
      echo "</option>"; //putting your parent cat as the first position on the list and going through it's children categories
}
/* until here. Below nothing changes */
      $sqld="SELECT id, path, name FROM ".$prefix."course_categories WHERE visible = '0' AND path LIKE '/".$parent["id"]."/%' ORDER BY name ASC";
      $mysqld = mysql_query($sqld, $con);
	 while($var = mysql_fetch_array($mysqld)) {
	 $path = explode("/", $var["path"]);
	 echo "<option value=\"".$var["id"]."\">";
	 if(count($path) > 2) {
	 $temp = array_shift($path); //don't need the parent cat
	 $temp = array_pop($path); //don't need the current cat either
	 foreach($path as $ids) {
	    $nextsql = "SELECT name FROM ".$prefix."course_categories WHERE id = '$ids'";
	    $name = mysql_fetch_array(mysql_query($nextsql, $con));
	    echo $name["name"].'/';
	    }
	    }
	 echo $var["name"]."</option>";
   }
   }
} // ELSE END

[savi16]

ups. My mistake. Correct would be:

if($parent["visible"] == 0) { //you just don't display parent if it's visible, but still go through it's subcats
      echo "<option value=\"".$parent["id"]."\">";
      echo $parent["name"];
      echo "</option>"; //putting your parent cat as the first position on the list and going through it's children categories 

[savi16]

OK, i'm a nice guy, and here's your entire code:

Make sure you change the script name "forumm.php" back into your "_toggle.php".

Regards

<?php
$host = "localhost";
$username = "root";
$password =  "...";
$databasename = "...";
$prefix = "mdl_";
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-2">
<title>Toggle</title>
<script>
function check_synlige_kategorier() {
if(document.synlige_kategorier["skjul_kategorier[]"].value == "")
{alert('Please pick categories to hide!');
return false; }
return true;
}
function check_skjul_kategorier() {
if(document.skjul_kategorier["vis_kategorier[]"].value == "")
{alert('Please pick categories to show!');
return false; }
return true;
}
</script>
</head>
<body>
<table>
  <tr>
    <td valign=top>
     <b>Visible categories</b>
     <form name="synlige_kategorier" action="" method="post" onSubmit="return check_synlige_kategorier();">
<select name="skjul_kategorier[]" multiple="multiple" size=15 style="width: 500px">
<?php
$con = mysql_connect($host, $username, $password);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db($databasename, $con);
mysql_query("set names 'utf8';");

// HVIS DER SUBMITTES VIS "VENT VENLIGST"
if ((isset($_POST['submit_skjul_kategorier'])) || (isset($_POST['submit_vis_kategorier'])))
{
?>
  <option value="">Please wait...</option>
<?php } else { // ELLERS TRAK KATEGORIER UD
$sqla = "SELECT id, name, visible FROM ".$prefix."course_categories WHERE parent = '0' ORDER BY name ASC"; //first quering all the parent cats
$mysqla = mysql_query($sqla, $con);
   while($parent = mysql_fetch_array($mysqla))
   {
   if($parent["visible"] == 1){
      echo "<option value=\"".$parent["id"]."\">";
      echo $parent["name"];
      echo "</option>"; //putting your parent cat as the first position on the list and going through it's children categories
	  }
      $sqlb="SELECT id, path, name FROM ".$prefix."course_categories WHERE visible = '1' AND path LIKE '/".$parent["id"]."/%' ORDER BY name ASC";
      $mysqlb = mysql_query($sqlb, $con);
	 while($var = mysql_fetch_array($mysqlb)) {
	 $path = explode("/", $var["path"]);
	 echo "<option value=\"".$var["id"]."\">";
	 if(count($path) > 2) {
	 $temp = array_shift($path); //don't need the parent cat
	 $temp = array_pop($path); //don't need the current cat either
	 foreach($path as $ids) {
	    $nextsql = "SELECT name FROM ".$prefix."course_categories WHERE id = '$ids'";
	    $name = mysql_fetch_array(mysql_query($nextsql, $con));
	    echo $name["name"].'/';
	    }
	    }
	 echo $var["name"]."</option>";
   }
   }
} // ELSE END
?>

</select><br>
<input type="submit" value="Hide categories &raquo;" name="submit_skjul_kategorier"/> (Subcategories are automatically included)
</form>

<?php
if (isset($_POST['skjul_kategorier']))
{
	$skjul_kategorier=$_POST['skjul_kategorier'];
	if ($skjul_kategorier){
	foreach ($skjul_kategorier as $skjult)
		{
		mysql_query("UPDATE ".$prefix."course_categories SET visible = '0' WHERE id = '$skjult'");
		mysql_query("UPDATE ".$prefix."course_categories SET visible = '0' WHERE path LIKE '%/".$skjult."/%'");
		}
	}
  	echo "Making categories hidden. Please wait...";
	echo "<meta http-equiv='Refresh' content='1.5; URL=forumm.php'>";
}
?>

</td>
<td width="20">
</td>
<td valign=top>
  <b>Hidden categories</b>
  <form action="" name="skjul_kategorier" method="post" onSubmit="return check_skjul_kategorier();">
<select name="vis_kategorier[]" multiple="multiple" size=15 style="width: 500px">
<?php

if ((isset($_POST['submit_skjul_kategorier'])) || (isset($_POST['submit_vis_kategorier'])))
{

?>
  <option value="">Please wait...</option>

<?php } else { //slight change below
$sqlc = "SELECT id, name, visible FROM ".$prefix."course_categories WHERE parent = '0' ORDER BY name ASC"; //first quering all the parent cats
$mysqlc = mysql_query($sqlc, $con);
   while($parent = mysql_fetch_array($mysqlc))
   {
/* this is what i'm adding */
if($parent["visible"] == 0) { //you just don't display parent if it's visible, but still go through it's subcats
      echo "<option value=\"".$parent["id"]."\">";
      echo $parent["name"];
      echo "</option>"; //putting your parent cat as the first position on the list and going through it's children categories
}
/* until here. Below nothing changes */
      $sqld="SELECT id, path, name FROM ".$prefix."course_categories WHERE visible = '0' AND path LIKE '/".$parent["id"]."/%' ORDER BY name ASC";
      $mysqld = mysql_query($sqld, $con);
     while($var = mysql_fetch_array($mysqld)) {
     $path = explode("/", $var["path"]);
     echo "<option value=\"".$var["id"]."\">";
     if(count($path) > 2) {
     $temp = array_shift($path); //don't need the parent cat
     $temp = array_pop($path); //don't need the current cat either
     foreach($path as $ids) {
        $nextsql = "SELECT name FROM ".$prefix."course_categories WHERE id = '$ids'";
        $name = mysql_fetch_array(mysql_query($nextsql, $con));
        echo $name["name"].'/';
        }
        }
     echo $var["name"]."</option>";
   }
   }
} // ELSE END 
?>

</select><br>
<input type="submit" value="&laquo; Show categories" name="submit_vis_kategorier"/> (Underkategorier medtages automatisk)
</form>

<?php
if (isset($_POST['vis_kategorier']))
{
	$vis_kategorier=$_POST['vis_kategorier'];
	if ($vis_kategorier){
	foreach ($vis_kategorier as $vist)
		{
		mysql_query("UPDATE ".$prefix."course_categories SET visible = '1' WHERE id = '$vist'");
	 	mysql_query("UPDATE ".$prefix."course_categories SET visible = '1' WHERE path LIKE '%/".$vist."/%'");
		}
	}
  	echo "Making categories visible! Please wait...";
	echo "<meta http-equiv='Refresh' content='1.5; URL=forumm.php'>";
}
mysql_close($con);
?>      

    </td>
  </tr>
</table>
</body>
</html>

PS>Did you think about doing it with AJAX in opposite to meta-refresh?

[bayswater]

savi16, yes, you are very kind, indeed 🙂 I really appreciate all your work. It seems to work - but only until you add another level of subcategory, then the sorting is messed up. In Moodle you may add as many subcategories you like, by default they get "sortorder = 999". 🙁

If I do a "ORDER BY sortorder ASC, id asc" the list will look like this:
http://img522.imageshack.us/img522/5553/udklipw.jpg

The numbers in the brackets in front of the category names are (ID / sortorder), so the red line illustrates the first order (sortorder) and the yellow line the subsequent order (id).

The blue line shows the errors/messed up sorting.

This is really becoming crazy-complicated! :queasy::queasy::queasy: