HELP: tree view of MySQL query

mduran · Jul 12, 2004

Hi,

I would like to visualize the following contents of my MySQL table 'example';

id	Country	Sort	     Type
-------------------------------------------
1	Spain	Fruit	     Orange
2	Cuba	Fruit	     Banana
3	Spain	Vegetable    Tomato
4	Cuba	Vegetable    Cucumber

in the following tree form:

[Spain,
	[Fruit,
		[Orange,
			[page_1.html]
              ]
       ]
	[Vegetable,
		[Tomato,
			[page_3.html]
              ],
       ]
[Cuba,
	[Fruit,
		[Banana,
			[page_2.html]
              ],
       ],
	[Vegetable,
		[Cucumber,
			[page_4.html]
                     ],
              ],
       ],
];

so that I can feed a javascript function to build a tree in the html page. Does anyone know have a function or routine (or watever) to do this ? I am not that good in recursivity... ;-(

thanks a lot.

tsinka · Jul 12, 2004

Hi,

an example using the stuff from

http://www.mattkruse.com/javascript/mktree/index.html

Download all the files an copy them to your server into one directory along with the code below:

<HTML>
<HEAD>
	<TITLE>JavaScript Toolbox - DHTML Expandable, Collapsable Tree</TITLE>
<SCRIPT LANGUAGE="JavaScript" SRC="mktree.js"></SCRIPT>
<LINK REL="stylesheet" HREF="mktree.css">
</HEAD>
<BODY BGCOLOR=#FFFFFF LINK="#00615F" VLINK="#00615F" ALINK="#00615F">

<A href="#" onClick="expandTree('tree1'); return false;">Expand All</A>&nbsp;&nbsp;&nbsp;
<A href="#" onClick="collapseTree('tree1'); return false;">Collapse All</A>&nbsp;&nbsp;&nbsp;
<?PHP
  $data = array();

  // connect
  $conn = mysql_connect("host","user","pass") or die("Connect: ".mysql_error());
  mysql_select_db("thedb",$conn) or die("Select DB: ".mysql_error());

  $sql = "SELECT Country,Sort,Type,id FROM example ORDER BY Country,Sort,Type,id";
  $res = mysql_query($sql,$conn) or die("Query: ".mysql_error());

  while ($row = mysql_fetch_array($res)) {
    $data[] = $row;
  }

  // to test comment out the mysql stuff above ...
  //$data[] = array('Country' => 'Spain','Sort' => 'Fruit','Type' => 'Orange', 'id' => 1);
  //$data[] = array('Country' => 'Spain','Sort' => 'Vegetable','Type' => 'Tomato', 'id' => 2);
  //$data[] = array('Country' => 'Cuba','Sort' => 'Fruit','Type' => 'Apple', 'id' => 3);
  //$data[] = array('Country' => 'Cuba','Sort' => 'Fruit','Type' => 'Banana', 'id' => 4);
  //$data[] = array('Country' => 'Italy','Sort' => 'Fruit','Type' => 'Orange', 'id' => 5);
  //$data[] = array('Country' => 'Italy','Sort' => 'Fruit','Type' => 'Orange', 'id' => 6);

  $country = "";
  $sort    = "";

  $tree    = "<ul class=\"mktree\" id=\"tree1\">\n";

  while (list($idx,$row) = each($data)) {
    $rct = $row['Country'];
    $rst = $row['Sort'];
    $rtp = $row['Type'];
    $rid = $row['id'];

if ($rct != $country) {
  if ($country != "")
    $tree .= "        </ul>\n      </li>\n    </ul>\n  </li>\n";
    $tree .= <<< EOHTML
  <li>$rct
    <ul>
      <li>$rst
        <ul>
          <li><a href="page_$rid.html">$rtp</a></li>

EOHTML;
      $country = $rct;
      $sort    = $rst;
    } else {
      if ($rst != $sort) {
        $tree .= <<< EOHTML
        </ul>
      </li>
      <li>$rst
        <ul>
          <li><a href="page_$rid.html">$rtp</a></li>

EOHTML;
        $sort = $rst;
      } else {
        $tree .= "          <li><a href=\"page_$rid.html\">$rtp</a></li>
";
      }
    }
  }
  $tree .= <<< EOHTML
        </ul>
      </li>
    </ul>
  </li>
</ul>
EOHTML;

  echo $tree;
?>
</BODY>
</HTML>

The code walks through the sorted table data and creates new list items either on a new Country or a new Sort.

The tree can be as deep as you want.

The ORDER BY may be slow. You might need to put indexes on some fields to speed up things a bit. Or maybe use your own custom array sort function.

On a second look I wouldn't use the $tree .= <<< in this case. It decreases the readability of the code.

Thomas

mduran · Jul 13, 2004

This works perfect, thanks !
But how do it change the code if I want to add one or two more depths ? say

 $data[] = array('Country' => 'Spain','Sort' => 'Fruit','Type' => 'Orange', 'Colour' => 'Orange', 'id' => 1); 
  $data[] = array('Country' => 'Spain','Sort' => 'Vegetable','Type' => 'Tomato', 'Colour' => 'Red', 'id' => 2); 
  $data[] = array('Country' => 'Cuba','Sort' => 'Fruit','Type' => 'Apple', 'Colour' => 'Green', 'id' => 3); 
  $data[] = array('Country' => 'Cuba','Sort' => 'Fruit','Type' => 'Banana', 'Colour' => 'Yellow', 'id' => 4); 
  $data[] = array('Country' => 'Italy','Sort' => 'Fruit','Type' => 'Orange', 'Colour' => 'Orange', 'id' => 5); 
  $data[] = array('Country' => 'Italy','Sort' => 'Fruit','Type' => 'Orange', 'Colour' => 'Orange', 'id' => 6);

thanks.

tsinka · Jul 14, 2004

Hi,

time to use recursion ...

You either need to get the data sorted by the query or by a custom function, since it doesn't work otherwise.

The looks a little bit complex but my tests were successfull:

<?PHP
  // insert code to connect to db server and to select database here
  // ....

  //$sql = "SELECT Country,Sort,Type,id FROM example ORDER BY Country,Sort,Type,id";
  //$res = mysql_query($sql,$dbconnect) or die ("SQL error: ".mysql_error());

  $arr = array();

  // test array
  $arr[] = array('Country' => 'Spain','Node2' => 'Zing','Dummy' => 'Test','Sort' => 'Fruit','Type' => 'Orange','id' => 1);
  $arr[] = array('Country' => 'Spain','Node2' => 'Zing','Dummy' => 'Test','Sort' => 'Vegetable','Type' => 'Tomato','id' => 2);
  $arr[] = array('Country' => 'Cuba','Node2' => 'Zing','Dummy' => 'Test','Sort' => 'Fruit','Type' => 'Banana','id' => 3);
  $arr[] = array('Country' => 'Cuba','Node2' => 'Zing','Dummy' => 'Test','Sort' => 'Vegetable','Type' => 'Cucumber','id' => 4);
  $arr[] = array('Country' => 'Italy','Node2' => 'Zing','Dummy' => 'Test','Sort' => 'Fruit','Type' => 'Apple','id' => 5);
  $arr[] = array('Country' => 'Italy','Node2' => 'Zing','Dummy' => 'Test','Sort' => 'Vegetable','Type' => 'Tomato','id' => 6);
  $arr[] = array('Country' => 'Italy','Node2' => 'Zong','Dummy' => 'Test2','Sort' => 'Vegetable','Type' => 'Cucumber','id' => 7);
  $arr[] = array('Country' => 'Italy','Node2' => 'Zong','Dummy' => 'Test3','Sort' => 'Vegetable','Type' => 'Cucumber','id' => 9);
  $arr[] = array('Country' => 'Italy','Node2' => 'Zong','Dummy' => 'Test3','Sort' => 'Vegetable','Type' => 'Tomato','id' => 10);  

  $tree    = "<ul class=\"mktree\" id=\"tree1\">\n";

  $nodeVals    = array();
  $treeDepth   = 0;
  $actualDepth = 0;

  function createTree($arr) {
    // get the global variables and reset retVal
    global $treeDepth,$nodeVals,$actualDepth;
    $retVal = "";

// get remaining array keys
$keys = array_keys($arr);

// get names of first and second key
$firstKey  = $keys[0];
$secondKey = $keys[1];

if (count($arr) > 0 && is_array($arr[$firstKey])) {
  // the next row
  $actualDepth = 0;

  // create node
  $retVal .= createTree($arr[$firstKey]);

  // remove first row
  unset($arr[$firstKey]);

  // now up to the next row
  $retVal .= createTree($arr);
} else {
  if (count($arr)>0 && !is_array($arr[$firstKey])) {
    // within current row
    // but how deep ?
    $actualDepth = $treeDepth - count($arr);

    if (count($arr) == 2) {
      // output link
      if ($nodeVals[$actualDepth] != $arr[$firstKey]) {
        // a new node name
        $retVal .= "<li><a href=\"page_".$arr[$secondKey].".html\">".$arr[$firstKey]."</a></li>\n";

        // store name of current node
        $nodeVals[$actualDepth] = $arr[$firstKey];
      } else {
        // just the same node
        $retVal .= "<li><a href=\"page_".$arr[$secondKey].".html\">".$arr[$firstKey]."</a></li>\n";
      }
    } else if (count($arr) > 2) {
      // we didn't reach the end of the node
      if ($nodeVals[$actualDepth] != $arr[$firstKey]) {
        // but we have a new node name
        if ($nodeVals[$actualDepth] != "") {
          if ($actualDepth == 0 ) {
            // root node so we need to close all the sub nodes created before
            for ($cnt=0;$cnt<($treeDepth-2);$cnt++)
              $retVal .= "</ul></li>";

            // now we need to reset the nodeVals array since this is a new root node
            $nodeVals = array();
          } else {
            for ($cnt=$actualDepth+1;$cnt<$treeDepth-1;$cnt++) {
              // not a root node but a node with a new name so we need to close 
              // the nodes down to the current                
              $retVal .= "</ul></li>";
            }
          }

          for ($cnt=$actualDepth+1;$cnt<($treeDepth);$cnt++) {
            // and now reset the node names below this node
            $nodeVals[$cnt] = "";
          }                  
        }

        // print the new node name
        $retVal .= "<li>".$arr[$firstKey]."<ul>";

        // update the name in the node name array
        $nodeVals[$actualDepth] = $arr[$firstKey];
      } 

      // remove that node from the current row array
      unset($arr[$firstKey]);

      // continue with the next node(s)
      $retVal .= createTree($arr);
    }
  }
}

// now return the current list
return $retVal;    
  }

  $treeDepth = count($arr[0]);

  $tree .= createTree($arr);
  echo $tree;
?>

Thomas

tsinka · Jul 14, 2004

Hi,

there is something missing in the code ... the last element of the tree is a link. The code that closes the tree finally is missing. But it's not too hard too add that code...

Thomas

mduran · Jul 15, 2004

Hi Thomas,

Your test works indeed, but when I try to implement it from a MySQL query I get every node double (i.e. each node is contained into itself). I order the elements from the query, like this:

$conn = mysql_connect("myServer","user","pass") or die("Connect: ".mysql_error()); 
  mysql_select_db("examples",$conn) or die("Select DB: ".mysql_error()); 

  $sql = "SELECT Country,Node2,Dummy,Sort,Type,id FROM examples ORDER BY Country,Node2,Dummy,Sort,Type,id";
  $res = mysql_query($sql,$conn) or die("Query: ".mysql_error()); 

   while ($row = mysql_fetch_array($res)) { 
    $arr[] = $row; 
  } 

...

how come ? does it have to do with the way I fetch the elements ?

As for your comment on closing the tree, I'm not sure I quite understand. I do get a link on the last nodes of the tree ?

Thanks,
Miguel

tsinka · Jul 15, 2004

Hi,

I need to know how the array looks like.
Is that data confidential ? If not, serialize the array and send it as an attachment.

Thomas

mduran · Jul 15, 2004

yes, I'm afraid the the data is confidential :-( but I get almost the same problem with the test data. With my real data it's a bit worse, because from the third entry on the whole tree appears spread and there is no + or - sign to fold it or unfold it (seems to me a problem of format. FYI, some of my entries contain '/' - they are dates.

I send you the MySQL table I created with your test data and the serialized array I obtain from the query.

Thanks for your time,
Miguel

tsinka · Jul 15, 2004

Hi,

one fault in my code here, don't use mysql_fetch_array, use mysql_fetch_assoc because mysql_fetch_array does fetch redundant data in order to access the data by index and/or by field name.

Then try it again. Sorry for that.

Still problems ? Just post the file again, it speeds up thing a lot if I've some "real live" data. String that containt just / shouldn't be a problem (exception: strings that contain HTML list tags or something like that).

Thomas

mduran · Jul 15, 2004

That's it. This works like a charm ! I should have seen that it had to be an associative one (sorry about that).

I also solved the problem of folding / unfolding -- some of my entries' fields were empty. I guess it's important that all fields contain something to keep the hierarchy...

thank again.
Miguel

tsinka · Jul 15, 2004

Glad to hear that

Did you add the code to finally close the list ? It's better to have correct HTML code.

Thomas

mduran · Jul 15, 2004

yes, thanks.

HELP: tree view of MySQL query

Mmduran

Ttsinka

Mmduran

Ttsinka

Ttsinka

Mmduran

Ttsinka

Mmduran

Ttsinka

Mmduran

Ttsinka

Mmduran