2. PHPRunner General questions
  3. How To Have Id Number In Seqenece?
This topic is locked

How To Have Id Number In Seqenece?

 		5/31/2013 1:29:38 AM
PHPRunner General questions
P
 phpcmk author

Hi,
Can someone please kindly advise if I delete a record, can my ID number still be in sequence number?
For example, I used an auto-increment in my database for the ID number. Thus, whenever record is created it will go in sequence (1, 2, 3, 4 etc).
However, if I delete a record with ID no 2, how can I still have the sequence as (1, 2, 3, 4 etc) and not (1, 3, 4 etc)?
Thanks.
Sergey Kornilov admin 5/31/2013

You cannot make that happen automatically, databases do not work that way. In general it's a good idea to separate the internals and presentation level. That being said you need to have two fields: ID (primary key, autonumber) and DispalyID.
DefaultID needs to be set equal to ID when record is created. Then, when you delete a record with ID=128, for instance, in AfterDelete event you can issue the following SQL query:

Update MyTable set DisplayID=DisplayID-1 where ID>128
P
 		phpcmk author 6/2/2013



You cannot make that happen automatically, databases do not work that way. In general it's a good idea to separate the internals and presentation level. That being said you need to have two fields: ID (primary key, autonumber) and DispalyID.
DefaultID needs to be set equal to ID when record is created. Then, when you delete a record with ID=128, for instance, in AfterDelete event you can issue the following SQL query:

Update MyTable set DisplayID=DisplayID-1 where ID>128



I have tried the above method, however I noticed that if I deleted a record and then add a new record, the displayid will still not be in sequence.
Example:

  1. Create new record (id auto-increment: 1,2,3,4) and (displayid based on id: 1,2,3,4).
  2. Delete a record (eg. id=2), (id auto-increment: 1,3,4) and (displayid: 1,2,3)
  3. If I created a new record, (id auto-increment: 1,3,4,5) and (displayid: 1,2,3,5).
    From step 3, the displayid will not be in sequence if displayid is set to default based on id.
    Please kindly advice. Thanks.
Sergey Kornilov admin 6/3/2013

As I mentioned in my previous post you have to implement AfterDelete event that will renumber DisplayID values after record is deleted. It will not work until you implement this event.
P
 		phpcmk author 6/3/2013



As I mentioned in my previous post you have to implement AfterDelete event that will renumber DisplayID values after record is deleted. It will not work until you implement this event.


Hi,
The following is the codes that I implemented in Events, I am not sure if it is correct as I will get the problem mentioned in my previous post.
Please kindly advice. Thanks.



Add Page > After record added:
global $dal;
$dal_table=$dal->Table("new");

$dal_table->Value["displayid"]=$values["id"];

$dal_table->Param["id"] = $values["id"];

$dal_table->Update();




List page > After record deleted:
CustomQuery("Update new set displayid=displayid-1 where id>$deleted_values[id]");
Sergey Kornilov admin 6/4/2013

Now you need to make sure:

  1. Each event is executed
  2. This code produces correct results
    If you have a valid support contract post your application to Demo Account and open a ticket at http://support.xlinesoft.com sending your Demo Account URL. 'Demo Account' button can be found on the last screen in the program. Somebody will assist you with this.