Inserting New Row With Unique ID Among Others

[drath]

I had no idea what to call the subject line here. I will try to describe as best as possible what I want to do. Here is how I have my table set up:

 

Column 1 | Column 2

 

1 | apple

2 | orange

3 | banana

4 | pumpkin

5 | pear

 

As you can see, the first column "ID" is a unique number that goes in sequence and order. Now say I wanted to add "3 | kiwi". What is the best way to do this? Meaning that I want "banana" to automatically change its ID to 4, as well as the rest after it to increment by 1. In theory, I would just loop through each item after the ID I was trying to add and update their IDs by 1. Is there a better way to do this? Maybe there is a special MYSQL way of doing this automatically?

 

Intended end result:

 

1 | apple

2 | orange

3 | kiwi

4 | banana

5 | pumpkin

6 | pear

[andrewgauger]

You are going to need a third column.  What you want to do is retain your ID as an auto_increment primary key and create a SORT column.  Start off by

 

ALTER TABLE table_name ADD COLUMN sortOrder;
UPDATE TABLE table_name SET (sortOrder=ID);

 

Then you will have a table such that

 

ID |    name  |    sortOrder

1 | apple      |  1

2 | orange    |  2

3 | banana    |  3

4 | pumpkin  |  4

5 | pear        |  5

 

Now you can create functions that will

function insert($name,$place){
$sql="UPDATE table_name SET (sortOrder=sortOrder+1) WHERE sortOrder >= $place";
//todo execute $sql
$sql="INSERT INTO table_name (name, sortOrder) VALUES ($name, $place)";
//todo execute $sql
}

 

 

[andrewgauger]

ALTER TABLE table_name ADD COLUMN sortOrder;

UPDATE TABLE table_name SET (sortOrder=ID);

 

The first line is wrong:

 

ALTER TABLE table_name ADD COLUMN sortOrder int;

 

Forgot the column_definition.