Solved

function / object to update and delete table entries

Posted on 2004-08-22
15
142 Views
Last Modified: 2006-11-17
Short question:

Is there a way to build an object and or function / function list (OR ANY EASIER WAY) that takes the trouble out of writing update and delete scripts for all your different tables as this is especially time consuming when you have a relational data model and entries in other tables may rely on what you are about to change or delete !
0
Comment
Question by:Delacourt
  • 7
  • 3
  • 2
  • +1
15 Comments
 
LVL 1

Author Comment

by:Delacourt
ID: 11865458
database would be mysql !!!
0
 
LVL 1

Author Comment

by:Delacourt
ID: 11865476
i.e. having ten tables, where table 1 relates to table 2, table 2 relates to table 3, table 3 relates to table 4, and so forth (where the table with the lower numbers id is in the next table as a foreign key)

problem, how would i be able to get a message about table10 having something to do with one of the tables with a lower number, before i go ahead and update and delete it, thereby losing what was in table10 ?

is my explanation working here ... ? , pls let me know :)

table1
table2
table3
table4
table5
table6
table7
table8
table9
table10
0
 
LVL 5

Expert Comment

by:Gitcho
ID: 11865553
Are you talking about getting the ID of an SQL insert statement, so you can update the next table with the new ID ? ( mysql_insert(); )

http://ca3.php.net/manual/en/function.mysql-insert-id.php
0
 
LVL 48

Expert Comment

by:hernst42
ID: 11865574
The database can handle all those things if you have support for InnoDB-Tables in Mysql. This is the fastest and error-less way to do it

See http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html and there the part about referencial integrety

example:
CREATE TABLE tab1 (PK int not null auto_increment, Disabled char(1) default 'N' not null,Host varchar (20) null,Id varchar (20) not null,lastlogin int null,Password varchar (40) null, primary key( PK )) TYPE = InnoDB;
CREATE TABLE tab2 (PK int not null auto_increment, FK_tab1 int null,Type char(1) null, INDEX (FK_tab1) , FOREIGN KEY (FK_tab1) REFERENCES tab1(PK) ON DELETE cascade ON UPDATE cascade, primary key( PK )) TYPE = InnoDB;
0
 
LVL 1

Author Comment

by:Delacourt
ID: 11865754
Thanks Guys,

To answer your question Gitcho: nope, I think I would use isset() for that (methinks:)
Looking more at the edit / delete process

Hernst42: Thanks for that, I dont have innoDB tables, and not going to be using them, however, is there another way, other than the tedious coding of all edit delete statements per table, to keep referential integrity of my tables entries without using innoDB ?
0
 
LVL 1

Author Comment

by:Delacourt
ID: 11865758
tabletype: myisam
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 48

Accepted Solution

by:
hernst42 earned 500 total points
ID: 11865848
If the work is not done by the database, you have to do it in your application. So you will have to code the deletion by yourself. Maybe it helps if you store the information about the references in an array e.g.
$riref['referencedtable']['referencedfield']['basetable'] = basefield;
for my example
$riref['tab1']['PK']['tab2'] = FK_tab1;
so tab.PK is used by tab2.FK

When we delete in tab1 we call recdelete('tab1','PK', 'PK=1');

Then write a generic function that deleted all depending records like the following (pseudocode)

function recdelete($table, $field, $condition) {
    global $riref;
    if (isset($riref[$table][$field])) {
        // first the values that exists for this condition need to be known
        SELECT DISTINCT $field FROM $table WHERE $condition;
        $id = array();
        while ($row = mysql_fetch_array()) {
            $id[] = $row[$field];
        }
        if (count($id)) {
            foreach ($riref[$table][$field] as $tabref => $fieldref)) {
                recdelete($tabref, $fieldref, "$fildref in ( ".implode(',', $id). ')';
            }
        }
    }
    DELETE FROM $table WHERE $condition;
}

Updates are much more complicated but may be done the same way.

One additional note:
If one step of that delete or update failes you will get an inconsitend database and an recover-mechanism is very hard to implement (like transactions supported by Innodb).
0
 
LVL 1

Author Comment

by:Delacourt
ID: 11865872
thanks for that, i am going to try and write this using your above suggestions, but was also thinking along the lines of

choosing something to delete or edit, then this would be passed to a page before going ahead with the execution of the code: essentially something like this:

when deleting from table 4:
having a select table10_field_name from table10, table9, table8, table7, table6, table5, table4
where table4.table4id = table5.table4id
and table5.table5id = table6.table5id
and table7.table7id = table8.table7id
and table9.table9id = table10.table9id

1. then if there is a field name, say, you must delete result[table10_field_name] first
2. or say, you are free to delete this item

BUT
what happens one level up, say there is nothing in table10, what about table9 ???

thats what i am getting at: let me know if you think this is feasible at all !

0
 
LVL 1

Author Comment

by:Delacourt
ID: 11865880
i meant in second last line, what happens if there is something in table9 that requires referential integrity

also, how would i go about deleting something from table2 in the same way, would it need to be hardcoded, or could i write some type of function ??

Thanks again for all your help Hernst42

0
 
LVL 5

Expert Comment

by:Gitcho
ID: 11865890
I've never seen a DB schema that required that much logic for deletes.   Are you sure your DB is in 3rd normal ? Unless this is a supremely complicated app, there's not normally that much logic required.  Hard to know though, as not much data provided.
0
 
LVL 1

Author Comment

by:Delacourt
ID: 11865915
Gitcho, just left a comment for you on another question of mine, I have some points for oyu, let me know where to put them up !

Basically, my tables are like this: each with a foreign key from the table above (product owned by suburb - thereby also by country)
also, each table before product also has their own images table for a pic gallery, so, how would i got about it !!! :)

country_table
province_table
city_table
suburb_table
product_table

another thing: each with a pic gallery table (i.e. city_pic_table) would it be better to have one, with Foreign Keys from each of the tables that would require images ?
0
 
LVL 5

Expert Comment

by:Gitcho
ID: 11866117
Awesome ! Thanks Delacourt - I only need a few more points to get my membership !  

Regarding this database, which tables have 1 -> 1 relationships ? 1 -> many ? many -> 1  ?

Depending on your business rules, instead of tying all the foreign keys together in the same table, you may want to set up a lookup table with all your foreign keys that ties these tables together.

Regarding the images table :  " another thing: each with a pic gallery table (i.e. city_pic_table) would it be better to have one, with Foreign Keys from each of the tables that would require images "  ... I would agree.  
0
 
LVL 2

Expert Comment

by:alberthendriks
ID: 11917960
I think you are looking for triggers. They are not yet implemented in mysql but they will be:
http://dev.mysql.com/doc/mysql/en/ANSI_diff_Triggers.html

With triggers, you can define functions (triggers) that are stored somewhere in the database and they are automatically called. I don't know the syntax but it would be something like

TRIGGER mytrigger: ON DELETE FROM table1 DO DELETE FROM table2 WHERE table2.table1_id=table1.id
Then when you execute "DELETE FROM table1 where id=2" entries in the other tables will automatically be deleted too.

You'll still have to write 10 triggers (well maybe they can be put in 1 statement too), but then you can delete from everywhere while deleting only entries from table1.

In Oracle you can define triggers.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now