Update Filed with data from another field

[n00b-init]

Hi

 

I am a serious noob, am reading all the oriellly stuff but after days of trying to figure out what to do, I am here asking for some help, took me 3 days to figure out how to create a table but good news I now have one.

 

ok here is my problem, I have a Table with 3 fields in them, I need to create a script or command that will do 2 things, create new fields based off one of the current fields, also it needs to monitor these new fields and take the value from one field and add it to another, and update itself when new data is entered.

 

example

 

Table = issues

Field 1 = ID

field 2 = issuenum

field 3 = time

field 4 = totaltime

 

ok issuenumb corresponds to an issue number/work order number, the time field and total time fields need to correlate to each issue number

 

so like if

 

issuenum = 23 then sql needs to create a field below that field named time and totaltime then when the field for time gets update with lets say

 

time = 4

totaltime needs to read the value of time = 4 and update itself to totaltime = 4

then if time field gets updated again with

 

time = 6

then totaltime needs to see that and update itself with totaltime = 10 since 4+6 = 10

 

then if a new issuenum gets created say

 

issuenum = 11

 

new fields need to be created automaticaly again under the field issuenum

 

so my original field is called

 

issuenum, every time a new value gets sent to that fiel

 

example, i guess this is called a hiearchal structure?

 

but it would look like thie

 

Issuenum

  |__issuenum = 13

  |          |__time = 4

  |          |__totaltime = 4

  |__issunum = 23

  |          |__time = 9

  |          |__totaltime = 9

  |__issuenum = 19

              |__time = 2

              |__totaltime = 2

 

and so forth, and like i said if time in issuenum = 13 above gets changed from 4 to 6 then total updates itself with basic addition math to totaltime = 10

 

 

any help would be greatly appreciated, thank you

 

Marcus

 

 

 

[fenway]

Hi marcus.. the situation you described, while technically possible, isn't really how dbs were designed to work.

 

The best way to do this is to have two tables: one for the issues themselves.  It will have a UID field -- will will be the "issuenum" later -- as well as details about the issue (e.g. name, description, date, etc.). 

 

The other table will "issue_responses", or something like that.  It will have a UID, a FK to the issue table, and a time field (or preferably, a start/end datetime field -- makes it more generic).  And possibly a textual description.  You simply insert a new record into this table every time someone works on an issue.  There's no "hierarchy" to speak of here.

 

There's no need to store totals in the db -- you can always calculate this whenever you need to.

 

So, to mirror your example -- create these tables, and then you'd run the following statements:

 

CREATE TABLE issue (
  issue_uid bigint(20) unsigned NOT NULL auto_increment,
  created_on datetime default NULL,
  description varchar(250),
  PRIMARY KEY  ( issue_uid )
) ENGINE=MyISAM;

CREATE TABLE issue_response (
  issue_response_uid bigint(20) unsigned NOT NULL auto_increment,
  issue_uid bigint(20) unsigned NOT NULL,
  response_start datetime NOT  NULL,
  response_end datetime NOT NULL,
  details varchar(250),
  PRIMARY KEY  ( issue_response_uid ),
  KEY  ( issue_uid )
) ENGINE=MyISAM;

# you wouldn't normally specify the uid here, but I want it to match what you describe -- you'd simply insert and use LAST_INSERT_ID() to get the newly created work order number
INSERT INTO issue ( issue_uid, created_on, description ) VALUES( 23, NOW(), 'issue 23' );
INSERT INTO issue_response ( issue_uid, response_start, response_end, details ) VALUES ( 23, NOW(), NOW() + INTERVAL 4 MINUTE, 'still not resolved' );
INSERT INTO issue_response ( issue_uid, response_start, response_end, details ) VALUES ( 23, NOW() + INTERVAL 20 MINUTE, NOW() + INTERVAL 26 MINUTE, 'finally fixed' );

 

And so and so forth.  I'm sure this is all very different than what you imagined, so let me know what doesn't really make sense and we'll take it from there.