2. PHPRunner General questions
  3. [SOLVED]  Insert a record into another table problems (NEWBIE!!!)
This topic is locked
[SOLVED]

 Insert a record into another table problems (NEWBIE!!!)

 		5/3/2007 1:51:06 PM
PHPRunner General questions
R
 rcurtin author

Ok I have 20 fields worth of data that is user specific (serial numbers, product keys and such) and this data is going to be accessable over the intranet with certain user specific rights and such. So far I have everything working the way I want it to but I would like some sort of data redundancy in place. I have searched the forums and there is alot of questions about the event to insert into new tables but I still don't understand enough about that process to make this work. Basically I want to be able to insert any records deleted from a specific table and drop it into another table (just in case accidental deletion). So since I am such a newbie can anyone point out which things I need to change and why?
A little info about what I have done so far. I created a new table in my database which (without the data) is an exact replica from the one I want to safeguard (different name of course). So if a user deletes a record (20 fields worth) that information just gets plopped into the other table (also set up to email me when this happens IS working) so that I can restore the record if need be without downtime. I am using PHPRunner 3.1 (build 218), PHP 5.2.1, Apache 2.2.4 server, and DB is built in MS Access 2000.
This is default code added from making the event for this action.
function AfterDelete()

{

//** Insert a record into another table ****

global $conn;

$strSQLInsert = "insert into TableName (Field1, Field2) values (Value1, Value2)";

db_exec($strSQLInsert,$conn);

}
From what I understand I need to change "TableName" to obviously the new table I want to insert into. Now here is where it gets hazy.... for the "Field 1, Field2" area I am assuming this is the actual field name?..... and also the "Value1, Value2" I am not sure at all what that should be or if there are any other specific changes I need to make.
Sorry for the long winded post and being such a newbie but I am enjoying this program so much I just want to explore more and more options.
this is a screenshot of my test enviroment of the error I am getting.. I am running this all locally until I actually get it working.


EDIT this is the line 56 it refers to in the error.
{

return odbc_exec($conn,$qstring);

}
Sergey Kornilov admin 5/3/2007

First of all - use BeforeDelete event. When AfterDelete event is executed record is deleted already and there is nothing to save.
Here is the sample code:

function BeforeDelete($where)

{
global $conn;

$strSQLInsert = "INSERT INTO SecondTable ( Field1, Field2, ... ) SELECT Field1, Field2, ... from FirstTable " . $where;

db_exec($strSQLInsert,$conn);
return true;
}
R
 		rcurtin author 5/4/2007

OK Thanks for the reply I have tried that sample code (after of course changing it) but I still get this error.

Technical information

Error type 2

Error description odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement., SQL state 37000 in SQLExecDirect

URL localhost/users/output/ARMPHP_list.php?

Error file C:\Apache Software Foundation\Apache2.2\htdocs\Users\output\include\dbconnection.php

Error line 56

SQL query delete from [ARMPHP] where [ARMPHP].[Serial Number]='ghu0h97 g'

Solution This is a general error. It occurs when there is an error in event code or in SQL.
Send your SQL or event code along with full error message tosupport@xlinesoft.com.


here is the "line 56" from the dbconnection.php

{

return odbc_exec($conn,$qstring);

}


and here is my exact code I used (which is your exact code with tables/fields changed)
[codebox]{
global $conn;

$strSQLInsert = "INSERT INTO ARMPHPDELETED (Client Name, Serial Number, Username, Machine Name, IP Address, Mac Address, Machine Type, Memory, Processor, Windows XP Key, Office Product Key, Monitor Model, Monitor Serial Number, Adobe Acrobat Serial, Ultra 2 Serial Number, Win DVD Serial, Movie Shrink and Burn, Adobe Premier 2, Department, Belark System Audit) SELECT Client Name, Serial Number, Username, Machine Name, IP Address, Mac Address, Machine Type, Memory, Processor, Windows XP Key, Office Product Key, Monitor Model, Monitor Serial Number, Adobe Acrobat Serial, Ultra 2 Serial Number, Win DVD Serial, Movie Shrink and Burn, Adobe Premier 2, Department, Belark System Audit FROM ARMPHP" . $where;

db_exec($strSQLInsert,$conn);
return true;
}[/codebox]
ARMPHP = the table that is working now with add/delete/edit/and even email functions

ARMPHPDELETED = the table I want all the data to go to.
Everything works perfect until I use that code (or any variation I have tried) so the DB connection itself is fine but once this code is entered I get that error everytime. The data showing in the error is just random keystrokes... I used to actually type in new info but it took too long so now I just spam the keyboard to creat a DB entry ~.
Rich
R
 		rcurtin author 5/4/2007

Problem Solved I had to add [ ] to the field entries this is the finished code that finally got it to work.
[codebox]function BeforeDelete($where)

{

global $conn,$strTableName;
$strSQLSave = "INSERT INTO ARMPHPDELETED ([Client Name], [Serial Number], [Username], [Machine Name], [IP Address], [Mac Address], [Machine Type], [Memory], [Processor], [Windows XP Key], [Office Product Key], [Monitor Model], [Monitor Serial Number], [Adobe Acrobat Serial], [Ultra 2 Serial Number], [Win DVD Serial], [Movie Shrink and Burn], [Adobe Premier 2], [Department], [Belark System Audit]) SELECT [Client Name], [Serial Number], [Username], [Machine Name], [IP Address], [Mac Address], [Machine Type], [Memory], [Processor], [Windows XP Key], [Office Product Key], [Monitor Model], [Monitor Serial Number], [Adobe Acrobat Serial], [Ultra 2 Serial Number], [Win DVD Serial], [Movie Shrink and Burn], [Adobe Premier 2], [Department], [Belark System Audit] FROM ".$strTableName." where ".$where;

db_exec($strSQLSave,$conn);
return true;
}[/codebox]
for some reason the lack of those boxes screwed the whole thing up. So now anything deleted from this table gets copied to a replica table that users cannot access. All this just to prevent accidental deletion of this data.........
Thanks so much for the help your program is an amazing tool /bow