CODEWITHSUNDEEP
phpsql
koca79331
koca79331

Reputation: 421

SQL get Categories with subcategories

Hello I tried to solve and googling how to sort with SQL categories and subcategories to select html element. I didn't found anythink which should worked for me. I have a table categories and it's looks like

id     name       parent
1      cat1       0
2      sub1       1
3      cat2       0
4      sub2       1

My query looks like this

SELECT `a`.*, `b`.* 
FROM `categories` AS `a`
LEFT JOIN `categories` AS `b` 
    ON `a`.`id` = `b`.`parent`
WHERE `a`.`parent` IS NULL

but it's returning me empty array :-/ please help.

I want result like this:

cat1
   sub1
   sub2
cat2

Upvotes: 3

Views: 2431

Answers (4)

otaviofcs
otaviofcs

Reputation: 785

You can make it on php with ease. In a way that would accomplish easily with the current data model, implementing a recursion in a function, that would receive the parent ID , starting with null.

In plain sql, your problem is not how to retrieve, but how to store your data, first place. I mean, if you want to do it in a single SQL (else you could try a stored procedure).

In plain sql there are many ways to do this. For example, the easiest one to retrieve is the hardest one to store.

Store your parent ID in a string:

Id Name      node
1 Cat1.           1
2 Cat2.           2
3 subcat11.   1.1
4 Cat 3.          3
5 subcat12.   1.2
6 subcat21.   2.1

Then it would be simply:

Select name,ID,node from table order by node

Upvotes: 0

Gustavo Rubio
Gustavo Rubio

Reputation: 10827

Like others have said, I think you should re-think how you are storing your data, or it'll be harder to fetch, that being said:

Assuming you are using MySql:

select
sub.id as id,
case 
    when sub.parent is null then sub.name 
    else concat('    ', sub.name)
end as category_name,
cat.name as subcategory_name,
sub.parent
from categories as cat
right outer join categories as sub
  on cat.id=sub.parent
where cat.parent is null or cat.parent='0'
order by sub.id, sub.name;

And for PostgreSQL:

select
sub.id as id,
case 
    when sub.parent is null then sub.name 
    else '    ' || sub.name
end as category_name,
cat.name as subcategory_name,
sub.parent
from categories as cat
right outer join categories as sub
  on cat.id=sub.parent
where cat.parent is null or cat.parent=0
order by sub.id, sub.name;

Should output something like this:

Sample resultset

That way you can already send the text indented (and you also get the item id) which is what you are trying to fill in the select with I'm guessing. Grab the id and category_name fields from your PHP resultset/array.

Upvotes: 1

Atif Tariq
Atif Tariq

Reputation: 2772

$query = "SELECT a.*, b.* from `categories` as a left join `categories` as b on a.id = b.parent where a.parent=0";
return $db->select($query);

enter image description here

Upvotes: 0

bastiherrmann
bastiherrmann

Reputation: 43

You get an empty array for filtering by 'null'. Change your query to 

"SELECT a.*, b.* from `categories` as a left join `categories` as b on a.id = b.parent where a.parent = 0"

That should do the trick.

Upvotes: 0

Related Questions