drath Posted April 19, 2010 Share Posted April 19, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/199031-inserting-new-row-with-unique-id-among-others/ Share on other sites More sharing options...
andrewgauger Posted April 20, 2010 Share Posted April 20, 2010 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 } Quote Link to comment https://forums.phpfreaks.com/topic/199031-inserting-new-row-with-unique-id-among-others/#findComment-1045014 Share on other sites More sharing options...
andrewgauger Posted April 20, 2010 Share Posted April 20, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/199031-inserting-new-row-with-unique-id-among-others/#findComment-1045255 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.