SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast ericd's Avatar
    Join Date
    Nov 2000
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a db with a many - many relationship..

    eg

    table subscribers:-

    userID - email

    1 - jonny
    2 - tommy
    3 - linda

    table lists:-

    listID - name

    1 - list1
    2 - list2

    table list_subscribers_xref:-

    listID - userID

    1 - 1 <-jonny is in list1
    2 - 1 <-jonny is in list2
    2 - 2 <-tommy in list 2
    2 - 3 <- linda in list 2


    now if i wanted to delete list1,

    how would i do this, so it deletes the list, the subscriber, and deleted all the xref??

    thanks in advanced

  2. #2
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It'd work the same way you handle your SELECT statements - with a WHERE claused based on the lookup IDs and such.

    For example: you delete a mailing list from the table "lists" with a ListID number of 4...

    Code:
    DELETE FROM lists WHERE ListID='4'
    Then, you could try something like this:

    Code:
    DELETE FROM list_subscribers_xref WHERE ListID='4'
    That is a *VERY* rough example, but you delete the two on the same criteria - I ran into this problem once. It's a bit tricky, but not too tough once you think it through.

  3. #3
    SitePoint Enthusiast ericd's Avatar
    Join Date
    Nov 2000
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey, thanks for the fast reply,,

    what about the actual subscribers, in the subscribers table,

    if list 2 is deleted it may have have 40 subscribers to it, but the subscribers may be subscribed to other lists, so i dont want them to delete only the subscribers that are listed only in that list.

    thanks again

  4. #4
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You're saying you want to delete all subscribers subscribed to ONLY that list, and keep the rest of them?

    If that's so, it might be better just to leave them in there - the extra records shouldn't slow things down much, and it might be tricky to delete them all.

    It might make for some problems if they try to signup for something later though. I'll have to think about this one.

  5. #5
    SitePoint Enthusiast ericd's Avatar
    Join Date
    Nov 2000
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    Thanks, I thought of many ways to do this, but Im affaid it may create some serious flaws in this app, so thats why im leaving this to the experts, thanks for your time.

  6. #6
    SitePoint Evangelist
    Join Date
    May 2000
    Location
    Canada
    Posts
    533
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i cant think of any efficient method of the fly.. since "deleting of lists" doesnt sound like its going to happen everyday .. you could do something like this:

    Code:
    get users in that specific list
    for each user
    {
    	if user is not in any other list
    	{
    		remove user
    	}
    	else
    	{
    		remove user only from xref db
    	}
    }
    cogito, ergo sum

  7. #7
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think this will work...

    This sql will return a result set {list_ID, user_ID, num} where the user is related to one-and-only-one list.
    Code:
    SELECT list_ID, user_ID, COUNT(*) AS num
    FROM list_subscribers_xref
    GROUP BY user_ID
    HAVING num = 1
    Note the value of num should always be 1 for each row of your result set.

    Now you will have a result set which contains a list of all users who subscribe to only one list. In PHP you will need to loop through the result set and check whether the list_ID matches the list you have deleted. If it does you know that you can safely delete the subscriber with that user_ID from your subscribers table.

    If mySQL supported sub-queries you could write an SQL query that automotically did the delete without needing any PHP code in the middle.


Bookmarks

Posting Permissions

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