db SELECT in concrete place

[eluser]Josssi[/eluser]
Hi people,

I am solving now one DB functionality in my application. In one module of application I need to create new item and store it to the database. My problem is, that i need to store them in concrete place. For example my database will contain some people:

ID FIRST LAST
1.Silvester Stalone
2.Sherlock Holmes
3.Christina Boccati

And now in my application I want to add NEW PERSON, but i need to give it before Sherlock. Is it possible to make this select? How can I place new value between two other??? Can you help me somebody pls?

[eluser]InsiteFX[/eluser]
If ID is a primary key and incremented then you cannot.

The database will store the values as they are added and inserted.

However you can sort the columns to the way you need them for displaying.

[eluser]Josssi[/eluser]
I wanted to rease it with ID, but as you said, its primary key and incremented so it isnt possible. I had to create new value for every input, it is named serial_number, it is incremented, but just manually with my own function, and if i want to place new person, first of all i have to get serial_number of this, which i want replace, than every serial >=selected_serial will increase +1, and then i can place it on place where I want. Surely, its like you said, it will be placed here just for displaying and not real to the db.

Anyway, thanks a lot for reaction

[eluser]Jason Tan Boon Teck[/eluser]
If you want to allow the user to sort the rows abritrarily, i.e. not serial, nor alphabetical, etc., then add an integer column to the table. Advise the user to assign the sort order in large multiples, e.g. 100's or 1,000's or more.

For example:
table_sort col_data
1000 ADKSFJ
2000 FJKAIE
3000 DJAERS

If a new data NEWDAT needs to be added between ADKSFJ and FJKAIE, then assign 1,500 to the table_sort when inserting.
1000 ADKSFJ
1500 NEWDAT
2000 FJKAIE
3000 DJAERS

The application/user need not resort the table for a while until it gets too congested.

Hope this solves your problem.

[eluser]Josssi[/eluser]
Thanks for your reaction , but i found a better solving I thing ...In db I create new column serial_number. If I want to add new item in my application, in form select i just choose item, before I want to place my new item, and in controller I will call model function, which will increment all serial_number = or > than serial_number of choosen item.
There is script of model method:

Code:

function addItemBefore($item_data)
{
  $value = 1;
  
  $this->db->where('serial_number >=', $item_data['serial_number'])
     ->set('serial_number', 'serial_number+'.$value, FALSE)
     ->update('products');
  
  $this->db->insert('products',$item_data);

}

note: serial_number cant be in db selected as PRMARY_KEY.