Janos Janos - 1 month ago 7
Ajax Question

PHP multiple select - option data sending with ajax

I want to change the status in the database, with a select dropdown field.
I am sending with ajax. The first row is always working, but with multiple data i cant update the second, third..etc

I tried with serialize(), but its not working.

select from database:

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<script src="https://code.jquery.com/jquery-1.11.3.js"></script>
<script type="text/javascript">
$(document).ready(function(){
$(".allbooks").change(function(){
var allbooks = $(this).val();
var dataString = "allbooks="+allbooks;
$.ajax({
type: "POST",
data: dataString,
url: "get-data.php",
success: function(result){
$("#show").html(result);
}
});

});
});
</script>

</head>
<body>
<?php
define("HOST","localhost");
define("USER","root");
define("PASSWORD","");
define("DATABASE","hotel");
$euConn = mysqli_connect(HOST, USER, PASSWORD, DATABASE);

$selectRooms = "SELECT * FROM proba WHERE status='inRoom'";
$resultRooms = mysqli_query($euConn,$selectRooms);
if (mysqli_num_rows($resultRooms) > 0) {
echo "<div id='reserved' align='center'>";
While ($row = mysqli_fetch_array($resultRooms)) {
echo $row[1];
echo $row[0];
?>

<select name="allbooks" id="allbooks">
<option name="years">Choose</option>
<?php

for($i=1; $i<=19; $i++)
{
echo "<option value=".$i.">".$i."</option>";
}
?>
</select><br />

<?php }

}

else
echo "<h4>nothing in the db</h4></div>";
?>
<div id="show">
</div>
</body>
</html>


and getting the results:

if(!empty($_POST["allbooks"])) {
var_dump($_POST);
$id = 2;
//echo $_POST['modelS'];
$room = $_POST['allbooks'];
$sql2 = "UPDATE proba SET room='$room' WHERE id_reservation='$id'";
$query = mysqli_query($euConn, $sql2);
var_dump($query);
}


How to change, or what would be a simple solution? Thanks for the help.

Answer

You have multiple select elements on the rendered page with the id allbooks That's wrong, IDs must be unique. You'll want to change those to a class and use $(".allbooks").change(function(){ ....

As far as sending the row id to the server with the update, you'll need to first add the row id to the select box so you can retrieve it later, something like '<select name="allbooks" class="allbooks" data-row-id="' . $row['id_reservation'] . '"> would work.

I would also recommend splitting the work up into several functions to better organize your code (classes would be even better)

It's hard to test without access to the DB, but this should do it for you. Note that I have the update function on the same page and updated the ajax url property to '' which will send the data to a new instance of the current page to handle the update.

<?php
require_once ("db_config.php");


function updateRoom($euConn, $id, $newRoomVal)
{
    $stmt = $euConn->prepare("UPDATE proba SET room=? WHERE id_reservation=?");
    $stmt->bind_param('ii', $newRoomVal, $id);
    /* execute prepared statement */
    $stmt->execute();
    /* close statement and connection */
    $affectedRows = mysqli_stmt_affected_rows($stmt) > 0;
    $stmt->close();
    return $affectedRows;
}
function getRooms($euConn)
{
    $selectRooms = "SELECT * FROM proba WHERE status='inRoom'";
    $resultRooms = mysqli_query($euConn,$selectRooms);
    $rows = mysqli_fetch_all($resultRooms,MYSQLI_ASSOC);
    return count($rows) < 1 ? '<h4>nothing in the db</h4></div>' : createSections($rows);
}

function createSections($rows)
{
    $sections = [];
    foreach( $rows as $row){
        $options = [];
        for ($i = 1; $i <= 19; $i++)
            $options[] = "<option value=" . $i . ">" . $i . "</option>";
        $options = implode('', $options);
        $select = '<select name="allbooks" class="allbooks" data-row-id="' . $row['id_reservation'] . '"><option value="">Choose</option>' . $options . '</select><br/>';
        // .. build all your other row elements here....
        $section = 'some other compiled html'.$select;
        $sections[]=$section;
    }
    return implode('', $sections);
}

$euConn = mysqli_connect(HOST, USER, PASSWORD, DATABASE); 

if(isset($_POST["allbooks"]) && $_POST["allbooks"] !='') {
    $updated = updateRoom($euConn,$_POST["allbooks"],$_POST["rowId"] );
    echo json_encode(['success'=>$updated]);
    exit;
}

$pageSections = getRooms($euConn);
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <script src="https://code.jquery.com/jquery-1.11.3.js"></script>
    <script type="text/javascript">
        $(document).ready(function(){
            $(".allbooks").change(function(){
                var $this = $(this);
                var allbooks = $this.val();
                var rowId = $this.data('row-id');
                var dataString = "allbooks="+allbooks+'&rowId='+rowId;
                $.ajax({
                    type: "POST",
                    data: dataString,
                    url: "",
                    success: function(result){
                        $("#show").html(result);
                    }
                });

            });
        });
    </script>
</head>
<body>

<div id='reserved' align='center'>
<?php echo $pageSections ?>
<div id="show">
</div>
</body>
</html>
Comments