2. PHPRunner General questions
  3. Record added by and modified by
This topic is locked

Record added by and modified by

 		6/25/2006 3:46:42 PM
PHPRunner General questions
O
 osluk author

I know this was answered but the posts were lost.
So here goes.
What do I need to add and where to allow the user and time to be recorded for record creation and modification.
Ideally add a time offset as the server is 7 hours behind the users in France.

Offset +7 hours when capturing now()
Cheers Chris
I assume location would be
Table Events -> Add page -> After record added

Table Events -> Edit page -> After record added
As for the code required some suggestions or advice would be great.
O
 		osluk author 6/27/2006

Hey guys I know someone answered this in the lost posts before I could get it working.

Any pointers appreciated.
Chris
Alexey admin 6/27/2006

Chris,
here is the sample code for Before record added event:

global $conn,$strTableName;

$strSQLSave = "INSERT INTO AnotherTable (User, Time, Field1, Field2) values (";
$strSQLSave .= "'".$_SESSION["UserID"]."',";

$strSQLSave .= "ADDTIME(now(),'07:00:00'),";

$strSQLSave .= $values["Field1"].",";

$strSQLSave .= $values["Field2"];
$strSQLSave .= ")";

db_exec($strSQLSave,$conn);
O
 		osluk author 6/27/2006

Error type 256

Error description
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'created, Record added) values ('sysadmin',ADDTIME(now(),'07:00:00'),)' at line 1
Ok I must be close it should add the existing user and time (adjusted) to the record being added.
Any ideas on the cause of this error.
Chris
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''User created','Record added') values ('sysadmin',ADDTIME(now(),'07:00:00'),)' at line 1
function BeforeAdd(&$values)

{
// Parameters:

// $values - Array object.

// Each field on the Add form represented as 'Field name'-'Field value' pair
//** Insert a record into another table ****

global $conn;

$strSQLSave = "INSERT INTO AnotherTable ('User created','Record added') values (";

$strSQLSave .= "'".$_SESSION["UserID"]."',";

$strSQLSave .= "ADDTIME(now(),'07:00:00'),";

$strSQLSave .= ")";

db_exec($strSQLSave,$conn);
return true;
// return true if you like to proceed with adding new record

// return false in other case
}
Alexey admin 6/27/2006

Here is the fixed code

function BeforeAdd(&$values)

{
// Parameters:

// $values - Array object.

// Each field on the Add form represented as 'Field name'-'Field value' pair
//** Insert a record into another table ****

global $conn;

$strSQLSave = "INSERT INTO AnotherTable (`User created`,`Record added`) values (";

$strSQLSave .= "'".$_SESSION["UserID"]."',";

$strSQLSave .= "ADDTIME(now(),'07:00:00')";

$strSQLSave .= ")";

db_exec($strSQLSave,$conn);
return true;
// return true if you like to proceed with adding new record

// return false in other case

}
O
 		osluk author 6/27/2006

Alexa you are a star,

That was perfect - I will take a look at why later.

I just need to get this thing working then I need to figure out

more about how and why it works and the syntax.
For now I am just relying on you guys to give me the framework

in which to work (I supply the irritating questions!).
I guessed the same code would work on edit
So I took the code with minor changes to

Table Events -> Edit Page -> Before record updated
Now in this case the code needs to know which record is being updated.

This I dont know how to do - sorry to bug you again.
Also my "PHP learner" logic would be to record the user and time after

the record has been updated (this is where I write to the audit log) and

by that time we know which record is being modified.
2 questions how to fix up the code below - when to use OnLoad - Before record added - After record Added

unless there is an article that explains this you can point me towards.

function BeforeEdit(&$values, $where)

{
// Parameters:

// $values - Array object.

// Each field on the Edit form represented as 'Field name'-'Field value' pair

// $where - string with WHERE clause pointing to record to be edited
global $conn;

$strSQLSave = "INSERT INTO data (`User modified`,`Record modified`) values (";

$strSQLSave .= "'".$_SESSION["UserID"]."',";

$strSQLSave .= "ADDTIME(now(),'06:00:00')";

$strSQLSave .= ")";

db_exec($strSQLSave,$conn);
return true;
// return true if you like to proceed with editing this record

// return false in other case
}


Thanks again for the help

It is a great product - great support
Cheers Chris
O
 		osluk author 6/28/2006

I have relaised that rather than data stamping the existing record I am creating a new record for each time the user and time are captured.
I have created a different stable version for the users to use as this was really confusing them - I now have a dev version for all the work in progress.
Create a record 2 were created the record and a record with the user created name and time

edit a record created anothr record with the user and time of the edit.

Removed from Table events -> Add page -> Before record added

function BeforeAdd(&$values)

{

// Parameters:

// $values - Array object.

// Each field on the Add form represented as 'Field name'-'Field value' pair
//** Insert a record into another table ****

global $conn;

$strSQLSave = "INSERT INTO data (`User created`,`Record added`) values (";

$strSQLSave .= "'".$_SESSION["UserID"]."',";

$strSQLSave .= "ADDTIME(now(),'06:00:00')";

$strSQLSave .= ")";

db_exec($strSQLSave,$conn);
return true;
// return true if you like to proceed with adding new record

// return false in other case

}


