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

score 2 · Accepted Answer · answered Jun 03 '13 at 05:51

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 03 '13 at 05:51

Vinoth Babu

6,724
10
36
55

yes .. that worked .. thanks... but how to make a list as shown ? that tree ? can you please help ? – ramesh Jun 03 '13 at 06:07
the tree for complete navigation through categories. Like I mentioned in my question – ramesh Jun 03 '13 at 06:33
yes ... that showing the end category working fine ... but I need to show a tree like above ... can you please help ? – ramesh Jun 03 '13 at 06:41
1

then your have to frame an array like this, array(1=>array(3,4), 2, 3=>array(5,6), 4, 5, 6); – Vinoth Babu Jun 03 '13 at 06:44
http://stackoverflow.com/questions/16891355/php-mysql-create-a-tree-view-from-product-table – ramesh Jun 03 '13 at 06:48

piotrm · Answer 2 · 2013-06-03T06:06:07.807

0

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

edited Jun 03 '13 at 06:06

answered Jun 03 '13 at 06:00

piotrm

12,038
4
31
28

php - mysql different query

2 Answers2