Category no parent with children yes parent without children

Databases
1

Hi,
(I’m sorry for the summary but I’ve not other idea)
I’ve this table with a few data example


CREATE TABLE IF NOT EXISTS `dc_category` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) unsigned DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text,
  `ico` varchar(255) DEFAULT NULL,
  `published` int(10) NOT NULL,
  `highlighted` enum('0','1') NOT NULL DEFAULT '0' COMMENT '0 no, 1 yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_dc_category_title` (`title`),
  UNIQUE KEY `unique_dc_category_slug` (`slug`),
  KEY `fk_dc_category` (`parent_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

--
-- Dumping data for table `dc_category`
--

INSERT INTO `dc_category` (`id`, `parent_id`, `title`, `slug`, `description`, `ico`, `published`, `highlighted`) VALUES
(5, NULL, 'Storage devices', 'storage-devices', NULL, NULL, 1318958095, '0'),
(8, 5, 'Floppy disk', 'floppy-disk', NULL, NULL, 1318958139, '0'),
(10, 5, 'Hard disk', 'hard-disk', NULL, NULL, 1318958177, '0'),
(11, 5, 'Usb key', 'usb-key', NULL, NULL, 1318958210, '0'),
(19, NULL, 'Monitors', 'monitors', NULL, NULL, 1234567890, '0');

ALTER TABLE `dc_category`
  ADD CONSTRAINT `fk_dc_category` FOREIGN KEY (`parent_id`) REFERENCES `dc_category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

I’ve to retrieve
Storage devices > Floppy disk
Storage devices > Hard disk
Storage devices > Usb key
Monitors

not Storage devices

with a query like this (my 5 cents :slight_smile: )


SELECT `c`.`id`, `c`.`title` FROM `dc_category` AS `c` 
LEFT JOIN `dc_category` AS `cc` ON cc.id = c.parent_id ORDER BY coalesce(c.parent_id, c.id), c.id ASC

I’ve to use it to build a select to insert items
so no item in the parent (in the menu a parent category with children is unclickabled)

Can you help me, please ?

2

two tips:

first, leave off those ugly, useless backticks – they are needed only if the table or column name contains a special character or space, or if it’s a reserved word, and if you have a table or column name like that, rename it so that you don’t need the backticks

second, learn how to use indents and new lines to make your sql a lot more readable

SELECT root.id
     , root.title 
     , node.id
     , node.title
  FROM dc_category AS root
LEFT OUTER
  JOIN dc_category AS node 
    ON node.parent_id = root.id 
 WHERE root.parent_id IS NULL
ORDER 
    BY root.id
     , node.id

:slight_smile:

3

Yes, sir !

btw thanks again :slight_smile:

4

Sorry to bother you again but I’ve an other problem how can I get the breadcrumbs by id ?
like
Storage devices (if it’s a top category)
Storage devices > Floppy disk (if it’s a children category)
I ended up with this simple query but of course
it works only for children category


SELECT root.id, 
  root.title, 
  node.id, 
  node.title
  FROM dc_category AS root
LEFT OUTER
  JOIN dc_category AS node 
  ON node.parent_id = root.id
WHERE node.id = 'myid'

:frowning:

5

you have to go up the tree, not down

SELECT node.id
     , node.title
     , root.id
     , root.title 
  FROM dc_category AS node
LEFT OUTER
  JOIN dc_category AS root 
    ON root.id = node.parent_id
 WHERE node.id = $myid