How to perform change in order sequence with SQL statement

5

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

Ihavealreadydonetheupdatethatmakestheorderchangeandisworking,myquestionisthefollowing,followingtheexampleoftheimagethatIposted,howcanIchangetheorder3to1andthe1totheorder3inthesameinstant.

Thecodethatdoestheupdatewithoutthisimplementedroutineisthis:

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 have already achieved a lot with the help of @Felipe Moraes, the code changed according to the suggestion looks 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 snippet into my BD and the change was successful, but by applying to my page the script gives me the message that the change was made, but it is not being executed. I think I'm making the error when replacing bind_param , the code looks 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";
    }
}
    
asked by anonymous 14.03.2016 / 18:49

3 answers

5

The technique for this is to do 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, change the order only for the 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 to suit your needs.

You can read more about it here: link

Update:

Your variable $sql would look like this:

$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 bind_param method would look like this:

$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"]
);

Note that the modified $_POST , came into being the Ordem_1 % and Ordem_2 , ie, you must inform the order of 2 records to be exchanged. If you want to change the record with the order 2 with the registration with the order 3, how did you could only change the record with the order 1 with any other.

    
16.03.2016 / 23:51
2

Change in table

You are having a hard time not having a unique identifier for each record. I recommend adding the id field to your table and mark it as auto increment so you 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, simply add a field to pass the id of the records you want to change.

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

Attention

If you want your code to identify two different records, you need to name the fields in order to send more records.

For example using the

<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 your HTML. In the example above the value will be 2 .

To get multiple records using a field-only name, add [] to the front, as this will treat PHP as an array. Example:

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

This will return the $_POST['id'] value in 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 indexes of an array start from 0;

Directly changing the position between two registers

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

If you put an index that indicates that the relationship between the IdContrato and Ordem fields is unique, you must place one of them in an invalid position, that is, it does not exist in the database, as -1 update the other and then update the first one 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 record that is currently in the order 10 to go to 2 , simply 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 registry 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}";

Please note that the upgrade is limited between a range, to ensure that no records change their order unnecessarily.

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

To change to a larger 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}";
    
18.03.2016 / 04:23
2
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: Is this "ORDER" just to display an order on the screen? If so, you would not need that record in the bank - the ordering could take place in the "preview" only, avoiding so much work.

    
21.03.2016 / 19:03