How do i insert a a new record between existing records using positioning

[antony2kx]

suppose i have two records in a mysql database table for example


id info position

1 hello 1


2 good 2




after wards i want to insert a a record in between id 1 and id 2, how do i go about doing that using the position. help please

[Mysterio]

I wonder why people always want to do difficult tasks.
Your question can be: How do I print it?
Because in the database is the same, if the record is first or is second in the list.

[antony2kx]

i know how to do everything else like print etc. i'm working on my wapbuilder thats why if a user ads two elements and want to insert a new element in between the two elements that already exist, how do i let the user alter that?

[Vayne]

use

UPDATE table SET field 

[something else]

The Problem is that im guessing "id" is the primary key making it harder to restructure the datbase accordingly
due to after "2 good 2" there might be another thousand entrys in the database making everything after 2 having to be renumbered id+1
I guess 1 way would be to use a temporary table transfer all data to that then return it to the example table eg:

$data=mysql_query(SELECT info, position, id FROM example");
while($list=mysql_fetch_array($data)){    
$done=mysql_query("INSERT INTO temp_table SET info='".$list[0]."', positione='".$list[1]."'");
if($done) print $list[2].': done';
}

mysql_query("TRUNCATE TABLE 'example'"); //delete contents of example table (might need permisions to do this)

$data=mysql_query(SELECT info, position, id FROM temp_table");
while($list=mysql_fetch_array($data)){
$done=mysql_query("INSERT INTO example SET info='".$list[0]."', positione='".$list[1]."'");
if($done) print $list[2].': done';
} 

also if you do use this method i would recommend backing up your database before doing so ... as if you have a large database the script may time out. (in which case you would need to limit your calls to database and do it in sections)

There is probably is an easier way of doing this.....