Re: How to find the top most member in a hierarchy of subcategories

2006-09-13 Thread Jo�o C�ndido de Souza Neto 
I found something that we can get all the tree.

SELECT cat_id, group_concat( id )
FROM categoria
GROUP BY cat_id

Try it and tell me if it´s good or not.

""abhishek jain"" <[EMAIL PROTECTED]> escreveu na mensagem 
news:[EMAIL PROTECTED]
> Hi,
> I have a table structure like :
> ID , NAME, PARENT_ID
> 1 , Top , 0
> 2 , Level 1 , 1
> 3 , Level 2 ,2
> 4 , Another Top , 0
>
>
> and so on.
> I wanted to know the topmost cat. if i have the lowest category id ie. 3 
> in
> this case.
> I wanted to get like 3->2->1
> Pl. help me , cn i do this in one query, also i do not know how many
> sublevels are there,
> Thanks,
> Abhishek jain
> 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to find the top most member in a hierarchy of subcategories

2006-09-04 Thread Renato Golin 

Peter Lauri wrote:

Yes, and this shows that you can not do it will MySQL purely :) But a
scripting language like php can do it for you with a recursive function as
the best option.


IMHO, the best option would do it with a procedure as you don't get out 
of the database and don't have any overhead from outside.


--renato

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: How to find the top most member in a hierarchy of subcategories

2006-09-04 Thread Peter Lauri 
Yes, and this shows that you can not do it will MySQL purely :) But a
scripting language like php can do it for you with a recursive function as
the best option.

/Peter


