How to make change in order sequence with SQL statement

Question

I have a form where I display to the user some phases that a particular contract needs to respect, follows image of the said form:

I’ve already done the update that makes the order change and is working, my doubt is the following, following the example of the image I posted, how can I change the order 3 to 1 and the order 1 to order 3 at the same time.

The code that updates without this implemented routine is this:

if ($_POST["Operacao"] == 'UpFaseObrigatoria') {

    $sql = "UPDATE `intranet_cocari`.`gerFaseObrigatoria` 
               SET `Ordem` = ? 
             WHERE `gerFaseObrigatoria`.`IdContrato` = ? 
               AND `gerFaseObrigatoria`.`IdTipoFase` = ?";

    if ($stmt = $conn->prepare($sql) ){
        $stmt->bind_param(
            "iii",
            $_POST["Ordem"],
            $_POST["IdContrato"],           
            $_POST["IdTipoFase"]
        );

        if ($stmt->execute()){
            $aretorno["msg"] = "Registro atualizado com sucesso.";          
            $stmt->close();
        } else {
            $aretorno["msg"] = "Ocorreu um erro na atualização dos dados: " . $stmt->error . ". Verifique.";
            $aretorno["status"] = "ERRO";
        }
    } else {
        $aretorno["msg"] = "Ocorreu um erro na preparação dos dados: "  . $stmt->error . ". Verifique.";
        $aretorno["status"] = "ERRO";
    }
}

I’ve achieved a lot with the help of @Felipe Moraes, the code changed according to the suggestion was like this:

UPDATE
    gerFaseObrigatoria AS FaseObrigatoria
    JOIN gerFaseObrigatoria AS gerFaseObrigatoriaUp ON
           ( FaseObrigatoria.Ordem = ? AND gerFaseObrigatoriaUp.Ordem = 1 )
        OR ( FaseObrigatoria.Ordem = 1 AND gerFaseObrigatoriaUp.Ordem = ? )
SET
    FaseObrigatoria.Ordem = gerFaseObrigatoriaUp.Ordem,
    gerFaseObrigatoriaUp.Ordem = FaseObrigatoria.Ordem
WHERE   
    FaseObrigatoria.IdContrato = ? AND gerFaseObrigatoriaUp.IdContrato = ? 

Copy and paste this code thingy into my BD and the change was made successfully, but when applying on my page the script gives me the message that the change was made, but the same is not being executed. I believe I’m making the mistake at the time of the bind_param, the code is like this:

if ($_POST["Operacao"] == 'UpFaseObrigatoria') {


    $sql = "UPDATE gerFaseObrigatoria AS FaseObrigatoria
                JOIN gerFaseObrigatoria AS gerFaseObrigatoriaUp ON
                           ( FaseObrigatoria.Ordem = ? AND gerFaseObrigatoriaUp.Ordem = 1 )
                        OR ( FaseObrigatoria.Ordem = 1 AND gerFaseObrigatoriaUp.Ordem = ? )
                SET
                    FaseObrigatoria.Ordem = gerFaseObrigatoriaUp.Ordem,
                    gerFaseObrigatoriaUp.Ordem = FaseObrigatoria.Ordem
                WHERE   
                    FaseObrigatoria.IdContrato = ? AND gerFaseObrigatoriaUp.IdContrato = ? ";

    if($stmt = $conn->prepare($sql) ){
        $stmt->bind_param(
            "iiii",     
            $_POST["Ordem"],    
            $_POST["Ordem"],        
            $_POST["IdContrato"],
            $_POST["IdContrato"]
        );

        if($stmt->execute()){
            $aretorno["msg"] = "Registro atualizado com sucesso.";          
            $stmt->close();
        }else{
            $aretorno["msg"] = "Ocorreu um erro na atualização dos dados: " . $stmt->error . ". Verifique.";
            $aretorno["status"] = "ERRO";
        }
    }else{
        $aretorno["msg"] = "Ocorreu um erro na preparação dos dados: "  . $stmt->error . ". Verifique.";
        $aretorno["status"] = "ERRO";
    }
}

