How update table database by php with the value of array

Question

my problem is that i am trying update a table database using php with the values of selected options saved in an array and i am not able to make it works. In the case i will explain now, i am trying update a row which has certain value with one of the values from the array.

This is the php that shows the information of the table database inside a table enter image description here

This is the table database which content want update enter image description here

And this is part of the code that create the table with the selected options

<div class="centro">
        <table class="tabla" style="width:100%">
            <thead>
                <tr>
                    <th>Numero</th>
                    <th>Coste</th>
                    <th>Usuario</th>
                    <th>Estado</th>
                    <th>Confirmar</th>
                </tr>
            </thead>

            <tbody>
                <tr>
                    <?php

                        //Variable para el nombre de usuario
                        $x = $user_data['usuar_nom'];
                        $conexion = mysqli_connect("localhost", "root", "", "llanoponte");
                        $sql = "SELECT * FROM carro";
                        $resultado = $conexion->query($sql);
                        $id = 0;

                        if($resultado->num_rows > 0){
                            while($row = $resultado-> fetch_assoc()) {   
                                echo"
                                <tr>
                                    <td>",$row['numero'],"</td>
                                    <td>",$row['coste'],"</td>
                                    <td>",$row['usuario'],"</td>
                                    <td>",$row["estado"],"</td>
                                    <td>
                                        <select id='$id' name='estados'>
                                            <option value='Por confirmar'>Selecciona estado</option>
                                            <option value='Cancelada'>Cancelada</option>
                                            <option value='En entrega'>En entrega</option>
                                        </select>
                                    </td>
                                </tr>
                                ";
                               $id++;
                            }
                        }
                        else {
                            echo "Sin resultados";
                        }
                        $conexion-> close();
                    ?>
                </tr>
            </tbody>
        </table>
    </div>

Then this is the code with an input when clicked that creates the array with the selected options and send it to the php which updates.

<input type="submit" id="cambios" name="cambios" class="cambios" onclick="cambios()" value="Cambiar">

    <script type="text/javascript">
        function cambios()
        {

            //array para ver length de select lists
            let lista = [];

            //Pasar variable del php para hacer bucle for
            var veces = '<?php echo $id?>';
            
            //Bucle que pasa por los select
            for(var i=0; i< veces; i++)
            {
                var e = document.getElementById(i);
                var value  = e.value;
                lista.push(value);
            }

            $.ajax({
                url:'modificando.php',
                type:'post',
                data:{lista:lista},
                success:function(respuesta)
                {
                    alert("Se han realizado los cambios");
                }
            });

        }
    </script>

And finally this is the php where i want update the database with value of the array

<?php

    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "llanoponte";

    $lista = $_POST['lista'];

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
    }

    $sql = "UPDATE carro SET estado='.$lista[1].' WHERE id=2";

    mysqli_query($conn, $sql);

    $conn->close();

?>

Thank you by reading all this, and sorry if it was difficult to understand

Answer 1

You need to add string quotes in SQL Queries when inserting/updating a string
I have modified the query to single quotes so we can use double quotes inside the Query

<?php

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "llanoponte";

$lista = $_POST['lista'];

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = 'UPDATE carro SET estaso="'.$lista[1].'" WHERE id=2';

mysqli_query($conn, $sql);

$conn->close();
?>

In addition, it will be helpful to see what is inside the lista array to make sure there's data in it and you are passing it correctly

print_r($lista); // Print whole array 
echo $lista[1];  // Print the variable you want