Join on same table?

[bayswater]

Hi, well, I think it's about joining within the same table, but I'm not sure. Here's my problem:

 

I have a table with a number of categories and undercategories, see screendump of database here:

 

I show the categories in my select by:

 

<option value="<?php echo $row['id']; ?>"><?php echo $row['name']; ?></option>

 

This gives me (here sorted alphabetically):

1.semester

3.semester

5.semester

Underkategori_1_i_1.semester

Underkategori_1_i_3.semester

Underkategori_1.1_i_3.semester

 

But I would like for it to show the path (the relation between categori and under-/subcategory (see "parent" and "path" in the database (screenshot above)), so that is will display the list like this:

 

1.semester

1.semester/Underkategori_1_i_1.semester

3.semester

3.semester/Underkategori_1_i_3.semester

3.semesterUnderkategori_1_i_3.semester/Underkategori_1.1_i_3.semester

5.semester

 

etc.

 

How can I join either "parent" or "path" with "id" (see screendump of database above) to create a listing like that? Or is that the correct way to do it at all?

 

Hope someone can help me out!

[johnny86]

I dont really know how you have designed those paths. I mean have you tought of them logically from the point of the application but:

You could try to add GROUP BY parent ORDER BY path  to the end of your query. See what it gives you then

[bayswater]

I dont really know how you have designed those paths. I mean have you tought of them logically from the point of the application but:

You could try to add GROUP BY parent ORDER BY path  to the end of your query. See what it gives you then

Hi Johnny, thanks for your input. I didn't design the database structure - it's "Moodle" (a php e-course system).

 

My SQL looks like this right now:

 

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

 

That gives me:

1.semester

3.semester

5.semester

Underkategori_1_i_1.semester

Underkategori_1_i_3.semester

Underkategori_1.1_i_3.semester

 

Your suggestion, that is:

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

 

results in:

Underkategori_1_i_1.semester

Underkategori_1_i_3.semester

Underkategori_1.1_i_3.semester

5.semester

 

 

I'm confused... 

[bayswater]

Having looked through the system's own pages, I've found this:

 

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

 

But I'm not into adodb or json. Could the array-thing above (I guess it's the first one) be done using my sql-setup:

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

$sql="SELECT id, name, parent, path, sortorder FROM ".$prefix."course_categories WHERE visible = '1' ORDER BY name asc";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result))
{
...
}
mysql_close($con);
?>

[johnny86]

Oh yeah my misstake. But did you try just with ORDER BY path?

 

Seems like the path is always right for the ordering. Since it gives all the parents first and at the last its own ID. It should work.

[bayswater]

Oh yeah my misstake. But did you try just with ORDER BY path?

 

Seems like the path is always right for the ordering. Since it gives all the parents first and at the last its own ID. It should work.

 

Hi johnny - that works fine. But I'm still missing that it will show the path (with names) in front of the category, like this:

1.semester

1.semester/Underkategori_1_i_1.semester

3.semester

3.semester/Underkategori_1_i_3.semester

3.semesterUnderkategori_1_i_3.semester/Underkategori_1.1_i_3.semester

5.semester

 

But I guess that's more tricky... It should - within my

 

while($row = mysql_fetch_array($result))

{

<option value="<?php echo $row['id']; ?>"><?php echo $row['name']; ?></option>

}

 

add another loop to translate/replace the id's in "path" with the matching "name":

 

... so that it will look like:

 

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 understand what I mean :-)

 

Thanks for your help!

[mmarif4u]

I am a bit confused here.

Can you explain, how you want it to show to users. Show us the example how you want to output and hope i will try to help.

[bayswater]

I am a bit confused here.

Can you explain, how you want it to show to users. Show us the example how you want to output and hope i will try to help.

Hi, yes, I can try :-)

 

Just for starters - my SQL is this:

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

 

My current select-option code looks like this:

<option value="<?php echo $row['id']; ?>"><?php echo $row['name']; ?></option>

 

That gives me this list:

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

 

If I add $row['path']; to the select-option code:

 <option value="<?php echo $row['id']; ?>"><?php echo $row['path']; ?>/<?php echo $row['name']; ?></option>

 

It gives me this:

/2/1.semester

/2/7/Underkategori_1_i_1.semester

/3/3.semester

/3/4/Underkategori_1_i_3.semester

/3/4/6/Underkategori_1.1_i_3.semester

/3/8/Underkategori_2_i_3.semester

/5/5.semester

 

But I want the names instead of the id's.

[bayswater]

Hi, some guy sent me the code below - and I think we're almost there, but I'm unclear about the statement:

 

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

 

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

 

 

 

<?php
$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>";
}
?> 

[bayswater]

Hi, some guy sent me the code below - and I think we're almost there, but I'm unclear about the statement:

 

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

 

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

 

 

 

<?php
$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>";
}
?> 

 

Geez, it was merely the databaseconnection - it works beautifully now. Thanks for your input!