CODEWITHSUNDEEP
phpmysql
t3cho
t3cho

Reputation: 61

Mysql after deleting record decrement other ID's

I have the database where i save my records. The records was saved as ID, name

example

  1. Nick
  2. Name
  3. Username

Now when i delete the record with id 2. in my database it remains as 1. Nick 3. Username I want that when i delete one record from database other id's automatic decrement for one value so i get. 1. Nick 2. Username Do you understand my question. Sry for bad english

Button delete code 

if(isset($_POST['tipkaobrisi'])) // pritisnuta tipka obrisi
{
    $obrisankorisnik = $_POST['obrisi'];
    if ( $obrisankorisnik == '' )echo "<div align='center'>Unesite korisnika kojeg  zelite obrisati!</div>";
    else
    {
        mysql_query("DELETE FROM Korisnici WHERE Korisnik ='$obrisankorisnik'");
        echo "<div align='center'>Korisnik obrisan<br><br></div>";
    }
}

Upvotes: 0

Views: 1068

Answers (2)

omgz0r
omgz0r

Reputation: 170

That is not the intention of most ID columns. However, you can mimick this functionality by modifying the statement you use to access those records.

E.g. rather than using the ID value, use the following

select  @rownum:=@rownum+1 as ID, name from users, (SELECT @rownum:=0) r order by id;

It will give each record returned a number, with no gaps. Depending on what you want this for, that may be enough. If you would like to treat this new ID value as the ID value, create a view and access your elements based on that, like so:

CREATE VIEW users_view as select  @rownum:=@rownum+1 as ID, name from users, (SELECT @rownum:=0) r order by id;

at which point you can SELECT * FROM users_view WHERE ID = $id and have it work as usual. So long as you never insert a new record that is not auto incremented, this scheme should work.

Keep in mind that deleting anybody changes a bunch of IDs, so it's not recommended.

Upvotes: 2

t3cho
t3cho

Reputation: 61

After listening other programer's advice's. I decide that i don't want to edit my ID's.

Thanks to all for participating.

Upvotes: 1

Related Questions