php - mysql different query

Question

I had a category table as follows

id     |    name       |  parent_of    |   created_on
-------+---------------+---------------+---------------------
1      |   Name 1      |  0            |  2013-05-1 00:00:00
-------+---------------+---------------+---------------------
2      |   Name 2      |  0            |  2013-05-1 00:00:00
-------+---------------+---------------+---------------------
3      |   Name 3      |  1            |  2013-05-1 00:00:00
-------+---------------+---------------+---------------------
4      |   Name 4      |  1            |  2013-05-1 00:00:00
-------+---------------+---------------+---------------------
5      |   Name 5      |  3            |  2013-05-1 00:00:00
-------+---------------+---------------+---------------------
6      |   Name 6      |  3            |  2013-05-1 00:00:00
-------+---------------+---------------+---------------------

As in the table some category has child categories.

In this table as follows. Specifying the table id

1
  |--> 3
  |   |--> 5
  |   |--> 6
  |--> 4
2

My question is while adding a product, need to select the category for each product. But only need to show the end category. ie need not showing id 1 and 3 since it is not the end. Only need to show 5,6,4 and 2 ( those are the categories with out any category according the list )

Can any one please help me with the MySQL query for listing the category drop down ?

Thanks in advance

Vinoth Babu · Accepted Answer · 2013-06-03 05:51:15Z

2

I hope that, this is what you want.

SELECT id FROM category where ID NOT IN (SELECT DISTINCT parent_of FROM category) ORDER BY id DESC

answered Jun 3, 2013 at 5:51

Vinoth Babu

6,82510 gold badges40 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ramesh Over a year ago

yes .. that worked .. thanks... but how to make a list as shown ? that tree ? can you please help ?

ramesh Over a year ago

the tree for complete navigation through categories. Like I mentioned in my question

ramesh Over a year ago

yes ... that showing the end category working fine ... but I need to show a tree like above ... can you please help ?

Vinoth Babu Over a year ago

then your have to frame an array like this, array(1=>array(3,4), 2, 3=>array(5,6), 4, 5, 6);

ramesh Over a year ago

stackoverflow.com/questions/16891355/…

piotrm · Accepted Answer · 2013-06-03 06:06:07Z

0

SELECT c.* FROM categories c 
LEFT JOIN categories c1 ON c.id = c1.parent_of
WHERE c1.id IS NULL

edited Jun 3, 2013 at 6:06

answered Jun 3, 2013 at 6:00

piotrm

12.4k5 gold badges33 silver badges28 bronze badges

Collectives™ on Stack Overflow

php - mysql different query

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related