Insert one new record when second table is updated

shkdxb · Post by **shkdxb** » Sat Jun 07, 2014 11:02 am

i need to insert a new record to table2 whenever any new record or update is done to exisitng records.
trying to include below script in service side event under Table specific row_inserted row_updated but not working.

table1-> IncidentNofromEIT, Callreceived_date, CallResponded_date, TSResponsetimehrs, Product, ProblemRelated, CallcompletedinTSRM_date, ContractStatus, Status,

table2-> IncidentNofromEIT, Callreceived_date, CallResponded_date, TSResponsetimehrs, Product, ProblemRelated, CallcompletedinTSRM_date, ContractStatus, Status, UserName, DateChanged

tried follwing insert but not working

ew_Execute ("INSERT INTO table2( IncidentNofromEIT,, Callreceived_date,CallResponded_date, TSResponsetimehrs, Product, ProblemRelated, CallcompletedinTSRM_date, ContractStatus, Status, UserName, DateChanged )VALUES ( " . $rsnew["IncidentNofromEIT"] . "," . $rsnew["IncidentNofromEIT"] . "," . $rsnew["Callreceived_date"] . "," . $rsnew["CallResponded_date"] . "," . $rsnew["TSResponsetimehrs"] . "," . $rsnew["Product"] . "," . $rsnew["ProblemRelated"] . "," . $rsnew["CallcompletedinTSRM_date"] . "," . $rsnew["ContractStatus"] . "," . $rsnew["Status"])");

what i am doing wrong?

how to capture username and date and time the changes are made in table2?.

mobhar · Post by **mobhar** » Sat Jun 07, 2014 11:17 am

Make sure you have enclosed with single quote character for each value which contains date, date/time, string, char, varchar. For example:

'" . $rsnew["IncidentNofromEIT"] . "', '" . $rsnew["IncidentNofromEIT"] . "', '" . $rsnew["Callreceived_date"] . "', ...
shkdxb wrote:

how to capture username and date and time the changes are made in table2?.

Simply use global function: CurrentUserName() and ew_CurrentDateTime() to get the related value respectively.

shkdxb · Post by **shkdxb** » Sat Jun 07, 2014 11:43 am

Thanks Mobhar
i am getting error as
Parse error: syntax error, unexpected '"')"' (T_CONSTANT_ENCAPSED_STRING) in C:\xampp\htdocs\phpmaker\eitsla\EITSLAinfo.php on line 1044

now the insert statement is

ew_Execute ("INSERT INTO Changes(
IncidentNofromEIT,, Callreceived_date,CallResponded_date, TSResponsetimehrs,
Product, ProblemRelated, CallcompletedinTSRM_date, ContractStatus, Status, UserName,
DateChanged
)VALUES
( '" . $rsnew["IncidentNofromEIT"] . "','" . $rsnew["IncidentNofromEIT"] . "',
'" . $rsnew["Callreceived_date"] . "','" . $rsnew["CallResponded_date"] . "',
'" . $rsnew["TSResponsetimehrs"] . "','" . $rsnew["Product"] . "',
'" . $rsnew["ProblemRelated"] . "','" . $rsnew["CallcompletedinTSRM_date"] . "',
'" . $rsnew["ContractStatus"] . "','" . $rsnew["Status"]"')");

couldn't find any stray ' or " character.

mobhar · Post by **mobhar** » Sat Jun 07, 2014 12:56 pm

Double check your SQL, there are some syntax error, such as:

IncidentNofromEIT,, <-- double comma character here

