Re: category with parentid
Hello ts. Tanaka, I've tried your way and succeed. Thank you. But I want to ask your help again what if the result will be like this: A. Cat A - Subcat A - Subcat A 2. CatB - Subcat B possible by using br or div which the previous result is Cat A - SubcatA, SubcatA Cat B - SubcatB thank you __ Information from ESET NOD32 Antivirus, version of virus signature database 7179 (20120529) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: category with parentid
and also id (either cat or subcat) can be called - Original Message - From: HaidarPesebe haidarpes...@gmail.com To: MySQL Lists mysql@lists.mysql.com Sent: Wednesday, May 30, 2012 2:31 PM Subject: Re: category with parentid Hello ts. Tanaka, I've tried your way and succeed. Thank you. But I want to ask your help again what if the result will be like this: A. Cat A - Subcat A - Subcat A 2. CatB - Subcat B possible by using br or div which the previous result is Cat A - SubcatA, SubcatA Cat B - SubcatB thank you __ Information from ESET NOD32 Antivirus, version of virus signature database 7179 (20120529) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __ Information from ESET NOD32 Antivirus, version of virus signature database 7179 (20120529) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: category with parentid
Pesebe-san, I'm sorry to i'm not clear what do you want to do. do you mean like this? (exampled by Perl) #--- use DBI; $cn = DBI-connect(DBI:mysql:test,root,); $st = $cn-prepare(SELECT t1.name AS cat,GROUP_CONCAT(t2.name) AS subcat FROM `table` AS t1 LEFT JOIN `table` AS t2 ON t1.id = t2.parentid WHERE t1.parentid = 0 GROUP BY cat;); $st-execute; while ($rs = $st-fetchrow_hashref) { print $rs-{cat},br\n; @buff = split(/,/,$rs-{subcat}); foreach $i (@buff) { print -,$i,br\n; } } #--- regards, ts. tanaka// 2012/5/30 HaidarPesebe haidarpes...@gmail.com: and also id (either cat or subcat) can be called - Original Message - From: HaidarPesebe haidarpes...@gmail.com To: MySQL Lists mysql@lists.mysql.com Sent: Wednesday, May 30, 2012 2:31 PM Subject: Re: category with parentid Hello ts. Tanaka, I've tried your way and succeed. Thank you. But I want to ask your help again what if the result will be like this: A. Cat A - Subcat A - Subcat A 2. CatB - Subcat B possible by using br or div which the previous result is Cat A - SubcatA, SubcatA Cat B - SubcatB thank you __ Information from ESET NOD32 Antivirus, version of virus signature database 7179 (20120529) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __ Information from ESET NOD32 Antivirus, version of virus signature database 7179 (20120529) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: category with parentid
Hello Pesebe-san, how about this? SELECT t1.name AS cat, GROUP_CONCAT(t2.name) AS subcat FROM table AS t1 LEFT JOIN table AS t2 ON t1.id = t2.parentid WHERE t1.parentid = 0 GROUP BY cat; +---+---+ | cat | subcat| +---+---+ | cat A | subcat A,subcat A | | cat B | subcat B | +---+---+ 2 rows in set (0.00 sec) regards, ts. tanaka// 2012/5/28 HaidarPesebe haidarpes...@gmail.com: Thanks for the information; I tried to call the database with something like this; select id,name from TABLE WHERE parentid='0' and a second call to the same table as this; select id,name from TABLE WHERE parentid='$id' (this $id is the result of calling the first call TABLE) and successfully. or are there other examples that simple? Thanks __ Information from ESET NOD32 Antivirus, version of virus signature database 7173 (20120527) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: category with parentid
2012/05/28 12:54 +0700, HaidarPesebe select id,name from TABLE WHERE parentid='0' and a second call to the same table as this; select id,name from TABLE WHERE parentid='$id' (this $id is the result of calling the first call TABLE) Others have said, and I agree, you have a graph, and some graph algorithm is needed for the indenting that you seek. You use some other programming language than MySQL for making up the queries. In that language keep an array of arrays; your first query, select id,name from TABLE WHERE parentid='0' generates more id's. Make an array of them, and that array is the other array s first element. Make a new query, select id,name from TABLE WHERE parentid in ($formerIDs), and so on, each $formerID all the id's in the earlier query returned, until no ID is returned. Then use the array-array, and hope that each id only once appears in it: if parentid=0, indent 0; if parentid found in first element, indent 1; if parentid found in second element, indent 2; ... You can do this with a generated CASE: CASE WHEN parentid = 0 THEN 0 WHEN parentid IN (first element) THEN 1 WHEN parentid IN (second element) THEN 2 .. END . This is in a REPEAT concatenated to your name, or LPAD with your name one of the arguments, say SELECT id, REPEAT(' ', CASE WHEN parentid = 0 THEN 0 WHEN parentid IN (first element) THEN 1 WHEN parentid IN (second element) THEN 2 .. END) || name, parentid FROM TABLE . If it is guaranteed that for each parentid there is at most one id, then all this becomes much simpler: it is an array of id's; the variable-comparison CASE is used. ** Scanned by MailScan AntiVirus and Content Security Software. Visit http://www.escanav.com for more info on eScan and X-Spam. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: category with parentid
O, and the algorithm that I described only then works when the graph is cycle-free. Otherwise it forever runs. ** Scanned by MailScan AntiVirus and Content Security Software. Visit http://www.escanav.com for more info on eScan and X-Spam. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: category with parentid
Thanks for the information; I tried to call the database with something like this; select id,name from TABLE WHERE parentid='0' and a second call to the same table as this; select id,name from TABLE WHERE parentid='$id' (this $id is the result of calling the first call TABLE) and successfully. or are there other examples that simple? Thanks __ Information from ESET NOD32 Antivirus, version of virus signature database 7173 (20120527) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
category with parentid
I have a database for the category id | name | parentid -- 1 | cat A | 0 2 | cat B | 0 3 | subcat A | 1 4 | subcat A | 1 5 | subncat B | 2 - I want to display the result like this: 1. Cat A - Subcat A - Subcat A 2. CatB - Subcat B ask for help for this. Thanks for the help __ Information from ESET NOD32 Antivirus, version of virus signature database 7165 (20120524) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com
Re: category with parentid
2012/05/25 14:57 +0700, HaidarPesebe id | name | parentid -- 1 | cat A | 0 2 | cat B | 0 3 | subcat A | 1 4 | subcat A | 1 5 | subncat B | 2 - I want to display the result like this: 1. Cat A - Subcat A - Subcat A 2. CatB - Subcat B If the degree of subordination is a number in your record, try function REPEAT (output concatenated to A or B), or LPAD. ** Scanned by MailScan AntiVirus and Content Security Software. Visit http://www.escanav.com for more info on eScan and X-Spam. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: category with parentid
You have a typical hierarchical data issue, can be solved with a graph. Theory review: http://www.algorithmist.com/index.php/Graph_data_structures Mysql and PHP implementation: http://www.phpro.org/tutorials/Managing-Hierarchical-Data-with-PHP-and-MySQL.html
Re: category with parentid
On 2012-05-25 8:35 AM, h...@tbbs.net wrote: 2012/05/25 14:57 +0700, HaidarPesebe id | name | parentid -- 1 | cat A | 0 2 | cat B | 0 3 | subcat A | 1 4 | subcat A | 1 5 | subncat B | 2 - I want to display the result like this: 1. Cat A - Subcat A - Subcat A 2. CatB - Subcat B It's a graph! See http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html PB - If the degree of subordination is a number in your record, try function REPEAT (output concatenated to A or B), or LPAD. ** Scanned by MailScan AntiVirus and Content Security Software. Visit http://www.escanav.com for more info on eScan and X-Spam. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: category with parentid
2012/05/25 10:07 -0500, Andrés Tello You have a typical hierarchical data issue, can be solved with a graph. Theory review: http://www.algorithmist.com/index.php/Graph_data_structures Mysql and PHP implementation: http://www.phpro.org/tutorials/Managing-Hierarchical-Data-with-PHP-and-MySQL.html 2012/05/25 11:31 -0500, Peter Brawley It's a graph! See http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html Of course--but does Haidar Pasabe want to see that? ** Scanned by MailScan AntiVirus and Content Security Software. Visit http://www.escanav.com for more info on eScan and X-Spam. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql