Update field in multiple columns

Question

I have a list of categories and I would like to sort them all at once, so I created a "sort" field where I can put the order number I want [1] and so by pressing to save [2] as the image:

//Controller: administracao.php
function update_ordenacao(){
        $this->load->model("administracao_model");

        $ordemCat = array (
            // coluna                                   // campo form
            "id_categoria_anuncio"              =>      $this->input->post("id_categoria_anuncio"),
            "page_id_categoria_anuncio"         =>      $this->input->post("ordenacao"),
        );

        $sucesso = $this->administracao_model->ordenacaoCats($ordemCat);

        if($sucesso) {
            $this->session->set_flashdata("success", "Ordem alterada com sucesso!");
            redirect("administracao/ver_cat_anuncios");
        } else {
            $this->session->set_flashdata("danger", "A ordem das categorias não pode ser alterada!");
            redirect("administracao/ver_cat_anuncios");
        }


    }

Model:

// Model: administracao_model.php
function ordenacaoCats($ordemCat){

            $data = array(
                array(
                  'id_categoria_anuncio'            => $ordemCat['id_categoria_anuncio'] ,
                  'page_id_categoria_anuncio'       => $ordemCat['page_id_categoria_anuncio']
                )
            );    

            $this->db->update_batch('categorias_anuncios', $data, 'id_categoria_anuncio'); 
            //echo $this->db->last_query();


    }

View:

//View: ver_cat_anuncios.php

                    <?php echo form_open("administracao/update_ordenacao", array('id' =>'categorias-dos-anuncios')); ?>
                    <table class="table table-striped">
                        <tr>
                            <th>#</th>
                            <th>Nome da Categoria</th>
                            <th>Ordenação <?php  echo form_button(array( "class"      => "btn-ordenacao fa fa-floppy-o",  "content"    => "",  "type" => "submit" )); ?></th>
                            <th>Status</th>
                            <th>Ações</th>
                        </tr>

                        <?php if (count($categorias)) : foreach ($categorias as $categoria) :?>
                        <tr>
                            <td><?=$categoria['id_categoria_anuncio']; ?></td>
                            <?php ($categoria['is_parent_categoria_anuncio'] !== "0") ? $espacamento = " " : $espacamento = "- "; ?>

                            <td><?= anchor('administracao/edit_cat_anuncios/' .$categoria['id_categoria_anuncio'], $espacamento.$categoria['titulo_categoria_anuncio']);?></td>
                            <td>
                                <div class="form-group">
                                    <?= form_hidden('id_categoria_anuncio', $categoria['id_categoria_anuncio']);?>
                                    <?= form_input(array(
                                        "name"          =>  "ordenacao",
                                        "value"         =>  $categoria['page_id_categoria_anuncio'],
                                        "id"            =>  "ordenacao",
                                        "class"         =>  "form-control",
                                        "maxlength"     =>  "3",
                                        "style"         =>  "width: 60px; text-align: center;"
                                    )); ?>
                                </div>
                            </td>
                            <td><?php if($categoria['show_menu_categoria_anuncio'] == 1) {echo "<span style=\"color: #009202; font-weight: bold;\">Ativado</span>";} else {echo "<span style=\"color: #e10707; font-weight: bold;\">Desativado</span>";} ?> </td>
                            <td>
                                <div class="btn-group" role="group" aria-label="Ações">
                                    <?= btn_edit('administracao/edit_cat_anuncios/'. $categoria['id_categoria_anuncio'] );?>
                                    <?= btn_delete('administracao/deleta_cat_anuncios/'. $categoria['id_categoria_anuncio'] );?>                                    
                                </div>                              
                            </td>
                        </tr>
                        <?php endforeach; ?>
                        <?php else: ?>
                            <tr>
                                <td colspan="5"><p class="text-info">Não há nenhuma categoria no sistema!</p></td>
                            </tr>

                        <?php endif;?>
                    </table>
                    <?php echo form_close(); ?>

This my code I did taking a look at the documentation of Codeigniter

$data = array(
   array(
      'title' => 'My title' ,
      'name' => 'My Name 2' ,
      'date' => 'My date 2'
   ),
   array(
      'title' => 'Another title' ,
      'name' => 'Another Name 2' ,
      'date' => 'Another date 2'
   )
);

$this->db->update_batch('mytable', $data, 'title'); 

// Produces: 
// UPDATE `mytable` SET `name` = CASE
// WHEN `title` = 'My title' THEN 'My Name 2'
// WHEN `title` = 'Another title' THEN 'Another Name 2'
// ELSE `name` END,
// `date` = CASE 
// WHEN `title` = 'My title' THEN 'My date 2'
// WHEN `title` = 'Another title' THEN 'Another date 2'
// ELSE `date` END
// WHERE `title` IN ('My title','Another title')

I wonder what I’m doing wrong?

Answer 1

From what I understand you are wanting to save the sort you put for each item (category), but the first mistake I see is in your view. You must enter the name and id of the field with []

<?= form_input(array(
    "name"          =>  "ordenacao[]",
    "value"         =>  $categoria['page_id_categoria_anuncio'],
    "id"            =>  "ordenacao[]",
    "class"         =>  "form-control",
    "maxlength"     =>  "3",
    "style"         =>  "width: 60px; text-align: center;"
)); ?>

I don’t know how this data is being sent to the controller, but I imagine that for a js, then you should take this data $this->input->post("ordenacao") and loop that data and then do the update_batch.

The way you did, so you wouldn’t have to change your controller and only your model would look like this:

function ordenacaoCats($ordemCat){

    $data = array();

    foreach ($ordemCat['page_id_categoria_anuncio'] as $ord) {
        $data[] = array(
                'id_categoria_anuncio'      => $ordemCat['id_categoria_anuncio'] ,
                'page_id_categoria_anuncio' => $ord
            );
    }    

    $this->db->update_batch('categorias_anuncios', $data, 'id_categoria_anuncio'); 
    //echo $this->db->last_query();


}

Remember, I can’t test here, but that’s the idea.