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();

Set a string or die? That makes no sense. Execute a string or die? Now, that makes sense!! — Strawberry
– Strawberry, Commented Dec 5, 2014 at 13:12
is_numeric() is no proper way to protect you from sql injection. Ask Sony. — Bobby Tables
– Bobby Tables, Commented Dec 5, 2014 at 13:14
my suggestion to you is that just submit the form nomrally, so that form inputs, including your select dropdown will be included in the request, then make you process on PHP — Kevin
– Kevin, Commented Dec 5, 2014 at 13:15
@Strawberry I don't get what you mean? Ghost are you suggest to send to db again same information? — Goro
– Goro, Commented Dec 5, 2014 at 13:19
This is gibberish: $query = "SELECT * FROM reservation WHERE id=$id" or die(mysqli_error($con)); — Strawberry
– Strawberry, Commented Dec 5, 2014 at 13:21

Prashant M Bhavsar · Accepted Answer · 2014-12-05 13:13:33Z

1

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

answered Dec 5, 2014 at 13:13

Prashant M Bhavsar

1,16410 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

score 1 · Accepted Answer · 2014-12-05 15:09:39Z

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));

edited Dec 5, 2014 at 15:09

answered Dec 5, 2014 at 14:27

user2367311

9 Comments

Goro Over a year ago

I didn't try with ajax or javascript because I have no idea about them yet. Where to put this?

user2367311 Over a year ago

Well in header between <script></script> with a function like so saveChange()

Goro Over a year ago

saveChange() on the button or? Because now button change status is pointing id and update the status field with the equal value like id.

user2367311 Over a year ago

Nono, you want the dropdown menu to save onchange right?

Goro Over a year ago

Yes, default value is 0. I want when I change from dropdown to 1 or 2 and hit the Change Status button to update that in database table. Yes, I've fixed this after @Strawberry commented about it.

|

Collectives™ on Stack Overflow

Update row in mysql from dropdown menu

2 Answers 2

Comments

Old

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Old

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related