Update row in mysql from dropdown menu

Question

I have table which is populated from mysql. One of the rows is status which is 0 by default and is have dropdown menu where I can choose 1 or 2. Then there is button update which should update that row in the table. The problem is that didn't update.

this is the table form

if(isset($_GET['rest_id']) && is_numeric($_GET['rest_id'])){
        $rest_id = $_GET['rest_id'];

        $query = mysqli_query($con, "SELECT * FROM reservation
                      WHERE table_res_id = $rest_id 
                      ORDER BY `DateTime` DESC ");

                echo "</p>";

                // display data in table

                echo '<table class="table table-striped table-bordered responsive">';
                echo "<thead>";
                echo '<tr>      
                        <th>Name</th> 
                        <th>Comment</th>
                        <th>Status</th>
                        <th></th>
                        </tr>
                      </thead>';
                echo '<div class="row">';
                echo '<div class="box col-md-12">';
                echo '<div class="box-content">';
                echo "<tbody>";
                // loop through results of database query, displaying them in the table
                while ($res = mysqli_fetch_assoc($query))
                {

                    echo '<tr>';                        
                    echo '<td>' . $query['name'] . '</td>';
                    echo '<td>' . $query['comment'] . '</td>';
                    echo '<td>                      
                    <div class="btn-group">
                    <select>
                        <ul class="dropdown-menu">
                        <li><option> Status:'. $query['status'] .' 
                            <span class="caret"></span>
                        </option></li>
                    <li><option>1</option></li>
                    <li><option>2</option></li>
                        </ul>
                </select>                    
                </div>
                           </td>';
                            echo '<td>
                        <a  class="btn btn-info" href="users/Confirm.php?id=' . $query['id'] . '">
                        <i class="glyphicon glyphicon-edit icon-white"></i>
                        Change status</a></td>';

                    echo "</tr>";

This is confirm.php

session_start();
include '../misc/db.inc.php';
ob_start();
if (isset($_GET['id']) && is_numeric($_GET['id']))
{

$id = $_GET['id'];
$query = "SELECT * FROM reservation WHERE id=$id" or die(mysqli_error($con));
$res = mysqli_query($con, $query);
$row = mysqli_fetch_assoc($res);
if ($stmt = $con->prepare("UPDATE reservation SET status = ? LIMIT 1"))

{
    $stmt->bind_param("i",$res['status');
    $stmt->execute();
    $stmt->close();
}
else
{
    echo "ERROR: could not prepare SQL statement.";
}
$con->close();

Answer 1

According to our previous comments:

Make a button <input type="button" onclick="saveChange('.$id.')"/>

Then set the function 'url' to users/confirm.php

and set the 'data' to the id..

    function saveChange(id){
        $.ajax({
                type: "POST",
                url: "users/confirm.php",
                data: 'id='+id
        });
    }

---

Old

---

I suggest you use something like that:

$.ajax({
        type: "POST",
        url: "save.php",
        data: data,
        success: function(msg){}
        });

with onchange="" event, and at save.php you simply use an update method.

By the way

$query = "SELECT * FROM reservation WHERE id=$id" or die(mysqli_error($con)); $res = mysqli_query($con, $query);

was ment to be like:

$query = "SELECT * FROM reservation WHERE id=$id"; $res = mysqli_query($con, $query) or die(mysqli_error($con));

Answer 2

As per your code your are sending database value again to confirm.php not the user selected value. For this, assign name to like ..and instead of anchor click use form submission.. it will work.

Another solution. You can call onchange event on where javascript function will get call and will update selected value in database via Ajax..Hope this logic will help you