Help updating sorting field

[lingo5]

Hi, I have a MySQL table that contains the following fields:

 

    `id` int(11) NOT NULL AUTO_INCREMENT,                                                                                                                    
              `orden` int(3) NOT NULL,                                                                                                                                 
              `nombre` varchar(80) DEFAULT NULL,                                                                                                                       
              `cargo_esp` varchar(80) DEFAULT NULL,                                                                                                                    
              `cargo_eng` varchar(80) DEFAULT NULL,                                                                                                                    
              `cargo_ger` varchar(80) DEFAULT NULL,                                                                                                                    
              `cargo_fra` varchar(80) DEFAULT NULL,                                                                                                                    
              `telefono` varchar(120) DEFAULT NULL,                                                                                                                    
              `email` varchar(80) DEFAULT NULL,       

 

The primary key is 'id'.

 

I output the name and cargo fields to a php page and sort the results by the field named 'orden'. No problem upto here.

 

I use he following code to update a record:

 

$nombre = $_POST["nombre"];
$posicion = $_POST["orden"];
$cargoesp = $_POST["cargo_esp"];
$cargoeng = $_POST["cargo_eng"];
$cargoger = $_POST["cargo_ger"];
$cargofra = $_POST["cargo_fra"];
$tel = $_POST["telefono"];
$email = $_POST["email"];


$Sql="UPDATE t_about_us SET nombre='$nombre', orden='$posicion', cargo_esp='$cargoesp', cargo_eng='$cargoeng', cargo_ger='$cargoger', cargo_fra='$cargofra', telefono='$tel',email='$email' WHERE id='$colname_junta_update_RS'";

mysql_query($Sql) or die('Error, query failed : ' . mysql_error()); 

 

The problem is that if I change the value of the field 'orden' of record 1 to 2, the record with 'orden' 2 stays the sam, so I end up with 2 records with the same 'orden' value.

 

This is not good because I need to sort by 'order' and can't have duplicates. What I need is to be able to change the 'orden' field of each record to whatever value I want and get the other 'order' fields to re-arrange themselves automatically.

i.e. if I change the 'orden' value of record x to 1, then this record will show in the first position and the record that had 'order' 1 before will become 'orden' 2, etc...

 

I don't know if this is clear enough...THANKS

[harkly]

Any reason why you can't make the "orden" field the primary key?

[lingo5]

I need the field 'id' to be the primary key as I use this field to relate to other tables.

[harkly]

How are you changing the orden field?

[lingo5]

via a text field in the update form.

 

[harkly]

You would need to write code that would increment the orden field when one is changed.

 

$orden++;

[lingo5]

Thanks, but could you please help me to do that?. I am quite new to PHP and I don't think I can do that without help.

[harkly]

Try something like this

 

$querys = '';

while($i<=$max)

{

$orden=$i+1;

$querys .= "UPDATE categories SET orden=$orden WHERE orden=$i; ";

 

$i++;

}

 

if($querys != '')

mysql_query($querys) or die(mysql_error());

 

 

Also here is a thread that pertains to the same thing you're tryin to do

phpfreak might remove the link

 

http://www.daniweb.com/forums/thread94707.html#