2. PHPRunner General questions
  3. Link from Record to all audits
This topic is locked

Link from Record to all audits

 		2/12/2014 10:45:17 AM
PHPRunner General questions
J
 JCJordan author

In order to comply with some federal regulations, I am required to keep an audit trail for all edits made to my db.
Example Audit description:



---Keys

id : 1

---Fields

model [old]: MKII

model [new]: MKIII



This is an example of the database setup. The main table is "item", "buy" & "sell" are children linked via id_num field. I am needing to be able to simply click on a link to pull up all changes made to the records. (The feds don't like having to use the search, we found out). The other option would be from the audit db itself, create a link to the actual record.
The preferable way would be from the record to the audit set up as a child link like this:


This database will always be ran from localhost as it is for internal use only. I am currently using PHPr 7.1.20347.

Thanks for any help.
Sergey Kornilov admin 2/12/2014

This won't be possible directly. Since we have to use a single audit table to store changes for all tables there is no dedicated field you use can use to link these two tables as master-details.
What you can do is insert a PHP code snippet into Edit/View page, execute SQL query there pulling related records and displaying them.
Sample SQL query:

select * from AuditTrail where table='item' and description like '%id : 1%'
J
 		JCJordan author 2/13/2014

Would it be possible to modify the audit database to store the primary key as a separate field, instead of included in the description?