Professional Web Applications Themes

SQL statments advice - PHP Development

Hi all I need some advise on how to approach this, I am building a site that handles images, blocks of images (profiles) and assigning profiles to banks. I can't change the structure of any of the tables (third party connecting to them) I'm trying to work out the best way to achieve the following so open to any ideas anybody may have 1) If the user deletes an images from the 'advertlibrary' table, it will delete the file from the server, and delete that entry from the table by using the 'filename' as the pointer (the filename is always ...

  1. #1

    Default SQL statments advice

    Hi all



    I need some advise on how to approach this, I am building a site that
    handles images, blocks of images (profiles) and assigning profiles to banks.
    I can't change the structure of any of the tables (third party connecting to
    them)



    I'm trying to work out the best way to achieve the following so open to any
    ideas anybody may have



    1) If the user deletes an images from the 'advertlibrary' table, it will
    delete the file from the server, and delete that entry from the table by
    using the 'filename' as the pointer (the filename is always unique). That I
    can do, now what I need to work out is how to delete all references to this
    image from the 'profiles' table.

    In the 'profiles' table there are 60 fields, filename1 description1,
    supplier1 . filename20 description20, supplier20, I need to scan though the
    table and look the 'filename', if I found the filename in say Filname4 and
    Filname8 then I need to do the following:
    filename4 = null
    description4 = null
    supplier4 = null,

    filename8 = null
    description8 = null
    supplier8 = null,



    So it now removes references to that filename, but I have to do this for
    every entry in the 'profiles' table that matches the filename.



    2) Renaming a profile: Each profile has a name (always unique for the user)
    If the user wants to rename it, I need to run a straight forward update to
    change the name on the 'profiles' table. Now I need update any references to
    the old name and update it in the 'conf' table where is belongs to that
    user. In the 'conf' table there are 24 fields numbered bank1 - bank24 to I
    need to say something like



    if bank1 = old name and user = x then update to new name, but got to run
    this on all 24 banks and all entries in the 'conf' table



    3) Removing a profile: Similar to deleting an image, run a deleted on the
    'profiles' table now find any references the profile name in the 'conf'
    table and remove it



    if bank1 = profile name and user = x then set bank1 to null, once again need
    to run this on all 24 banks and all entries in the 'conf' table




    There maybe a quick way of doing this, but there again maybe not, so any
    suggestions would be great



    Thanks



    Brian




    Brian Guest

  2. #2

    Default Re: SQL statments advice

    Brian wrote:
     

    Read about foreign kyes
    Kleist Guest

  3. #3

    Default Re: SQL statments advice

    >> 1) If the user deletes an images from the 'advertlibrary' table, it will 
    >
    > Read about foreign kyes[/ref]

    foreign keys in MySQL?

    Brian


    Brian Guest

  4. #4

    Default Re: SQL statments advice

    Brian wrote: 
    >>
    >>Read about foreign kyes[/ref]
    >
    >
    > foreign keys in MySQL?[/ref]

    Yes supported in InnoDBs. You should have a look at CASCADE ...

    Regards
    Stefan
     
    Stefan Guest

  5. #5

    Default Re: SQL statments advice


    Stefan wrote:
     
    >>
    >> foreign keys in MySQL?[/ref]
    >
    > Yes supported in InnoDBs. You should have a look at CASCADE ...[/ref]


    I might be missing something here, but I have done the following though a
    PHPMyAdmin
    on my server.

    ALTER TABLE advertlibrary TYPE = InnoDB
    ALTER TABLE profiles TYPE = InnoDB
    (can't use ENGINE = INNODB as
    ALTER TABLE advertlibrary ADD FOREIGN KEY (filename) REFERENCES profiles
    (filename1)

    No errors came back

    then I ran

    DELETE FROM advertlibrary WHERE filename = '1122246317.jpg'

    It deleted it from advertlibrary table but not form the profiles table, have
    i missed something here?

    and I tried

    ALTER TABLE t2sadvertlibrary ADD FOREIGN KEY (filename) REFERENCES
    t2sprofiles (filename1) ON DELETE CASCADE

    then

    DELETE FROM advertlibrary WHERE filename = '1122246317.jpg'

    Still did not work

    Brian



    Brian Guest

  6. #6

    Default Re: SQL statments advice


    "Brian" <ab.c> wrote in message
    news:WEeGe.3066$ntli.net... 
    >>
    >> Yes supported in InnoDBs. You should have a look at CASCADE ...[/ref]
    >
    >
    > I might be missing something here, but I have done the following though a
    > PHPMyAdmin
    > on my server.
    >
    > ALTER TABLE advertlibrary TYPE = InnoDB
    > ALTER TABLE profiles TYPE = InnoDB
    > (can't use ENGINE = INNODB as
    > ALTER TABLE advertlibrary ADD FOREIGN KEY (filename) REFERENCES profiles
    > (filename1)
    >
    > No errors came back
    >
    > then I ran
    >
    > DELETE FROM advertlibrary WHERE filename = '1122246317.jpg'
    >
    > It deleted it from advertlibrary table but not form the profiles table,
    > have i missed something here?
    >
    > and I tried
    >
    > ALTER TABLE t2sadvertlibrary ADD FOREIGN KEY (filename) REFERENCES
    > t2sprofiles (filename1) ON DELETE CASCADE
    >
    > then
    >
    > DELETE FROM advertlibrary WHERE filename = '1122246317.jpg'
    >
    > Still did not work[/ref]

    I found just found out that the tables types are MyISAM I have tried
    running a alter statement to
    change them to InnoDB and tried creating a test table and setting the type
    to InnoDB at the time
    of creation, but has not worked.
    Is this my hosting supplier that is blocking this or not supporting it?

    Brian



    Brian Guest

  7. #7

    Default Re: SQL statments advice

    Brian wrote: 
    >>
    >>
    >>I might be missing something here, but I have done the following though a
    >>PHPMyAdmin
    >>on my server.
    >>
    >>ALTER TABLE advertlibrary TYPE = InnoDB
    >>ALTER TABLE profiles TYPE = InnoDB
    >>(can't use ENGINE = INNODB as
    >>ALTER TABLE advertlibrary ADD FOREIGN KEY (filename) REFERENCES profiles
    >>(filename1)
    >>
    >>No errors came back
    >>
    >>then I ran
    >>
    >>DELETE FROM advertlibrary WHERE filename = '1122246317.jpg'
    >>
    >>It deleted it from advertlibrary table but not form the profiles table,
    >>have i missed something here?
    >>
    >>and I tried
    >>
    >>ALTER TABLE t2sadvertlibrary ADD FOREIGN KEY (filename) REFERENCES
    >>t2sprofiles (filename1) ON DELETE CASCADE
    >>
    >>then
    >>
    >>DELETE FROM advertlibrary WHERE filename = '1122246317.jpg'
    >>
    >>Still did not work[/ref]
    >
    >
    > I found just found out that the tables types are MyISAM I have tried
    > running a alter statement to
    > change them to InnoDB and tried creating a test table and setting the type
    > to InnoDB at the time
    > of creation, but has not worked.
    > Is this my hosting supplier that is blocking this or not supporting it?[/ref]

    Yes maybe. Ask him ;)

    Regards
    Stefan
     
    Stefan Guest

  8. #8

    Default Re: SQL statments advice

    Brian wrote: 
    >>
    >>Yes supported in InnoDBs. You should have a look at CASCADE ...[/ref]
    >
    >
    >
    > I might be missing something here, but I have done the following though a
    > PHPMyAdmin
    > on my server.
    >
    > ALTER TABLE advertlibrary TYPE = InnoDB
    > ALTER TABLE profiles TYPE = InnoDB
    > (can't use ENGINE = INNODB as
    > ALTER TABLE advertlibrary ADD FOREIGN KEY (filename) REFERENCES profiles
    > (filename1)
    >
    > No errors came back
    >[/ref]

    Thats fine, since myISAM tables just ignore FOREIGN KEY declarations and don't use them
     

    Yes you have had to add a ON DELETE CASCADE statement (see the manual), but it won't work
    on myISAM either.
     

    Oh ok, you got it ;)
     

    As I said, its because of the myISAM tables
     
    Stefan Guest

  9. #9

    Default Re: SQL statments advice

    Brian wrote: 
    >>
    >>
    >>I might be missing something here, but I have done the following though a
    >>PHPMyAdmin
    >>on my server.
    >>
    >>ALTER TABLE advertlibrary TYPE = InnoDB
    >>ALTER TABLE profiles TYPE = InnoDB
    >>(can't use ENGINE = INNODB as
    >>ALTER TABLE advertlibrary ADD FOREIGN KEY (filename) REFERENCES profiles
    >>(filename1)[/ref][/ref]

    As far as I remeber InnoDB engine is not supported before
    some (I don`t remember exactly) version of MySQL.
    Kleist Guest

  10. #10

    Default Re: SQL statments advice


    "Stefan Rybacki" <net> wrote in message
    news:net... 
    >>
    >>
    >>
    >> I might be missing something here, but I have done the following though a
    >> PHPMyAdmin
    >> on my server.
    >>
    >> ALTER TABLE advertlibrary TYPE = InnoDB
    >> ALTER TABLE profiles TYPE = InnoDB
    >> (can't use ENGINE = INNODB as
    >> ALTER TABLE advertlibrary ADD FOREIGN KEY (filename) REFERENCES profiles
    >> (filename1)
    >>
    >> No errors came back
    >>[/ref]
    >
    > Thats fine, since myISAM tables just ignore FOREIGN KEY declarations and
    > don't use them

    >
    > Yes you have had to add a ON DELETE CASCADE statement (see the manual),
    > but it won't work on myISAM either.

    >
    > Oh ok, you got it ;)

    >
    > As I said, its because of the myISAM tables
    > [/ref]
    I have just heard from my hosting company, and because it's a sheared server
    they
    do not support INNODB, so I'm back to the beginning, what's the best way to
    do what
    I need 2 do on a myISAM table?
    Once again, thanks for your help and still open to suggestions

    Brian



    Brian Guest

  11. #11

    Default Re: SQL statments advice

    Brian wrote: 
    >>
    >>Thats fine, since myISAM tables just ignore FOREIGN KEY declarations and
    >>don't use them
    >>
    >> 
    >>
    >>Yes you have had to add a ON DELETE CASCADE statement (see the manual),
    >>but it won't work on myISAM either.
    >>
    >> 
    >>
    >>Oh ok, you got it ;)
    >>
    >> 
    >>
    >>As I said, its because of the myISAM tables
    >>
    >> [/ref]
    >
    > I have just heard from my hosting company, and because it's a sheared server
    > they
    > do not support INNODB, so I'm back to the beginning, what's the best way to
    > do what
    > I need 2 do on a myISAM table?
    > Once again, thanks for your help and still open to suggestions
    >
    > Brian
    >
    >
    >[/ref]
    Well it seems you need to keep track what you delete, rename and what you need to alter to
    stay consistent in your php script.

    Like create an array of tables that needs to be altered after deleteing a record.

    $ondelete=array('table1','table2','table3');

    so when you delete from the main table

    DELETE FROM maintable WHERE id=10

    you just run thru the array and delete each record that belongs to the record (10) of the
    maintable

    foreach($ondelete as $v) {
    $sql="DELETE FROM $v WHERE id=$id";
    mysql_query($sql);
    }

    Same for the other cases (you could centralize it in an own include file, where you just
    provide a function for delete, rename that does all the work for you)

    Regards
    Stefan
    Stefan Guest

Similar Threads

  1. Need Advice .. please!
    By MaiCans in forum Coldfusion Flash Integration
    Replies: 1
    Last Post: December 4th, 02:35 PM
  2. SQL statments that don,t output correctly
    By JanyerCFML in forum Coldfusion Database Access
    Replies: 4
    Last Post: April 19th, 04:41 PM
  3. Flash Forms with cfif statments?
    By webcommunic8or in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: February 18th, 02:29 AM
  4. If...Then statments
    By DataGrid in forum ASP.NET Data Grid Control
    Replies: 5
    Last Post: July 10th, 06:56 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139