Answer 1

The technique for this is to make a Join to get the 2 lines to be exchanged and then do a simple update, see:

UPDATE
    tabela AS tabela1
JOIN 
    tabela AS tabela2 ON ( tabela1.ordem = 1 AND tabela2.ordem = 3 )
SET
    tabela1.ordem = tabela2.ordem,
    tabela2.ordem = tabela1.ordem

If you need to apply only to a particular contract, just add the clause where, for example, exchange order only for contract 35:

WHERE
    troca1.contrato = 35 AND troca2.contrato = 35

The above example does not match the actual structure of your table, it should be adapted according to your need.

You can read more about it here: http://www.microshell.com/database/sql/swap-values-in-2-rows-sql/

Update:

Your variable $sql would be as follows:

$sql = "
    UPDATE
        gerFaseObrigatoria AS FaseObrigatoria
    JOIN 
        gerFaseObrigatoria AS gerFaseObrigatoriaUp 
    ON
        (FaseObrigatoria.Ordem = ? AND gerFaseObrigatoriaUp.Ordem = ? )
    SET
        FaseObrigatoria.Ordem = gerFaseObrigatoriaUp.Ordem,
        gerFaseObrigatoriaUp.Ordem = FaseObrigatoria.Ordem
    WHERE   
        FaseObrigatoria.IdContrato = ? AND                
        gerFaseObrigatoriaUp.IdContrato = ? "
;

The method bind_param would be as follows:

$stmt->bind_param(
    "iiii",     
    $_POST["Ordem_1"], //aqui é a ordem atual do 1º registro          
    $_POST["Ordem_2"], //aqui é a ordem atual do 2º registro        
    $_POST["IdContrato"],
    $_POST["IdContrato"]
);

Notice I modified the $_POST, the Ordem_1 and Ordem_2, that is, it will be necessary to inform the order of the 2 records that must be exchanged. If you wish to exchange the record with order 2 with the record with order 3, the way you did could only exchange the record with order 1 with any other.

Answer 2

Table change

You are experiencing a difficulty because you do not have a unique identifier for each record. I recommend adding the field in your table id and mark as auto increment so you will have a primary key that will identify the record.

Your table should look like this:

- Id          INT(11)     NOT NULL    UNSIGNED    AUTO_INCREMENT
- IdContrato  INT(11)     NOT NULL    UNSIGNED
- IdTipoFase  INT(11)     NOT NULL    UNSIGNED
- Ordem       INT(11)     NOT NULL    UNSIGNED

Form

In your form just add a field to pass the id of the records you want to change.

<input type="hidden" value"{id}" name="id">

Heed

If you want your code to identify two different records, you need to name the fields so that you can send more records.

For example using the code

<input type="hidden" value"1" name="id">
<input type="hidden" value"2" name="id">

The value of $_POST['id'] will always be the value of the last field with that name in its HTML. In the example above the value obtained will be 2.

To get multiple entries using a field-only name, add [] in front, so PHP will treat as an array. Example:

<input type="hidden" value"1" name="id[]">
<input type="hidden" value"2" name="id[]">

This will return in $_POST['id'] the value array (1,2).

echo `$_POST['id'][0]`; // Imprime: 1
echo `$_POST['id'][1]`; // Imprime: 2

foreach ($_POST['id'] as $id) {
    echo 'Id: ' . $id . "\n";
}
// Imprime 
// Id: 1
// Id: 2

Remembering that the contents of an array start from 0;

Directly changing the position between two records

In case of changing the position of one by the other it is enough to recover the records using the informed Ids and update the orders.

If you have placed an index indicating that the relation between the fields IdContrato and Ordem is unique, it will be necessary to place one of them in an invalid position, that is to say, that it does not exist in the database, as -1 update the other and then update the first with the right position.

-- {id1} : Id do primeiro registro
-- {nova_ordem1} : é a ordem que o primeiro registro irá ficar

