SQL problem
 

My problem is thus:

I am creating a PHP/MySQL gallery and have Galleries that hold pictures. Galleries can also have sub galleries.

Now I want to be able to delete a Gallery only if it has no pictures in either itself or ANY of its sub Galleries. But Galleries can have any number of sub galleries. So I could have a situation like this:

code:


Top Gallery

|-pic1

|--sub Gallery

   |

   |--sub gallery

      |-pic2

Whereby I don't want to be able to delete "Top Gallery" because then I wouldn't be able to access any of the sub galleries and thus any pictures in the sub gallery. This wouldn't be so bad if it wasn't also possible to have a gallery that only contained a sub-gallery and not pictures.

What I want to find out is
1) Will deleting the Gallery leave me with any "hanging" pictures, and if so, which ones?
2) Will deleting the Gallery leave me with any "hanging" galleries, and if so, which ones?

My DB tables are thus:

code:


CREATE TABLE t_pictures_group (

  id int(11) NOT NULL auto_increment,

  parent int(11) default '0',

  name varchar(20) NOT NULL default '',

  pic_id int(11) default NULL,

  PRIMARY KEY  (id)

)

code:
CREATE TABLE pictures (

  id int(11) NOT NULL auto_increment,

  group_id int(11) default '0',

  name varchar(50) NOT NULL default '',

  width int(11) default NULL,

  height int(11) default NULL,

  filesize int(11) default NULL,

  `type` int(11) NOT NULL default '0',

  hits int(11) NOT NULL default '0',

  added_date datetime NOT NULL default '0000-00-00 00:00:00',

  `status` int(11) NOT NULL default '0',

  PRIMARY KEY  (id)

)

Where t_pictures_group.parent links to t_pictures_group.id to form the tree heirarchy and pictures.group_id links to t_pictures_group.id.

You can ignore t_pictures_group.pic_id, that's for something else altogether.


I don't have any experiance of using tree structures in SQL and want to exaust all options before I start looking at writing a mamoth cursor script.

Any takers?

Re: SQL problem
 

I can't answer ye question.

Quick Question: Where on the server is the pictures being hold?

SQL only handles the information as to where the file is located. You delete the info in the SQL table, it is still on your server.

-Duane

Deleting a picture where the address is known (probably pictures.name) is fairly simple though, Duane -- I think it's the SQL specifically that Phily's wanting help with. Although Phily, I'm not sure why you're using a database for this -- since your schema doesn't (as far as I can tell) allow for membership of multiple groups, couldn't you just use a folder hierarchy and file attributes? The DB is probably faster though, I guess.

My SQL is atrocious, but I think you want to do some kind of recursive query here. No idea how to go about it, though, sorry.

I know just enough SQL to get me into trouble. :p But this caught my eye...

code:
name varchar(50) NOT NULL default '',

Wouldn't this try to set a not-null value to a null default?

I'm sure I could be completely wrong, so maybe someone else could confirm this. And I know this doesn't address your original question, but I wanted to point it out just in case.

NULL is a seperate value to an empty string if I remember correctly, Scor.

NOT NULL meaning you're setting the NULL attributes of a table to NOT NULL

Ah, I see.