( '" . $rsnew["IncidentNofromEIT"] . "','" . $rsnew["IncidentNofromEIT"] . "', <-- the first and second value are the same?

UserName,
DateChanged
...
VALUES ... ? <-- you have not included the value of both fields in VALUES clausa

shkdxb · Post by **shkdxb** » Sat Jun 07, 2014 1:58 pm

yes noticed your suggestions and included userName and DateChanged, but still i am getting same error

ew_Execute ("INSERT INTO Changes( IncidentNofromEIT,Callreceived_date,CallResponded_date,TSResponsetimehrs,Product,ProblemRelated,CallcompletedinTSRM_date,ContractStatus,Status,UserName,DateChanged
)VALUES
( '" . $rsnew["IncidentNofromEIT"] . "','" . $rsnew["Callreceived_date"] . "','" . $rsnew["CallResponded_date"] . "','" . $rsnew["TSResponsetimehrs"] . "','" . $rsnew["Product"] . "','" . $rsnew["ProblemRelated"] . "','" . $rsnew["CallcompletedinTSRM_date"] . "','" . $rsnew["ContractStatus"] . "','" . $rsnew["Status"]"','" . $rsnew["CurrentUserName()"] . "','" . $rsnew["ew_CurrentDateTime()"] . "')");

scs · Post by **scs** » Sat Jun 07, 2014 6:52 pm

.... $rsnew["Status"]"','" . $rsnew["CurrentUserName()"] ....

See the problem?

$rsnew["Status"] {{{ missing a dot here }}} "','" . $rsnew["CurrentUserName()"]

Should be like this:-

$rsnew["Status"] . "','" . $rsnew["CurrentUserName()"]

I think you better put this in any array and process it.

Otherwise, use external editor to edit the syntax then copy to your program.

shkdxb · Post by **shkdxb** » Sat Jun 07, 2014 8:02 pm

yes the dot solved the problem.
now everything is getting updated in the table2, except username and changed date.

is this syntax is correct?

'" . $rsnew["CurrentUserName()"] . "','" . $rsnew["ew_CurrentDateTime()"] . "'

scs · Post by **scs** » Sat Jun 07, 2014 11:43 pm

Just use CurrentUserName() and ew_CurrentDateTime() will do. No need $rsnew[].

shkdxb · Post by **shkdxb** » Sun Jun 08, 2014 12:55 am

tried but getting error

Failed to execute SQL. Error: FUNCTION sekarbye_eitsla.CurrentUserName does not exist

scs · Post by **scs** » Sun Jun 08, 2014 10:34 am

Check phpmaker help with CurrentUserName and/or CurrentUserInfo.

You can get result there.

shkdxb · Post by **shkdxb** » Wed Jun 25, 2014 12:29 am

sorry not much help available for this CurrentUserName or CurrentUserName
anyone help me pl?

keithh0427 · Post by **keithh0427** » Wed Jun 25, 2014 2:20 am

Are you using currentUserName()

Note the parantheses

keithh0427 · Post by **keithh0427** » Wed Jun 25, 2014 2:23 am

In reviewing your posts, you have:

'" . $rsnew["CurrentUserName()"] . "','" . $rsnew["ew_CurrentDateTime()"] . "'

there is not $rsnew['CurrentUserName()"]

Just use CurrentUserName() without $rsnew or the brackets. Same with CurrentDateTime().

shkdxb · Post by **shkdxb** » Wed Jun 25, 2014 2:48 am

this is what i use

ew_Execute ("INSERT INTO Changes( IncidentNofromEIT,Callreceived_date,CallResponded_date,TSResponsetimehrs,Product,ProblemRelated,CallcompletedinTSRM_date,ContractStatus,Status,UserName,DateChanged )VALUES
( '" . $rsnew["IncidentNofromEIT"] . "',
'" . $rsnew["Callreceived_date"] . "',
'" . $rsnew["CallResponded_date"] . "',
'" . $rsnew["TSResponsetimehrs"] . "',
'" . $rsnew["Product"] . "',
'" . $rsnew["ProblemRelated"] . "',
'" . $rsnew["CallcompletedinTSRM_date"] . "',
'" . $rsnew["ContractStatus"] . "',
'" . $rsnew["Status"] . "',
CurrentUserName(),
ew_CurrentDateTime())");

mobhar · Post by **mobhar** » Wed Jun 25, 2014 9:26 am

Try this:

ew_Execute ("INSERT INTO Changes( IncidentNofromEIT, Callreceived_date, CallResponded_date, TSResponsetimehrs, Product, ProblemRelated, CallcompletedinTSRM_date, ContractStatus, Status, UserName, DateChanged ) VALUES
( '" . $rsnew["IncidentNofromEIT"] . "',
'" . $rsnew["Callreceived_date"] . "',
'" . $rsnew["CallResponded_date"] . "',
'" . $rsnew["TSResponsetimehrs"] . "',
'" . $rsnew["Product"] . "',
'" . $rsnew["ProblemRelated"] . "',
'" . $rsnew["CallcompletedinTSRM_date"] . "',
'" . $rsnew["ContractStatus"] . "',
'" . $rsnew["Status"] . "',
'" . CurrentUserName() . "',
'" . ew_CurrentDateTime()) ."'");

shkdxb · Post by **shkdxb** » Wed Jun 25, 2014 10:56 am

thank you very much. it works now. small correction on last line should read like this

'" . ew_CurrentDateTime() ."')");