Hi,
In a small part of my database I have two tables as follows:
---------- MySQL dump ----------
Table structure for table 'WMS_Data'
CREATE TABLE WMS_Data (
Data_ID int(11) DEFAULT '' NOT NULL auto_increment,
Booking_ID int(11) DEFAULT '0' NOT NULL ,
Data_Field_1 varchar(100) ,
Data_Field_2 varchar(100) ,
Data_Field_3 varchar(100) ,
Data_Field_4 varchar(100) ,
Data_Field_5 varchar(100) ,
Data_Field_6 varchar(100) ,
Data_Field_7 varchar(100) ,
Data_Field_8 varchar(100) ,
Data_Field_9 varchar(100) ,
Data_Field_10 varchar(100) ,
PRIMARY KEY (Data_ID)
);
Table structure for table 'WMS_Fields'
CREATE TABLE WMS_Fields (
Field_ID int(11) DEFAULT '' NOT NULL auto_increment,
Field_Name varchar(40) DEFAULT '' NOT NULL ,
Project_ID int(11) DEFAULT '0' NOT NULL ,
Work_Type_ID int(11) DEFAULT '0' NOT NULL ,
Data_Field varchar(20) ,
PRIMARY KEY (Field_ID)
);
----------- Dump ends -----------
The purpose of this is for every project created in the database the user can create various data fields and collect data. The data is stored in table WMS_Data and the data fields are named in table WMS_Fields. In this table column Data_Field stores the column name of the corresponding Data_Field in table WMS_Data. How can i create a query that deletes everything from a given column in table WMS_Data, if I only have the Field_ID for table WMS_Fields?
Something like: DELETE FROM WMS_Data D COLUMN WHERE WMS_Fields.Data_Field = D.Column_Name AND WMS_Fields.Field_ID = '$my_variable';
Please excuse the poor SQL, just trying to explain what I am trying to do!
Thanks for your help
