Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

function / object to update and delete table entries

Posted on 2004-08-22
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 !
Question by:Delacourt
  • 7
  • 3
  • 2
  • +1

Author Comment

ID: 11865458
database would be mysql !!!

Author Comment

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 :)


Expert Comment

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(); )

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 48

Expert Comment

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

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;

Author Comment

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 ?

Author Comment

ID: 11865758
tabletype: myisam
LVL 48

Accepted Solution

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).

Author Comment

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

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 !


Author Comment

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


Expert Comment

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.

Author Comment

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 !!! :)


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 ?

Expert Comment

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.  

Expert Comment

ID: 11917960
I think you are looking for triggers. They are not yet implemented in mysql but they will be:

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.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PHP Installer 5 37
Help with PHP 13 27
simple html dom php accessing table/cell  values 4 18
MSSQL - Lock Row from reading by other programs 9 35
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

839 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