CODEWITHSUNDEEP
phpmysql
Keith
Keith

Reputation: 11

MySQL id numbering in table

Let's say that I have a table linked to a database like this: http://www.falkencreative.com/forum/records/view.php

How do I make it so that when I delete a record and then add a new one, the new replaces the previously deleted record and has the previously deleted id number instead of the next id?

For example

  1. Red
  2. Blue
  3. Green

click button to delete green.

  1. Red
  2. Blue

click button to add new color. input orange

  1. Red
  2. Blue
  3. Orange

As it stands, what I'm working with is putting out: 1. Red 2. Blue 4. Orange (What should set this to 3?)

It's probably something simple in terms of the table set up, but I have no idea what to do and I'm pressed for time. Can anyone guide me on what I should do?

Upvotes: 1

Views: 292

Answers (4)

Brian
Brian

Reputation: 15706

I think you want to UPDATE the record, rather than DELETE the old and INSERT a new one.

Edit:

If you want to insert a new record and assign it the next sequential id, use a non-auto_increment column and do this when adding a new record:

INSERT INTO myTable (id, ...) VALUES ( (SELECT MAX(id) + 1 FROM myTable), ...)

I don't have a database handy to test this, but I think it will work. But I'm not sure what will happen when the table is empty. Also this may create a race condition.

Upvotes: 0

Curfew
Curfew

Reputation: 1

Your problem is not about database IDs, it's about not understanding the difference between an ID and a line number. They have nothing to do with each other. Just simply output the loop counter ("$i") instead of the ID.

<?php
for ($i = 1; $i <= foo(); $i++) {
    print "<tr><td>{$i}</td><td>...</td></tr>\n";
}
?>

Upvotes: 0

Milan
Milan

Reputation: 3325

You may have 2 options. Either update the same row where the "Green" value was previously. i.e.:

mysql_query("UPDATE table_name SET color = 'orange' WHERE ID = '3'");

or

mysql_query("UPDATE table_name SET color = 'orange' WHERE color = 'green'");

or

whatever your specifiers could be.

this should be from the MySql standpoint the easiest way. Or you can delete it as you initially outlined and re insert new row with mysql_query("INSERT INTO .... but then MySql PK UID will need to be reset i.e.:

mysql_query("ALTER TABLE table_name AUTO_INCREMENT = 1");

which will remove your used PK IDs and renumbers all rows. Or is there an easier solution ?

Upvotes: 1

Andrea V. Abbondanza
Andrea V. Abbondanza

Reputation: 364

No, when you delete a database row, if the column have the AUTO_INCREMENT active, it will take the value from a sequence. If you delete 3, the sequence always start from 3+1. You can see\set the auto-increment value for example from php myadmin. I don't know what DBMS are you using, but with oracle you can manipulate the sequence.

So you have various way.

  1. Complicated: When you insert\delete you update the sequence with the triggers
  2. You use 3 coloumn: id, IdToShow, Value.

id is the same of now, you use it with autoinc. idToShow also is what you show. When you insert you can insert it counting the rows present in the table (you have various ways).

Hope it help.

Upvotes: 0

Related Questions