Get half tree of category : Without using join query in mySql

Question

I want to get Half Tree of category from Category table.

mySql code:

CREATE TABLE IF NOT EXISTS `category` (
  `cat_id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL,
  `cat_name` varchar(255) NOT NULL,
  PRIMARY KEY (`cat_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

INSERT INTO `category` (`cat_id`, `parent_id`, `cat_name`) VALUES
(1, 0, 'sadasd'),
(2, 0, 'fsdfsadf'),
(3, 1, 'fasdfasd'),
(4, 1, 'ewrwer'),
(5, 4, 'cxzxczxc'),
(6, 5, 'sadasdqwqwqw'),
(7, 2, 'sadasd');

Problem:

I want to get hierarchy structure of category where cat_id is 1.

Without using join query, use only 1 query, Without any php loops

If you want to change database structure then you can change it.

I want output like this:

cat_id 1
 - cat_id 3
 - cat_id 4
   - cat_id 5
     - cat_id 6

Very specific (and odd) requirements. Can we use the letter 'e'? What about words of 3 syllables? — Strawberry, Aug 02 '15 at 09:35

score 1 · Accepted Answer · answered Aug 03 '15 at 16:52

After a very hard work finaly get solution of this question.

mySql Code:

CREATE TABLE IF NOT EXISTS `category` (
  `cat_id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL,
  `cat_name` varchar(255) NOT NULL,
  `parentids` varchar(255) NOT NULL,
  PRIMARY KEY (`cat_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

INSERT INTO `category` (`cat_id`, `parent_id`, `cat_name`, `parentids`) VALUES
(1, 0, 'sadasd', ''),
(2, 0, 'fsdfsadf', ''),
(3, 1, 'fasdfasd', '|1|'),
(4, 1, 'ewrwer', '|1|'),
(5, 4, 'cxzxczxc', '|1|,|4|'),
(6, 5, 'sadasdqwqwqw', '|1|,|4|,|5|'),
(7, 2, 'sadasd', '|2|');

After create this table fire above sql query.

select * from category where parentids LIKE "%|1|%";

score 0 · Answer 2 · edited May 23 '17 at 12:19

0

with parent/child (cat_id,parent_id) in SQL not possible. You have to do this with a recursive function in your Framework/Code-Area.

If you want to do this with SQL, you can try the "Nested Set Model":

php / Mysql best tree structure

edited May 23 '17 at 12:19

Community

1
1

answered Aug 02 '15 at 09:51

Henry

597
4
12

sry sir, can't use recursive function. – Dr.Tricker Aug 02 '15 at 09:59
so you going to use Nested-Sets? – Henry Aug 02 '15 at 10:21

Get half tree of category : Without using join query in mySql

mySql code:

Problem:

2 Answers2