-- {id2} : Id do segundo registro
-- {nova_ordem2} : é a ordem que o segundo registro irá ficar

UPDATE gerFaseObrigatoria
SET `ordem` = "{nova_ordem1}"
WHERE `Id` = "{id1}";

UPDATE gerFaseObrigatoria
SET `ordem` = "{nova_ordem2}"
WHERE `Id` = "{id2}";

-- Caso possua o índice unique

UPDATE gerFaseObrigatoria
SET `ordem` = "-1"
WHERE `Id` = "{id1}";

UPDATE gerFaseObrigatoria
SET `ordem` = "{nova_ordem2}"
WHERE `Id` = "{id2}";

UPDATE gerFaseObrigatoria
SET `ordem` = "{nova_ordem1}"
WHERE `Id` = "{id1}";

Changing the position of a specific record to any position above.

If you want the registration that is currently in order 10 pass to the 2, just get the record you want to change from the database to know the current position, then update all the minor order records by adding 1, and then update the record you really want to change.

Your SQL should look something like this

-- {nova_ordem} : é a ordem que o registro irá ficar
-- {ordem_atual} : é a ordem que o registro está antes de iniciar a alteração

UPDATE gerFaseObrigatoria
SET `ordem` = `ordem` + 1
WHERE `IdContrato` = "55"
AND `ordem` >= "{nova_ordem}"
AND `ordem` < "{ordem_atual}"
ORDER BY `IdContrato_id`, `ordem`;

UPDATE gerFaseObrigatoria
SET `ordem` = "{nova_ordem}"
WHERE `Id` = "{id}";

Note that the update is limited between a track, to ensure that no record changes its order without need.

Changing the position of a specific record to any position below.

To switch to a higher value order, repeat the previous process by reversing the signals.

UPDATE gerFaseObrigatoria
SET `ordem` = `ordem` - 1
WHERE `IdContrato` = "55"
AND `ordem` <= "{nova_ordem}"
AND `ordem` > "{ordem_atual}"
ORDER BY `IdContrato_id`, `ordem`;

UPDATE gerFaseObrigatoria
SET `ordem` = "{nova_ordem}"
WHERE `Id` = "{id}";

Answer 3

if ($_POST["Operacao"] == 'UpFaseObrigatoria') {

// 1º Criar a "ordem" oposta
    $o1 = $_POST['Ordem'] === 1 ? 3 : 1;
    $o2 = $o1 === 1 ? 3 : 1;

// 2º Modificar a ordem do "outro" registro
    $sql = 'UPDATE gerFaseObrigatoria
               SET Ordem = :o1
             WHERE Ordem = :o2';
    $stmt = $conn->prepare($sql);
    $stmt->execute([':o1'=>$o1, ':o2'=>$o2]);

// 3º Inserir a nova ordem no registro indicado (contrato + tipo)
    $sql = 'UPDATE gerFaseObrigatoria
               SET Ordem = :o2
             WHERE IdContrato = :contrato
               AND IdTipoFase = :tipo';

    $stmt = $conn->prepare($sql);
    $res = $stmt->execute([':o2'=>$o2,
                           ':contrato'=>$_POST['idContrato'], 
                           ':tipo'=>$_POST['IdTipoFase']
                          ]);

    if($res){
        $aretorno["msg"] = "Registro atualizado com sucesso.";          
        $stmt->close();
    } else {
        $aretorno["msg"] = "Ocorreu um erro na atualização dos dados: "
                           .$stmt->error.". Verifique.";
        $aretorno["status"] = "ERRO";
    }
} else {
    $aretorno["msg"] = "Ocorreu um erro na preparação dos dados: "
                       .$stmt->error.". Verifique.";
    $aretorno["status"] = "ERRO";
}

}

I tested it here in a simulation and it worked!

I have a question: This "ORDER" is only to present ordered on the screen? If yes, I would not need this register in the bank - the ordering could take place in the "visualization" only, avoiding so much work.