2. PHPRunner General questions
  3. Auto_decrement...?
This topic is locked

Auto_decrement...?

 		7/26/2006 16:40:21
PHPRunner General questions
T
 Tubaplayer author

Hi!
I am making a sheet music database for a band, and all seems to be working pretty good. But I have one problem. I need to use a field for reference number (in physical archives), and currently I'm using ID (also primary key) for that. Problem is, if I delete an entry from the db, I would need the ID to auto_decrement.
The ideal solution would be if I could ALWAYS get the first available number to pop up in the ID field (or another field if that would work better) when using the add-page.
To make shure you know what I want:

  • when I add the first entry to the db, I want a field to display the number 1 automatickly.
  • when I add the second entry, it should display 2 automatickly.
  • if I delete the second entry, and after that I add a new entry, that should also display 2 automatickly.
  • I will only delete the entry with the highest ID. So if I have 10 entrys, I would never delete entry no. 8.
    Why do I need this? Now and then a blank entry is posted. That can happen when I'm adding a new piece to the db, and I reload the "add new" page to get the latest data from a Lookup Wizard field with "update on the fly" after adding a new option there.. Naturally, I want to delete the blank field and have the Was that possible to understand?
    Is this possible? If it is, how do I make it happen? <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=3065&image=1&table=forumtopics' class='bbc_emoticon' alt=':)' />
    Thanks!

    Paal
Alexey admin 7/27/2006

Paal,
sure, you can do this using events.

Put the following into Default value box for your ID field:

$_SESSION["nextid"]
Then put this code into Add on Load event:

global $conn;

$rs = db_query("select max(id) from mytable",$conn);

if($data=db_fetch_numarray($rs))

$maxid=$data[0];

else

$maxid=0;

$_SESSION["nextid"]=$maxid+1;



And this code into Before Delete event.

global $conn;

$rs = db_query("select max(id) from mytable",$conn);

if($data=db_fetch_numarray($rs))

$maxid=$data[0];

else

$maxid=0;

global $key;

if($key<$maxid)

return false;

return true;


where ID and mytable are your actual key field and table names.