Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004

    Unanswered: Problem Deleting by Foreign ID

    I'm building a simple photo gallery type application, and have the current table structure:

    topcat (this is the top level image category)
    >subcat1 (subcategory1 - referenced to topcat by foreign id)
    >subcat2 (subcategory2 - referenced to subcat1 by foreign id)
    >imageattributes (contains actual images - links back via foreign id to EITHER subcat1 or subcat2)

    here is a link to the sql file that will replicate the exact DB i'm working with:

    Now on to my 2 questions - I'm working on a tool that will allow her to delete a top category and EVERYTHING under it, or a subcat1 category and everything under that...and so on down to individual image level

    Right now i'm doing a SELECT rather than a delete, for testing purposes, and so far I have this:

    select topcat.*,subcat1.*,subcat2.*,imageattributes.* from topcat,subcat1,subcat2,imageattributes where = 1
    AND = subcat1.topcat_id
    AND = subcat2.subcat1_id
    AND (
    (imageattributes.subcat1entry = 1 AND imageattributes.subcat1or2_id =
    (imageattributes.subcat2entry = 1 AND imageattributes.subcat1or2_id =

    When run against the exact database i currently have set up in mysql control center, this seems to do pretty much what i want - however if you run this query:

    select topcat.*,subcat1.*,subcat2.* from topcat,subcat1,subcat2 where = 1
    AND = subcat1.topcat_id
    AND = subcat2.subcat1_id

    Column #8, Row #2 has a name value of Tindouf(a city in algeria) - this record wasn't included in the first query

    I'm pretty sure i know WHY it wasn't included (the last AND condition from the first sql statement exludes it b/c there are no actual picture files for that category), but if someone deletes a top level category and everything under it, i want the "Tindouf" row to be deleted as well - In other words, if you delete the top level category Africa, ALL things that fall udner Africa should be deleted, whether they contain image data or not....what do i need to change in order to accomplish this?

    Question #2: As a side note, I'd also like to be able to somehow collect all the image names targeted in the delete sql statement...these are image paths to actual files on the web server, so it would be nice if i could build a quick array of all the image names fixing to be deleted AS i'm in the process of deleting them - so i could go back and delete them off the server afterwards - any way to incorporate that into one sql statement?

    sorry for such a long post - any help is greately appreciated

  2. #2
    Join Date
    Oct 2004
    just wanted to give an update - looks like the problem is fixed - a moderator from another forum gave this answer which works:
    I think what you need is a LEFT JOIN:

    Something like this (untested):

    select topcat.*, subcat1.*,subcat2.*,imageattributes.*
    from topcat
    LEFT JOIN subcat1
    ON = subcat1.topcat_id
    LEFT JOIN subcat2
    ON = subcat2.subcat1_id
    LEFT JOIN imageattributes
    ON (imageattributes.subcat1entry = 1 AND imageattributes.subcat1or2_id =
    (imageattributes.subcat2entry = 1 AND imageattributes.subcat1or2_id =
    where = 1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts