2. PHPRunner General questions
  3. [SOLVED]  Logical delete of records instead of phisical
This topic is locked
[SOLVED]

 Logical delete of records instead of phisical

 		12/21/2010 9:10:52 AM
PHPRunner General questions
M
 marcolongo author

Hello everybody,
I'm restructuring the company database in PHPrunner and I need to delete records only in logical way, so that we can mantain history and allow performance analisys.
In order to do this, I need to set in every single table a flag up, which tells me if a record has been removed (boolean column "isRemoved") or not.

If so the record shouldn't be searchable or listable.
In brief:
-on listing: SELECT <attributes> FROM <table> WHERE <table>.isRemoved!=0 --> list only if the records has not been (logically) deleted;

-on search: <show AJAX hint only if a record is not logically deleted, and list relative results>

-on delete: UPDATE <table> SET <table>.isRemoved=1 (--> next listing of table would be without this record!)
(in every future query I'll put the WHERE <table>.isRemoved!=0 and <other conditions> clause!)
Which pages in PHPrunner am I supposed to modify to achive the logical delete?
Thank you,
Enrico
Admin 12/21/2010

Should be pretty straightforward.

  1. Modify SQL query to select active records only



select ...

from ...

where isRemoved=0


2. Implement BeforeDelete event:

CustomQuery("UPDATE <table> SET isRemoved=1 where ".$where);

return false;
M
 		marcolongo author 12/22/2010

It's not as easy as it looks... <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=55230&image=1&table=forumreplies' class='bbc_emoticon' alt=':(' />
M
 		marcolongo author 12/22/2010



Should be pretty straightforward.

  1. Modify SQL query to select active records only
    select ...

    from ...

    where isRemoved=0


Yep...but there is something wrong if I assign $strWhereClause = "isRemoved<>0" on the "List Page: Before SQL query" event, because altough the table_list.php works properly, an inside search ends with showing all records.
Example:

  • By DEFAULT (no $strWhereClause assigned):
    I list my whole table then go to the search-text-edit and start typing some character so that AJAX show me the suggested records.

    Search options set: "All fields, Searchable" on the column I want be prompted by suggests (the attribute is NOT KEY!).
    By clicking on the search button I get correct results perfectly matching with the typed string.
  • After assign $strWhereClause = "isRemoved<>0"
    I list perfectly the table with no-logically-removed records, but despite of anything I wrote on text_edit, search-button ignores it and works exactly as the "Show All" button. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=55231&image=1&table=forumreplies' class='bbc_emoticon' alt=':blink:' />

    ()
    Am I supposed to use this code (coming from manual)?



global $strTableName;
if (isset($_SESSION[$strTableName.'_advsearch'])){

include_once("classes/searchclause.php");

$searchClauseObj = unserialize($_SESSION[$strTableName.'_advsearch']);

if ($searchClauseObj->isUsedSrch()){

if ($searchClauseObj->_where[$strTableName."_simpleSrch"] == ''){

foreach($searchClauseObj->_where[$strTableName."_srchFields"]

as $srchField){

if($srchField['value1'] != ''){

return;

}

}

$strWhereClause = " 1=0";

}

}

};


I did not understand which values should I change (manual said all of these):
[color="#FF0000"]-classes/searchclause.php: this is supposed to be ok
: should I change these strings with "_myadvsearch" or can I hold the original ones?
[color="#FF0000"]$srchField['value1'] != '': here I'm supposed to insert 'isRemoved' in place of value1 and 0 in place of '', is it right?
<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=55231&image=2&table=forumreplies' class='bbc_emoticon' alt=':)' />
Thank you in advance.
Kindly Regards,
Enrico (Marcolongo S.r.l.)
Admin 12/22/2010

Enrico,
you should not modify $strWhere variable. Per my recommendation modify SQL Query on SQL Query screen in PHPRunner.
M
 		marcolongo author 12/23/2010



Enrico,
you should not modify $strWhere variable. Per my recommendation modify SQL Query on SQL Query screen in PHPRunner.


But if I do that, how can list all the records afterwards, if I would?
(I planned to set profiles and I wish to allow an admin to fix a wrong delete by unchecking the flag, if occours).
Best Regards,

Enrico
Admin 12/23/2010

I would suggest to setup a separate view for an admin where all records will be displayed, active and inactive. You can restrict access to this view for non-admin users via User Group Permissions.
M
 		marcolongo author 12/30/2010



I would suggest to setup a separate view for an admin where all records will be displayed, active and inactive. You can restrict access to this view for non-admin users via User Group Permissions.


Alright, thank you so much!