relations :/

[atmorell]

Hello.

I have created 3 tables like this: (see below)
cms_form(table1) can have many cms_form_fields(table2) and one cms_form_field can have many cms_form_attribues. (table3) How do I delete e.g form1 + all form_fields and form_fields attribtues that belong to form1. The webserver is hoted at an ISP so I can't change the configuration of php/mysql/apache/phpmyadmin.

Best regards
Asbjørn.

CREATE TABLE object.cms_forms (
form_id int( 11 ) NOT NULL AUTO_INCREMENT ,
object_element_id int( 11 ) NOT NULL ,
form_name varchar( 20 ) COLLATE latin1_danish_ci NOT NULL ,
form_action varchar( 20 ) COLLATE latin1_danish_ci NOT NULL ,
form_target varchar( 20 ) COLLATE latin1_danish_ci NOT NULL ,
form_div varchar( 20 ) COLLATE latin1_danish_ci NOT NULL ,
form_class varchar( 20 ) COLLATE latin1_danish_ci NOT NULL ,
PRIMARY KEY ( form_id )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 COLLATE = latin1_danish_ci;

CREATE TABLE object.cms_form_field (
form_field_id int( 11 ) NOT NULL AUTO_INCREMENT ,
form_id int( 11 ) NOT NULL ,
form_field_type varchar( 20 ) COLLATE latin1_danish_ci NOT NULL ,
PRIMARY KEY ( form_field_id )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 COLLATE = latin1_danish_ci;

CREATE TABLE object.cms_form_attributes (
attribute_id int( 11 ) NOT NULL AUTO_INCREMENT ,
form_field_id int( 11 ) default NULL ,
attribute_type varchar( 20 ) COLLATE latin1_danish_ci default NULL ,
attribute varchar( 20 ) COLLATE latin1_danish_ci default NULL ,
PRIMARY KEY ( attribute_id )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 COLLATE = latin1_danish_ci;

[bogu]

First of all u have your ids from cms_form that needs to be deleted

Ex. $ids1 = '1,2,3';

The select all the ids that are linked to cms_form_field

SELECT form_field_id FROM cms_form_field
WHERE form_id IN ($ids1)

and u have all the ids that need to be deleted from cms_form_field

Ex. $ids2 = '1,2,3,...,n';

Then u do the same thing for table cms_form_attributes using the $ids2

Ex. $ids3 = '1,2,3,...,m';

After u find all the ids that u need to delete

DELETE FROM cms_form WHERE form_id IN ($ids1)
DELETE FROM cms_form_field WHERE form_id IN ($ids2)
DELETE FROM cms_form_attributes WHERE form_id IN ($ids3)

[atmorell]

I would like to do the whole thing in one query! I know it can be done: http://phpbuilder.com/board/showthread.php?t=10307473&highlight=delete+relation But I can't figure out how the syntax should be.

Best regards.
Asbjørn Morell.

[bogu]

Hmmm, let me try to build one ...

[bogu]

Try this one, delete table1_id = 1 and all the records from the other tables ...

DELETE table1, table2, table3 
FROM table1 
LEFT JOIN table2 USING(table1_id) 
LEFT JOIN table3 USING(table2_id) 
WHERE table1.table1_id IN (1)

This should apply to u:

DELETE cms_forms, cms_form_field, cms_form_attributes 
FROM cms_forms 
LEFT JOIN cms_form_field USING(form_id) 
LEFT JOIN cms_form_attributes USING(form_field_id) 
WHERE cms_forms.form_id IN (1,2)