Sort sequence of numbers in a database with PHP

Question

I’m having trouble developing a system that changes a sequence of numbers. I need this sequence to dispose of items in the correct order. When saving an item in the bank, it receives a number for sequence 1, 2, 3... and so on.

I decided to modify and try to explain better, the list below, comes from a database, the first number only shows the id of the bank the second the order in which they were saved, the rest is only description. I want to know how to modify this order by clicking on one of the items. In the photo item 2 is selected, in this case, when I click the up arrow I want 2 to be 1 and 1 to be 2. How can I make this algorithm?

$mo = $maxOrder - 1; //ultima numeração de ordem da materia menos 1

        if($ordem == 1){ //se ordem atual for o primeiro registro

            $result = $ordem + $mo; //soma-se ortem atual com $mo para ficar por ultimo

        }elseif($ordem == $maxOrder){ //ou se ordem for a ultima

            $result = $ordem - 1; //subtrai-se por 1 para passar a ser a primeira

        }else{
            $result = $ordem + 1; //senão soma-se com 1 para subir
        }

To rescue this data I send a request through the angular to the Function below, in it, I call the model that makes the select, store the result in an object and then move around the object with a foreach and store the result in data array name. after that return the data via json with json_encode(). Follows the cited code:

public function getMateriaByPortaria()
{

  $this->layout=""; //retornar dados numa tela sem layout

  $materias = $this->PortariaMateriaM->get_all_portaria_materia(); //pega todos os dados da tabela portaria_has_materia

  $data = array(); //array data

  foreach ($materias as $mat) { //foreach que percorre o objeto e armazena no array data
        $data[] = array(
            "idportaria_materia" => $mat->idportaria_materia,
            "ordem" => $mat->ordem,
            "numerada" => $mat->numerada,
            "idportaria" => $mat->idportaria,
            "data_inicio" => $mat->data_inicio,
            "data_fim" => $mat->data_fim,
            "titulo" => $mat->titulo,
            "descricao" => $mat->descricao,
            "descricao_internacional" => $mat->descricao_internacional,
            "assinatura_instrutor" => $mat->assinatura_instrutor,
            "sigla" => $mat->sigla,
            "idmateria" => $mat->idmateria,
            "nome_materia" => $mat->nome_materia,
            "nome_ingles" => $mat->nome_ingles,
            "numero_tempo" => $mat->numero_tempo,
            "carga_horaria" => $mat->carga_horaria,
            "modulo" => $mat->modulo
        );
    }

    print_r(json_encode($data));
    return json_encode($data); //retorna dados do array em json

}

Answer 1

I believe that what you want is to change the ordering and so change the other sequences avoiding that there are 2 equal values. You might be doing the following!

Let’s assume it has the following variables::

// array com os valores obtidos
$frutas = array(
    0 => 'maça',
    1 => 'banana',
    2 => 'morango',
    3 => 'melância',
    4 => 'laranja'
);

$maxOrder = count($itens); // 4

Using this function you can change the position without repeating

function sequencia($array, $keyArray, $position)
{
    $i = 0; // valor inicial

    // verifica se a posição que pede é primeira ou última
    switch ($position) {
        case 'first':
            $position = $i;
            break;
        case 'last':
            $position = count($array);
    }

    // reorganiza o array
    foreach ($array as $key => $value) {
        if ($keyArray < $position) {
            if ($key != $keyArray && $key != $position) {
                $result[$i] = $value;
            } elseif ($key == $position) {
                $result[$i] = $array[$key];
            }
        } else {
            if ($key != $keyArray && $key != $position) {
                $result[$i] = $value;
            } elseif ($key == $position) {
                $i++;
                $result[$i] = $value;
            } elseif ($key == $keyArray) {
                $result[$position] = $array[$keyArray];
            }
        }
        $i++;
    }

    return $result;
}

Examples

I want the banana go to position 3 you use

sequencia($frutas, 1, 3);

I want the laranja go to position 1

sequencia($frutas, 4, 1);

You may also be using the arguments first or last for first and last positions

sequencia($frutas, 3, 'first'); // muda posição para primeiro da lista
sequencia($frutas, 2, 'last'); // muda posição para último da lista

Edit to adapt with new question addition

I understood what you meant, in case you just add the values of the variable $result to the database.

Example with Mysql:

Before:

$result[$i] = $array[$key];

Afterward:

$conn->prepare("UPDATE tabela SET ordem = $array[$key] WHERE ordem = $i");

I hope it helped!