Jump to content

Recommended Posts

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

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
}

 

 

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.