Changing MySQL with ajax and PHP

2

I have created a system that allows the user to view data from a mysql database and eventually interact with that data (by changing the select column in the database). Currently there is a php script that runs when the user selects the new status and clicks a button that submits the submit.

The problem

After the user selects the new status and gives submit, it is necessary that he update the page to see the new status, which is quite annoying.

I was able to create the AJAX so that it takes the data of that field (the checkbox that after the line id and the value of the field status ) and both values are being sent thus by ajax:

Object {changePedi: "separacao", checkbox: "256"} // checkbox é o valor da id no mysql

The question

index.php (where are the select and where the tables are generated for the user)

<div id="statusPedidos">
<label>Selecione o status:</label>
<select id="changePed">
    <option value="separacao">Em Separação</option>
    <option value="separado">Separado</option>
    <option value="faturado">Faturado</option>
    <option value="exp">Expedido</option>
    <option value="entrg">Mercadoria Entregue</option>
    <option value="cancelado">Cancelado</option>            
</select>
<button class="btn btn-success" type="submit" onclick="altera_status()">Alterar</button>                        
<div id="myDiv"><table id='pedidos' class='table table-hover table-responsive'>
    <thead>
        <tr>
            <th><input type='checkbox' name='select-all' id='select-all' /></th>
            <th>Data</th>
            <th>EMS</th>
            <th>Pedido do  cliente</th>
            <th>Cliente</th>
            <th>Valor</th>
            <th>Aut. Comercial</th>
            <th>Status</th>
            <th>Nota Fiscal</th>
        </tr>
    </thead>
    <?php
    $vaziodata = date("Y-m-d");
    $mes = date("Y-m-d", strtotime("-1 week"));
    $resultFIL = mysqli_query($connect, "SELECT * FROM 'pedidos' WHERE 'emissaoPed' BETWEEN '$mes' AND '$vaziodata' "); 
    while($row = mysqli_fetch_array($resultFIL))
    {
        $dataped = $row['emissaoPed'];
        $valorped = $row['vlr'];
        echo "<tbody><tr>";
        echo "<td><input class='checkped' name='checkbox[]' type='checkbox' value=" . $row['id'] . "></td>";
        echo "<td>" . date('d/m/Y', strtotime($dataped)) . "</td>";
        echo "<td><a id='ver_pedido' data-ref=".$row['nPedido']." data-toggle='modal' id='abremodal' href='#myModal'>" . $row['nPedido'] . "</a></td>";
        echo "<td>" . $row['NrPedido'] . "</td>";
        echo "<td>" . $row['nomeAbrev'] . "</td>";
        echo "<td>" . number_format($valorped, 2, ',', '.') . "</td>";
        echo "<td><input type='button' value='Autorizado' name='autCom' ></td>";
        echo "<td>" . $row['status'] . "</td>";
        echo "<td><input type='text' placeholder='0012345' style='width: 70px; id='nfd';>&nbsp<button class='btn btn-success' type='submit' onclick='att_nf()'><i class='fa fa-check' aria-hidden='true'></i></button></td> ";
        echo "</tr></tbody>";

    }
    echo "</table>";

    ?>

This is the ajax script that sends and receives data

function altera_status()
{
var checkboxValues = $.map($('.checkped:checked'), function(e){
    return $(e).val();
}).join();
//dados a enviar, vai buscar os valores dos campos que queremos enviar para a BD
var dadosajax = {
    'changePedi' : $("#changePed").val(),
    'checkbox' : checkboxValues,

};
console.log(dadosajax), 
pageurl = 'status.php';
//para consultar mais opcoes possiveis numa chamada ajax
//http://api.jquery.com/jQuery.ajax/
$.ajax({

    //url da pagina
    url: pageurl,
    //parametros a passar
    data: dadosajax,
    //tipo: POST ou GET
    type: 'POST',
    //cache
    cache: false,
    //se ocorrer um erro na chamada ajax, retorna este alerta
    //possiveis erros: pagina nao existe, erro de codigo na pagina, falha de comunicacao/internet, etc etc etc
    error: function(){
        alert('Erro: Inserir Registo!!');
    },
    //retorna o resultado da pagina para onde enviamos os dados
    success: function(response)
    { 

    }
});
}

status.php (where information sent by ajax goes and where queries are executed)

 <?php  

     $_REQUEST['changePedi'] = $statusPed;
     $_REQUEST['checkbox'] = $checkStatus; 

     echo $checkStatus;


     $connect = mysqli_connect("localhost","root","", "notas-34ca74") or die ("Forninho fall"); 



     switch($_REQUEST['changePedi']){
        case 'separacao':
        function filter( $dados ){
            $arr = Array();
            foreach( $dados AS $dado ) $arr[] = (int)$dado;
            return $arr;
        }     
        if(isset($_REQUEST['checkbox'])){
            $arr = filter($_REQUEST['checkbox']);
            $sql = 'UPDATE pedidos SET status="Em separação" WHERE id IN('.implode( ',', $arr ).')';
            $result = mysqli_query($connect,$sql);
        }
        break;
        case 'cancelado':
        function filter( $dados ){
            $arr = Array();
            foreach( $dados AS $dado ) $arr[] = (int)$dado;
            return $arr;
        }     
        if(isset($_REQUEST['checkbox'])){
            $arr = filter( $_REQUEST['checkbox']);
            $sql = 'UPDATE pedidos SET status="Cancelado" WHERE id IN('.implode( ',', $arr ).')';
            $result = mysqli_query($connect,$sql);
        }
        break;
    
asked by anonymous 15.06.2017 / 18:54

0 answers