create a record in second table when first table is updated

[shkdxb]

hi

i want some help in hooks.

i have two table table1 and table2. i need table2 to get it updated with a new record from table1.field1 and table1.field2 with user name and userid. hope this can be done. i have gone through this forum & couldn't find a correct script. appreciate any help from forum members.

[KSan]

I think this can be do e in the after_insert hook of table1 with an SQL insert into table2 kind of line.

[shkdxb]

Ksan,
thanks. yes this can be done by Tablename_after_insert hook. can anyone can give script for this? i am very new and don't know how to write this script.

[shkdxb]

i have created a script going through some of the forum posts. can anybody tell me whether this will work?

function MainTable_after_insert($data, $memberInfo, &$args){

$ID = $data['ID'];
$Jobnumber = $data['Jobnumber'];
$Status = $data[‘Status’];
$withwhom = $data['withwhom'];
$Location = $data['Location'];
$ChangedBy = $memberinfo['username'];
$ChangedDate = $data['date'];

mysql_connect("localhost", "user", "pass") or die(mysql_error());
mysql_select_db("your_database_name") or die(mysql_error());

if ($username <> 'admin'){ //I trap to not record admin access as it is not relevant to the stats

mysql_query( "INSERT INTO StatusTracking (Jobnumber, Status, WithWhom, Location, ChangedDate, ChangedBy) VALUES ('$Jobnumber', '$Status', '$withwhom', '$ChangedBy', '$ChangedDate')") or die(mysql_error());
return TRUE;
}

[a.gneady]

You don't need to use mysql_connect and mysql_select_db as there is already an open connection ready to use.

[shasta59]

Ahmad

I was finding if I did not use the mysql_connect etc it sometimes did not work properly. Rather than figure out the reason for this I put this structure in to ensure a connection. I tried it both ways and could not find a reason for the error when it did not work but did not get an error when I used the mysql connect. As it happened so rarely but was most annoying when it did I went to forcing open the connection using the mysql_connect structure.

It is on my list to try and see a reason for it but it has a very low priority. Could be server side or some other reason.

Alan

[shkdxb]

I could able to make to work. but i have one problem.

the statement:

mysql_query( "INSERT INTO StatusTracking (Jobnumber, Status, WithWhom, Location, ChangedDate, ChangedBy) VALUES ('$Jobnumber', '$Status', '$withwhom', '$Location', '$ChangedDate', '$ChangedBy')") or die(mysql_error());

returns ID field instead actual data for "Status" field in main table. this field is configured as lookup field from another table and this lookup table has two columns, ID and Status. how to force mysql to insert correct column?

[shkdxb]

done it1

The main table is saving the ID field only- not actual looked up value from lookup fields. pointed the StatusTracking to point to the same lookup table as MainTable. problem solved.

[toconnell]

Can you put in your final code? I would like to do the same thing. I am interested in your fix for the look up field. I cannot seem to get that part to work. Thanks, Tina

[stevelizardi]

Is there a tutorial on how to do this? I was trying to see if I could get it to work in just creating a app and reference a field, but it didnt work. Any help would be great.

Thanks!

[toconnell]

For the status to show up you need to put in a join to the foreign table..

INNER JOIN 'statustable' ON
statustable.record_id=.... you get the idea right?