2. PHPRunner General questions
  3. record history
This topic is locked

record history

 		7/10/2007 5:31:58 AM
PHPRunner General questions
M
 morpheus author

I have a database with quite a few records, to keep a history I have a status field, when a record is amended (copied) it needs to set the status of the original to OLD
the primary key field is "uniqueid"
I've tried the following code in after record edit

global $conn;

$strSQLInsert = "update items

set status = OLD

where uniqueid = '".$values["uniqueid"]."'";

db_exec($strSQLInsert,$conn);
return true;


The above code does nothing, what am I doing wrong?
I also need to generate an incremental value in a field called "id" in the same table when NEW records are added, there are existing records in the format "ITMxxxx", I need to generate and insert an auto incrementing value on new records.
regards
Jason
J
 		Jane 7/10/2007

Jason,
to change status of edited record use Before record updated event on the Events tab.

Here is a sample code:

$values["status"]="OLD";


Regarding the second question.

I'm not sure that I understand you correctly. Where do you want to generate this value?
M
 		morpheus author 7/10/2007

Thanks for your help,
I tried the method shown above and am having mixed success, Maybe I didnt explain very well, so I'll try again <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=19564&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
I've changed the copy function on my list.php to say amend, the idea is that the status of this new record is set to "AVL" (available) by default and the old one is set to "OLD" so that there is a new record for each change thats made, this gives us a complete audit trail,
using the method you suggested actually changes the record I'm trying to preserve
below is a quick idea of what I want to do..
create record A

copy record A to new record B and set A's status to old

copy record B to new record C and set B's status to old

copy record C to new record D and set C's status to old
and so on, this way theres a complete audit trail and I can easily pull just the "current" record in a table.
as for the ITM number question........
each record is given a "uniqueid" number that the end user never sees, however when a new record is created there is an ITM number field, this ITM number then follows that piece of kit throughout its life, sooooo when I generate a new record for a piece of kit it needs to increment the ITM number by 1 (but only for new records).
hope this is clearer <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=19564&image=2&table=forumreplies' class='bbc_emoticon' alt=':unsure:' /> <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=19564&image=3&table=forumreplies' class='bbc_emoticon' alt=':blink:' />
J
 		Jane 7/11/2007

Jason,
if you want to use copy function to create new record use following code to update old record in the CopyOnLoad event:

global $conn;

$strSQLInsert = "update items set status = 'OLD' where uniqueid = '".$_REQUEST["copyid1"]."'";

db_exec($strSQLInsert,$conn);


To increase ITM number when new record is created use Before record added event on the Events tab:

global $conn;

$str = "select max(ITM) from TableName";

$rs = db_query($str,$conn);

$data = db_fetch_numarray($rs);

$values["ITM"]= $data[0]+1;
M
 		morpheus author 7/13/2007

I tried the code shown below but the text box "id" is just coming up blank when I open the "add new" page, looks like I'm doing something wrong again <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=19618&image=1&table=forumreplies' class='bbc_emoticon' alt=':(' />

To increase ITM number when new record is created use Before record added event on the Events tab:


[/quote]
i've created the second table _iteminc to give me an incremental number and this is has worked fine by adding

global $conn;

$strSQLInsert = 'INSERT INTO `stockcontrol`.`_iteminc` (`value`, `dummy`) VALUES (NULL, \'bob\');';

db_exec($strSQLInsert,$conn);


what I'd like to do is take the highest number from _iteminc and prefix it with ITM then paste that in to the "id field when a add new record is opened.
J
 		Jane 7/16/2007

Hi,
BeforeAdd event runs when you click on the Save button.
If you want to fill this field before use Add page: Before display event.

Her is a sample code:

global $conn,$smarty;

$str = "select max(ITM) from TableName";

$rs = db_query($str,$conn);

$data = db_fetch_numarray($rs);

$smarty->assign("value_ITM",$data[0]+1);