[RESOLVED] Delete category only if it doesnt have a subcat

tryingtolearn

Hi all
Im at a loss (Well Im confusing myself)

I have a categories table set up like this
id | title | parent

1 | Blah1 | 0
2 | Blah2 | 0
3 | Blah3 | 0
4 | Blah4 | 0
5 | blah5 | 3
6 | blah6 | 1
7 | blah7 | 1
8 | blah8 | 1

I have an option in an admin area to delete categories
The problem Im running into is that if I delete a category
but another category has the deleted ones id as its parent
It throws things off

How can I delete the category ONLY if it does not have a parent -

Like on the delete category page
List the categories like
Blah1 cannot be deleted (Because blah 6, 7 and 8 are under it)
Blah2 can be deleted
Blah3 CANNOT be deleted (Because Blah 5 is under it)
etc...
Does that make sense?

krotkruton

...it kinda makes sense. If you just want the option of deleting to be enabled or disabled, why don't you change run a query to check to see if it has a parent before you delete? Otherwise, if you run a query to get all the information anyway, do something like this:

<?php
$query = "SELECT * FROM categories";
$result = mysql_query($query, $conn) or die(mysql_error());

while ($row = mysql_fetch_assoc($result)) {
  \\echo id and title
  if ($row['parent'] != 0) {
    echo "Cannot delete";
  } else {
    echo "Can delete";
  }
}
?>

This assumes that if the parent field is 0, the category doesn't have any parents.

I have a feeling that's not what you meant though. If what you really want is to have the option to delete only if the category is not a parent of any other categories (or, it doesn't have any children), then you probably want this for:

<?php
$query = "SELECT c1.*, count(c1.parent) as parent_count FROM categories c1
  LEFT JOIN categories c2 ON c1.id = c2.parent
  GROUP BY c1.id";
$result = mysql_query($query, $conn) or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
  \\echo id and title
  if ($row['parent_count'] != 0) {
    echo "Cannot delete";
  } else {
    echo "Can delete";
  }
}
?>

I haven't tested that, so I'm not completely sure it will work, but I'm sure if you play around with that query and check what results you get, you'll be able to get it working.

tryingtolearn

Thanks for the fast reply

Your first example is exactly why I confused myself
Thats what I was trying because in my head, the logic sounds like what I want to do but it results in telling me not to delete the child

Im working with your second example but it just says that I acnt delete any

so I suppose I will need to change some things around.

tryingtolearn

Here is the problem
I hope you can follow this
I echoed the parent count and got 1 for all categories without subcategories (Not 0)
So I changed

if ($row['parent_count'] != 0)

if ($row['parent_count'] != 1)

But the big problem is
The parent_count returns 1 if there are no subcategories OR if there is 1 subcategory

The only way it returns more than 1 is if there are 2 or more subcategories

Is there a way to get count to return 0 if no subcats???

Am I making sense?

Weedpacket

If you replace "LEFT JOIN" with "INNER JOIN" you won't count rows that have 0 children.

Compare

SELECT c1.id,c2.parent, count(c1.parent) as parent_count FROM categories c1
  LEFT JOIN categories c2 ON c1.id = c2.parent
  GROUP BY c1.id,c2.parent

with

SELECT c1.id,c2.parent, count(c1.parent) as parent_count FROM categories c1
  INNER JOIN categories c2 ON c1.id = c2.parent
  GROUP BY c1.id,c2.parent

Or, simpler,

select distinct c1.id from categories c1, categories c2 where c2.parent=c1.id;

And allow deletion if the id doesn't appear in the list.
In fact

select distinct parent from categories where parent<>0

would do that job.

tryingtolearn

Thanks weedpacket
That worked out just fine!