2. PHPRunner General questions
  3. saving old data into another table
This topic is locked

saving old data into another table

 		7/10/2008 4:13:05 PM
PHPRunner General questions
W
 wfcentral author

I'm trying to use the event "Save old data record in another table"
I have a table called "tbl_EPR_MSGT" where I'm storing performance reports for employees. When a supervisor edits it I want to have an event BEFORE save that puts a copy of the old record into another table called "tbl_archived_EPR_MSGT" ... I decided to just start with two fields [user_ID] and [user_rank] to see if I could get it to work and then I could add in the rest of the fields I want.
I started with this basic code from phpRunner
//** Save old data record in another table ****

global $conn,$strTableName;
$strSQLSave = "INSERT INTO AnotherTable (Field1, Field2) SELECT Field1, Field2 FROM ".$strTableName." where ".$where;

db_exec($strSQLSave,$conn);

  • after reading through the forum and trying various changes I ended up with the code below (so far nothing has worked...)
    //** Save old data record in another table ****

    global $conn,$strTableName;

    $strSQLSave = "INSERT INTO tbl_archived_EPR_MSGT ([user_ID],[user_rank]) values ('";

    $strSQLSave .=$values["user_ID"]."','";

    $strSQLSave .=$values["user_rank"];

    $strSQLSave .="')";

    echo $strSQLSave;

    db_exec($strSQLSave,$conn);
J
 		Jane 7/11/2008

Hi,
your code looks good.

Here are some tips:

  1. you need to add single quotes around text and date fields only in the SQL query:
    insert into TableName (NumberField,TextField,DateField) values (123,'text','10/10/2008')



2. to debug code print query before execution and the run it in the database directly.
If it doesn't help publish your project on Demo Account and send to support@xlinesoft.com a URL to your pages along with instructions on reproducing this error.

I'll find what's wrong with your project inspecting it at Demo account site.
W
 		wfcentral author 7/11/2008

okay, just to test my code I went right to the database and ran this
insert into tbl_archived_EPR_MSGT (user_ID,user_rank) values (12,24)
it inserted a record just fine.
so, I went back and put that same line in my phprunner event code and commented other lines out...
global $conn,$strTableName;

$strSQLSave = "insert into tbl_archived_EPR_MSGT (user_ID,user_rank) values (12,24)";

echo $strSQLSave;

db_exec($strSQLSave,$conn);
and nothing inserts...
so, either

  1. I'm putting this in the wrong event place (I have it in Before record Updated)
  2. there is something wrong with the code above
    Hi,

    your code looks good.

    Here are some tips:
  3. you need to add single quotes around text and date fields only in the SQL query:
  4. to debug code print query before execution and the run it in the database directly.
    If it doesn't help publish your project on Demo Account and send to support@xlinesoft.com a URL to your pages along with instructions on reproducing this error.

    I'll find what's wrong with your project inspecting it at Demo account site.
J
 		Jane 7/14/2008

It's difficult to tell you what's happening without seeing actual files.
Please publish your project on Demo Account and send to support@xlinesoft.com a URL to your pages along with instructions on reproducing this error.

I'll find what's wrong with your project inspecting it at Demo account site.
M
 		Mandy 8/18/2009

[quote name='wfcentral' date='Jul 11 2008, 02:33 PM' post='30967']

okay, just to test my code I went right to the database and ran this
insert into tbl_archived_EPR_MSGT (user_ID,user_rank) values (12,24)
it inserted a record just fine.
so, I went back and put that same line in my phprunner event code and commented other lines out...
global $conn,$strTableName;

$strSQLSave = "insert into tbl_archived_EPR_MSGT (user_ID,user_rank) values (12,24)";

echo $strSQLSave;

db_exec($strSQLSave,$conn);
and nothing inserts...
so, either

  1. I'm putting this in the wrong event place (I have it in Before record Updated)
  2. there is something wrong with the code above
    I presume you got this working eventually? Can you share the solution?
    Mandy
J
 		Jane 8/18/2009

Mandy,
It's difficult to tell you what's happening without seeing actual files.
Please publish your project on Demo Account and open a ticket at http://support.xlinesoft.com sending a URL to your pages along with instructions on reproducing this error.