-Original Message-
From: João Cândido de Souza Neto [mailto:[EMAIL PROTECTED] 
Sent: Monday, September 04, 2006 7:55 PM
To: mysql@lists.mysql.com
Subject: Re: How to find the top most member in a hierarchy of subcategories

I´m not quite sure if it could help you, because it´s whole in portuguese, 
but i´ll send you.

My table is called categoria and has the follow structure:

CREATE TABLE `categoria` (
 `id` int(20) NOT NULL auto_increment,
`cat_id` int(20) NOT NULL default '0',
`nome` varchar(50) NOT NULL default '',
`icone` varchar(255) NOT NULL default '',
`cod_shop` varchar(5) NOT NULL default '',
`topo` char(1) NOT NULL default '',
PRIMARY KEY  (`id`)
)

Where the relations each register of the tree are like this:

ID , CATEGORIA, CAT_ID
1 , Top , 0
2 , Level 1 , 1
3 , Level 2 ,2
4 , Another Top , 0

And then i use the follow routine to get the tree:

$sql = "SELECT
   *
  FROM
   categoria
  ORDER BY
   cat_id, nome";
 $con->Query($sql);

 $counter = $con->count;
 $categorias=array();
 for($z=0;$z<$con->count;$z++){
  $con->Seek($z);
  list($id_cat, $cat_id, $nome_cat, $icone_cat) = $con->result;
  $categorias[$id_cat]=array("id_cat" => $id_cat, "cat_id" => $cat_id, 
"nome_cat" => $nome_cat, "icone_cat" => $icone_cat, "familia" => $id_cat, );
  $continua=$cat_id!=null && $cat_id>0;
  if ($continua) {
   $qual=$cat_id;
   $categorias[$id_cat]["indice"]="";
   while ($continua) {
 
$categorias[$id_cat]["indice"]=$categorias[$qual]["nome_cat"].$categorias[$i
d_cat]["indice"];
$continua=$categorias[$qual]["cat_id"]!=null && 
$categorias[$qual]["cat_id"]>0;
$tem_pai=$cat_id!=null && $cat_id>0;
if ($tem_pai) $pai=$cat_id;
while ($tem_pai) {
 if (!strpos($categorias[$pai]["familia"],", 
".$categorias[$id_cat]["id_cat"]))
  $categorias[$pai]["familia"].=", ".$categorias[$id_cat]["id_cat"];
 $tem_pai=$categorias[$pai]["cat_id"]!=null && 
$categorias[$pai]["cat_id"]>0;
 $pai=$categorias[$pai]["cat_id"];
}
if ($continua) {
 $qual=$categorias[$qual]["cat_id"];
}
   }
  }
 }
 reset($categorias);
 $linhas=array();
 foreach ($categorias as $categoria) {
  $linhas[$categoria["indice"].$categoria["nome_cat"]]=array("id_cat" => 
$categoria["id_cat"], "nome_cat" => $categoria["nome_cat"], "icone_cat" => 
$categoria["icone_cat"], "indice" => $categoria["indice"], "familia" => 
$categoria["familia"]);
 }
 ksort($linhas);
 reset($linhas);

Hope help you.

-- 
João Cândido de Souza Neto
Curitiba Online
[EMAIL PROTECTED]
(41) 3324-2294 (41) 9985-6894
http://www.curitibaonline.com.br


""abhishek jain"" <[EMAIL PROTECTED]> escreveu na mensagem 
news:[EMAIL PROTECTED]
> Hi,
> I have a table structure like :
> ID , NAME, PARENT_ID
> 1 , Top , 0
> 2 , Level 1 , 1
> 3 , Level 2 ,2
> 4 , Another Top , 0
>
>
> and so on.
> I wanted to know the topmost cat. if i have the lowest category id ie. 3 
> in
> this case.
> I wanted to get like 3->2->1
> Pl. help me , cn i do this in one query, also i do not know how many
> sublevels are there,
> Thanks,
> Abhishek jain
> 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: How to find the top most member in a hierarchy of subcategories

2006-09-04 Thread Peter Lauri 
MySQL is not recursive. This might help you:

http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html

/Peter

www.lauri.se - personal web site
www.dwsasia.com - corporate web site 


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
abhishek jain
Sent: Monday, September 04, 2006 4:29 PM
To: mysql@lists.mysql.com
Subject: How to find the top most member in a hierarchy of subcategories

Hi,
I have a table structure like :
ID , NAME, PARENT_ID
1 , Top , 0
2 , Level 1 , 1
3 , Level 2 ,2
4 , Another Top , 0


and so on.
I wanted to know the topmost cat. if i have the lowest category id ie. 3 in
this case.
I wanted to get like 3->2->1
Pl. help me , cn i do this in one query, also i do not know how many
sublevels are there,
Thanks,
Abhishek jain


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to find the top most member in a hierarchy of subcategories

2006-09-04 Thread Jo�o C�ndido de Souza Neto 
I´m not quite sure if it could help you, because it´s whole in portuguese, 
but i´ll send you.

My table is called categoria and has the follow structure:

CREATE TABLE `categoria` (
 `id` int(20) NOT NULL auto_increment,
`cat_id` int(20) NOT NULL default '0',
`nome` varchar(50) NOT NULL default '',
`icone` varchar(255) NOT NULL default '',
`cod_shop` varchar(5) NOT NULL default '',
`topo` char(1) NOT NULL default '',
PRIMARY KEY  (`id`)
)

Where the relations each register of the tree are like this:

ID , CATEGORIA, CAT_ID
1 , Top , 0
2 , Level 1 , 1
3 , Level 2 ,2
4 , Another Top , 0

And then i use the follow routine to get the tree:

$sql = "SELECT
   *
  FROM
   categoria
  ORDER BY
   cat_id, nome";
 $con->Query($sql);

 $counter = $con->count;
 $categorias=array();
 for($z=0;$z<$con->count;$z++){
  $con->Seek($z);
  list($id_cat, $cat_id, $nome_cat, $icone_cat) = $con->result;
  $categorias[$id_cat]=array("id_cat" => $id_cat, "cat_id" => $cat_id, 
"nome_cat" => $nome_cat, "icone_cat" => $icone_cat, "familia" => $id_cat, );
  $continua=$cat_id!=null && $cat_id>0;
  if ($continua) {
   $qual=$cat_id;
   $categorias[$id_cat]["indice"]="";
   while ($continua) {

$categorias[$id_cat]["indice"]=$categorias[$qual]["nome_cat"].$categorias[$id_cat]["indice"];
$continua=$categorias[$qual]["cat_id"]!=null && 
$categorias[$qual]["cat_id"]>0;
$tem_pai=$cat_id!=null && $cat_id>0;
if ($tem_pai) $pai=$cat_id;
while ($tem_pai) {
 if (!strpos($categorias[$pai]["familia"],", 
".$categorias[$id_cat]["id_cat"]))
  $categorias[$pai]["familia"].=", ".$categorias[$id_cat]["id_cat"];
 $tem_pai=$categorias[$pai]["cat_id"]!=null && 
$categorias[$pai]["cat_id"]>0;
 $pai=$categorias[$pai]["cat_id"];
}
if ($continua) {
 $qual=$categorias[$qual]["cat_id"];
}
   }
  }
 }
 reset($categorias);
 $linhas=array();
 foreach ($categorias as $categoria) {
  $linhas[$categoria["indice"].$categoria["nome_cat"]]=array("id_cat" => 
$categoria["id_cat"], "nome_cat" => $categoria["nome_cat"], "icone_cat" => 
$categoria["icone_cat"], "indice" => $categoria["indice"], "familia" => 
$categoria["familia"]);
 }
 ksort($linhas);
 reset($linhas);

Hope help you.

-- 
João Cândido de Souza Neto
Curitiba Online
[EMAIL PROTECTED]
(41) 3324-2294 (41) 9985-6894
http://www.curitibaonline.com.br


""abhishek jain"" <[EMAIL PROTECTED]> escreveu na mensagem 
news:[EMAIL PROTECTED]
> Hi,
> I have a table structure like :
> ID , NAME, PARENT_ID
> 1 , Top , 0
> 2 , Level 1 , 1
> 3 , Level 2 ,2
> 4 , Another Top , 0
>
>
> and so on.
> I wanted to know the topmost cat. if i have the lowest category id ie. 3 
> in
> this case.
> I wanted to get like 3->2->1
> Pl. help me , cn i do this in one query, also i do not know how many
> sublevels are there,
> Thanks,
> Abhishek jain
> 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How to find the top most member in a hierarchy of subcategories

2006-09-04 Thread abhishek jain 

Hi,
I have a table structure like :
ID , NAME, PARENT_ID
1 , Top , 0
2 , Level 1 , 1
3 , Level 2 ,2
4 , Another Top , 0


and so on.
I wanted to know the topmost cat. if i have the lowest category id ie. 3 in
this case.
I wanted to get like 3->2->1
Pl. help me , cn i do this in one query, also i do not know how many
sublevels are there,
Thanks,
Abhishek jain