Removed from Table events -> Edit page -> Before record added

function BeforeEdit(&$values, $where)

{
// Parameters:

// $values - Array object.

// Each field on the Edit form represented as 'Field name'-'Field value' pair

// $where - string with WHERE clause pointing to record to be edited
global $conn;

$strSQLSave = "INSERT INTO data (`User modified`,`Record modified`) values (";

$strSQLSave .= "'".$_SESSION["UserID"]."',";

$strSQLSave .= "ADDTIME(now(),'06:00:00')";

$strSQLSave .= ")";

db_exec($strSQLSave,$conn);
return true;
// return true if you like to proceed with editing this record

// return false in other case
}


Now I can capture the required infomation I just need to capture in in the record.
Cheers Chris
This is how it should be
O
 		osluk author 6/28/2006

Would love someone to look at the details here the concept is sound so as long as it is possible to add data to the same record this should be doable!
Cheers Chris
What I need - record has a date stamp to show user that created in and last modification


What I currently get is a new record with the user and datestamp
O
 		osluk author 6/29/2006


OK a little more scientific this time.

User date stamp event generates a record 577 before the record I am creating 578.

Then when modifiying this record 578 a new record 579 & 580 is created each time.
I really want to crack this - any suggestions as to what should go where would be gratefully

received - I have too many examples now of how to not quite achieve this.
Cheers Chris
These are the events and where they currently are located.

Thanks to everyone who has got me this far!

Located in Table events -> Add page -> Before record added

function BeforeAdd(&$values)

{

// Parameters:

// $values - Array object.

// Each field on the Add form represented as 'Field name'-'Field value' pair
//** Insert a record into another table ****

global $conn;

$strSQLSave = "INSERT INTO data (`User created`,`Record added`) values (";

$strSQLSave .= "'".$_SESSION["UserID"]."',";

$strSQLSave .= "ADDTIME(now(),'06:00:00')";

$strSQLSave .= ")";

db_exec($strSQLSave,$conn);
return true;
// return true if you like to proceed with adding new record

// return false in other case

}


Located in Table events -> Edit page -> Before record updated

function BeforeEdit(&$values, $where)

{
// Parameters:

// $values - Array object.

// Each field on the Edit form represented as 'Field name'-'Field value' pair

// $where - string with WHERE clause pointing to record to be edited
global $conn;

$strSQLSave = "INSERT INTO data (`User modified`,`Record modified`) values (";

$strSQLSave .= "'".$_SESSION["UserID"]."',";

$strSQLSave .= "ADDTIME(now(),'06:00:00')";

$strSQLSave .= ")";

db_exec($strSQLSave,$conn);
return true;
// return true if you like to proceed with editing this record

// return false in other case
}
Alexey admin 6/29/2006

Chris,
You need to know some PHP in order to extend the functionality of your pages.

I can recommend you to check http://www.w3schools.com website as a good start to learn PHP.
O
 		osluk author 6/29/2006

Thanks Alexa looks like I have run out of offical goodwill!

I appreciate all the help you have given me.
Anyone else know if it is possible to write the username and datetime into the record being entered or modified.
I have devoted a LOT of time to these posts and it looks like a while until my PHP will catch up with the level required - which afterall is why I didnt try to write it myself!
Assuming it is possible someone with the right knowledge should have pretty much all the detail required.
Cheers Chris
Paid consultancy anyone.


If the unpaid support has run out PM me with a price to achieve what I have setout above!

Paypal OK half in advance half on completion - start once you have convinced me

you know what you doing.
Or I could make a donation to a good cause on your behalf - if it is the challenge you like

  • paypal receipt supplied!
    Cheers Chris UK
A
 		Alan4573 6/29/2006

Chris,
Are you just wanting to add details of modified records (Who modified and when)?
I presume the users who are editing have their details held in a Users table?
If so, could you not simply add a couple of hidden fields containing this info to write the details into yor table.

You can also use a hidden timestamp to write the modified date/time.
I use this method all the time to track who is editing my databases - only difference is I dont display the info, I save it in the table for future use if required.
This task is very easily accomplished using DreamweaverMX, so it should theoretically be easy from within PHP runner. I imagine you just need to add the hidden fields to the Edit Record form on generated xxxx_.edit.php and edit the SQL accordingly (Don't know off the top of my head where the update SQL is stored, but shouldn't take long to find)
I guess it could also be accomplished somehow using events
I'm going to have a play and see if I can achieve this
Cheers
Alan
O
 		osluk author 6/29/2006

Thanks Alan,
The reason it is displayed is for admin only.

There is a different view for the public.

Just allows me to sort by the field and when a users messes

up I can see exactly where they jave been!
Cheers Chris
A
 		Alan4573 7/12/2006

Thanks Alan,

The reason it is displayed is for admin only.

There is a different view for the public.

Just allows me to sort by the field and when a users messes

up I can see exactly where they jave been!
Cheers Chris



Chris,
Have you managed to sort this yet?
If not, let me know by PM. I've managed to crack it.

Basically you need to change the edit format for adding new records (default value - hidden field). To achieve the same with editing records, you need to manually tweak the code generated in ...._edit.php.
Enters username and date/time whenever a record is added or modified.
Cheers
Alan