categorties/sections (parents)

@knowjNov 04.2007

I have a MySQL table

id | name | parent
1 item1

2 item2 item1
3 item3 item1
4 item4 item1
5 item5 item2
6 item6 item2

Basically i want the items to be ordered by their parent e.g.

item1
item2
item5
item6
item3
item4

[code=php] <label>Section Parent</label><br/> <select name=”section”> <option></option> <?php $product->view_sections(); $fields = array( ‘id’ => ‘text’, ‘name’ => ‘text’, ‘parent’ => ‘text’); $sections = $product->drowstoarray($fields); for($i=0;$i<=count($sections[$i]);$i++) { if (!empty($sections[$i][‘parent’])) { $space = ‘  ’; } echo ‘<option value=”‘.$sections[$i][‘id’].'”>’.$space.$sections[$i][‘name’].'</option>’.”nr”; $space = ”; } ?> </select> [/code]

[code=php]function view_sections() { $this->query = “SELECT * FROM `sections` ORDER BY `name` ASC”; $this->execute(); }[/code]

Thanks in advance.

to post a comment

PHP

6 Comments(s) _↴

@BrainDonorNov 04.2007 — #wouldn't this do it?

[code=php]
 
 $this->query = "SELECT * FROM sections ORDER BY parent, name"; 
 
 [/code]

@knowjauthorNov 04.2007 — #but each parent isnt going to have a defining parent so i would need some way of relating each name (that is a parent) to its childern

@BrainDonorNov 04.2007 — #what if you select unique parent and use that in a while loop to gather the child items in their own loop?

@knowjauthorNov 04.2007 — #use 2 SQL querys?

@bokehNov 04.2007 — #Using code from the tread I directed you to yesterday I came up with the following:

[code=php]<?php
 
 # Start: config
 mysql_connect('localhost', $_SERVER["MYSQL_USER"], $_SERVER["MYSQL_PASS"]);
 mysql_select_db('test');
 $tablename = 'my_test_table';
 $root_item = 'item1';
 # End: config
 
 // create a test table
 mysql_query("
 CREATE TABLE $tablename (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 name VARCHAR( 255 ) NOT NULL ,
 parent VARCHAR( 255 ) NULL ,
 parent_id INT UNSIGNED NULL 
 ) ENGINE = innodb
 ");
 
 // fill it with your test data
 mysql_query("
 INSERT INTO $tablename 
 (name,parent) 
 VALUES 
 ('item1',null),
 ('item2','item1'),
 ('item3','item1'),
 ('item4','item1'),
 ('item5','item2'),
 ('item6','item2')
 ");
 
 // build a 3D array of your tree structure
 $query = "SELECT * FROM $tablename WHERE name = '$root_item' LIMIT 1";
 $result = mysql_query($query);
 if(mysql_num_rows($result) > 0)
 {
 $branch[$root_item] = branches(mysql_fetch_assoc($result), $tablename);
 }
 else
 {
 $branch = 'no result returned';
 }
 
 // drop the test table
 mysql_query("DROP TABLE $tablename");
 
 //test output
 header('Content-Type: text/plain');
 print_r($branch);
 
 function branches($row, $tablename)
 {
 $query = "SELECT * FROM $tablename WHERE parent = '{$row['name']}'";
 $result = mysql_query($query);
 while($row = mysql_fetch_assoc($result))
 {
 $branch[$row['name']] = branches($row, $tablename);
 }
 return !empty($branch)?$branch:null;
 }
 
 
 ?>[/code]

@knowjauthorNov 05.2007 — #thanks for the help this is how i decided to solve the problem in the end:

Returning the sections(navigation)

[code=php]<dl>
 <?php
 $product->view_parent_sections();
 $fields = array(
 'id' => 'text',
 'name' => 'text',
 'parent' => 'text');
 $sections = $product->drowstoarray($fields);
 for($i=0;$i<=count($sections[$i]);$i++)
 { 
 echo '<dt><a href="index.php?id='.$sections[$i]['id'].'">'.$sections[$i]['name'].'</a></dt>'."nr";
 $product->view_child_sections($sections[$i]['id']);
 $child = $product->drowstoarray($fields);
 if (!empty($child))
 {
 for($n=0;$n<=count($child[$n]);$n++)
 {
 echo '<dd><a href="index.php?id='.$child[$n]['id'].'">'.$child[$n]['name'].'</a><dd/>'."nr";
 }
 }
 $child = '';
 }
 ?>
 </dl>[/code]

class_database

[code=php]
 function drowstoarray($array)
 {
 $i = 0;
 while ($row = mysql_fetch_assoc($this->results))
 {
 foreach($array as $name=>$value)
 {
 $return[$i][$name] = $this->check_type($row[$name], $value);
 }
 $i++;
 }
 return $return;
 }
 
  function check_type($input, $type)
   {
   //usable types: text, image, video, price
   switch ($type)
   {
   case 'text':
   return $input;
   break;
   case 'image':
   return '<img src="'.$input.'"/>';
   break;
   case 'video':
   return '<object width="100" height="100"><param name="movie" value="'.$input.'"></param><param name="wmode" value="transparent"></param><embed src="'.$input.'" type="application/x-shockwave-flash" wmode="transparent" width="100" height="100"></embed></object>';
   break;
   case 'price':
   return '&pound;'.$input;
   break;
   default:
   return $input;
   break;
   }
   }
 [/code]

class_products

[code=php]
 function view_parent_sections()
 {
 $this->query = "SELECT * FROM sections WHERE parent='0' ORDER BY name ASC";
 $this->execute();
 }
 
  function view_child_sections($id)
   {
   $this->query = "SELECT * FROM sections WHERE parent='$id' AND parent>'0' ORDER BY name ASC";
   $this->execute();
   }
 [/code]

Also in #PHP _↴

Php Smilie Array problem.copy file from one domain to other domain PayPal Issue

Success!

Help @knowj spread the word by sharing this article on Twitter...

Tweet This

categorties/sections (parents)

6 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

categorties/sections (parents)

6 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

6 Comments(s) _↴

Also in #PHP _↴