1 some_data1

3 some_data3

4 some_data4

5 some_data5

I wish to do an Place to include ---> 2 some_data2. Can there be an SQL command or a way to do this?

OK. Maybe basically explain every factor what i wish to accomplish you will see why i am attempting to do what i wish to do. I developing a Web admin page in php that merely shows data from the database table.now these displayed rows are "draggable". example,you are able to drag row #2 to express, row #4. now around the "mouseup" event, i wish to save the brand new order within the database.

1 data1 -------------------------> 1 data1
2 data2 -------After Drag-----> 3 data3
3 data3 -------------------------> 4 data4
4 data4 -------------------------> 2 data2
See my problem? i cant basically just update the worthiness within the INDEX column for that one value which was pulled. I'm going to update all of the values which were pulled over. 3 needs to be a 2, 4 a 3 and a pair of needs to end up being the new 4. Thats a lot of sql updates (expecially when the table is bigger). So i wish to remove row #2 and place it in the correct location.

Just place the information tables aren't essentially purchased. The only real ordering of table results happens whenever you define it yourself inside your choose statement.

Edit: if what you would like is to possess a separate ordering, you'd be well offered by getting another "order" column. I'd recommend which makes it of type float, so that you can place records between other records without needing any upgrading. For instance, for those who have entry "A" with "order" column value "1", entry "B" with "order" column value "2", and you need to place entry "C" therefore it turns up between entry "A" and entry "B", just place it using the "order" column value set to at least one.5 (1 + 2 / 2.). This way, you are able to perform your choose by having an "order by" in your "order" column, and things should turn view you would like. Floats really are a reasonably good means to fix this problem, since there's enough space to aid lots of "in-between" adds.

You need to just have the ability to add it like regular:

INSERT INTO some_table (index, some_data) VALUES (2, 'some_data2')

After placing, should you really want the rows to become purchased by index, run this:

ALTER TABLE some_table ORDER BY index;

Generally an order is actually trivial though. You ought to be using indexes for posts you need to do finds on, and ORDER BY when getting result sets. You shouldn't depend around the place order of rows within the table for either optimizing or sorting.

Yes, you are able to specify the main key from the row whenever you place.

insert into yourtablename (index, some_data) values (2, "some_data2");

It's an awful idea however to bother with getting contiguous primary secrets. Their job ought to be to distinctively identify the row and absolutely nothing more.

Okay, I am responding to again since your update teaches you are asking a totally different question than everybody thought you had been.

How we referred to -- update the index from the moved row and each one out of their email list after it -- is the right method here.

So why do you state that "that's a lot of sql updates"? As lengthy as you've a MySQL index put on the index column, individuals updates is going to be amazing fast, for large lists (they'll be around the order of milliseconds).

I believe you're either pre-optimizing, or you do not have a catalog defined. To include a catalog (if you do not curently have one), run this command:

CREATE INDEX row_index_index ON `some_table` (`index`);

Additionally you may want to reconsider renaming your "index" column to something similar to "position" to prevent confusion about nomenclature by having an actual MySQL index.